Lookup in a Form

[BrookieOU]

OK, I know lookup fields are bad at the table level. And, I am trying to do
everything correct.

So I have an "Employees" table that lists standard stuff (name, address,
client they work for, group they belong to). I also have a table for
"Client" and another one for "Group". In the table I set it up to have the
ClientID and the GroupID be the foreign keys in the "Employees" table. Now,
for a data entry form I am trying to make the ClientID and GroupID fields be
combo boxes where they are allowed to choose what client and what group they
work for. I do not want the combo box to list the autonumber ID I am using
for those fields but rather the actual client or group name. Whenever I try
to do that I am getting an error that says I am trying to save something that
is not a number in a number field (cause they are autonumber).

How do I write the look-up so that it will store the ID's but show the
actual names in the combo box? Also, if what I wrote isn't too
convoluted...am I doing it correctly?

Thanks!

 

[Sean Timmons]

So, the combo box you're creating, is it a field within your Employees table,
or are you pulling it from your Client ID or Group ID fields? You want it to
be the former, not the latter. In the properties of the combo, are you
entering a column Clount of 2 and entering 2 as the bound column?

 

[John W. Vinson]

How do I write the look-up so that it will store the ID's but show the
actual names in the combo box? Also, if what I wrote isn't too
convoluted...am I doing it correctly?

You're absolutely on the right track.

The way a combo works is that it *gets* data from a Query - its RowSource -
and *stores* data in a field - its Control Source. What the computer sees need
not be the same as what the user sees; you can control this by setting the
combo's ColumnCount and ColumnWidths properties. The combo box wizard will
actually (usually) do this for you, but it's worth doing it from scratch just
so you know what's happening!

For a simple example, let's suppose you have a table named Groups, with two
fields - GroupID autonumber, and GroupName, text. Create a Query by just
adding these two fields to the query grid, sorting by GroupName (so the groups
show up alphabetically) and saving this query as qryGroups.

For now, open the form in design view but turn OFF the magic wand icon in the
toolbar (we'll let the wizards have their fun later). Create a new Combo Box
using the combo box tool.

You'll need to set the following properties:

RowSource - qryGroups. This will tell the combo where to get its data.
ControlSource - GroupID. This is where it will store the user's selection.
ColumnCount - 2 (the groupID and the group name).
Bound Column - 1 (it's the groupID that you want to store).
ColumnWidths - This is a string of numbers separated by semicolons; each
number is the displayed width of the corresponding column in the row source.
In this case use

0";1"

to display (hide actually) the GroupID with zero width, and the group name one
inch (adjust this as needed to fit the names).

Now try the same with the other combo - but use the wizard this time, and
follow the prompts. Then look at the properties of the combo that you get and
compare them with the manually created one.

 

[BrookieOU]

Thank you sooooo much for your explanations. Not only do I know how to do
it, but I really actually understand the why and how behind doing it. Your
explanation was great!

 

[BrookieOU]

OK, one more question with this. I have done everything as explained below
and it works great! Now, my question is how do I get both lookup fields to
show in the combo box? For instance: I have the EmployeeID field as the
bound column (it is the data I want stored) and the First Name and Last Name
fields as the "lookup" fields. When I click on the drop-down for the combo
box it shows both names as requested, but once clicked (or if I start typing
it will fill-in) only the first name (because it is the column I have listed
first). Since we have multiple people with the same first name is there a
way to list both first and last name in the combo box and not just in the
drop-down choices?

 

[John W. Vinson]

OK, one more question with this. I have done everything as explained below
and it works great! Now, my question is how do I get both lookup fields to
show in the combo box? For instance: I have the EmployeeID field as the
bound column (it is the data I want stored) and the First Name and Last Name
fields as the "lookup" fields. When I click on the drop-down for the combo
box it shows both names as requested, but once clicked (or if I start typing
it will fill-in) only the first name (because it is the column I have listed
first). Since we have multiple people with the same first name is there a
way to list both first and last name in the combo box and not just in the
drop-down choices?

Base the Combo - not directly on the Table - but on a Query which has the full
name as a concatenated field:

SELECT EmployeeID, [First Name] & " " & [Last Name]
FROM Employees
ORDER BY [First Name], [Last Name];

to display (e.g.)

Aaron Jones
Alexis Ivanov
Beth Smith

or

SELECT EmployeeID, [Last Name] & ", " & [First Name]
FROM Employees
ORDER BY [Last Name], [First Name];

to display

Adams, John
Buchanan, Thomas
....

 

[BrookieOU]

Ahhhhh. Got it. Thanks!

OK, one more question with this. I have done everything as explained below
and it works great! Now, my question is how do I get both lookup fields to
show in the combo box? For instance: I have the EmployeeID field as the
bound column (it is the data I want stored) and the First Name and Last Name
fields as the "lookup" fields. When I click on the drop-down for the combo
box it shows both names as requested, but once clicked (or if I start typing
it will fill-in) only the first name (because it is the column I have listed
first). Since we have multiple people with the same first name is there a
way to list both first and last name in the combo box and not just in the
drop-down choices?

Base the Combo - not directly on the Table - but on a Query which has the full
name as a concatenated field:

SELECT EmployeeID, [First Name] & " " & [Last Name]
FROM Employees
ORDER BY [First Name], [Last Name];

to display (e.g.)

Aaron Jones
Alexis Ivanov
Beth Smith

or

SELECT EmployeeID, [Last Name] & ", " & [First Name]
FROM Employees
ORDER BY [Last Name], [First Name];

to display

Adams, John
Buchanan, Thomas
....