Autonumber synchronization between subform & main form

[DGregg]

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.

 

[Jeff Boyce]

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

 

[DGregg]

Thank you for your response & clarification. I think I may understand what
you suggest and will give it a try.

 

[DGregg]

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?

 

[Jeff Boyce]

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
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.

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

 

[DGregg]

Thank you once again. I'll give it another try.

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
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.

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
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.

 

[DGregg]

Got it working properly. Thank you for your help, Jeff.

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
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.

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
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.