Retreiving a value from one query to use in another query

[eluhnabroad]

So here's my issue: I'm trying to run an SQL query to add a record to a table. One of the fields, however, is the autonum key from the record for another table that's just been generated with another query, which is being run on a recordset from yet another query on another table. (I'm trying to work with a system designed by someone else -- don't get me started.)



In the code below, the commented line is the query I'm trying to run. ItemID needs to be the ID for the record in tblInventory just created by the query above it. I was hoping that since that query was just executed, I could just reference the tblInventory.ID field, but that didn't work.

My first-born to whoever can tell me a way to just get the ID from the tblInventory record just created, without trying to set up nested recordsets and my brain and the database exploding.

    Set db = CurrentDb()
    
    intPONUM = Me.PONUM
  
    strSQLItems = "select * from tblPOItems where ponum = " & intPONUM
    
    Set rsItem = db.OpenRecordset(strSQLItems)
    recCount = rsItem.RecordCount
    
    If rsItem.RecordCount > 0 Then
        Do While Not rsItem.EOF
            If Not IsNull(rsItem!ITEMNUM) Then
                intItem = rsItem!ITEMNUM
                intNewQty = rsItem!ReQty
           
                DoCmd.RunSQL "UPDATE tblInventory SET tblInventory.QTY = tblInventory.QTY + " & intNewQty & " WHERE tblInventory.item = '" & intItem & "'"

'                DoCmd.RunSQL "INSERT INTO tblInventoryByLocation ( ItemID, LocQTY, BinLocID ) SELECT " & tblInventory.ID & " AS Expr1, " & intNewQty & " AS Expr2, " & "71 AS Expr3"
'
                rsItem.MoveNext
            Else
                rsItem.MoveNext
            End If
        Loop

[John_G]

ItemID needs to be the ID for the record in tblInventory just created by the query above it.

The query above it doesn't create a record - it updates an existing one identified by tblInventory.item = '" & intItem & "'"

Is that the same record that contains the ItemID you want?

The INSERT INTO statement is probably wrong - it desnt have a where clause to identify which records to select.

[ssanfu]

The INSERT INTO statement is probably wrong - it desnt have a where clause to identify which records to select.

I see that the insert statement is commented out, but I would say it is definitely wrong - there is no FROM clause in the SELECT statement.


What are the fields in table " tblPOItems" (recordset rsItem)?

[eluhnabroad]

The query above it doesn't create a record - it updates an existing one identified by tblInventory.item = '" & intItem & "'"

Is that the same record that contains the ItemID you want?

It's a very complicated setup. The tblInventory record is identified by an autonum field called ID. "Item" is the number tied to yet another table that is a parts list. The practical upshot of the UPDATE query is that it creates a new record in tblInventory, the key of which is the autonum just generated in the ID field. My INSERT INTO query (the one I've commented out in the example) is trying to create a new record in tblInventoryByLocation, which also has an autonum key called ID, but I need to fill the ItemID field with the ID from the tblInventory record created by the UPDATE query.

(I want to emphasize again, I did NOT write this... system. I was just hired to deal with it.)

And yes, the INSERT INTO statement doesn't have a WHERE clause; that's just the problem. I don't know how to get the ID value of the tblInventory record created by the UPDATE query.

Please, let's not get into the tblPOItems mess. I just need to get tblInventory.ID into tblInventoryByLocation.ItemID.

Let me also add: This database doesn't have any table relationships. Everything is being done through queries and VBA.

I did NOT write this.

[June7]

UPDATE does not create a record, it edits a record.

[eluhnabroad]

Okay, then.

[June7]

This has been marked solved - is it?