Updating a table Access and Excel VBA

Question

Hi, I have one table called: Transaction. This table has the following fields: (ID,ProductName,Amount,Date) placed in an excel sheet that is connected with MS Access database. ID is the only unique field. Sometimes, my user submits a transaction that has let's say 5 records. Then, they want to modify the submitted data in case if they entered incorrect amount and they want to correct it. I want to write a code in VBA that will do the update. my current query is:

Update table Transaction(ProductName,Amount) set ProductName=@Product,Amount=@Amount)
where Date=@date;

This query does not work fine because obviously it replaces all the records data with the data of the last resubmitted record because my condition is weak. My difficulty is that I can't find a good condition in the where clause that will do the update a record by record accordingly. Please help,

Answer 1

Hi,

You will need to use the unique id of the record, in your case the ID field to guarantee you are updating the correct record.

Something like the following:

Update table Transaction(ProductName,Amount) set ProductName=@Product,Amount=@Amount) where ID = "id of record you want to update"

Enjoy!