Results 1 to 12 of 12
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Capture the ID of an Appended Record (Access 2003/SP3)

    I'm appending a record in VBA, and wish to capture the unique ID of the record. I'm not sure that running a query will give me the information, since multiple records could be appended on the same date with the same variables.
    Is there a way to find this in VBA?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Capture the ID of an Appended Record (Access 2003/SP3)

    How are you adding the record? Do you use AddNew in an ADO or DAO recordset, or do you run an append query, or do you move to a new record on a form?

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Capture the ID of an Appended Record (Access 2

    When I do this using a DAO Recordset, immediately after adding the new record, I then put in a RS.movelast command then read the ID from that record.

    Theoretically (in a multi user environment) someone else might create a record at the same moment and I could end up with the wrong ID, but the probability is very low in most cases.
    Regards
    John



  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Capture the ID of an Appended Record (Access 2

    I'm running an append query in VBA using an SQL string.
    I'd like to populate the form with the ID of the new record created.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Capture the ID of an Appended Record (Access 2

    Immediately after the append query is run, the new record will have the largest ID in the table.
    So you could use DMax to find it.
    Or open a recordset and go to the last record.
    Regards
    John



  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Capture the ID of an Appended Record (Access 2

    Instead of running an append query, you could open a recordset and use AddNew to create a new record. As soon as you have set a field value, you can retrieve the value of the AutoNumber field.

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Capture the ID of an Appended Record (Access 2

    <P ID="edit" class=small>(Edited by WebGenii on 12-Sep-06 19:22. )</P>Yes that's how it turned out...

    However, I tried to use the addnew function a second time within the same subprocedure to add a record to a related table and had no joy.
    So I went back to the SQL query method. Neverthless it bugs me, since I should (I think) be able to use that method throughout the same sub.

    Any thoughts? Should I provide the code?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Capture the ID of an Appended Record (Access 2

    Yes, provide the database so we can all have a look see

  9. #9
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Capture the ID of an Appended Record (Access 2

    <img src=/S/angel.gif border=0 alt=angel width=15 height=21> Well, instead of the whole DB how about the sub procedure in question?

    Private Sub btn_ActivateTransferOut_Click()
    Dim lngIANumber As Long
    Dim dtOrderDate As Date
    Dim intQuantity As Integer
    Dim strNotes As String
    Dim strSQL As String
    Dim intPCN As Integer
    Dim strOrderType As String
    Dim strToSupplier As String
    Dim curPrice As Currency
    Dim varGST As Variant

    'append record for transfer out
    'set variables
    On Error GoTo ErrorFeedback

    lngIANumber = Me.IANumberPick
    dtOrderDate = Format(DateValue(TransferDate.Value), "mm/dd/yyyy")
    intQuantity = -(Me.From_QtyTransferred.Value)
    strNotes = Me.From_Notes.Value

    Application.SetOption "Confirm Action Queries", False
    strSQL = "INSERT INTO [tblReceived-Released] ( InventoryActionNumber, [Date], Quantity, [Commit], Notes, EnteredBy, [Release])"
    strSQL = strSQL & " SELECT " & lngIANumber & " AS InventoryActionNumber, "
    strSQL = strSQL & "#" & dtOrderDate & "# AS [Date], "
    strSQL = strSQL & intQuantity & " AS Quantity, "
    strSQL = strSQL & "True AS [Commit], "
    strSQL = strSQL & Chr(34) & strNotes & Chr(34) & " AS Notes, '"
    strSQL = strSQL & CurrentUser() & "' AS EnteredBy, "
    strSQL = strSQL & "true AS [Release]"
    DoCmd.RunSQL strSQL
    Application.SetOption "Confirm Action Queries", True
    'append record for transfer in
    'create internal order
    'set variables
    intPCN = Me.To_PCN
    strOrderType = Me.OrderType
    strToSupplier = Me.To_Supplier
    strNotes = Me.To_Notes.Value
    intQuantity = Me.From_QtyTransferred.Value
    curPrice = Me.Price.Value
    varGST = Me.GST.Value

    ' Add Inventory Record and Return Inventory Action Number
    Dim Connxn1 As ADODB.Connection
    Dim RcdSet1 As ADODB.Recordset
    Dim RcdSet2 As ADODB.Recordset 'not used currently

    Set Connxn1 = CurrentProject.Connection
    Set RcdSet1 = New ADODB.Recordset

    RcdSet1.Open "tblInventory", Connxn1, adOpenKeyset, adLockPessimistic, adCmdTable

    With RcdSet1
    .AddNew
    .Fields("OrderDate") = TransferDate.Value
    .Fields("ProtocolControlNumber") = Me.To_PCN.Value
    .Fields("OrderType") = Me.OrderType.Value
    .Fields("EnteredBy") = CurrentUser()
    .Fields("Supplier") = Me.To_Supplier.Value
    .Fields("Notes") = Me.To_Notes.Value
    .Fields("QtyOrdered") = Me.From_QtyTransferred.Value 'to represent as a positive value
    .Fields("ArrivalDate") = TransferDate.Value
    .Fields("Price") = Me.Price.Value
    .Fields("GST") = Me.GST.Value
    .Update
    End With

    'return Inventory Action Number
    lngIANumber = RcdSet1("InventoryActionNumber") 'to be used in the next append record section

    'clean up
    RcdSet1.Close
    Set RcdSet1 = Nothing
    <font color=blue> Comment:
    This is where I thought I could A) Use RcdSet1 again and add a new record to the related Table or [img]/forums/images/smilies/cool.gif[/img] Launch and use RcdSet2 how ever I got no joy. So I'm wondering if I should have closed RcdSet1 differently (or not at all) or declared RcdSet2 differently

    The following is just the SQL statement I'm currently using while I puzzle over the current method with Recordset.</font color=blue>
    'append record for transfer in
    Application.SetOption "Confirm Action Queries", False
    strSQL = "INSERT INTO [tblReceived-Released] ( InventoryActionNumber, [Date], Quantity, [Commit], Notes, EnteredBy, [Release])"
    strSQL = strSQL & " SELECT " & lngIANumber & " AS InventoryActionNumber, "
    strSQL = strSQL & "#" & dtOrderDate & "# AS [Date], "
    strSQL = strSQL & intQuantity & " AS Quantity, "
    strSQL = strSQL & "True AS [Commit], "
    strSQL = strSQL & Chr(34) & strNotes & Chr(34) & " AS Notes, '"
    strSQL = strSQL & CurrentUser() & "' AS EnteredBy, "
    strSQL = strSQL & "False AS [Release]"

    DoCmd.RunSQL strSQL
    Application.SetOption "Confirm Action Queries", True
    'update to numbers
    Me.To_IANumber.Value = lngIANumber
    Me.SwapFromDetail.Requery
    UpdateFromData
    UpdateToData
    UpdateFromNumbers
    UpdateToNumbers

    strMsgTitle = "Database"
    strMessage = "Transfer Complete on Inventory Action Number: " & lngIANumber
    intMsgBtn = 0
    strResponse = MsgBox(strMessage, intMsgBtn, strMsgTitle)


    ErrorFeedback:
    If Err.Number = 94 Then
    strMsgTitle = "Database"
    strMessage = "Please select an Inventory Action Number"
    intMsgBtn = 16
    strResponse = MsgBox(strMessage, intMsgBtn, strMsgTitle)
    Me.IANumberPick.SetFocus
    ElseIf Err.Number > 0 Then
    MsgBox Err.Number & ": " & Err.Description
    End If

    End Sub
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Capture the ID of an Appended Record (Access 2

    If you set the recordset to Nothing, then it no longer exists so you can't use it again. If you want to reuse it, you can just close it for insurance and then use a new Set statement to repopulate it as needed.
    Charlotte

  11. #11
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Capture the ID of an Appended Record (Access 2

    And the bouncing back between the SQL append method and the Recordset add new method. Is there a penalty for doing that - or just the annoyance I feel at its' "untidiness"?

    hmm
    after some more poking around I notice that it is generating a SQL error in FROM clause. Though the SQL portion is working perfectly...
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  12. #12
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Capture the ID of an Appended Record (Access 2

    The infamous adCmdTable vs adCmdTableDirect I think!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •