Dim StoreNoToUpdate As String
Dim Marsha As String
StoreNoToUpdate = "ABCXYZ123"
Marsha = "hi"
db.Execute "Update TblLodgingReport set [MarshaCode]= 'Marsha' where [Store Number ID]= 'ABCXYZ123'"
I am trying to update "hi" at "ABCXYZ123" location. but its not pasting "hi", instead its pasting "Marsha".
You need to get away from SQL concatenation and start using parameters.
Query with parameters:
PARAMETERS [prmMarshaCode] Text (50), [prmStoreNoToUpdate] Text (50);
UPDATE TblLodgingReport SET [MarshaCode] = [prmMarshaCode]
WHERE [Store Number ID] = [prmStoreNoToUpdate];
Calling the above query in VBA:
With CurrentDb().QueryDefs("qryName")
.Parameters("[prmMarshaCode]").Value = Marsha
.Parameters("[prmStoreNoToUpdate]").Value = StoreNoToUpdate
.Execute dbFailOnError
End With
Try:
Dim StoreNoToUpdate As String
Dim Marsha As String
StoreNoToUpdate = "ABCXYZ123"
Marsha = "hi"
Db.Execute "Update TblLodgingReport set [MarshaCode]='" & Marsha & "'where [Store Number ID]= 'ABCXYZ123'"
Try changing the following line
db.Execute "Update TblLodgingReport set [MarshaCode]='" & Marsha & "' where [Store Number ID]='" & StoreNoToUpdate & "'"
Its because 'Marsha' means you are literally sending Marsha as string and not using the variable.
