linking two fields

[Guest]

Hi I have question on linking, I have to tables one with employee name and
surname call table A, the other table has emplyee info called table B, I used
a combo box looking up table A employye id and returnng that to employee id
in table B thats pretty straig forward.
Now how do I get the surname from table A and show it on the Form (I dont
need to save it in table B) But when the employee id changes I want the
Surname to Change.

Would appreciate any help.

Michael

 

[Allen Browne]

Simplest way would be to use a combo on your form.

The combo's properties will probaby be something like this:
Row Source SELECT EmployeeID,
Surname & ", " & FirstName AS FullName
FROM Employees ORDER BY Surname,
FirstName;
Column Count 2
Bound Column 1
Column Widths 0

This combo has 2 columns.
The first one (the ID) is stored (the bound column.)
The second one is displayed (since the first column is zero-width.)

If you don't want to hide the ID, you can still display the name by adding
another text box beside the combo, and setting its Control Source to:
=[Combo1].Column(1)

Alternatively, if you don't want to use a combo at all, you could use a text
box with a DLookup() expression in its Control Source. More info on that
approach:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

 

[Guest]

Thanks Allen that was great
--
Michael A

Simplest way would be to use a combo on your form.

The combo's properties will probaby be something like this:
Row Source SELECT EmployeeID,
Surname & ", " & FirstName AS FullName
FROM Employees ORDER BY Surname,
FirstName;
Column Count 2
Bound Column 1
Column Widths 0

This combo has 2 columns.
The first one (the ID) is stored (the bound column.)
The second one is displayed (since the first column is zero-width.)

If you don't want to hide the ID, you can still display the name by adding
another text box beside the combo, and setting its Control Source to:
=[Combo1].Column(1)

Alternatively, if you don't want to use a combo at all, you could use a text
box with a DLookup() expression in its Control Source. More info on that
approach:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi I have question on linking, I have to tables one with employee name and
surname call table A, the other table has emplyee info called table B, I
used
a combo box looking up table A employye id and returnng that to employee
id
in table B thats pretty straig forward.
Now how do I get the surname from table A and show it on the Form (I dont
need to save it in table B) But when the employee id changes I want the
Surname to Change.

Would appreciate any help.

Michael