Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-25-2004, 07:44 AM   #1
pbuethe
Returning User
 
pbuethe's Avatar
 
Join Date: Apr 2002
Location: Merrick, NY, USA
Posts: 187
Thanks: 2
Thanked 0 Times in 0 Posts
pbuethe
Data not saving and duplicate values message

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 by pbuethe; 02-25-2004 at 12:46 PM.
pbuethe is offline   Reply With Quote
Old 02-25-2004, 01:04 PM   #2
theprez
Newly Registered User
 
Join Date: Nov 2001
Location: Outside Washington DC
Posts: 140
Thanks: 0
Thanked 0 Times in 0 Posts
theprez
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 by theprez; 02-26-2004 at 11:35 AM.
theprez is offline   Reply With Quote
Old 02-25-2004, 01:12 PM   #3
pbuethe
Returning User
 
pbuethe's Avatar
 
Join Date: Apr 2002
Location: Merrick, NY, USA
Posts: 187
Thanks: 2
Thanked 0 Times in 0 Posts
pbuethe
Quote:
Originally Posted by theprez
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.

pbuethe is offline   Reply With Quote
Old 02-25-2004, 01:16 PM   #4
theprez
Newly Registered User
 
Join Date: Nov 2001
Location: Outside Washington DC
Posts: 140
Thanks: 0
Thanked 0 Times in 0 Posts
theprez
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.
theprez is offline   Reply With Quote
Old 02-25-2004, 01:55 PM   #5
pbuethe
Returning User
 
pbuethe's Avatar
 
Join Date: Apr 2002
Location: Merrick, NY, USA
Posts: 187
Thanks: 2
Thanked 0 Times in 0 Posts
pbuethe
Quote:
Originally Posted by theprez
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 by pbuethe; 02-26-2004 at 06:28 AM.
pbuethe is offline   Reply With Quote
Old 02-26-2004, 11:33 AM   #6
theprez
Newly Registered User
 
Join Date: Nov 2001
Location: Outside Washington DC
Posts: 140
Thanks: 0
Thanked 0 Times in 0 Posts
theprez
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.
theprez is offline   Reply With Quote
Old 02-26-2004, 01:06 PM   #7
pbuethe
Returning User
 
pbuethe's Avatar
 
Join Date: Apr 2002
Location: Merrick, NY, USA
Posts: 187
Thanks: 2
Thanked 0 Times in 0 Posts
pbuethe
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.

pbuethe is offline   Reply With Quote
Old 02-27-2004, 11:07 AM   #8
theprez
Newly Registered User
 
Join Date: Nov 2001
Location: Outside Washington DC
Posts: 140
Thanks: 0
Thanked 0 Times in 0 Posts
theprez
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.
theprez is offline   Reply With Quote
Old 02-27-2004, 12:24 PM   #9
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,808
Thanks: 13
Thanked 1,510 Times in 1,436 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 03:04 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World