Help with Complex Query By Form

  • Thread starter Thread starter William Wisnieski
  • Start date Start date
W

William Wisnieski

Hello Everyone

Access 2000

Looking for some suggestions and advice on how to proceed with this. I hope
its not as complicated as it seems to me right now.

I've got an unbound main form with a subform bound to a query
[qrySearchResults]. What I'd like to do is have the user choose criteria
from four objects on the main form then click a button that displays the
filtered results on the subform.

What I have so far on the main form going from left to right is this:

[lstboxdept] an unbound list box of departments. when the user selects a
department, the next list box [lstmajor] displays the majors associated with
those departments. This works fine.

then an unbound option [opthonors] group with a yes and no button

then another unbound list box of states [lststate].

What makes this more complicated is the fact that the user may only want to
filter on state, or honor and state, or just department and major, etc.

How do I pass the criteria from the four unbound objects to the subform's
query? Should I put the code in the onclick event of the button on the main
form or does the code go in the query grid?

Thank you for any and all help with this. I feel like I'm in over my head
on this one!

William
 
Hi,

What I would do is put a button on the main form called say.. Search.

Then write code to dynamically build a Where clause when the button is
clicked.

This Where clause could then be used to filter the subform.

The code would be somthing like:

Private Sub cmdSearch_Click()
Dim strWhere As String
If Len(Me.lstboxdept & "") > 0 Then
strWhere = "[Dept] = '" & Me.lstboxdept & "'"
End If

If Len(Me.lstmajor & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Majors] = '" & Me.lstmajor & "'"
Else
strWhere = "[Majors] = '" & Me.lstmajor & "'"
End If
End If

If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Honours] = " & Me.opthonors
Else
strWhere = "[Honors] = " & Me.opthonors
End If

If Len(Me.lststate & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.lststate & "'"
Else
strWhere = "[State] = '" & Me.lststate & "'"
End If
End If

Forms(Me.Name)("subformctl").Form.Filter = strWhere
Forms(Me.Name)("subformctl").Form.FilterOn = True

End Sub

HTH

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/
 
Mark,

Thank you very much for your solution. So far so good. It works great.
I'm stuck on two parts however.

The optHonors option group has two choices yes (-1) and no (0) because the
Honors field in the subform is a yes/no field. How would I incorporate the
strWhere for a boolean field?

Also, is there any kind of code I could add to another button to "clear the
filter" so the user could start over if they wanted, or do you recommend I
add the code to the beginning of my exisitng cmdSearch button?

Thanks again for your help!

William


Mark Phillipson said:
Hi,

What I would do is put a button on the main form called say.. Search.

Then write code to dynamically build a Where clause when the button is
clicked.

This Where clause could then be used to filter the subform.

The code would be somthing like:

Private Sub cmdSearch_Click()
Dim strWhere As String
If Len(Me.lstboxdept & "") > 0 Then
strWhere = "[Dept] = '" & Me.lstboxdept & "'"
End If

If Len(Me.lstmajor & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Majors] = '" & Me.lstmajor & "'"
Else
strWhere = "[Majors] = '" & Me.lstmajor & "'"
End If
End If

If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Honours] = " & Me.opthonors
Else
strWhere = "[Honors] = " & Me.opthonors
End If

If Len(Me.lststate & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.lststate & "'"
Else
strWhere = "[State] = '" & Me.lststate & "'"
End If
End If

Forms(Me.Name)("subformctl").Form.Filter = strWhere
Forms(Me.Name)("subformctl").Form.FilterOn = True

End Sub

HTH

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/


William Wisnieski said:
Hello Everyone

Access 2000

Looking for some suggestions and advice on how to proceed with this. I hope
its not as complicated as it seems to me right now.

I've got an unbound main form with a subform bound to a query
[qrySearchResults]. What I'd like to do is have the user choose criteria
from four objects on the main form then click a button that displays the
filtered results on the subform.

What I have so far on the main form going from left to right is this:

[lstboxdept] an unbound list box of departments. when the user selects a
department, the next list box [lstmajor] displays the majors associated with
those departments. This works fine.

then an unbound option [opthonors] group with a yes and no button

then another unbound list box of states [lststate].

What makes this more complicated is the fact that the user may only want to
filter on state, or honor and state, or just department and major, etc.

How do I pass the criteria from the four unbound objects to the subform's
query? Should I put the code in the onclick event of the button on the main
form or does the code go in the query grid?

Thank you for any and all help with this. I feel like I'm in over my head
on this one!

William
Click to expand...
Click to expand...
 
You might try using Filter by Form instead of using a query. This is like
Query by Form without the query.

Con Giacomini
 
Determine which controls have items selected or whatever. In the case
of a listbox, check the ItemsSelected.Count property. Then build your
query on the fly - just stuff it into a string variable. Then assign
the recordsource for the subform to that sql statement.
 
If the Option group is either -1 (True) or 0 (False) then it should work as
per my example.

To removed the filter create another button with the following code for the
On Click event:

Forms(Me.Name)("subformctl").Form.FilterOn = False

HTH

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/



William Wisnieski said:
Mark,

Thank you very much for your solution. So far so good. It works great.
I'm stuck on two parts however.

The optHonors option group has two choices yes (-1) and no (0) because the
Honors field in the subform is a yes/no field. How would I incorporate the
strWhere for a boolean field?

Also, is there any kind of code I could add to another button to "clear the
filter" so the user could start over if they wanted, or do you recommend I
add the code to the beginning of my exisitng cmdSearch button?

Thanks again for your help!

William


Mark Phillipson said:
Hi,

What I would do is put a button on the main form called say.. Search.

Then write code to dynamically build a Where clause when the button is
clicked.

This Where clause could then be used to filter the subform.

The code would be somthing like:

Private Sub cmdSearch_Click()
Dim strWhere As String
If Len(Me.lstboxdept & "") > 0 Then
strWhere = "[Dept] = '" & Me.lstboxdept & "'"
End If

If Len(Me.lstmajor & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Majors] = '" & Me.lstmajor & "'"
Else
strWhere = "[Majors] = '" & Me.lstmajor & "'"
End If
End If

If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Honours] = " & Me.opthonors
Else
strWhere = "[Honors] = " & Me.opthonors
End If

