I have set up a form which looks for a code which has already been entered. It then looks up the code in a linked table and returns the current value. This is working fine, but I would like to store the returned answer in a table. Because I'm using the Control Source to run the DLookup Function I can't store the result in a field in the table. Is there a way around this please?

Thanks, Fintan

Don't use the control source as the DLookUp function. Put that into a hidden textbox that retreives the data, and then assign the retreived info into the real textbox thas is bound to the control source of the table.

Either than or assign the real textbox's value directly via the DLookUp function using VBA instead.

Either way, if this info is always in this linked table for you to get, you shouldn't have to store it in yours. You're just creating redundant info that takes up space.

__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division

Thanks Greg. I'll try that. The DLookup looks up the current price only, but I want to keep the info as a snapshot of what happened at that time, like in an order where the price changes each year but I want to keep the details of what was bought and exactly how much was paid.

Thanks again,

Fintan

Is the issue solved?

I have the same problem but I have not understood what to do.

Please help

Can you describe the nature of what you're trying to do in more detail?

__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division

I generally us a control's after update event to look up the value and store it in the desired control.

For example in an Invoice form, when you select the product/item/service code the after update event would look up the prince and store it int he Unit Price control on the form.

There is an example database that ships with Access called Northwind. The Order form shows exactly how to do this. I urge you to check it out.

TIP: The Northwind example is a great learning tool.

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

I have done similar things with what you are speaking of, but instead of DLookup() I used Alan Browne's ELookup(). It is much more efficient and resolves a lot of issues that Dlookup() has.

Here is a link to his webpage - http://allenbrowne.com/ser-42.html

I have used Elookup() with SQL to save its output to a table for future use or logging.