Chain-Link Binding

[Tatakau]

============
The Short Version:
============

Input Field 1 has a value of X. I would like Input Field 2 to automatically
check a table (columns are: ID, Description), find the record with the ID
equal to x, and then set itself equal to the Desecription.


======================================
The Very Long Overly-Complicated and Drawn Out Version:
======================================

In my form I have a drop-down box to select "Keycodes". When a keycode is
selected, bound text fields auto-fill with the values from other columns from
the "Keycodes" table.

~EG:
~Keycode > Promotion ID, Source ID, and Market ID

So, if I select Keycode "XYZ Zippers", the bound text fields (promotion,
source, and market) will recieve the values 1, 2, and 3, respectively.

Each bound text field has an associated description field:

~EG:
~Keycode >>
~Promotion ID > Promotion Description;
~Source ID > Source Description;
~Market ID > Market Description;

All three IDs have tables that associate their ID numbes with a description.
I am trying to code the description fields to look up their IDs from their
associated fields in a table and then set itself equal to the description
that is also stored in that table.

~EG:
~Keycode Selected > "XYZ Zippers"
~Bound Fields > Promo ID = 1, Source ID = 2, Market ID = 3
~Update>Promo Description = select description from tbl_promo where id = 1
~ Source Description = select description from tbl_source where
id = 2
~ Market Description = select description from tbl_market where
id = 3

.... something like that. I know what needs to be done - I just have no idea
how to do it. I really appreciate any help that you can give, or even trying
to understand my issue for that matter.

Many thanks in advance!

Nick

 

[Daniel Doyle]

From what you describe I would suggest that you either use the DLookup
function in each of the fields, or you create a subform that is related to
the main form on KeyCode and displays Promotion ID, Source ID, and Market
ID.

Dan.

 

[Ron Weiner]

The Short Answer

In the AfterUpdate Event of InputField1 try

If the InputField1 is a Number:
InputField2.Value = Dlookup("Description","TheNameOfTheTable", "ID=" &
Nz(InputField1.Value,0))

If the InputField1 is a string:
InputField2.Value = Dlookup("Description","TheNameOfTheTable", "ID='" &
Nz(InputField1.Value,"") & "'")

I could not follow the logic of the second version.

 

[Tatakau]

Thanks to both of you! The Dlookup function does the trick!

Nick