Capture primary key value

ms-access, vba

Solution

The easiest way I can think of for you to get the value of RunID is declaring a variable and setting that variable equal to the field before updating the record. Something like this:

   Dim rs1 As DAO.Recordset
   Set rs1 = CurrentDb.OpenRecordset("QUOTE-Run", dbOpenDynaset)
   Dim TempRunID As Long

   rs1.AddNew
   rs1.Fields("[QuoteNumber]").value = [txtQuoteNumber].value
   rs1.Fields("[LeadTime]").value = [txtLeadTime].value
   rs1.Fields("[Qty]").value = [txtQty].value
   rs1.Fields("[Title]").value = [txtTitle].value
   rs1.Fields("[Date]").value = VBA.DateTime.Date
   rs1.Fields("[Time]").value = VBA.DateTime.Time
   rs1.Fields("[InitiatedBy]").value = Application.CurrentUser
   rs1.Fields("[IncompleteProblemNotes]").value = [txtIncompleteProblemNotes].value
   rs1.Fields("[CustomerNotes]").value = [txtCustomerNotes].value
   rs1.Fields("[Memo]").value = [txtMemo].value
   rs1.Fields("[Memo1]").value = [txtMemo1].value
   rs1.Fields("[PrefferedQuoteRunSelect]").value = [txtPrefferedQuoteRunSelect].value
   rs1.Fields("[CombinedRun]").value = [chkCombinedRun].value

   TempRunID = rs1.Fields("[RunID]").value
   rs1.Update

Problem

I'm adding a record to a table using the following code: ``` Dim rs1 As DAO.Recordset Set rs1 = CurrentDb.OpenRecordset("QUOTE-Run", dbOpenDynaset) rs1.AddNew rs1.Fields("[QuoteNumber]").value = [txtQuoteNumber].value rs1.Fields("[LeadTime]").value = [txtLeadTime].value rs1.Fields("[Qty]").value = [txtQty].value rs1.Fields("[Title]").value = [txtTitle].value rs1.Fields("[Date]").value = VBA.DateTime.Date rs1.Fields("[Time]").value = VBA.DateTime.Time rs1.Fields("[InitiatedBy]").value = Application.CurrentUser rs1.Fields("[IncompleteProblemNotes]").value = [txtIncompleteProblemNotes].value rs1.Fields("[CustomerNotes]").value = [txtCustomerNotes].value rs1.Fields("[Memo]").value = [txtMemo].value rs1.Fields("[Memo1]").value = [txtMemo1].value rs1.Fields("[PrefferedQuoteRunSelect]").value = [txtPrefferedQuoteRunSelect].value rs1.Fields("[CombinedRun]").value = [chkCombinedRun].value rs1.Update ``` When creating the record there is an AutoNumber primary key field called RunID that gets a value. I need to know what that value is so I can use it later in the code. What is the easiest way of capturing that value?

Original source