Data not saving and duplicate values message (1 Viewer)

pbuethe

Returning User
Local time
Today, 13:07
Joined
Apr 9, 2002
Messages
210
I have a form which is based on a query which contains 5 tables in a one-to-one relationship (tblHeader, tblPartA, tblTrimester1, tblTrimester2, tblTrimester3). In the query there is a relationship from CaseNbr of tblHeader to CaseNbr in each of the other tables. The form contains a tab control and there is a page for entering data for each table (other than tblHeader, which has a separate form). tblDrugs is a "many" side table, and another page of the tab control contains a subform based on tblDrugs. This was all working fine for a few days. Now the following happens:

The user enters data into the first 4 pages of the form. (Each page has code to take the user directly from the last field on one page to the first field on the next page.) Page 5 (containing the subform for tblDrugs) comes up automatically, but without setting the focus on the first entry field (a combobox). When the user clicks on the arrow of the combobox, she gets the message about "the record could not be added to the table because it would create duplicate values in the index, primary key, or relationship". Not only does this prevent entries into this subform, but also the tblTrimester2 and tblTrimester3 data (from pages 3 and 4 of the form) did not get saved.

I can't think of what I may have changed that would have caused this. I did notice that tblPartA and tblTrimester1 have a record with CaseNbr 0. Should I delete it? How could this have been created? Could it be related to these problems?

Another thing, it looks like it works for existing records that did not have this problem originally. I guess these were records that were entered before this started happening. But if you go back into the records that got this error it still occurs.

I appreciate any help. This needs to be fixed ASAP.
 
Last edited:

theprez

Registered User.
Local time
Today, 17:07
Joined
Nov 8, 2001
Messages
140
The casenum 0 may have been created if someone cancelled before entering all the data. You may try deleting it, but not before you backed up your data. Also, you probably did this, but you want to enforce data integrity on your relationships with tableheader.
 
Last edited:

pbuethe

Returning User
Local time
Today, 13:07
Joined
Apr 9, 2002
Messages
210
theprez said:
The casenum 0 may been created if someone cancelled before entering all the data. You may try deleting it, but not before you backed up your data. Also, you probably did this, but you want to enforce data integrity on your relationships with tableheader.

Thanks for your response, prez. I will try the data integrity thing.

I also want to clarify that it is not happening for every record. The user says it is happening for every other record or every third record that is entered.
 

theprez

Registered User.
Local time
Today, 17:07
Joined
Nov 8, 2001
Messages
140
How are new records created when the user has multiple records to enter?

If they are able to go to the second tab, without going to the table header, this may be part of the problem. You may have to set up a new button that clears all the fields and forces the user to the first tab.
 

pbuethe

Returning User
Local time
Today, 13:07
Joined
Apr 9, 2002
Messages
210
theprez said:
How are new records created when the user has multiple records to enter?

If they are able to go to the second tab, without going to the table header, this may be part of the problem. You may have to set up a new button that clears all the fields and forces the user to the first tab.

The header table has been pre-populated with partial data for all records. The user clicks a button for data entry and gets a form based on the header table. There is a combobox from which the user selects the case (record) desired. She can then complete the entries in the header. After the header entry, she clicks a button to go to the tabbed form. It starts on the first entry field of the first tab.

I tried putting in data integrity (previously I did not have anything in the relationship window, all my relationships were in the queries), but now I am getting the message "you cannot add or change a record because a related record is required in table 'tblHeader'".
 
Last edited:

theprez

Registered User.
Local time
Today, 17:07
Joined
Nov 8, 2001
Messages
140
Dude, that is the key to your problem. You have what we call "orphan records" they are on the "Many side" of the join, but not on the one side (tblHeader). The records on the many side need to be deleted OR you need to add a corresponding record in tblheader. To find the orphan records, create a new query using the "Find Unmatched Query Wizard". Once you correct this, you can perform the referential integrity and the forms will no longer allow orphan records on the many side.
 

pbuethe

Returning User
Local time
Today, 13:07
Joined
Apr 9, 2002
Messages
210
Theprez,
It did not look like I had any orphan records when I looked at the table. I could not run the find unmatched query wizard because it was not installed on my machine. I deleted all the relationships since they seemed to be causing more problems. Then there were problems with closing the database including "module not found" and "sharing violation" messages. After making sure everyone was out of the db, and replacing the front end with an earlier copy, everything seemed to work fine. There are no error messages and all the data appears to be saving. This seems to be one of those things that fixed itself. Thanks for your suggestions.
 

theprez

Registered User.
Local time
Today, 17:07
Joined
Nov 8, 2001
Messages
140
OK, I'm glad it worked, but be forwarned. The whole basis for MS Access is the relationships between tables. Without that, you could run into problems later with those tables that should be related to tblheader. I can tell you how to run an unmatched query without the wizard. I will monitor this thread from time to time to see if you need additional assistance.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:07
Joined
Feb 19, 2002
Messages
42,971
Removing referential integrity is most certainly NOT the solution to your problem. You seem to have stuck your head in the sand and said if it doesn't complain, I'm not going to worry about it. The whole idea behind RI is to prevent bad data from being saved. Your application is clearly causing some data integrity problem or removing RI would not have appeared to solve the problem.
 

Users who are viewing this thread

Top Bottom