Update query help

[Access_Blaster]

Hello,



I want to use a local temp table to update a SQL table, for the rare times the network is down or inaccessible. There are two fields I need to update, counted and DateStamp. Counted is True/False and datecounted is a date field. The SQL below works on the True/false, but I need the actual value in the tblInvTemp.DateStamp not the current date . Any help would be appreciated.

UPDATE tblInvTemp INNER JOIN tblInv ON tblInvTemp.ID = tblInv.ID
SET tblInv.Counted = True, tblInv.DateStamp = Date()
WHERE (((tblInvTemp.Counted)=True) AND ((tblInvTemp.DateStamp)=Date()));

[orange]

Where would you get that Date????
If you are updating tblInv.DateStamp

where is the value coming from?

[June7]

SET tblInv.Counted = True, tblInv.DateStamp = tblInvTemp.DateStamp

Will the value of DateStamp be the same for all records in tblInvTemp? If so then this filter criteria is not needed. Otherwise, you need to provide the date value to filter on. This can be statically configured in the query or input by a popup prompt (which I NEVER use) or reference to a form control. As is, the update will only occur for records with a DateStamp that is equal to current date.

[Access_Blaster]

Where would you get that Date????
If you are updating tblInv.DateStamp

where is the value coming from?

Sorry, the date is being generated from my print button.

   Private Sub cmdPrintTag_Click()   
    On Error GoTo Form_Err


    DoCmd.OpenReport "rptAssetTag_Temp", acViewNormal


    CurrentDb.Execute "UPDATE tblInvTemp SET Counted = 1 WHERE ID = " & Forms!FRM_SearchMulti_Temp!SearchResults.Column(0), dbFailOnError Or dbSeeChanges
    CurrentDb.Execute "UPDATE tblInvTemp SET DateStamp = Date() WHERE ID = " & Forms!FRM_SearchMulti_Temp!SearchResults.Column(0), dbFailOnError Or dbSeeChanges


    Me.SearchResults.Requery
    Me.SearchFor.SetFocus


Form_Exit:
    Exit Sub


Form_Err:
    MsgBox err.Description
    Resume Form_Exit
End Sub

Basically any dates in the temp table tblInvTemp should also be in the SQL table tblInvTemp.

[June7]

I am confused. If the date is already in tblInv, why do you need to UPDATE that field?

[Access_Blaster]

I am confused. If the date is already in tblInv, why do you need to UPDATE that field?

Private Sub cmdPrintTag_Click()       On Error GoTo Form_Err


    DoCmd.OpenReport "rptAssetTag_Temp", acViewNormal


    CurrentDb.Execute "UPDATE tblInvTemp SET Counted = 1 WHERE ID = " & Forms!FRM_SearchMulti_Temp!SearchResults.Column(0), dbFailOnError Or dbSeeChanges
    CurrentDb.Execute "UPDATE tblInvTemp SET DateStamp = Date() WHERE ID = " & Forms!FRM_SearchMulti_Temp!SearchResults.Column(0), dbFailOnError Or dbSeeChanges


    Me.SearchResults.Requery
    Me.SearchFor.SetFocus


Form_Exit:
    Exit Sub


Form_Err:
    MsgBox err.Description
    Resume Form_Exit End Sub

[orange]

That is where I had the issue as well, June. Seems the value was already there, but maybe we're missing something.

[June7]

More confused. Your original post said "use a local temp table to update a SQL table". But now you show queries updating the local table.

Exactly what is the issue?

[Access_Blaster]

If I put a value (date) in a temp table (tblInvTemp) it does not exist in the main table (tblInv) , there for I need to update the main table if there is changes in the temp. I'll get it eventually thanks.

[June7]

Did my suggestion in post 3 not help?

[Access_Blaster]

Did my suggestion in post 3 not help?

Yes, your suggestion in post#3 gave me
the help I needed. Thank you both.

June7

SET tblInv.Counted = True, tblInv.DateStamp = tblInvTemp.DateStamp

UPDATE tblInvTemp INNER JOIN tblInv ON tblInvTemp.ID = tblInv.ID
SET tblInv.Counted = [tblInvTemp].[Counted], tblInv.DateStamp = tblInvTemp.DateStamp
WHERE (((tblInvTemp.Counted)=True));