If Len(Me.lststate & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.lststate & "'"
Else
strWhere = "[State] = '" & Me.lststate & "'"
End If
End If

Forms(Me.Name)("subformctl").Form.Filter = strWhere
Forms(Me.Name)("subformctl").Form.FilterOn = True

End Sub

HTH

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/


William Wisnieski said:
Hello Everyone

Access 2000

Looking for some suggestions and advice on how to proceed with this.
Click to expand...
I
hope
its not as complicated as it seems to me right now.

I've got an unbound main form with a subform bound to a query
[qrySearchResults]. What I'd like to do is have the user choose criteria
from four objects on the main form then click a button that displays the
filtered results on the subform.

What I have so far on the main form going from left to right is this:

[lstboxdept] an unbound list box of departments. when the user
Click to expand...
Click to expand...
selects
a
department, the next list box [lstmajor] displays the majors
Click to expand...
associated
with
those departments. This works fine.

then an unbound option [opthonors] group with a yes and no button

then another unbound list box of states [lststate].

What makes this more complicated is the fact that the user may only
Click to expand...
want
to
filter on state, or honor and state, or just department and major, etc.

How do I pass the criteria from the four unbound objects to the subform's
query? Should I put the code in the onclick event of the button on
Click to expand...
the
main
form or does the code go in the query grid?

Thank you for any and all help with this. I feel like I'm in over my head
on this one!

William
Click to expand...
Click to expand...
Click to expand...
 
Thanks again Mark for the excellent help!

Mark Phillipson said:
If the Option group is either -1 (True) or 0 (False) then it should work as
per my example.

To removed the filter create another button with the following code for the
On Click event:

Forms(Me.Name)("subformctl").Form.FilterOn = False

HTH

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/



William Wisnieski said:
Mark,

Thank you very much for your solution. So far so good. It works great.
I'm stuck on two parts however.

The optHonors option group has two choices yes (-1) and no (0) because the
Honors field in the subform is a yes/no field. How would I incorporate the
strWhere for a boolean field?

Also, is there any kind of code I could add to another button to "clear the
filter" so the user could start over if they wanted, or do you recommend I
add the code to the beginning of my exisitng cmdSearch button?

Thanks again for your help!

William


Mark Phillipson said:
Hi,

What I would do is put a button on the main form called say.. Search.

Then write code to dynamically build a Where clause when the button is
clicked.

This Where clause could then be used to filter the subform.

The code would be somthing like:

Private Sub cmdSearch_Click()
Dim strWhere As String
If Len(Me.lstboxdept & "") > 0 Then
strWhere = "[Dept] = '" & Me.lstboxdept & "'"
End If

If Len(Me.lstmajor & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Majors] = '" & Me.lstmajor & "'"
Else
strWhere = "[Majors] = '" & Me.lstmajor & "'"
End If
End If

If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Honours] = " & Me.opthonors
Else
strWhere = "[Honors] = " & Me.opthonors
End If

