Assign Lookup Values to Form

[Howard]

I have a lookup field on a form which does a lookup from a
table, displaying four fields. When I select an entry from
the lookup table, the value does get inserted into the
control as expected. I would also like to copy the other
three values from the lookup table to corresponding
controls on the form. In other areas of my application, I
avoid this by only storing the lookup table's record ID
and always displaying the values directly from the lookup
table on the form, but in this case I need to duplicate
the values as they may change in the in the lookup table
but cannot change in the main table once assigned. I hope
this is clear. Please help. You can email me at
(e-mail address removed). Thanks!

 

[Michel Walsh]

Hi,


In the After Update event of the CONTROL, add lines of codes like:


Me.OtherControl.Value = DLookup( "FieldNameOtherControlGot",
"LookingUpTableNameHere", "FieldNameForThisControl=" &
Me.ThisControl.Value )



The LookingUpTableNameHere is the table that can "translate" the value from
ThisControl, say 222, into the value appropriate to the OtherControl, say
"1, Microsoft Place". Note that ThisControl.Value is the real value, not
the one formatted and maybe (probably) not the one displayed in the form.
Try a


Debug.Print Me.ThisControl.Value

in the line before, to be sure of what the value is "really". If it is a
text, you have to change the DLookup statement to something like:


Me.OtherControl.Value = DLookup( "FieldNameOtherControlGot",
"LookigUpTableNameHere", "FieldNameForThisControl=""" &
Me.ThisControl.Value & """" )




Hoping it may help,
Vanderghast, Access MVP

 

[Howard]

Hi Vanderghast,

Your solution sounds correct, but I am having difficulty
implementing it, as my DLookup statement(s) is not
working. If I could impose by presenting you my actual
data, coul you please respond with the actual DLookup
statement I need and I will create the others based on
your example. Here goes ....

I have a "component" table which contains the following
fields:
customer ID
system type
system location
component ID
description
supplier
part number
cost

I have a "components query" query with essentially the
same fields as above.

I have a "component lookup" table which contains the
following fields:
component ID
description
supplier
part number
cost

I also have a "component lookup query" query which I
created on your advise, with all the same fields as
the "component lookup" table above.

I have a "components" form which has a record source
of "components query" as described above.

On my "components" form, the "component ID" field has a
row source of "component lookup query" which implements
the lookup of the components nicely and sets the value of
the "component ID" field to the selected component,
although it displays the description.

In the "after update" event of the "component ID" field,
using the code builder selection, what DLookup statement
is required to assign the "Unit Price" from the "component
lookup query" to the "Unit Price" control on
the "components" form or into the respective "components"
table via the "components query" query. Thanks!
-Howard

 

[Albert D. Kallal]

If the component id field is a combo box, and that combo box displays ALL of
those fields in order like:

I have a "component lookup" table which contains the
following fields:
component ID
description
supplier
part number
cost

Then, in the after update event of the combo box for component ID, you can
use:

me!Desc = me!comboCID.column(1)
me!Supplier = me!comboCID.column(2)
me!PartNo = me!comboCid.column(3)

You can see that the above is only 3 lines of code, and not much at all.

Note the column function is zero based.

 

[Howard]

Thanks Albert,
That is exactly what I did and it worked perfectly. I
received an email earlier from a Chris Jared with a
similar solution. Thanks again, very much appreciated!
-Howard