Problem with Autonumbers affecting more than just new record

[MariahJ]

Hello,

I'm having problems with autonumbers in new table records affecting
other records.

The central table in the DB is the "Scenario table", which has a field
called "ScenarioID" as it's primary key, which is also an autonumber.
Each record in the Scenario table has a "Machine Group ID", which
corresponds to a record in the "Machine table", where "Machine Group
ID" is the primary key and an autonumber as well (it is just a regular
number in the "Scenario table"). The "Machine table" is linked to the
"Scenario table" by "Machine Group ID" using a one to many relationship
with referential integrity enforced (Cascade Update Related Fields and
Cascade Delete Related Records are not checked).

When I add a new record to the "Scenario table", the new record is fine
but it changes the "Machine Group ID" of the first record in the table
to the same number as the new record. It should only be affecting the
final record. How can I make sure that the older records are not
affected when new records are added?

Any help would be greatly appreciated.

 

[Roger Carlson]

Are you working directly in the tables or in a form/subform?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[MariahJ]

I am adding to/deleting from the tables by clicking a button on a form.
The click event simply uses an ".AddNew" to the records in the scenario
and machine tables. I open both recordsets for the scenario and machine
tables,

The code is something like this:

Set scenRS = CurrentDb.OpenRecordset("scenarioTbl", dbOpenDynaset)
scenRS.AddNew
scenID = scenRS("ScenarioID") 'get ScenarioID of new Scenario

Set machRS = CurrentDb.OpenRecordset("machineGroupTbl", dbOpenDynaset)
machRS.AddNew
MGID = machRS("machineGroupId") 'get machineGroupID of new
machine group
machRS.Update
machRS.Close

scenRS("machineGroupId") = MGID 'use new machineGroupID in new
scenario record
scenRS.Update
scenRS.Close

 

[Roger Carlson]

Access has a subform control that can embed another form in a main form.
There are Link Parent Link Child properties in the subform control that will
automatically add the primary key value of a new record in the mainform into
the the foreign key of the subform. You don't need to program any
recordsets at all. I would strongly recommend you do this.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "FormSubform.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L