Filter form from combo box

[gwog]

Using Access 97.

I have a form with a combo box. I want the detail section of the form to limit records to those = to the value of the combobox or all records if no value is selected.

Here is the code I have for the afterupdate property of the combo box:

Sub Combo40_AfterUpdate()

Dim strquery As String
If Not IsNull(Me.Combo40) Then
strquery = Me.Combo40.Value
Me.Filter = "LOB = " & strquery
Me.FilterOn = True
End If

End Sub

What happens is after a selection is made in the combo box, a dialog box pops up that says "Enter Parameter Value" has the value I selected listed and then a blank to enter a value, with an Ok and Cancel button.

If I type the value selected in the box it does indeed filter my data. But is there a way to make it automatically filter the data with having to retype the combo box selection.

Thanks in advance for your assistance.

Lisa.

 

[pooksa]

Hi Lisa,

I believe all you need to do is refresh the information before the query is run so it registers the value you have selected in the combo box. Try this:

Sub Combo40_AfterUpdate()

Dim strquery As String
me.Refresh
If Not IsNull(Me.Combo40) Then
strquery = Me.Combo40.Value
Me.Filter = "LOB = " & strquery
Me.FilterOn = True
End If
End Sub

This is the first post I have answered - hope I'm not off base

Chris

 

[swaybright]

Lisa,
There is another way you can approach this function.
Create an "(ALL)" selection in the combo box. This can be done with a union sql statement in the rowsource property of the combobox:

SELECT  "(ALL)" AS [DesiredFieldName] FROM DesiredTable UNION SELECT DesiredFieldName FROM DesiredTable;

Then set the defaultvalue property of the combobox to:
"(ALL)"

Now create a query that has all of the fields you want to see for the recordset. In the field cell of a blank column put:

IIf(([forms]![YourFormName]![ComboboxName]="(ALL)"),"Like *",[DesiredFieldName]=[forms]![YourFormName]![ComboboxName])

And in the criteria cell of that column put:

<>False

Set the recordsource of your form to this query you created and in the after update of your combobox put only:
Me.Requery

What should happen is when the form opens, the combobox should read "(ALL)" and all records are shown. As the user chooses different values in the combobox, the corresponding records are shown.

I hope that helps!

Shane

 

[gwog]

Chris thanks for the reply.

Shane - I think your response will get me closer to what I need.

I tried to follow your directions. But here is what is happening.

When I open the form the (ALL) does appear in the combo box, but no records are shown. It does filter the data when I select something besides (ALL) in the combo box.

Here is the code in my query:

Expr1: IIf(([Forms]![NATIONAL - ABS]![Combo44]="((ALL)&quot,"LIKE *",[NATIONAL - ABS]![LOB]=[Forms]![NATIONAL - ABS]![Combo40])

with <>false in the criteria of that field

Any suggestions as to where I went wrong?

Thanks.

 

[swaybright]

sorry for getting back to you so late... been on vacation

check your Iif statement criteria.
It should read:

Expr1: IIf(([Forms]![NATIONAL - ABS]![Combo40]=&quot;(ALL)&quot,&quot;LIKE *&quot;,[NATIONAL - ABS]![LOB]=[Forms]![NATIONAL - ABS]![Combo40])

A note about nomenclature...you should avoid using spaces and punctuation when naming things (you can have these in form captions etc) Thus consider changing the form name to NationalABS. Also make your combobox name(s) more descriptive. This will help folks following after you who might have to add to you db design

Shane