Autonumber Primary Key to Second 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.

Here's a code snippet:

Private Sub cmdSave_Click()
Dim rs As DAO.Recordset

Set rs = dbWrap.OpenRecordset("tblStudentLog", dbOpenTable)

With rs
.AddNew
If txtStudentNo.Text <> &quot;&quot; Then
.Fields(&quot;StudentNo&quot = txtStudentNo.Text
End If

If dtpDateLog.Value <> &quot;&quot; Then
.Fields(&quot;Date&quot = dtpDateLog.Value
End If
If txtStaffNo.Text <> &quot;&quot; Then
.Fields(&quot;StaffNo&quot = Suser
End If
If cboSchool.Text <> &quot;&quot; Then
.Fields(&quot;SchoolNo&quot = cboSchool.ItemData(cboSchool.ListIndex)
End If
If txtGrade.Text <> &quot;&quot; Then
.Fields(&quot;Grade&quot = txtGrade.Text
End If
If txtComment.Text <> &quot;&quot; Then
.Fields(&quot;Comment&quot = txtComment.Text
End If
If txtGAFScore.Text <> &quot;&quot; Then
.Fields(&quot;GAF&quot = txtGAFScore.Text
End If
If txtAmountOfTime.Text <> &quot;&quot; Then
.Fields(&quot;AmtOfTime&quot = txtAmountOfTime.Text
End If
.Update
End With


Call AddJuvJust
Call AddPF
Call AddRF
Call AddRefReasons
Call AddRefSources
Call AddWRT
Call AddInt


MsgBox &quot;Save successful!&quot;, vbInformation

Unload frmSearch
Unload Me
End Sub

Private Sub AddJuvJust()
Dim rsAddLog As DAO.Recordset
Dim I As Integer
Dim sCatID

Set rsAddLog = dbWrap.OpenRecordset(&quot;tblLog&quot;, dbOpenTable)

With rsAddLog
For I = 1 To tvwAddJuvJust.Nodes.Count
sCatID = Right(tvwAddJuvJust.Nodes.Item(I).Key, Len(tvwAddJuvJust.Nodes.Item(I).Key) - 2)
.AddNew
.Fields(&quot;StudentNo&quot = txtStudentNo.Text

'====>Here is where I want to add the StudentLogNo

.Fields(&quot;StaffNo&quot = txtStaffNo.Text
.Fields(&quot;SchoolNo&quot = cboSchool.ItemData(cboSchool.ListIndex)
.Fields(&quot;CatID&quot = sCatID
.Fields(&quot;CatNo&quot = 13
.Fields(&quot;Date&quot = dtpDateLog.Value
.Update
Next I
End With
rsAddLog.Close
Set rsAddLog = Nothing
End Sub

 

[SemperFiDownUnda]

You'll have to save the record then grab the number after saving. This can be done via the code below.

What you are doing is having ADO basically hold the place while grabbing the new data about the current record (ie get the ID field)

Dim vBM as Variant
Dim lNewID as Long
.
.
.
rs.Update
vBM = rs.AbsolutePosition
rs.Requery
rs.absolutePosition = vBM
lNewID = rs.Fields("ID").Value

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[LostInCode]

Hopefully this isn't a multi-user environment!

If so, then using the AbsolutePosition is not a good idea.

Under ADO this can be achieved using a server side cursor:

rs.AddNew
NewId = rs.Fields.Item.(IDField).Value

or

rs2 = myConn.Execute(&quot;SELECT @@Identity&quot
NewId = rs2.Fields.Item.(0).Value

Under DAO, which is what you are using, it is not that easy in a multi-user environment.
You would need to lock the table until you Add the record and retrieve the new ID, or use a second unique key such creating a GUID on the client and adding this to a field and then using this as criteria in order to find the record again, and then it's new ID.

Best would be to not use an AutoNumber field as the PK, but just a GUID (and if desired, a TimeStamp as well) created on the client as the PK.

 

[BradB]

Does anyone have an example of a sequential numbering scheme?

I know I need to change to ADO, but I haven't spent the time to change my programs yet. Someday...