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?

Answer 1

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:

1. rearrange fields in query so Item is first column

2. set third column as Bound column

3. 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.