Parameter forms with a twist...

[Guest]

I have a form that is used for searching a data table. I would like to have
five different search options in drop-down lists in the forms Header. How
would I construct a query that would take at least one of these values and
populate its corresponding data into the form? I tried using OR operators in
between my Form! Statements, but that bases everything off of the first
parameter and I want to give a user the functionality to select which
particular search optionee wants to search with. Any ideas?

 

[John Vinson]

I have a form that is used for searching a data table. I would like to have
five different search options in drop-down lists in the forms Header. How
would I construct a query that would take at least one of these values and
populate its corresponding data into the form? I tried using OR operators in
between my Form! Statements, but that bases everything off of the first
parameter and I want to give a user the functionality to select which
particular search optionee wants to search with. Any ideas?

Two options:

1. Use a criterion of

=[Forms]![YourForm]![cboA] OR [Forms]![YourForm]![cboA] IS NULL

for each field you're searching; note that the query design grid will
look VERY wierd after you save and reopen this query but with five
fields it should still work (I wouldn't go any higher).

2. Use the AfterUpdate event of each combo box to add its criterion to
the form's Filter property:

Private Sub cboA_AfterUpdate()
If Me!cboA & "" <> "" Then
Me.Filter = (Me.Filter + " AND") & "[FieldA] = " & Me!cboA
Me.FilterOn = True
End Sub


John W. Vinson[MVP]

 

[Guest]

Thanks John, but can you explain option 2?

I have a form that is used for searching a data table. I would like to have
five different search options in drop-down lists in the forms Header. How
would I construct a query that would take at least one of these values and
populate its corresponding data into the form? I tried using OR operators in
between my Form! Statements, but that bases everything off of the first
parameter and I want to give a user the functionality to select which
particular search optionee wants to search with. Any ideas?

Two options:

1. Use a criterion of

=[Forms]![YourForm]![cboA] OR [Forms]![YourForm]![cboA] IS NULL

for each field you're searching; note that the query design grid will
look VERY wierd after you save and reopen this query but with five
fields it should still work (I wouldn't go any higher).

2. Use the AfterUpdate event of each combo box to add its criterion to
the form's Filter property:

Private Sub cboA_AfterUpdate()
If Me!cboA & "" <> "" Then
Me.Filter = (Me.Filter + " AND") & "[FieldA] = " & Me!cboA
Me.FilterOn = True
End Sub


John W. Vinson[MVP]