Search with checkboxes

[Alex]

Hi all!
Here's my question:

I have created 1 table (Clients) with 10 fields (ClientID, Name, Adress,
Phone etc...).
Next, there is a form (Clients) that allows me to fill data into the
relevant fields of the table (ClientID, Name, Adress, Phone etc...)

Just below these 10 fields, there are 5 check boxes. They are all
positioned under the text label "Client wished to contact via:" that allows
me to select one or more of the following check boxes "E-mail, Post, Tel,
Fax, Telex".

So far, so good..

The thing I would like to do is to create a form which will act as a search
form. Inside this new form, I want to be able to have the above mentioned 5
check boxes (Under the text label "Please click on the appropriate search
criteria") and to be able to select one, two or all 5 of them. Finally,
pressing the "Search now" button, a report should be generated with the
records-clients that have selected to be contacted via the selected
methods-check boxes.

Can anyone pls tell me which is the easier method to do this due to that I
am new using Access...

Sorry for the long description... Any guidance will be highly appreciated!

Tnx, Alex

 

[Ken Sheridan]

Alex:

Create a report, rptClients say, based on the Clients table. By default
this will show all clients, so you need to filter it when its opened from
your unbound search form. Quite how you build this filter depends on whether
you want to show clients who match all of the search criteria, e.g. who, if
you've checked e-mail and tel in the search form, have indicated they want to
be contacted by both of these methods, or have indicated they want to be
contacted either by only one or by both of these methods. Logically the
difference is a Boolean AND or a Boolean OR.

So, if you want to show those who have indicated they want to be contacted
by all of the methods checked on the search form the code in the Click event
procedure of your button would be like this:

ConMESSAGE = "No criteria selected. Show all clients?"
Dim strFilter As String

' examine each check box and built a filter string
' using Boolean AND operators
If [chkE-Mail] Then
strFilter = strFilter & " And [E=Mail]"
End If

If [chkPost] Then
strFilter = strFilter & " And [Post]"
End If

If [chkTel] Then
strFilter = strFilter & " And [Tel]"
End If

If [chkFax] Then
strFilter = strFilter & " And [Fax]"
End If

If [chkTelex] Then
strFilter = strFilter & " And [Telex]"
End If

' if no criteria have been selected then get
' user confirmation to show all clients or not
If len(strFilter) = 0 Then
If MsgBox(conMESSAGE,vbQuestion + vbYesNo, "Warning") = vbNo
' don't open report
Else
' open report unfiltered
DoCmd.OpenReport "rptClients", _
View:=acViewPreview
Else
' first strip off leading ' And '
strFilter = Mid(strFilter,6)
' then open report filtered to selected criteria
DoCmd.OpenReport "rptClients", _
View:=acViewPreview,
WhereCondition:=strFilter
End If

In the above chkEmail etc and E-Mail etc represent the names of the check
boxes on the search form and the corresponding Boolean (Yes/No) fields in the
table.

For the second scenario, where you want to show those who have indicated
they want to be contacted by any one of the methods checked on the search
form you'd simply replace each AND operator with an OR operator, and strip
off the first 4 rather than 5 characters (' Or ' rather than ' And ') like so:

ConMESSAGE = "No criteria selected. Show all clients?"
Dim strFilter As String

' examine each check box and built a filter string
' using Boolean OR operators
If [chkE-Mail] Then
strFilter = strFilter & " Or [E=Mail]"
End If

If [chkPost] Then
strFilter = strFilter & " Or [Post]"
End If

If [chkTel] Then
strFilter = strFilter & " Or [Tel]"
End If

If [chkFax] Then
strFilter = strFilter & " Or [Fax]"
End If

If [chkTelex] Then
strFilter = strFilter & " Or [Telex]"
End If

' if no criteria have been selected then get
' user confirmation to show all clients or not
If len(strFilter) = 0 Then
If MsgBox(conMESSAGE,vbQuestion + vbYesNo, "Warning") = vbNo
' don't open report
Else
' open report unfiltered
DoCmd.OpenReport "rptClients", _
View:=acViewPreview
Else
' first strip off leading 'Or '
strFilter = Mid(strFilter,5)
' then open report filtered to selected criteria
DoCmd.OpenReport "rptClients", _
View:=acViewPreview,
WhereCondition:=strFilter
End If

Ken Sheridan
Stafford, England

 

[Alex]

Dear Ken,
tnx for the reply.

I'll start working according to your
guidance, and will let you know as
soon as I have a result.

B regards


Ο χÏήστης "Ken Sheridan" έγγÏαψε:

Alex:

Create a report, rptClients say, based on the Clients table. By default
this will show all clients, so you need to filter it when its opened from
your unbound search form. Quite how you build this filter depends on whether
you want to show clients who match all of the search criteria, e.g. who, if
you've checked e-mail and tel in the search form, have indicated they want to
be contacted by both of these methods, or have indicated they want to be
contacted either by only one or by both of these methods. Logically the
difference is a Boolean AND or a Boolean OR.

So, if you want to show those who have indicated they want to be contacted
by all of the methods checked on the search form the code in the Click event
procedure of your button would be like this:

ConMESSAGE = "No criteria selected. Show all clients?"
Dim strFilter As String

' examine each check box and built a filter string
' using Boolean AND operators
If [chkE-Mail] Then
strFilter = strFilter & " And [E=Mail]"
End If

If [chkPost] Then
strFilter = strFilter & " And [Post]"
End If

If [chkTel] Then
strFilter = strFilter & " And [Tel]"
End If

If [chkFax] Then
strFilter = strFilter & " And [Fax]"
End If

If [chkTelex] Then
strFilter = strFilter & " And [Telex]"
End If

' if no criteria have been selected then get
' user confirmation to show all clients or not
If len(strFilter) = 0 Then
If MsgBox(conMESSAGE,vbQuestion + vbYesNo, "Warning") = vbNo
' don't open report
Else
' open report unfiltered
DoCmd.OpenReport "rptClients", _
View:=acViewPreview
Else
' first strip off leading ' And '
strFilter = Mid(strFilter,6)
' then open report filtered to selected criteria
DoCmd.OpenReport "rptClients", _
View:=acViewPreview,
WhereCondition:=strFilter
End If

In the above chkEmail etc and E-Mail etc represent the names of the check
boxes on the search form and the corresponding Boolean (Yes/No) fields in the
table.

For the second scenario, where you want to show those who have indicated
they want to be contacted by any one of the methods checked on the search
form you'd simply replace each AND operator with an OR operator, and strip
off the first 4 rather than 5 characters (' Or ' rather than ' And ') like so:

ConMESSAGE = "No criteria selected. Show all clients?"
Dim strFilter As String

' examine each check box and built a filter string
' using Boolean OR operators
If [chkE-Mail] Then
strFilter = strFilter & " Or [E=Mail]"
End If

If [chkPost] Then
strFilter = strFilter & " Or [Post]"
End If

If [chkTel] Then
strFilter = strFilter & " Or [Tel]"
End If

If [chkFax] Then
strFilter = strFilter & " Or [Fax]"
End If

If [chkTelex] Then
strFilter = strFilter & " Or [Telex]"
End If

' if no criteria have been selected then get
' user confirmation to show all clients or not
If len(strFilter) = 0 Then
If MsgBox(conMESSAGE,vbQuestion + vbYesNo, "Warning") = vbNo
' don't open report
Else
' open report unfiltered
DoCmd.OpenReport "rptClients", _
View:=acViewPreview
Else
' first strip off leading 'Or '
strFilter = Mid(strFilter,5)
' then open report filtered to selected criteria
DoCmd.OpenReport "rptClients", _
View:=acViewPreview,
WhereCondition:=strFilter
End If

Ken Sheridan
Stafford, England

Hi all!
Here's my question:

I have created 1 table (Clients) with 10 fields (ClientID, Name, Adress,
Phone etc...).
Next, there is a form (Clients) that allows me to fill data into the
relevant fields of the table (ClientID, Name, Adress, Phone etc...)

Just below these 10 fields, there are 5 check boxes. They are all
positioned under the text label "Client wished to contact via:" that allows
me to select one or more of the following check boxes "E-mail, Post, Tel,
Fax, Telex".

So far, so good..

The thing I would like to do is to create a form which will act as a search
form. Inside this new form, I want to be able to have the above mentioned 5
check boxes (Under the text label "Please click on the appropriate search
criteria") and to be able to select one, two or all 5 of them. Finally,
pressing the "Search now" button, a report should be generated with the
records-clients that have selected to be contacted via the selected
methods-check boxes.

Can anyone pls tell me which is the easier method to do this due to that I
am new using Access...

Sorry for the long description... Any guidance will be highly appreciated!

Tnx, Alex

 

[Alex]

Dear Ken,
when pressing the button which has the 1st scenario's code (OnClick Event
procedure), I get the following message:

Compile Error
Syntax Error, and with red color the line which writes:
If MsgBox(conMESSAGE,vbQuestion + vbYesNo, "Warning") = vbNo

I would appreciate if you could pls provide me some info regarding this
error...

Tnx again

Ο χÏήστης "Ken Sheridan" έγγÏαψε:

Alex:

Create a report, rptClients say, based on the Clients table. By default
this will show all clients, so you need to filter it when its opened from
your unbound search form. Quite how you build this filter depends on whether
you want to show clients who match all of the search criteria, e.g. who, if
you've checked e-mail and tel in the search form, have indicated they want to
be contacted by both of these methods, or have indicated they want to be
contacted either by only one or by both of these methods. Logically the
difference is a Boolean AND or a Boolean OR.

So, if you want to show those who have indicated they want to be contacted
by all of the methods checked on the search form the code in the Click event
procedure of your button would be like this:

ConMESSAGE = "No criteria selected. Show all clients?"
Dim strFilter As String

' examine each check box and built a filter string
' using Boolean AND operators
If [chkE-Mail] Then
strFilter = strFilter & " And [E=Mail]"
End If

If [chkPost] Then
strFilter = strFilter & " And [Post]"
End If

If [chkTel] Then
strFilter = strFilter & " And [Tel]"
End If

If [chkFax] Then
strFilter = strFilter & " And [Fax]"
End If

If [chkTelex] Then
strFilter = strFilter & " And [Telex]"
End If

' if no criteria have been selected then get
' user confirmation to show all clients or not
If len(strFilter) = 0 Then
If MsgBox(conMESSAGE,vbQuestion + vbYesNo, "Warning") = vbNo
' don't open report
Else
' open report unfiltered
DoCmd.OpenReport "rptClients", _
View:=acViewPreview
Else
' first strip off leading ' And '
strFilter = Mid(strFilter,6)
' then open report filtered to selected criteria
DoCmd.OpenReport "rptClients", _
View:=acViewPreview,
WhereCondition:=strFilter
End If

In the above chkEmail etc and E-Mail etc represent the names of the check
boxes on the search form and the corresponding Boolean (Yes/No) fields in the
table.

For the second scenario, where you want to show those who have indicated
they want to be contacted by any one of the methods checked on the search
form you'd simply replace each AND operator with an OR operator, and strip
off the first 4 rather than 5 characters (' Or ' rather than ' And ') like so:

ConMESSAGE = "No criteria selected. Show all clients?"
Dim strFilter As String

' examine each check box and built a filter string
' using Boolean OR operators
If [chkE-Mail] Then
strFilter = strFilter & " Or [E=Mail]"
End If

If [chkPost] Then
strFilter = strFilter & " Or [Post]"
End If

If [chkTel] Then
strFilter = strFilter & " Or [Tel]"
End If

If [chkFax] Then
strFilter = strFilter & " Or [Fax]"
End If

If [chkTelex] Then
strFilter = strFilter & " Or [Telex]"
End If

' if no criteria have been selected then get
' user confirmation to show all clients or not
If len(strFilter) = 0 Then
If MsgBox(conMESSAGE,vbQuestion + vbYesNo, "Warning") = vbNo
' don't open report
Else
' open report unfiltered
DoCmd.OpenReport "rptClients", _
View:=acViewPreview
Else
' first strip off leading 'Or '
strFilter = Mid(strFilter,5)
' then open report filtered to selected criteria
DoCmd.OpenReport "rptClients", _
View:=acViewPreview,
WhereCondition:=strFilter
End If

Ken Sheridan
Stafford, England

Hi all!
Here's my question:

I have created 1 table (Clients) with 10 fields (ClientID, Name, Adress,
Phone etc...).
Next, there is a form (Clients) that allows me to fill data into the
relevant fields of the table (ClientID, Name, Adress, Phone etc...)

Just below these 10 fields, there are 5 check boxes. They are all
positioned under the text label "Client wished to contact via:" that allows
me to select one or more of the following check boxes "E-mail, Post, Tel,
Fax, Telex".

So far, so good..

The thing I would like to do is to create a form which will act as a search
form. Inside this new form, I want to be able to have the above mentioned 5
check boxes (Under the text label "Please click on the appropriate search
criteria") and to be able to select one, two or all 5 of them. Finally,
pressing the "Search now" button, a report should be generated with the
records-clients that have selected to be contacted via the selected
methods-check boxes.

Can anyone pls tell me which is the easier method to do this due to that I
am new using Access...

Sorry for the long description... Any guidance will be highly appreciated!

Tnx, Alex

 

[John Spencer]

You are missing the word THEN at the end of the line.


If MsgBox(conMESSAGE,vbQuestion + vbYesNo, "Warning") = vbNo THEN
'Don't open the report
Else
...
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Dear Ken,
when pressing the button which has the 1st scenario's code (OnClick Event
procedure), I get the following message:

Compile Error
Syntax Error, and with red color the line which writes:
If MsgBox(conMESSAGE,vbQuestion + vbYesNo, "Warning") = vbNo

I would appreciate if you could pls provide me some info regarding this
error...

Tnx again

Ο χÏήστης "Ken Sheridan" έγγÏαψε:

Alex:

Create a report, rptClients say, based on the Clients table. By default
this will show all clients, so you need to filter it when its opened from
your unbound search form. Quite how you build this filter depends on whether
you want to show clients who match all of the search criteria, e.g. who, if
you've checked e-mail and tel in the search form, have indicated they want to
be contacted by both of these methods, or have indicated they want to be
contacted either by only one or by both of these methods. Logically the
difference is a Boolean AND or a Boolean OR.

So, if you want to show those who have indicated they want to be contacted
by all of the methods checked on the search form the code in the Click event
procedure of your button would be like this:

ConMESSAGE = "No criteria selected. Show all clients?"
Dim strFilter As String

' examine each check box and built a filter string
' using Boolean AND operators
If [chkE-Mail] Then
strFilter = strFilter & " And [E=Mail]"
End If

If [chkPost] Then
strFilter = strFilter & " And [Post]"
End If

If [chkTel] Then
strFilter = strFilter & " And [Tel]"
End If

If [chkFax] Then
strFilter = strFilter & " And [Fax]"
End If

If [chkTelex] Then
strFilter = strFilter & " And [Telex]"
End If

' if no criteria have been selected then get
' user confirmation to show all clients or not
If len(strFilter) = 0 Then
If MsgBox(conMESSAGE,vbQuestion + vbYesNo, "Warning") = vbNo
' don't open report
Else
' open report unfiltered
DoCmd.OpenReport "rptClients", _
View:=acViewPreview
Else
' first strip off leading ' And '
strFilter = Mid(strFilter,6)
' then open report filtered to selected criteria
DoCmd.OpenReport "rptClients", _
View:=acViewPreview,
WhereCondition:=strFilter
End If

In the above chkEmail etc and E-Mail etc represent the names of the check
boxes on the search form and the corresponding Boolean (Yes/No) fields in the
table.

For the second scenario, where you want to show those who have indicated
they want to be contacted by any one of the methods checked on the search
form you'd simply replace each AND operator with an OR operator, and strip
off the first 4 rather than 5 characters (' Or ' rather than ' And ') like so:

ConMESSAGE = "No criteria selected. Show all clients?"
Dim strFilter As String

' examine each check box and built a filter string
' using Boolean OR operators
If [chkE-Mail] Then
strFilter = strFilter & " Or [E=Mail]"
End If

If [chkPost] Then
strFilter = strFilter & " Or [Post]"
End If

If [chkTel] Then
strFilter = strFilter & " Or [Tel]"
End If

If [chkFax] Then
strFilter = strFilter & " Or [Fax]"
End If

If [chkTelex] Then
strFilter = strFilter & " Or [Telex]"
End If

' if no criteria have been selected then get
' user confirmation to show all clients or not
If len(strFilter) = 0 Then
If MsgBox(conMESSAGE,vbQuestion + vbYesNo, "Warning") = vbNo
' don't open report
Else
' open report unfiltered
DoCmd.OpenReport "rptClients", _
View:=acViewPreview
Else
' first strip off leading 'Or '
strFilter = Mid(strFilter,5)
' then open report filtered to selected criteria
DoCmd.OpenReport "rptClients", _
View:=acViewPreview,
WhereCondition:=strFilter
End If

Ken Sheridan
Stafford, England

Hi all!
Here's my question:

I have created 1 table (Clients) with 10 fields (ClientID, Name, Adress,
Phone etc...).
Next, there is a form (Clients) that allows me to fill data into the
relevant fields of the table (ClientID, Name, Adress, Phone etc...)

Just below these 10 fields, there are 5 check boxes. They are all
positioned under the text label "Client wished to contact via:" that allows
me to select one or more of the following check boxes "E-mail, Post, Tel,
Fax, Telex".

So far, so good..

The thing I would like to do is to create a form which will act as a search
form. Inside this new form, I want to be able to have the above mentioned 5
check boxes (Under the text label "Please click on the appropriate search
criteria") and to be able to select one, two or all 5 of them. Finally,
pressing the "Search now" button, a report should be generated with the
records-clients that have selected to be contacted via the selected
methods-check boxes.

Can anyone pls tell me which is the easier method to do this due to that I
am new using Access...

Sorry for the long description... Any guidance will be highly appreciated!

Tnx, Alex

 

[Alex]

Thank you very much John.

Still getting "compile error, Else without If".
Regarding the foll "Else"...

DoCmd.OpenReport "rptClients", _
View:=acViewPreview
Else
' first strip off leading ' And '


Any further assistance pls?

 

[John Spencer]

' if no criteria have been selected then get
' user confirmation to show all clients or not
If len(strFilter) = 0 Then
If MsgBox(conMESSAGE,vbQuestion + vbYesNo, "Warning") = vbNo THEN
' don't open report
Else
' open report unfiltered
DoCmd.OpenReport "rptClients", _
View:=acViewPreview
END IF 'MISSING <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Else
' first strip off leading ' And '
strFilter = Mid(strFilter,6)
' then open report filtered to selected criteria
DoCmd.OpenReport "rptClients", _
View:=acViewPreview,
WhereCondition:=strFilter
End If

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Alex]

John, thank you again for your valuable help,
but I am again prompted to correct a syntax error
regarding the WhereCondition line:

View:=acViewPreview, WhereCondition:=strFilter
End If

Why is this happening?

Appreciate your assistance...

 

[John Spencer]

This should have a line continuation (space followed by underline)

View:=acViewPreview, _
WhereCondition:=strFilter

OR it should all be on one line

View:=acViewPreview, WhereCondition:=strFilter

Sorry, I should have checked the entire routine instead of stopping when
I find one error in it.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================