Using check boxes to select criteria to run query

[Natalie]

I need some help. I have 5 check boxes. There is one for each country of

Canada
UK
USA
France
Germany

And i want the users to be able to tick which countries they wish to view
data for. The data is in a table with a column for country.

Please help as I am stuck.

Many Many thank you.

Natalie

 

[KARL DEWEY]

Rather than checkboxes I would use an Option Group set to display checkboxes.
Only one can be selected at a time but you can include one to show all.

When using the Option Group create a table --
tblCountry --
Num Country
1 Canada
2 UK
3 USA
4 France
5 Germany
6 All

In the query WHERE --
WHERE YourTable.Country = tblCountry.Country AND [Forms]![YourForm]![Frame0]
= tblCountry.NUM

For checkboxes --
In the query WHERE --
WHERE YourTable.Country = IIF([Forms]![YourForm]![CBO1] = -1, "Canada", "")
OR YourTable.Country = IIF([Forms]![YourForm]![CBO2] = -1, "UK", "") OR
YourTable.Country = IIF([Forms]![YourForm]![CBO3] = -1, "USA", "") OR
YourTable.Country = IIF([Forms]![YourForm]![CBO4] = -1, "France", "") OR
YourTable.Country = IIF([Forms]![YourForm]![CBO5] = -1, "Germany", "")

 

[Wolfgang Kais]

Hello Natalie.

I need some help. I have 5 check boxes.
There is one for each country of

Canada
UK
USA
France
Germany

And i want the users to be able to tick which countries they
wish to view data for. The data is in a table with a column
for country.

Since the DoCmd.OpenQuery method does not support passing criteria
to the query, I suggest to create a form that displays all records
of your table. This can be opened in DataSheet view, so it looks
like a normal datasheet.
The 5 chceckboxes should have a default value of False, such that
they will not contain a Null-value. Making some assumptions about
the names of the combo boxes and the name of the form with the data,
I came up with the following code for the OK-button of the dialog:


Private Sub OKButton_Click()

Dim stCountries As String
Dim stDocName As String
Dim stLinkCriteria As String

' generate a list of selected countries
If Me.CanadaCheckBox Then stCountries = stCountries & ", 'Canada'"
If Me.UnitedKingdomCheckBox Then stCountries = stCountries + ", 'UK'"
If Me.UnitedStatesCheckBox Then stCountries = stCountries & ", 'USA'"
If Me.FranceCheckBox Then stCountries = stCountries & ", 'France'"
If Me.GermanyCheckBox Then stCountries = stCountries & ", 'Germany'"

'open the data form if at least one country was selected
If stCountries = vbNullString Then
MsgBox "Please select at least one country.", vbExclamation
Else
stCountries = Mid$(stCountries, 3) ' remove the leading ", "
stDocName = "frmDataForm"
' create a WHERE clause and open the form as datasheet (FormDS)
stLinkCriteria = "[Country] IN " & "(" & stCountries & ")"
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria
'close this dialog form
DoCmd.Close acForm, Me.Name
End If

End Sub

 

[KenSheridan via AccessMonster.com]

Natalie:

I'd suggest a different approach which is driven by the data rather than by
the form definition. This would be to use a multiselect list box of the
countries. You'll need to create a table Countries with a primary key column
Country first, if you don't have one already, and insert one row for each
country. You should also create a relationship between the Countries table
and your main table on the Country columns and enforce referential integrity
and cascade updates. This ensures only valid country names can be inserted
in the main table, and if it should prove necessary to change a country name,
changing the one row in Countries will automatically change the name in the
matching rows in the main table.

You can then select as few or as many countries from the list and open a form
or report based on your main table with a button on the form.

Set up the list box like this:

For its RowSource property:

SELECT Country FROM Countries ORDER BY Country;

For other properties:

Name: lstCountries
MultiSelect: Simple or Extended as preferred.

Add a button to the form to open the form or report (a report called
YourReport is assumed in this example) with the following in its Click event
procedure:

Dim varItem As Variant
Dim strCountryList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstCountries

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCountryList = strCountryList & ",""" & ctrl.ItemData(varItem)
& """"
Next varItem

' remove leading comma
strCountryList = Mid(strCountryList, 2)

strCriteria = "Country In(" & strCountryList & ")"

DoCmd.OpenReport "YourReport", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No countries selected", vbInformation, "Warning"
End If

If a form is to be opened rather than a report it would merely require a
simple amendment of the above code:

DoCmd.OpenForm "YourForm", _
WhereCondition:=strCriteria

With this approach the countries which can be selected are determined by the
data in the Countries table, so any amendment to the list of countries merely
requires the data in that table to be updated. By using check boxes or an
option group any amendment to the list of countries would require a change to
the form definition and to the code.

Ken Sheridan
Stafford, England