Filling text boxes from table filtered by 3 combo boxes

Question

I have a table tblUniqueRec with 14 fields. I'm using a form, frmUniqueRec to display those fields in text boxes and using 3 combo boxes to filter the records. 3 of the 14 fields will be used as a filter criteria. See this table for an example:

Example Table

Value1, Value2, Value3 are the 3 values that will be in 3 separate combo boxes, and depending on which is selected, it will show Field4-Field14 that aligns with all 3 criteria.

I have tried the following:

Populate each textbox using a query in VBA by setting the control source for that text box a query:

1Val = Nz(Me.Value1.Value, "")
2Val = Nz(Me.Value2.Value, "")
3Val = Nz(Me.Value3.Value, "")

qryZone = "SELECT Value1FROM tblUniqueRec WHERE Value1= '" & 1Val & "' and Value2= '" & 2Val & "' and Value3 = '" & 3Val & "'"""
Me.textbox1.ControlSource = qryZone

I've tried doing so in the property sheet as well as VBA, but neither worked.

I have also tried setting the default value in the property sheet for each text box to a DLookUp, but this also didn't work:

=DLookUp([Field1],[tblUniqueRec],[Value1]=[1Val] And [Value2]=[2Val] And [Value3]=[3Val])

I also tried DLookUp where I hard coded the values and it didn't work.

How can I do this properly?

qryZone is set to an SQL statement. Can't set a textbox ControlSource property to an SQL statement. Would have to open a recordset object with that SQL then pull value from field of recordset. Or set form RecordSource to that SQL. Or use DLookup() domain aggregate function expression in ControlSource, not DefaultValue. Why don't you use a bound form and apply filter criteria to the form? Review stackoverflow.com/questions/5827047/… — June7
Or VBA to set textbox Value property with DLookup() expression. But really, I would say a bound form and bound controls and apply filter to form is most proper approach. — June7
This is a common question. The proper approach is to use 3 unbound comboboxes to filter the form. the comboboxes are hooked up to the form in the their respective afterupdate events with vba to update the form filter. updating the recordsource doesn't work as access doesn't expect that. — mazoula

mazoula mazoula · Accepted Answer · 2020-08-07T08:15:55

The common way to do this is to filter the form with unbound combo boxes:

form before filter:

form after filtering:

First note is that normally you would have a Value1 type table filled with all the values Value1 can take. Normally the combobox rowsource is made using a type table including the type table index but here we don't have that. Instead select only Value1 and make sure to find the unique values only property of the rowsource query and set it to yes.
Once the comboboxes are done hook them up to the form using the afterupdate event. use the same code in each event or abstract it to a function.

Private Sub cmbValue1_AfterUpdate()
'Form_frmUniqueRecords.Filter = cmbValue1.Value
'note the single quotes around values ( '  ') this is an unfortunate necessity when testing strings
If Not IsNull(cmbValue1) And Not IsNull(cmbValue2) And Not IsNull(cmbValue3) Then
Me.Filter = "Value1 = '" & cmbValue1.Value & "' AND Value2 = '" & cmbValue2.Value & "' AND Value3 = '" & cmbValue3.Value & "'"
Me.FilterOn = True
Else
'decide how to handle default values and empty boxes to figure out what to do else
End If
End Sub

Filling text boxes from table filtered by 3 combo boxes

1 Answers