Problem with inserting DLookup value into other table with criteria

[JB83]

Dear reader(s),

I have (again) a problem with a DLookup value. I would like to paste the retrieved value into another table. My question is: can this be done with a normal insert query or has this to be done with a special command?

Thanks in advance,

Jochen. --------------------------------------------------------------------------------
It never hurts to help and it never helps to hurt.

 

[RickSpr]

In general, you can use DLookup() as a value source in an INSERT query, depending on where the selection criteria values are located.

However, it's very possibly not the best way to go. Keep in mind that DLookup(), or any of the other domain aggregate functions, will build and execute a complete query of the lookup table each time they're invoked. If you put DLookup() in the VALUES list of an INSERT query, for example, it's invoked once for each row inserted. That's not a problem if you're only inserting one row, but if you're inserting 1000 rows, the lookup table query will be run 1000 times.

Is DLookup() is too slow for your purposes, you can use a join instead. I can't be more specific than that without knowing more about what you're doing. In particular, I need to know where all the data to be inserted comes from, and where you get the values for the DLookup() criteria expression. Rick Sprague

 

[JB83]

Rick,

I have already found a solution for this problem: At first DLookup searches for the needed value in a table and then (if it exists) it is being inserted into another table with the DoCmd.RunSQL command with the same criteria as in the DLookup method. But nevertheless thank you for your general information about DLookup.

Keep up the good work!



Jochen. --------------------------------------------------------------------------------
It never hurts to help and it never helps to hurt.