Combo Box to Filter Form Records

[bjohnson3132]

Hi,

I am a novice at this so I hope this makes sense. I am trying to apply a
filter to an unbound combo box within the main form 'ArcView_Users' that will
allow me to select a value and filter out only those records in the main form
that contain that value. The values in the combo come from subform 'Classes'
using this SQL statement in the Row Source of the combo;

Row Source Type - Table/Query
Row Source - SELECT DISTINCT [Classes.CLASS] FROM [Classes] ORDER BY [Classes.
CLASS];
'Which gives me only the unique text values from the Classes' table CLASS
field'

The underlying query for the combo is called-
ClassType


What SQL statement or VBA code do I need to add in order to get the records I
need in the Form based on the value I select in the combo box? Any help
would be appreciated.

Thanks,

Brian

 

[Marshall Barton]

I am a novice at this so I hope this makes sense. I am trying to apply a
filter to an unbound combo box within the main form 'ArcView_Users' that will
allow me to select a value and filter out only those records in the main form
that contain that value. The values in the combo come from subform 'Classes'
using this SQL statement in the Row Source of the combo;

Row Source Type - Table/Query
Row Source - SELECT DISTINCT [Classes.CLASS] FROM [Classes] ORDER BY [Classes.
CLASS];
'Which gives me only the unique text values from the Classes' table CLASS
field'

The underlying query for the combo is called-
ClassType

What SQL statement or VBA code do I need to add in order to get the records I
need in the Form based on the value I select in the combo box? Any help
would be appreciated.

You could construct the form's record source SQL statement,
probably in the combo box's AfterUpdate event proceure:

Dim strSQL As String
strSQL = "SELECT * FROM maintable " & _
"WHERE somefield = " & Me.thecomboboxname & _
" ORDER BY whatever"
Me.RecordSource = strSQL

Or, you could modify the main form's record source query to
use the criteria:

Forms!ArcView_Users.thecomboboxname

and then just use:

Me.Requery in the AfterUpdate event.

Another way is to set the main form's Filter property, but I
do not recommend this because it interacts with any subforms
in very strange ways.

 

[deeds]

Hello...I used your code for 2 combo boxes for filter:

Forms!ArcView_Users.thecomboboxname
and then just use:
Me.Requery in the AfterUpdate event.

This works for both of my combo boxes when selections are made...however,
when just one is selected it does not return data. I put the code in for
both fields that I want to filter on. Bottom line is I want to be able to
filter using 1 or both combo boxes as the criteria...any thoughts? Thanks.

I am a novice at this so I hope this makes sense. I am trying to apply a
filter to an unbound combo box within the main form 'ArcView_Users' that will
allow me to select a value and filter out only those records in the main form
that contain that value. The values in the combo come from subform 'Classes'
using this SQL statement in the Row Source of the combo;

Row Source Type - Table/Query
Row Source - SELECT DISTINCT [Classes.CLASS] FROM [Classes] ORDER BY [Classes.
CLASS];
'Which gives me only the unique text values from the Classes' table CLASS
field'

The underlying query for the combo is called-
ClassType

What SQL statement or VBA code do I need to add in order to get the records I
need in the Form based on the value I select in the combo box? Any help
would be appreciated.

You could construct the form's record source SQL statement,
probably in the combo box's AfterUpdate event proceure:

Dim strSQL As String
strSQL = "SELECT * FROM maintable " & _
"WHERE somefield = " & Me.thecomboboxname & _
" ORDER BY whatever"
Me.RecordSource = strSQL

Or, you could modify the main form's record source query to
use the criteria:

Forms!ArcView_Users.thecomboboxname

and then just use:

Me.Requery in the AfterUpdate event.

Another way is to set the main form's Filter property, but I
do not recommend this because it interacts with any subforms
in very strange ways.