Storing DLookup Values

[akaivyleaf]

Can you store the value of a DLookUP in the underlying table? I have my DLookUps working on my form and want to store the results in the table. Is that possible?

 

[MichaelRed]

"Yes, but I wowuldn't recommend it" (from Tic-Tac).

A basic concept for database design is to not store variable information. After all, you can NEVER trust it to be current, except at the moment of calculation, so why bother to save it?

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[akaivyleaf]

I understand that but at this time I can't think of a better way to store information that this table needs to collect. It won't really be changing, it's just aiding in input of data. For instance if a person types in a 1, the other fields must be 1 and 2, and the dlookup is placing 1 and 2 in the appropriate boxes. There are 118 different variables for the field where they type in a number. The other two numbers change accordingly. They can pick 118 3 times. . . but I liked the way DLookUP did the trick.

I am open to other ideas if you have any. . .

Thanks for responding so quickly

 

[JaneInMA]

If the database you are using needs to hold historical information you may have to store this information. The only way to get round this is to create a complex historical relationship to note that when a value is found at a particular date it is X but if found a month later it is Y.
For example storing training records, you can stire the ID# but regulatory agencies may require you to produce the information that echos the way it is found on the paper copy. Ie person Jane Smith of Dept A trained on procedure Y.
The fact that this person is now known as Jane Brown of Dept B does not reflect the paper copy you must show them.
Sometimes company policy must override the best relational database theory.

 

[Rohdem]

"Sometimes company policy must override the best relational database theory."

Amen to that!!!!
Mike Rohde
rohdem@marshallengines.com

 

[akaivyleaf]

I guess that is where I am with it. . . I need to know how to store it, even though I shouldn't do it. . .

 

[MichaelRed]

Then, I guess, wer'e all back to the original question. Just do an update query using hte DLookUp function as the value to update to. If you get caught in the circular reference loop/trap, you MAY need to put a layer between the two (query "A" gets (Selects) the DLookUp value(s) from the table, query "B" Updates the table with the values from query "A&quot

Have a very merry Circular Reference Query!


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[jagilman]

I understand the question a diferent way. It sounds like the value that needs to be saved is actually being produced on the form by the DLookup function at the time the user is editing the record. If this is correct, then it sounds like you would like to save the value you see displayed in the text box at that time. I would place a hidden field on the form bound to the field you want the value stored in. Then use the before update event of the form to place the value of the displayed text box into this hidden field.

John A. Gilman
gms@uslink.net

 

[MichaelRed]

A variation would be to use a ComboBox/ListBox control. Bind it to the Table!Field, set it's Rowsource to the calculation.


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[akaivyleaf]

Thanks so much for the wonderful responses. I am going to try to link to a text box first before doing the combo box and I will report back on my progress.