"Syncronise" Auto-IDs to match in serveral forms?

[jules]

Hello all,


this is probably a simple question for most of you out there
but this problem has me totally stumped...

Intro: My database consists of five tables and ten different
forms (two forms access the same table but only different
fields). The number of fields exceeded Access' permissable
number of fields, so I resorted to above mentioned divison
of what usually could have fitted into one "root" table without
further nuisances.

Problem: After splitting the db as described, I created two
main with buttons which open the subforms, however the syncronization
between main- and subfroms does not work at all.
My VBA Macro looks like this:

--

Private Sub init_open_Click()

Dim stDocName1 As String
Dim stDocName2 As String
Dim stDocName3 As String
Dim stDocName4 As String
Dim stLinkCriteria As String

stDocName1 = "Subform_1"
stDocName2 = "Subform_2"
stDocName3 = "Subform_3"
stDocName4 = "Subform_4"

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName1, , , stLinkCriteria
DoCmd.OpenForm stDocName2, , , stLinkCriteria
DoCmd.OpenForm stDocName3, , , stLinkCriteria
DoCmd.OpenForm stDocName4, , , stLinkCriteria

End Sub

--

The subforms open as desired but it is not possible to save
the data - for a different matter the AutoIDs don't correspond.


Any suggestions greatly appreciated,


Julia

 

[Steve Schapel]

Jules,

If your tables are in a one-to-one relationship with each other, a
maximum of one of these tables only can have an Autonumber data type
for the ID. An autonumber field doesn't give you much control over
the value of the number entered into it. Hterefore, you have to
assign the ID number in the other tables via a VBA procedure, or via
the LinkChildFields/LinkMasterFields property settings of subforms, or
some other such methodology. At this stage I don't think you have
given enough information to comment further on this.

BUT: Here's the key question... Is the data structure of this
database set in concrete, or could you consider a revision? The fact
that you are trying to work with so many fields almost certainly
99.99% indicates that you have not understood something about the core
principles of database design. Normalising your table design will
solve this, and many other future problems for you. If you are
interested to explore this aspect, please post back with some examples
of the tables and fields you are using at present, and someone will be
able to offer some good advice.

- Steve Schapel, Microsoft Access MVP