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
procedu
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
Natalie wrote:
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
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1