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