Dlookups and storing to table

[johnc2806]

HI Guys,

Looking for some help, before I actually smash my head against a wall.

I have a simple data entry form based on a table. However I have a few fields that I do a lookup in a field on the form from a query, and yes I know I should not have a lookup in the control source however, this is the way that I will be doing it on this occasion.

=DLookUp("[Salary]","[Salary Query]")

Can anyone please tell me how I get the value from this unbound field to enter into the actual field in the table. Do I bring the actual field into the form and hide, and do some sort of after update, as I have tried and it does not work.

I have called the unbound field with lookup "Salary Level Base" and the actual field in the table is "Salary Base".

Can anyone please help?

 

[CJ_London]

not enough information to provide an informed response. Storing calculated values is always more complex than just looking up when required, although there are occasions when it is justified.

Please provide the following answers:
Is the form a single form or continuous?
what have you tried which didn't work?
what is the name of the table you want to update?
how do you identify which record in the table is to be updated? - is it the current record on the form? If so, what is the uniqueID for the record
why do you need to store the value, when it can just be looked up when required? what is the SQL of the [Salary Query]?
What happens if the parameters which define what the value should be changes, so you need to change the stored value?

 

[robslob]

It looks to me like you've gone about this the wrong way, the Control Source should be the [Salary Base] field and the Row Source of that control will be the [Salary Query]. There is no need for an unbound field.

 

[johnc2806]

not enough information to provide an informed response. Storing calculated values is always more complex than just looking up when required, although there are occasions when it is justified.

Please provide the following answers:
Is the form a single form or continuous?
what have you tried which didn't work?
what is the name of the table you want to update?
how do you identify which record in the table is to be updated? - is it the current record on the form? If so, what is the uniqueID for the record
why do you need to store the value, when it can just be looked up when required? what is the SQL of the [Salary Query]?
What happens if the parameters which define what the value should be changes, so you need to change the stored value?

Sorry, I will try and explain a bit better.

It is a single form that I am using. I have tried various things like putting the lookup in the default value in after update etc, even tried to put a hidden text box in with the control source of what I need populated and then referencing the lookup field.
It is the current record on the form that I need updated, and it does have a unique ID.

Its all a bit complicated.

I just thought there would be a simple code you put in to say whatever appears in this field, store it in this field in the table.

 

[CJ_London]

Its all a bit complicated.

I just thought there would be a simple code you put in to say whatever appears in this field, store it in this field in the table.

You are not really answering my questions so I can't really help

Best I can suggest is if the field you want to put this value in is called txtSalary then in perhaps the current event put

me.txtsalary=DLookUp("[Salary]","[Salary Query]")

and your txtsalary control should be bound to whatever the field is in your table.

but I have no idea if this will work for you since you haven't answered

why do you need to store the value, when it can just be looked up when required? what is the SQL of the [Salary Query]?
What happens if the parameters which define what the value should be changes, so you need to change the stored value?

 

[johnc2806]

Thanks for trying to help.

I managed to do it by bringing in another text box and making in the control source, and then when I hit save it runs a macro to set the lookup value in the another text box, which in turns save it.

Long way round but it works.

Appreciate the help

Thanks