how to combine a combobox with dlookup in Access

Question

Objective: Form will provide the correct Unit of Measure and corresponding quantity per container using the item selected in a combo box. The units of measure are in a separate table.

Problem: The dlookup is looking up user id instead of item. I THINK it is because the bound column

Structure: Step 1) The user selects his/her name from a Combo box #1 Step 2) Combo box #2 will only show location assigned to the user -- user selects location. Step 3) Combo box #3 will only show items store in that location -- user selects item

Code for combo box #3
Private Sub Item_GotFocus()
Dim user_filter, location_filter As String
user_filter = Me.Count_By
location_filter = Me.Location
With Me.Item
    .RowSource = "SELECT WeeklyCountOptions.User, WeeklyCountOptions.Location, WeeklyCountOptions.Item" _
                    & " FROM WeeklyCountOptions" _
                    & " WHERE (((WeeklyCountOptions.User)='" & user_filter & "') AND ((WeeklyCountOptions.Location)='" & location_filter & "'));"
    ' by having here the property sheet is ignore
    .BoundColumn = 1
    .ColumnCount = 3
    .ColumnWidths = "0in.;0in.;1in."
End With
End Sub

Code for dlookup

Private Sub Whole_Count_GotFocus()
Dim Item_Filter As String
DoCmd.OpenTable "item_Detail"
Item_Filter = "[ItemId]=" & "'" & Me.Item.Value & "'"
MsgBox (Item_Filter) ' just for testing need to delete
Me.Units_in_UOM = DLookup("[QPC]", "[Item_Detail]", Item_Filter)
End Sub

What can I do to fix it this?

Edit question to show SQL of combo 3 RowSource. I assume name of combo 3 is Item. Opening table serves no purpose. Not necessary to prefix every field name with the tablename in SQL statement. Better to do search/filter with ID, not text like user name or location name. — June7, Jan 15 '21 at 16:33
The ID fields are text type? Not using autonumber as primary key? SQL does not show inclusion of ItemID field, just Item, yet code references ItemID field. — June7, Jan 15 '21 at 21:55

June7 · Answer 1 · 2021-01-16T20:22:43.900

Yes, combobox has User value. Don't really need to include User and Location fields as columns in that combo. However, if you really want to, consider options:

rearrange fields in query so Item is first column
set third column as Bound column
reference third column by its index, index is zero-based: Me.Item.Column(2)

Assuming numeric autonumber ID is set as primary key and saved as foreign key (which should be the case), all 3 comboboxes should have RowSource that includes ID and descriptive field and use ID for filtering.

If WeeklyCountOptions is a junction table where only ID values are saved as foreign keys, not descriptive info, combobox RowSource would possibly have to be an SQL that joins WeeklyCountOptions with ItemDetail so descriptive info can be retrieved. All depends on table structures and how data is saved.

how to combine a combobox with dlookup in Access

1 Answers1