Update query, How to change values on a table depending on values from a query.

[ManuelLavesa]

Hello all, Now I have a different problem. I am keeping track of the StockInhand using a Union query which I called QryStockMovements. This query keeps track of the incoming and outgoings of all the products and starts with the given values that are stored on the Table Stocktakes. Now I expect this query to get very slow when there are thousands of parts to keep track of. So I was trying to make an Update query to change the values of the Table Stocktakes so that the starting values for the Union query are renewed from time to time and the additions and substractions are diminished. For this I made a Query QryStockInHand which gives the StockInHand for each product. Then I thought of using an Update Query to change the values of the Stocktakes table with the values from the QryStockInHand. The SQL code I wrote for this Update query is as follows:

UPDATE qryStockInhand INNER JOIN StockTakes ON qryStockInhand.fkProductID = StockTakes.fkProductID SET StockTakes.Quantity = [StockInHand], StockTakes.StockTakeDate = Date();

The problem I am having is that I can not update the values of the Table with the values from the Query. It gives the following error "operation must use un updatable query". Do you have any ideas of how to do this with or without the Update query. The important part is changing the values on the Table Stocktakes to the new values from the Query StockInHand. Thank you for your help.

[June7]

Only possible option I can think of is opening and manipulating recordsets in VBA with looping code structure.

[ManuelLavesa]

Thank you June 7. I was afraid of that. The problem is I am not very good with recordsets. I know how to change a field at a time or things like that. Easy when you know the values. The difficult part is to assign the values from the query to the values on the table. For example I could write:

Dim MyDatabase as DAO database
Dim rstStocktakes as DAO recordset
Set MyDatabase = CurrentDb
Set rstStocktakes =MyDatabase.Openrecordset ("Stocktakes")
rstStockTakes.MoveFirst
Do Until rstStockTakes.EOF
Quantity = StockTakes Quantity... How do I get the values from the query??
rstStockTakes.MoveNext
Loop

Could you please give me some pointers to how to get the values from the query into the recordset using the ProductID. Thank you for your help.

[June7]

Something like:

CurrentDb.Execute "UPDATE table SET field = '" & rstStockTakes!field & "'"

If the destination field is not a text type, don't use the apostrophe delimiters. If it is a date/time type, use # delimiter.

[ManuelLavesa]

Thank you for the code idea. This is how I wrote it but it does not run yet.

Dim MYDATABASE1 As DAO.Database
Dim rstStockTakes As DAO.Recordset
Set MYDATABASE1 = CurrentDb
Set rstStockTakes = MYDATABASE1.OpenRecordset("StockTakes")
rstStockTakes.MoveFirst
Do Until rstStockTakes.EOF
CurrentDb.Execute "Update rstStockTakes Set Quantity = &QryStockInHand!StockInHand&"
rstStockTakes.Edit
rstStockTakes!StockTakeDate = "Date()"
rstStockTakes.Update
rstStockTakes.MoveNext
Loop
rstStockTakes.Close
Set rstStockTakes = Nothing
Set MYDATABASE1 = Nothing

the Quantity field is a number. I am not sure to make it a sub, a function or module... What do you think? Am I going the right way? Thank you for your help so far...

[June7]

I don't know your data structure or business process so hard to be specific, but I guess could be code in a button Click event.

The concatenation for the SQL statement is not correct. Look again at the example I gave.

Cannot pull value from query object like that. Code does not make sense.

Code would open a recordset of source data and then run UPDATE action to modify data in table.

[ManuelLavesa]

Yeah I agree with you, I will put it on the click event of a button. About pulling the value from a query I still don't know how to do it. I searched the internet with no luck... As far as I know you can only have one recordset open at a time. Is this true?? Otherwise I was thinking of opening another recordset for the query then compare the ProductID and change the values of the table for the values on the query. Can this be done?? Thanks for your help.

[June7]

Can have a bunch of recordsets open. VBA does have a limit on how many but I've never hit it. And then computer memory can be a limitation.

I showed how to get value from a recordset.

Pulling value directly from table or query object can be done with domain aggregate functions.

Yes, can open one recordset for source data and another recordset to write into if you don't want to use CurrentDb.Execute to write direct to table.

Can do searches of recordset (research FindFirst method and NoMatch property). Example:

