using a query for a lookup column

[mitchmcc]

I have a seeming simple question, but I cannot figure it out.

I have two tables, Contacts, and Donations. The Contacts table has a
ContactId for the primary key, and the Donations table uses ContactId
as a foreign key. Also, the Contacts table has standard FirstName and
LastName fields.

I want to create a form that will allow a new Donation to be entered,
where it will have a list box with all of the Contacts so that the
user can choose a contact by name and does not have to know the
ContactId. (I have done this many times with a table-based lookup,
but not a query).

I created a query called ContactNames that basically concatenates the
FirstName and the LastName.

When I go to build my new Donations form, I insert a list box and say
that it will take its values from my ContactNames query, and it does
so.

My problem is as follows. The Donations table has only the
ContactId. When using a query for the lookup field to show the
"FirstName LastName", how does Access get the ContactId? I tried
putting it in the query but not showing it, but that did not seem to
work.

How do I get a query to save the ContactId in the new Donations form?

Thanks,

Mitch

 

[strive4peace]

Hi Mitch,

your query should have the first column be ContactID

query name --> qContacts
SELECT ContactID, Lastname & ", " & Firstname as Contact
FROM Contacts
ORDER BY Lastname, Firstname

then, for your listbox:

ControlSource --> ContactID
RowSource --> qContacts
ColumnCount --> 2
ColumnWidths --> 0;2

The trick is to set the first column in ColumnWidths to zero...

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*