Can't add new record to a form

[Aurora]

I am using Access 2000.

I created twp databases that are linked by one field. The
first db is called "MDO" - the 2nd is "ECR", they are
linked by one field [MDO], which is also the primary key.
The primary key in the ECR DB is the [ECR] field. Both
fields are considered "text" fields.

I created a form to enter in the infomation for the MDO
Db. I created a 2nd form to take part of the information
from the MDO db and add additional inform that goes into
the ECR Db.
As long as I enter information in the ECR form by calling
up the MDO information first - everything works well. But
I noticed that I could not add a new ECR form without the
MDO information.

So I created a 3rd form strictly from the ECR Table to add
information into the ECR Db that did not require the MDO
information. But I could not add new records. I keep
getting a message that I am adding duplicate values in the
index, primary key or relationships. But I have no
relationships in this new form. In fact, I deleted all
the relationships from the Db > Tools > Relationships.
This form is based on the the ECR TAble Query and the
primary field is an automatic number field. I do not
enter that piece of information.

When I looked at my ECR table in database view, I found a
column at the beginning of the database that included
small "plus signs" in front of all of the records. I
don't know where this came from and I can't see to delete
the column. But I have been playing with this problems
for two days now and who knows what I have done.

Does anyone know what the "plus signs" mean. Can anyone
offer any suggestions????????

Aurora

[Arvin Meyer]

Click on the plus and you'll see the related data if it exists. In the
table's Design View, Choose View ... Properties and turn off [Auto] in the
SubdatasheetName (change it to [None]) and delete the link fields.

You still may have the problem if your indexes do not allow duplicates. The
tables either need to be linked, or not. The Primary Key in only 1 table can
be an autonumber, if there is a relationship of one to one. If there is a
relationship of one to many, the Primary Key in the first table must be a
foreign key in the second table.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
[url]http://www.datastrat.com[/url]
[url]http://www.mvps.org/access[/url]

"Aurora" <adickson@AOL.com> wrote in message
news:9eab01c34664$8dd89080$a401280a@phx.gbl...

> I am using Access 2000.
>
> I created twp databases that are linked by one field. The
> first db is called "MDO" - the 2nd is "ECR", they are
> linked by one field [MDO], which is also the primary key.
> The primary key in the ECR DB is the [ECR] field. Both
> fields are considered "text" fields.
>
> I created a form to enter in the infomation for the MDO
> Db. I created a 2nd form to take part of the information
> from the MDO db and add additional inform that goes into
> the ECR Db.
> As long as I enter information in the ECR form by calling
> up the MDO information first - everything works well. But
> I noticed that I could not add a new ECR form without the
> MDO information.
>
> So I created a 3rd form strictly from the ECR Table to add
> information into the ECR Db that did not require the MDO
> information. But I could not add new records. I keep
> getting a message that I am adding duplicate values in the
> index, primary key or relationships. But I have no
> relationships in this new form. In fact, I deleted all
> the relationships from the Db > Tools > Relationships.
> This form is based on the the ECR TAble Query and the
> primary field is an automatic number field. I do not
> enter that piece of information.
>
> When I looked at my ECR table in database view, I found a
> column at the beginning of the database that included
> small "plus signs" in front of all of the records. I
> don't know where this came from and I can't see to delete
> the column. But I have been playing with this problems
> for two days now and who knows what I have done.
>
> Does anyone know what the "plus signs" mean. Can anyone
> offer any suggestions????????
>
> Aurora

[John Vinson]

On Wed, 9 Jul 2003 14:53:38 -0700, "Aurora" <adickson@AOL.com> wrote:

>As long as I enter information in the ECR form by calling
>up the MDO information first - everything works well. But
>I noticed that I could not add a new ECR form without the
>MDO information.

well... exactly. That's precisely what relational integrity is
designed to prevent: orphan records.

Why not make the ECR form a Subform of the MDO form, using the linking
field as the master/child link field? This takes care of it
automatically!

>Does anyone know what the "plus signs" mean. Can anyone
>offer any suggestions????????

The "plus signs" indicate a Subdatasheet: if you click on the plus on
a record for which there exist linked records, you'll see the ECR
records corresponding to the selected MDO (which you would also see on
the Form/Subform suggested above). I suspect you deleted the *table
icons* in the Relationships window; doing so does not remove the
relationship (which is probably a good thing - I suspect that an ECR
record without any corresponding MDO record would be an error, though
not knowing your business that's only a hunch).

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
[url]http://go.compuserve.com/msdevapps?loc=us&access=public[/url]

[John Vinson]

On Wed, 9 Jul 2003 14:53:38 -0700, "Aurora" <adickson@AOL.com> wrote:

>I created twp databases that are linked by one field. The
>first db is called "MDO" - the 2nd is "ECR", they are
>linked by one field [MDO], which is also the primary key.
>The primary key in the ECR DB is the [ECR] field. Both
>fields are considered "text" fields.

Two "Tables" please. It's just jargon, but you can confuse folks! A
Database in Access is the .mdb file, a container for multiple Forms,
Reports, Tables, Queries and so on.

>I created a form to enter in the infomation for the MDO
>Db. I created a 2nd form to take part of the information
>from the MDO db and add additional inform that goes into
>the ECR Db.

It sounds like you're storing the MDO information redundantly in the
ECR table. Don't! A relational database uses the "Grandmother's
Pantry" principle: "a place - ONE place! - for everything, everything
in its place". Store the link field ONLY, and if you need to see MDO
information, use a Query or a Form/Subform or a Combo Box to display
it.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
[url]http://go.compuserve.com/msdevapps?loc=us&access=public[/url]

[Aurora]

Thank you for your reply. I thought the x stood for a
subform - Somehow I must have created that during one of
my playing session. Since I am leaving for vacation
today, I will not be able to try your suggestions until
next month. Thank you again for replying.

aurora

>-----Original Message-----
>On Wed, 9 Jul 2003 14:53:38 -0700, "Aurora"

<adickson@AOL.com> wrote:

>

>>I created twp databases that are linked by one field.

The

>>first db is called "MDO" - the 2nd is "ECR", they are
>>linked by one field [MDO], which is also the primary

key.

>>The primary key in the ECR DB is the [ECR] field. Both
>>fields are considered "text" fields.

>
>Two "Tables" please. It's just jargon, but you can

confuse folks! A

>Database in Access is the .mdb file, a container for

multiple Forms,

>Reports, Tables, Queries and so on.
>

>>I created a form to enter in the infomation for the MDO
>>Db. I created a 2nd form to take part of the

information

>>from the MDO db and add additional inform that goes into
>>the ECR Db.

>
>It sounds like you're storing the MDO information

redundantly in the

>ECR table. Don't! A relational database uses

the "Grandmother's

>Pantry" principle: "a place - ONE place! - for

everything, everything

>in its place". Store the link field ONLY, and if you need

to see MDO

>information, use a Query or a Form/Subform or a Combo Box

to display

>it.
>
> John W. Vinson[MVP]
> Come for live chats every Tuesday and Thursday
>[url]http://go.compuserve.com/msdevapps?loc=us&access=public[/url]
>.
>