If Len(Me.lststate & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.lststate & "'"
Else
strWhere = "[State] = '" & Me.lststate & "'"
End If
End If

Forms(Me.Name)("subformctl").Form.Filter = strWhere
Forms(Me.Name)("subformctl").Form.FilterOn = True

End Sub

HTH

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/


Hello Everyone

Access 2000

Looking for some suggestions and advice on how to proceed with this. I
hope
its not as complicated as it seems to me right now.

I've got an unbound main form with a subform bound to a query
[qrySearchResults]. What I'd like to do is have the user choose criteria
from four objects on the main form then click a button that displays the
filtered results on the subform.

What I have so far on the main form going from left to right is this:

[lstboxdept] an unbound list box of departments. when the user
Click to expand...
selects
a
department, the next list box [lstmajor] displays the majors associated
with
those departments. This works fine.

then an unbound option [opthonors] group with a yes and no button

then another unbound list box of states [lststate].

What makes this more complicated is the fact that the user may only want
to
filter on state, or honor and state, or just department and major, etc.

How do I pass the criteria from the four unbound objects to the subform's
query? Should I put the code in the onclick event of the button on the
main
form or does the code go in the query grid?

Thank you for any and all help with this. I feel like I'm in over my
head
on this one!

William
Click to expand...
Click to expand...
Click to expand...
 
Hello Again,

Mark, I guess I spoke too soon. How could I get it to work with a number
field (Long Integer)?

Thanks,

William


Mark Phillipson said:
If the Option group is either -1 (True) or 0 (False) then it should work as
per my example.

To removed the filter create another button with the following code for the
On Click event:

Forms(Me.Name)("subformctl").Form.FilterOn = False

HTH

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/



William Wisnieski said:
Mark,

Thank you very much for your solution. So far so good. It works great.
I'm stuck on two parts however.

The optHonors option group has two choices yes (-1) and no (0) because the
Honors field in the subform is a yes/no field. How would I incorporate the
strWhere for a boolean field?

Also, is there any kind of code I could add to another button to "clear the
filter" so the user could start over if they wanted, or do you recommend I
add the code to the beginning of my exisitng cmdSearch button?

Thanks again for your help!

William


Mark Phillipson said:
Hi,

What I would do is put a button on the main form called say.. Search.

Then write code to dynamically build a Where clause when the button is
clicked.

This Where clause could then be used to filter the subform.

The code would be somthing like:

Private Sub cmdSearch_Click()
Dim strWhere As String
If Len(Me.lstboxdept & "") > 0 Then
strWhere = "[Dept] = '" & Me.lstboxdept & "'"
End If

If Len(Me.lstmajor & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Majors] = '" & Me.lstmajor & "'"
Else
strWhere = "[Majors] = '" & Me.lstmajor & "'"
End If
End If

If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Honours] = " & Me.opthonors
Else
strWhere = "[Honors] = " & Me.opthonors
End If

