Access 2002: Primary key Autonumber to Another Table

[BradB]

I have a Visual Basic Form that has a multi-tab format. The first tab is where all the student information is entered. The student information is kept in a table called tblStudentLog. The primary key is an Autonumber called [StudentLogNo]. The five remaining tabs have information stored in a separate table called tblLog. TblLog has an Autonumber too as a primary Key called LogNo.

When I click the Save button on the VB form, I want to save the StudentLogNo, an Autonumber and Primary Key, to BOTH the tblStudentLog and tblLog tables.

I might post this also in the VB forum in case it pertains to that forum more than this one.

 

[BNPMike]

Just a comment here - it's dangerous to use autonumber for this sort of thing. It's not clear from what you've said but it looks as though you want to have the autonumber as a key in both tables.

If you create a new StudentLog record and get a new StudentLogNo autonumberically, you cannot guarantee that you will get a matching number in TblLog. I suggest you create your own sequential numbering scheme.

 

[BradB]

BNPMike, do you know where I can find an example of a sequential numbering scheme? I've read the FAQ by MichalRed, but I don't think that will work with my application.

 

[BNPMike]

I suspect you haven't actually got a problem.

Let's say StudentLogNo is autonumber. If Logno is one-to-one with StudentLogNo then just copy StudentLogNo onto the keyfield of TblLog. That'll work as longs as LogNo hasn't be declared as autonumber.

On the other hand if the relationship is one-to-many then you'll just put the StudentLogNo into a foreign key field, in which case you don't need to worry about what key value is generated for TblLog.