Updating a table Access and Excel VBA

Question

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,

You will need to gain access to the unique ID field and use that in your where clause for the updates. — Doug
– Doug, Commented Sep 20, 2010 at 16:10
Is ID different for each record? Could you add a where criteria that lists all the IDs? — John M
– John M, Commented Sep 20, 2010 at 16:12
Update table Transaction(ProductName,Amount) set ProductName=@Product,Amount=@Amount) where ID = "id of record you want to update" — Doug
– Doug, Commented Sep 20, 2010 at 16:38

Doug · Accepted Answer · 2010-09-20 17:45:28Z

1

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!

edited Sep 20, 2010 at 17:45

answered Sep 20, 2010 at 17:25

Doug

5,3681 gold badge26 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Updating a table Access and Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related