CODEWITHSUNDEEP
vbams-access
Shawn V. Wilson
Shawn V. Wilson

Reputation: 1111

How to reference foreign key of lookup table?

My Access database has MainTable that includes, among other fields, Event Description.

I get the Event Description from another table, EventTable, that has two fields, EventID and Event Description

My form has a combobox that lets users select the "Event Description", and that works okay. But in my VBA code, I have to be able to capture the EventID itself.

Is there a way to do that without putting an "EventID" control on the form? (Even an invisible one?)

Upvotes: 0

Views: 257

Answers (1)

Parfait
Parfait

Reputation: 107577

Simply use a hidden column on the combobox which you can set up via the control wizard. However, specific design steps include:

  1. Under Data tab of Property Sheet, use EventID as the Control Source of combo box. This assumes form is bound to a record source.

  2. Under Data tab of Property Sheet, use a two column SQL query that includes EventID (hidden column) and EventDescription (displayed column) as Row Source with Row Source Type being Table/Query. You can add more columns with corresponding adjustments to #3 below. Be sure that 1 is used for Bound Column or whatever column position aligns to EventID.

    SELECT EventID, EventDescription
FROM Events

  3. Under Format tab of Property Sheet, use 2 (or more for every column in query) for Column Count and zero for first item and non-zero for second or more items in Column Widths (e.g., 0"; 2"). Ideally, the widths add up to the total width of combo box.

Altogether, above will hide EventID in combobox but show to users EventDescription and any choice selected will return the corresponding EventID value.

Upvotes: 1

Related Questions