combo box with query

  • Thread starter Thread starter Lodewijk
  • Start date Start date
L

Lodewijk

On my form i have a combo box cboReference. The first four digets show the
year in which the record is made, and the last three are just the number in
that year.
When you select a record through cboReference, the contents of that record
are shown on the form.

With a button cmdFilter, a form is open to choose which records are filtered
by year. When I close that form the following is performed, where sFltr can
be "(Year(txtDate)=2004)", and f = forms!frmMyForm

If Len(sFltr) = 0 Then
f.FilterOn = False
Else
f.Filter = sFltr
f.FilterOn = True
End If

But that does not change the combo box. I can still choose all records.
How can I also change the combo box that it only shows records from 2004?
 
On my form i have a combo box cboReference. The first four digets show the
year in which the record is made, and the last three are just the number in
that year.
When you select a record through cboReference, the contents of that record
are shown on the form.

With a button cmdFilter, a form is open to choose which records are filtered
by year. When I close that form the following is performed, where sFltr can
be "(Year(txtDate)=2004)", and f = forms!frmMyForm

If Len(sFltr) = 0 Then
f.FilterOn = False
Else
f.Filter = sFltr
f.FilterOn = True
End If

But that does not change the combo box. I can still choose all records.
How can I also change the combo box that it only shows records from 2004?
Click to expand...
You need to reset the RowSource of the combo box to include a WHERE
clause of the filter.

Dim strSrce As String

If Len(sFltr) = 0 Then
f.FilterOn = False
Else
f.Filter = sFltr
f.FilterOn = True
strSrce = Me.cboReference.RowSource
'Remove semicolon
strSrce = Left(strSrce, Len(strSrce)-1)
'append the WHERE
strSrce = strSrce & " WHERE " & sFltr
Me.cboReference.RowSource = strSrce
End If


Note: If you have an OrderBy on the RowSource you will have to remove
it, add the WHERE, and then put it back.

- Jim
 
Back
Top