Full Version: Store value of DLookUp for other uses
UtterAccess Forums > Microsoft® Access > Access Forms
Without going into a lot of explanation, I need ot store the value of a field I have retrieved by using DLookUp. Can someone help me with how to go about storing it in the field that is now displaying it?
Thanks for your help.
Jack Cowley
Me.NameOfControlOnFormToStoreValue = DLookup(....)
Not sure where or when your run DLookup() but the above will do what you want...
I see you don't want to go 'into a lot of explanation', but storing a value you can retrieve whenever it is needed is rarely a good idea. You're asking for data integrity issues 99% of the time when you do so.
I'm running the =DLookUp() on a from in the Control Source. How would I apply the above in that type of situation?
I do understand the reason for not storing a value just whenever it is needed. But to explain, I have a table that stores values that will change on a monthly basis. I have a nother table that refers to a value at the time of entry. I need for the value to remain static over time for historical and tracking purposes.
hope this clarifies. So back to my problem how to use the store command on a form.
Thanks for all the help this forum offers.
Jack Cowley
You are correct in saving the value, as this is one of the instances where you do want to save the value! As for where you should use your code I cannot say, but it needs to look like the sample I gave you. If you have another control on the form that you change, add data to or whatever, maybe you can trigger the code there to put the value from DLookup() into the control in question.
Hello again,
I have tried just about everything to save my DLookUp value. Here are some more details maybe you can see what I'm doing wrong or simply missing. The lookup works great just storing the value with the record I can't figure out.
My control source on Form:(frmFile.FCSN) - which I have tried using different names in order to prevent conflict reflects the following:
=DLookUp("[qryFilePlanEdtSurveyTR]![FCSN]","[qryFilePlanEdtSurveyTr]","[qryFilePlanEdtSurveyTr]![SchedID]='" & Forms!frmFile.SchedNo & "'")
I have tried the Me.FCSN (and other values) in the AFTER UPDATE and other actions as well as other fields
I have managed to get an INSERT statement to work but that put the lookup field into a second record with only the lookup information.
PLease any suggestions or comments would be appreciated. If you need further details please let me know. and I'll try to explain.
Hey Sherri,
take a shot at helping you. Give this a try.
=DLookUp("[FCSN]","[qryFilePlanEdtSurveyTr]","[SchedID]=" & Forms!frmFile.SchedNo)
Not positive but see if that will fly for ya.
Jack Cowley
Sherri726 -

Let's do the obvious first. Create a command button on your form and add this code to the [Event Procedure] in the On Click event:

Me.NameOfControlToSaveValueFromDLookup = DLookup("[FSN]", "qryFilePlanEdtSurveyTr", "[SchedID] = '" & Me.SchedNo & "'" )

Now when you click the button it should save the value in the table. If it does then you can use that very line of code in the After Update event of a control. Give it a try and it should work. If it does not I will be very surprised, since you said your DLookup() returns the right record. If it DOES fail, then can you make a demo of what you are doing and post it or post the whole db as long as it is not too large.

If you want to post your db be sure and compact it first then zip it. The max size you can post here is 500k.

I hope this solves your problem, but if not be sure and let us know!

Thank you so much! It is doing exactly what I needed.
This forum has been a great help.
Jack Cowley
Sherri726 -
I am very pleased to hear that you have solved your problem. We are very glad to have you as a member of Utter Access and that you find this forum beneficial! Continued success with the project!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.