If Len(Me.lststate & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.lststate & "'"
Else
strWhere = "[State] = '" & Me.lststate & "'"
End If
End If

Forms(Me.Name)("subformctl").Form.Filter = strWhere
Forms(Me.Name)("subformctl").Form.FilterOn = True

End Sub

HTH

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/


Hello Everyone

Access 2000

Looking for some suggestions and advice on how to proceed with this. I
hope
its not as complicated as it seems to me right now.

I've got an unbound main form with a subform bound to a query
[qrySearchResults]. What I'd like to do is have the user choose criteria
from four objects on the main form then click a button that displays the
filtered results on the subform.

What I have so far on the main form going from left to right is this:

[lstboxdept] an unbound list box of departments. when the user
Click to expand...
selects
a
department, the next list box [lstmajor] displays the majors associated
with
those departments. This works fine.

then an unbound option [opthonors] group with a yes and no button

then another unbound list box of states [lststate].

What makes this more complicated is the fact that the user may only want
to
filter on state, or honor and state, or just department and major, etc.

How do I pass the criteria from the four unbound objects to the subform's
query? Should I put the code in the onclick event of the button on the
main
form or does the code go in the query grid?

Thank you for any and all help with this. I feel like I'm in over my
head
on this one!

William
Click to expand...
Click to expand...
Click to expand...
 
As a follow up to my previous message regarding the Integer field--I thought
it might be helpful if I post the specific code that's now giving me a
run-time error 2001 "you cancelled the previous operation." EMPLID is long
integer.


If Len(Me.txtEMPLID & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [EMPLID] = '" & Me.txtEMPLID & "'"
Else
strWhere = "[EMPLID] = '" & Me.txtEMPLID & "'"
End If
End If


William Wisnieski said:
Hello Again,

Mark, I guess I spoke too soon. How could I get it to work with a number
field (Long Integer)?

Thanks,

William


Mark Phillipson said:
If the Option group is either -1 (True) or 0 (False) then it should work as
per my example.

To removed the filter create another button with the following code for the
On Click event:

Forms(Me.Name)("subformctl").Form.FilterOn = False

HTH

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/



incorporate
the "clear
the
Click to expand...
recommend
I
add the code to the beginning of my exisitng cmdSearch button?

Thanks again for your help!

William


Hi,

What I would do is put a button on the main form called say.. Search.

Then write code to dynamically build a Where clause when the button is
clicked.

This Where clause could then be used to filter the subform.

The code would be somthing like:

Private Sub cmdSearch_Click()
Dim strWhere As String
If Len(Me.lstboxdept & "") > 0 Then
strWhere = "[Dept] = '" & Me.lstboxdept & "'"
End If

If Len(Me.lstmajor & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Majors] = '" & Me.lstmajor & "'"
Else
strWhere = "[Majors] = '" & Me.lstmajor & "'"
End If
End If

If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Honours] = " & Me.opthonors
Else
strWhere = "[Honors] = " & Me.opthonors
End If

If Len(Me.lststate & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.lststate & "'"
Else
strWhere = "[State] = '" & Me.lststate & "'"
End If
End If

Forms(Me.Name)("subformctl").Form.Filter = strWhere
Forms(Me.Name)("subformctl").Form.FilterOn = True

End Sub

HTH

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/


Hello Everyone

Access 2000

Looking for some suggestions and advice on how to proceed with
Click to expand...
this.
I
hope
its not as complicated as it seems to me right now.

I've got an unbound main form with a subform bound to a query
[qrySearchResults]. What I'd like to do is have the user choose
criteria
from four objects on the main form then click a button that
Click to expand...
displays
the
filtered results on the subform.

What I have so far on the main form going from left to right is this:

[lstboxdept] an unbound list box of departments. when the user selects
a
department, the next list box [lstmajor] displays the majors associated
with
those departments. This works fine.

then an unbound option [opthonors] group with a yes and no button

then another unbound list box of states [lststate].

What makes this more complicated is the fact that the user may
Click to expand...
only
want
to
filter on state, or honor and state, or just department and major, etc.

How do I pass the criteria from the four unbound objects to the
subform's
query? Should I put the code in the onclick event of the button
Click to expand...
on
the
main
form or does the code go in the query grid?

Thank you for any and all help with this. I feel like I'm in
Click to expand...
Click to expand...
over
my
Click to expand...
Click to expand...
 
To make it work with numbers do not use the apostrophe (')

i.e.

strWhere = "[EMPLID] = " & Me.txtEMPLID

HTH

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/


William Wisnieski said:
As a follow up to my previous message regarding the Integer field--I thought
it might be helpful if I post the specific code that's now giving me a
run-time error 2001 "you cancelled the previous operation." EMPLID is long
integer.


If Len(Me.txtEMPLID & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [EMPLID] = '" & Me.txtEMPLID & "'"
Else
strWhere = "[EMPLID] = '" & Me.txtEMPLID & "'"
End If
End If


William Wisnieski said:
Hello Again,

Mark, I guess I spoke too soon. How could I get it to work with a number
field (Long Integer)?

Thanks,

William


work
as for
the because
the recommend
Click to expand...
button
is
clicked.

This Where clause could then be used to filter the subform.

The code would be somthing like:

Private Sub cmdSearch_Click()
Dim strWhere As String
If Len(Me.lstboxdept & "") > 0 Then
strWhere = "[Dept] = '" & Me.lstboxdept & "'"
End If

If Len(Me.lstmajor & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Majors] = '" & Me.lstmajor
Click to expand...
&
"'"
Else
strWhere = "[Majors] = '" & Me.lstmajor & "'"
End If
End If

If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Honours] = " & Me.opthonors
Else
strWhere = "[Honors] = " & Me.opthonors
End If

If Len(Me.lststate & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.lststate
Click to expand...
&
"'"
Else
strWhere = "[State] = '" & Me.lststate & "'"
End If
End If

Forms(Me.Name)("subformctl").Form.Filter = strWhere
Forms(Me.Name)("subformctl").Form.FilterOn = True

End Sub

HTH

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/


Hello Everyone

Access 2000

Looking for some suggestions and advice on how to proceed with this.
I
hope
its not as complicated as it seems to me right now.

I've got an unbound main form with a subform bound to a query
[qrySearchResults]. What I'd like to do is have the user choose
criteria
from four objects on the main form then click a button that displays
the
filtered results on the subform.

What I have so far on the main form going from left to right is this:

[lstboxdept] an unbound list box of departments. when the user
selects
a
department, the next list box [lstmajor] displays the majors
associated
with
those departments. This works fine.

then an unbound option [opthonors] group with a yes and no button

then another unbound list box of states [lststate].

What makes this more complicated is the fact that the user may only
want
to
filter on state, or honor and state, or just department and major,
etc.

How do I pass the criteria from the four unbound objects to the
subform's
query? Should I put the code in the onclick event of the button on
the
main
form or does the code go in the query grid?

Thank you for any and all help with this. I feel like I'm in
Click to expand...
over
my
head
on this one!

William
Click to expand...
Click to expand...
Click to expand...
 
Back
Top