Fill 2 controls from 1 lookup

M

Mary Hartman

I have a control destination and a control value on a form.

The form is from a table "certificates" in which the "Destination" is
a loopup.

I have created a table called "destination" in which both the
destination and the "value" of the destination is listed.

When I lookup "Destination" on the form, I would like it to fill in
the value.

I have tried every kind of relationship I can think of, but none seem
to work. Could someone help please?

Thank You
 
K

Klatuu

Well, there is not quite enough information to give you an exact answer. You
don't say what kinds of controls destination and value are. BTW, Value is
not a good name for any object in Access. It is a common property name and
therefore a reserved word. Access can get confused when you use reserved
words.

Also, you don't say whether either of the controls are bound or not.

As a general pointer, and depending on what kinds of controls they are and
how you are using them, a common technique would be to use the After Update
event of the destination control to populate the value control. Since the
DLookup (normally, but not necessarily) returns only one field and if you are
using DLookup to look up the destination, you may need a second DLookup to
get the value.

If you need more help, post back with more detail, and we will see what we
can do.
 
J

Joan Wild

You can accomplish this using a combobox control on your form. You don't really need two controls, but you can if you wish.

The easiest way to do this is to use the control wizard. Delete the controls you currently have on the form for the 'destination'.

Ensure the field list is displayed (View, Field List), and with the toolbox displayed (View, Toolbox) (and the wizard button activated on it):
click on the combobox tool on the toolbox, then drag the 'destination' field from the field list to the form. The wizard will start and walk you through creating the combo.
 
A

Al Campagna

Mary,
First, if you have a "Lookup" field defined in your table, get rid of that. I've found
Lookup fields to be more trouble than they are worth.
Read http://www.mvps.org/access/lookupfields.htm for more info.

On my website, I have a sample app for A97 and A2003 called Combo Populates Multiple
fields... which is what I think your trying to do... one combo lookup provides two field
values. My app shows how to "display" the ancillary values, but it would be simple to
make it "save" those values to fields in the record.
Be sure to study the Combo setup (No of Columns/Coulmn Widths) and the query behind the
combo.

I'd like to see several sample records from your Destination/Value table before I
suggest anything further... other than just getting my sample technique to work on your
form.
When you get to that point, post back on this thread.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."
 
S

SeeJay

PMJI

Do you have an example of this type of code to replace Dlookup in an After
Update routine

Thanks
 
Top