Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: VBA to multiple append queries and capturing new data

    I am trying to run two append queries at the same time with an on click event.

    I'm working with a basic access one-to-many relationship of Orders and OrderDetails
    I am appending back orders to the Orders table, and line items to the OrderDetails table, after invoices have been queried through to determine whats left. I've built the query to retain records that need to be back ordered and

    The first append runs and I isolate new records by using today's date, but the problem I'm having is that I need to capture the ID#'s it assigns, an autonumber field AOOID. (primary key)
    I need AOOID to be assigned to the second appended table records in the field of the same name, just like they are originally joined when new orders are entered in the tables through main form/subform.

    I am using one query appended to two tables, and was hoping to manipulate the code to enter the value of AOOID into the second table
    I've attached a sample of the data from the query. AOOID and AODID are primary key autonumber fields in the two tables. The yellow fields go to Orders, the pink go to OrderDetails.

    Is there a way in VBA to capture the AOOID that goes into the table after the first execute shown below, and assign it to the value of AOOID in the table after the second execute?
    I will lose the query join now that I am splitting them, so I'm not sure how to recognize what lines go with what unless I use some kind of count. That is a little over my head I'm sorry to say
    Code:
    Private Sub Command28_Click()
    CurrentDb.Execute "qryAMZBOac", dbFailOnError
    CurrentDb.Execute "qryAMZBOad", dbFailOnError
    End Sub
    qryAMZBOab.pdf

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As the value you need to retrieve is from an AutoNumber column, you can use one of these techniques:

    1. If the first query always appends a single row into the table:
    a) Run the first query:
    Code:
    CurrentDb.Execute "qryAMZBOac", dbFailOnError
    b)retrieve the AOOID value of the new row:
    Code:
    LngId = DMax("AOOID", "<TableName>")
    2. If the first query can append several rows into the table:
    a) Retrieve the Max value for the column before running the first query:
    Code:
    LngId = DMax("AOOID", "<TableName>")
    b) Run the first query:
    Code:
    CurrentDb.Execute "qryAMZBOac", dbFailOnError
    c) Retrieve the AOOID values of the new rows
    Code:
    Dim qdf as DAO.QueryDef
    Dim rst As DAO.RecordSet
    Dim var As Variant
    Dim i As Long
    
    Set qdf : CurrentDb.CreateRecordset("", "SELECT AOOID FROM <TableName> WHERE AOOID > " & lngId)
    SET rst = qdf.OpenRecordset
    With rst
        If .EOF = False Then 
            .MoveLast
            .MoveFirst
            var = .GetRows(.RecordCount)
            .Close
        End If
    End With
    If IsArray(var) Then
    
        ' var(0,x) contains the AOOID values of the newly inserted rows.
        '
        For i = 0 To Ubound(var,2)
            Debug.Print i, var(0, i)
        Next i
    Else
        
        ' No rows were inserted.
        '
    End If
    Have a nice day!

  3. #3
    Join Date
    Jun 2010
    Posts
    186
    It will append several rows, but it is order activity dependent. Will option 2 work for just one row as well?
    Also, where would I code the second query to get the newly inserted AOOID's?

    I wrote like this, there is a compile error on createrecordset - method or data member not found:
    Code:
    Private Sub Command28_Click()
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim var As Variant
    Dim i As Long
    
    lngId = DMax("AOOID", "tblAOpenOrder")
    CurrentDb.Execute "qryAMZBOac", dbFailOnError
    
    Set qdf = CurrentDb.CreateRecordset("", "SELECT AOOID FROM tblAOpenOrder WHERE AOOID > " & lngId)
    Set rst = qdf.OpenRecordset
    
        If .EOF = False Then
            .MoveLast
            .MoveFirst
            var = .GetRows(.RecordCount)
            .Close
        End If
    End With
    If IsArray(var) Then
    
        ' var(0,x) contains the AOOID values of the newly inserted rows.
        '
        For i = 0 To UBound(var, 2)
            Debug.Print i, var(0, i)
        Next i
    Else
        
        ' No rows were inserted.
        '
    End If
    
    
    End Sub
    thank you
    Quote Originally Posted by Sinndho View Post
    As the value you need to retrieve is from an AutoNumber column, you can use one of these techniques:

    1. If the first query always appends a single row into the table:
    a) Run the first query:
    Code:
    CurrentDb.Execute "qryAMZBOac", dbFailOnError
    b)retrieve the AOOID value of the new row:
    Code:
    LngId = DMax("AOOID", "<TableName>")
    2. If the first query can append several rows into the table:
    a) Retrieve the Max value for the column before running the first query:
    Code:
    LngId = DMax("AOOID", "<TableName>")
    b) Run the first query:
    Code:
    CurrentDb.Execute "qryAMZBOac", dbFailOnError
    c) Retrieve the AOOID values of the new rows
    Code:
    Dim qdf as DAO.QueryDef
    Dim rst As DAO.RecordSet
    Dim var As Variant
    Dim i As Long
    
    Set qdf : CurrentDb.CreateRecordset("", "SELECT AOOID FROM <TableName> WHERE AOOID > " & lngId)
    SET rst = qdf.OpenRecordset
    With rst
        If .EOF = False Then 
            .MoveLast
            .MoveFirst
            var = .GetRows(.RecordCount)
            .Close
        End If
    End With
    If IsArray(var) Then
    
        ' var(0,x) contains the AOOID values of the newly inserted rows.
        '
        For i = 0 To Ubound(var,2)
            Debug.Print i, var(0, i)
        Next i
    Else
        
        ' No rows were inserted.
        '
    End If

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It should be :
    Code:
    Set qdf = CurrentDb.CreateQueryDef("", "SELECT AOOID FROM tblAOpenOrder WHERE AOOID > " & lngId)
    If you need to use 2 queries, you'll have to create a persistent query (i.e. a query that has a name and appears in the database window), at least for the first query and use it to build the second QueryDef by code.

    The number of row(s) does not matter, except for the execution time of the query.
    Have a nice day!

Posting Permissions

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