getting more than one field at a time

[Access2000_JS1]

HI, I have a table called cust_order, with fields: orderNo, product, ...

I have a form which updates these values.

On the 'enter' event of a button to 'update changes' I collect the stored values cust_order and compare these old values with the new values. I do it using the dlookup command like this;

Private Sub UpCust_OrderDetBut_Enter()
Dim old_product as string
old_product = DLookup("product", "cust_order", "orderNo = '" & Forms![update_order].orderNo + "'")
Dim old_product as variant
old_qty = DLookup("qty", "cust_order", "orderNo = '" & Forms![update_order].orderNo + "'")

' THERE ARE OTHER FIELDS

'I then compare these values with the ones in the form. Where they are different I write them along with info into another table to keep control of the changes.

End Sub

I would rather return all the fields in a select query but I do not know how to get the selected data into local variables. Assuming I want the two fields above, can someone show me how to achieve it through a query - something like:

docmd.runsql("select product into " &old_product + ", qty into " &old_qty +" from cust_order where orderNo = " & forms![update_order].orderNo )

Thanks John.

 

[RuralGuy]

You evidently have an aversion to using bound forms. May I ask why?

 

[Access2000_JS1]

.Oldvalue works for me, obviously this is what the system stores for a rollback. I would still like to know how to get to the values from the select, not for this but I would like to know.
Thanks

 

[reclusivemonkey]

I'm just looking into starting an audit trail for some of my databases. This is very helpful indeed!

 

[Access2000_JS1]

My advice in developing an audit trail would be to create an entry for each and every change. Store the user, the date/time, the change, a comment and what it relates to. If you write this instruction for every field, which you should, you will get every change. In my case, which has nothing to do with an audit trail, it is sufficient to simply string the changes together update = update + " this changed to that...;" this becomes a visual only and is useless for auditing.