I hope this makes sense...

I have a form based on a query that uses data from two tables joined by a keyfield. The value that is "looked up" from the join saves keystokes, but is not saved into the data table with the rest of the form data. Since the "looked up" data is derived only for the form, if the "looked up" value is changed in the source table, all the form records are changed.

I need to be able to save the "looked up" value for each record, so if the value is changed in the future, the older records still retain the original value.

Any suggestions for saving a value derived by a lookup query?

Either use VBA code to store the data into your table, or an update query to store the value in all/a selection of records at once.

Post back if you need more information, or search the archives. This topic has some good information on time-sensitive calculations that need to be stored: http://www.access-programmers.co.uk/ubb/Forum2/HTML/001409.html

HTH,
David R

Thanks for the suggestion - Does this sound right?

An update query should be fairly easy to integrate into the form and I can attach it to run as a macro when I exit the control.

I can set an [update?] Y/N flag to be updated when the looked up value is updated by the query. I can limit the update query only to [update?]=null.

One other question... Can I turn off the query notification (You are about to update xx records...) so the query runs completely in the background?

Again, thanks for the help!

DoCmd.SetWarnings False
Do whatever
DoCmd.SetWarnings True

Rich -

At the risk of exposing my ignorance (I'm just learning Access with not much formal programming experience), where would I place the commands you suggested?

Thanks

Rich -

I think I've figured it out. I think I can use the SetWarnings (off) command within the macro that runs the query. If this doesn't work, I'll repost my question.

Thanks for the help!

C

Yes, SetWarnings is the macro command you want (equivalent to the piece of VBA code Rich gave you). However be SURE to turn SetWarnings back on afterward, or you will mess yourself up royally.

Good luck,
David R

If you have existing data that needs to be updated, you'll need an update query to take care of it. However, to fix your form, you need to include both the bound field and the "lookup" field. The "lookup" field should NOT be displayed in a control on the form. In the on Insert event, copy the value from the "lookup" field to the bound field.

Me.BoundField = Me.LookupField