Access Userform: How best to show fields from a lookup table?

0
votes

Table1 has a field ItemID; I use that to lookup the corresponding record in Table2, which has fields ItemID and ItemDescription.

My form uses Table1 as its Recordsource. I want to display ItemDescription from Table2 in a combobox or textbox.

From what I've read, there are several ways to accomplish this, but I can't tell which is considered the standard method:

  • Add a subform to the form, with Table2 as its Recordsource.
  • Make a query combining Table1 and Table2, and use the query as the form's Recordsource.
  • Use a SELECT statement as Table1's Recordsource that pulls in fields from both tables. (Basically, using the aforementioned query in a different way.)
  • Use DLOOKUP as the Control Source of the textbox, something like =Dlookup("ItemDescription","Table2","ItemID = Forms!MyForm!ItemID"

In my case, I expect I'll have to link Table1 to about five different lookup tables; and some of those may have lookup tables of their own.

What is the best way to show data on the form from these multiple tables?

1
ms-accessuserform

1 Answers

1
votes

  • Add a subform to the form, with Table2 as its Recordsource.
    This is an overkill. Do this only for detail records where the main form is on the one side of a one-to-n relation.

  • Make a query combining Table1 and Table2, and use the query as the form's Recordsource.
    This works only one way and is okay for a read-only form. But you cannot edit the value and the value will not change when you make edits to another field containing the id

  • Use a SELECT statement as Table1's Recordsource that pulls in fields from both tables.
    Same as above.

  • Use DLOOKUP as the Control Source of the textbox.
    This works well and you can update the textbox with textbox.Requery() when necessary. But of course, you cannot edit the value.

An option that works for displaying AND for editing the value is to use a ComboBox. Setup the combo box like this:

  • (in the Data section) Set the Control Source to ItemID. This is a column of the Record Source of the form.
  • Set the Row Source Type to Table/Query
  • Either specify a query name or a SELECT statement as Row Source. Select the Id as first column and the display name as the second. E.g. 
    SELECT ItemID, ItemDescription FROM Table2 ORDER BY ItemDescription;
  • Set the Bound Column to 1 (the ID column)
  • Set Limit To List to Yes
  • (in the Format section) Set Column Count to 2
  • Set Column Widths to 0 to hide the first column with the Id. You can specify a comma or semicolon delimited list of column widths here if you want to display several columns.
  • You can specify a higher number of rows in ListRows than the default 8 if you want.

Now the combo box displays ItemDescriptions automatically and is editable. I.e. you can select another Item from the drop down displaying ItemDescriptions and the ItemID on the form will be updated.