Finding autonumber to add new records.

[Howlsta]

I'm making a form where a student can pick from a choice of modules. A student enters their card number and surname and they are then allowed to choose options from a cbo. The problem is I want to add their choice of options by adding the moduleIDF and StudentIDF to another table. The StudentIDF is autonumber, and the card number is unique and in the same table. I think i need code that gathers the StudentIDF from the library card number entered so the addnew method will work properly. Does anyone understand, if so do you know how to this pls?


Private Sub cmdSelect_Click()

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "Select * from qryPickOption"
.AddNew
!StudentIDF = Me.StudentIDF
!ModuleIDF = Me.cboOptionalMod
.Update
End With

Me.lboOptions.Requery

End Sub

 

[chrismcbride]

When I want to add a group of records to that go to both sides of a one-to-many relationship, I use straight SQL in the code of the form (usually on update or a button click or something).
1. Insert the master record into the one side table.
2. Use SELECT Max(autonumber_field_name) AS NewNum FROM one-side_table_name;
3. Assign the return of the SQL statment to a Recordset.
4. Use the Recordset value in any susbsequent Insert statements that are inserting to many-side table...
HTH

Chris