Recordset ID - multiple recordsets (1 Viewer)

[cocowomble]

Hi All,

not sure if this is even possible, by any help would be appreciated.

I have a form which opens 2 recordsets.

the first
RS1 adds a new record


the second - RS2 needs to add the ID of the new RS1 record,

hope that makes sense eg.

rs1.AddNew
rs1!AgeGroup = Me.cmbAgeGroup
rs1!CompetitionType = Me.cmbCompetitionType
rs1.Update
rs2.AddNew
rs2!PlayerID = Me.CmbPlayer1
rs2!matchID = ***** RS1 ID of record just added*******


thanks in advance

 

[pbaldy]

Here's an example:

        With rsMaster
          .AddNew
          'field values added
          .Update
          .Bookmark = .LastModified
          lngARKey = !KeyField
        End With

 

[cocowomble]

sorry to sound stupid where would up put that in the current code I have written?

 

[pbaldy]

That would all relate to rs1. You'd use the variable with rs2.

 

[cocowomble]

really sorry I'm still quite new to access - learning as I go,
how would I put that into this??


Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset2


Set db = CurrentDb
Set rs1 = db.OpenRecordset("tblFixtures")
Set rs2 = db.OpenRecordset("tblFixturePlayerData")


rs1.AddNew
rs1!AgeGroup = Me.cmbAgeGroup
rs1!CompetitionType = Me.cmbCompetitionType
rs1.Update
rs2.AddNew
rs2!PlayerID = Me.CmbPlayer1
rs2!matchID = ***** RS1 ID of record just added*******
rs2!GoalsScored = Me.txtGoalP1
rs2.Update
rs1.Close
rs2.Close

 

[pbaldy]

These are the key lines to add, along with declaring a variable:

rs1.Bookmark = rs1.LastModified
YourVariableName = rs1!KeyField

 

[cocowomble]

would the variable be string?

 

[pbaldy]

I'd use the data type of the ID field. The value you're putting in it.