 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / December 2007Autonumber synchronization between subform & main form
I have a main form (EVENT) whose PK is an autonumber (EVENTID) and a second field (EVENTNUMBER) which may or may not be unique, hence my use of the autonumber. I then have 10 suforms (each with FK as an autonumber EMP1...EMP10 and EVENTNUMBER) that need to be linked based on the main form autonumber (EVENTID). This is where I'm running into problems. I need the subform autonumbers to stay in synch with the main form autonumber, however, not all 10 subforms will be populated for each single record entered (at minimum only 1 subform will be populated). My form is for data entry purposes only and I have a true one-to-one relationship (as long as I understand this correctly). I have the Link Master Records for each subform set to the master form primary keys EVENTID;EVENTNUMBER with the Link Child Records as the subform foreign keys EMP1;EVENTNUMBER (subform 1), EMP2;EVENTNUMBER (subform2), etc. I am finding that the subform autonumber gets out of synch with the master form autonumber when I have entered a record, for example, where there is an entry in subform1 but not for subform2; then my second record entry will contain entries for both subform1 & subform2. At that point, subform2 with display an autonumber of 1 when I really need it set to 2 (based on the fact that it is the second main record entered which is what the main form autonumber reads). What am I doing wrong? I've been working on this for over a week with no success. Please help. You may have a mistaken idea about what Access Autonumbers represent. An Autonumber is a (generally) unique row identifier. It is (generally) unfit for human consumption, is not guaranteed to be sequential (almost certainly will NOT be), and IS NOT in ANY WAY related to autonumbers in ANY OTHER TABLE! You cannot "synchronize" autonumbers. Instead, if your situation has a one-to-many relationship between two tables, the "child" table needs to have a "foreign key" field that will hold the value of the parent record's ID (?an autonumber in the parent table, hence, a LongInt in the child table). You are certainly welcome to have a primary key in the child table (a good idea!), but it will NOT be synchronized with any other table if you are using autonumbers. Regards Jeff Boyce Microsoft Office/Access MVP >I have a main form (EVENT) whose PK is an autonumber (EVENTID) and a second > field (EVENTNUMBER) which may or may not be unique, hence my use of the [quoted text clipped - 25 lines] > autonumber reads). What am I doing wrong? I've been working on this for > over a week with no success. Please help. Thank you for your response & clarification. I think I may understand what you suggest and will give it a try. > You may have a mistaken idea about what Access Autonumbers represent. > [quoted text clipped - 46 lines] > > autonumber reads). What am I doing wrong? I've been working on this for > > over a week with no success. Please help. Please forgive my ignorance as I am fairly new to working with Access. I'm not sure how to go about doing what you suggest. I kept the autonumber field in the subforms/tables but added a new field (INCIDENTID) in each of the tables that are used as subforms. I set the Control Source of the new INCIDENTID to be the autonumber that is populated on the main form for INCIDENTID (=[INCIDENT]![IncidentID] ). Perhaps I should not have changed the control source? The subforms are set to Link Child Fields: INCIDENTID (not an autonumber);CASENUMBER and Link Master Fields: INCIDENTID;INCIDENTCASENUMBER. I now receive two separate error messages. When I first try to make an entry in the first subform which is a tab control, I receive the message "Can't Assign a value to this object", however, the form appears to still accept the my entry. Then upon clicking on the next tab to access subform2 I receive the message "Cannot add or change a record because a related record is required in "INCIDENT". What am I doing wrong? Could it be how I have the relationships set up? > You may have a mistaken idea about what Access Autonumbers represent. > [quoted text clipped - 46 lines] > > autonumber reads). What am I doing wrong? I've been working on this for > > over a week with no success. Please help. Here's an example of one-to-many tables: tblOrder OrderID (a primary key, may well be an Autonumber) OrderDate CustomerID (a foreign key, pointing back to the customer record/table) tblItemOrdered ItemOrderedID (a primary key, may well be an Autonumber, but NOT related to the Autonumber in tblOrder) OrderID (a foreign key, pointing back to the "parent" Order in tblOrder -- a LongInt if you used an Autonumber in tblOrder) ProductID (a foreign key, pointing back to the Product table/row) ... Regards Jeff Boyce Microsoft Office/Access MVP > Please forgive my ignorance as I am fairly new to working with Access. > I'm [quoted text clipped - 78 lines] >> > for >> > over a week with no success. Please help. Thank you once again. I'll give it another try. > Here's an example of one-to-many tables: > [quoted text clipped - 98 lines] > >> > for > >> > over a week with no success. Please help. Got it working properly. Thank you for your help, Jeff. > Here's an example of one-to-many tables: > [quoted text clipped - 98 lines] > >> > for > >> > over a week with no success. Please help. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.