Private Sub tbxLabNum_BeforeUpdate(Cancel As Integer)
With Me.RecordsetClone
.FindFirst "LabNum='" & Me.tbxLABNUM & "'"
If .NoMatch Then
    MsgBox "Invalid Lab Number", , "EntryError"
End If
End With
End Sub

[ManuelLavesa]

Well I am glad I was wrong. So I will try opening two recordsets. Also I was studying your statement on mail#4 and I hope I understand it now. Let me try.
First we have a recordset which I will call rstQryStockInhand, which is the query that has the values I want. Then I have the table StockTakes, that is the one I want to edit or Update. So the statement should be

CurrentDb.Execute "UPDATE StockTakes SET Quantity = & rstQryStockInHand!StockInHand & "

without the apostrophes as the fields Quantity and StockInhand are numbers. Also with this method I will only need one recordset for the query and write directly to the table. Is this correct or I am still lost? Thanks again for all your help. I am really learning something.

[June7]

Yes, that is basically what I suggested.

[ManuelLavesa]

I am liking your method better than the two recordsets. But I also need to change the field StockTakesDate and update to the current date. Could I do this using the statement :
CurrentDb.Execute "UPDATE StockTakes SET StockTakeDate = Date()"
Or do I need the # delimiter
CurrentDb.Execute "UPDATE StockTakes SET StockTakeDate =#" &Date()& "#"
Or is it better as I did it in post#5

[June7]

The first should work.

You probably need WHERE clause otherwise every record in table will receive the new value. Sorry, did not include that in example.

You can update multiple fields in one action.

[ManuelLavesa]

I have been trying to get this thing to work but without success My code is:

Private Sub Command35_Click()
    Dim MYDATABASE1 As DAO.Database
    Dim rsQryStockInhand As DAO.Recordset
    Set MYDATABASE1 = CurrentDb()
    Set rsQryStockInhand = MYDATABASE1.OpenRecordset("QryStockInhand", dbOpenDynaset)
       
        rsQryStockInhand.MoveFirst
        Do Until rsQryStockInhand.EOF
            CurrentDb.Execute "UPDATE StockTakes SET Quantity = & rsQryStockInhand!StockInHand &, StockTakeDate = Date(); ", dbFailOnError
            
        rsQryStockInhand.MoveNext
        Loop
   
   'clean up
    rsQryStockInhand.Close
    Set rsQryStockInhand = Nothing
    Set MYDATABASE1 = Nothing

I am trying to take one record at a time from the table Stocktakes and update the value of the Quantity filed and StockTakeDate With the values from the query. The records on the table and the ones in the query should have the same order by ProductID. I could include a WHERE clause as you suggested before. I am just not sure if this procedure would do what I want to accomplish. The program keeps getting an error on the Currentdb.Execute UPDATE statement. The error says the following Run Time error 3075. Syntax error (missing operator) in query expression '& rsQryStockInhand!StockInHand &'. Do you know what this error means? Thank you for your help.

[June7]

Concatenation is not correct. Better use WHERE clause as described.

CurrentDb.Execute "UPDATE StockTakes SET Quantity =" & rsQryStockInhand!StockInHand & ", StockTakeDate = Date() WHERE ProductID=" & rsQryStockInhand!ProductID, dbFailOnError

[ManuelLavesa]

I did what you suggested and still gives me an error in the CurrentDb,Execute statement. The New code is:

Private Sub Command35_Click()
    Dim MYDATABASE1 As DAO.Database
    Dim rsQryStockInhand As DAO.Recordset
    Set MYDATABASE1 = CurrentDb()
    Set rsQryStockInhand = MYDATABASE1.OpenRecordset("QryStockInhand", dbOpenDynaset)
       
        rsQryStockInhand.MoveFirst
        Do Until rsQryStockInhand.EOF
        
            CurrentDb.Execute "UPDATE StockTakes SET Quantity = " & rsQryStockInhand!StockInHand & ", StockTakeDate = Date() WHERE ProductID=" & rsQryStockInhand!ProductID, dbFailOnError
           
        rsQryStockInhand.MoveNext
        Loop
   
   'clean up
     rsQryStockInhand.Close
    Set rsQryStockInhand = Nothing
    Set MYDATABASE1 = Nothing
    
End Sub

the error message is Run-Time error 3265 item not found in this collection. I checked the names of the fields, table and query and they are spelled correctly. Is there a concatenation problem? Thanks for all your help.