Can't add new record to a form

  • Thread starter Thread starter Aurora
  • Start date Start date
A

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
 
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:
http://www.datastrat.com
http://www.mvps.org/access
 
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.
Click to expand...

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????????
Click to expand...

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).
 
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.
Click to expand...

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.
Click to expand...

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.
 
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-----
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.
Click to expand...

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.
Click to expand...

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.


.
Click to expand...
Click to expand...
 
Back
Top