Locate edit and update record.

[pekajo]

Hi,
Can you help. I currently have two tables one that I need to update and has 4000 records and another table with 60 records that has data I need to update the first table.
I currently loop thu the first table and dlookup on the second and if found update the record. To do this I need to loop thu all 4000 records to make it work.
I thought is there a way of looping thu the second table, locate that record in the first and update it. This way I only need to loop 60 records instead of 4000.
Any code would be handy
Cheers
Peter

 

[plog]

Really depends on the logic of the updates, but most likely a simple UPDATE query will do the job quickly and efficiently:

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[CJ_London]

why not just run an update query?

 

[arnelgp]

example:

Table1:
InvoiceNumber (text)
InvoiceTotal (double)

Table2
InvoiceNumber(text)
ProductCode (text)
Quantity (single)
Price (single)

you want to Update InvoiceTotal of Table1 with the Total (Quantity * Price) from Table2 on the condition that
the two tables must have the same InvoiceNumber.

using SQL Update:

Update Table1 Set InvoiceTotal = DSum("Quantity * Price", "Table2", "InvoiceNumber='" & [InvoiceNumber & "'")