If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using check boxes to select criteria to run query
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 |
#2
|
|||
|
|||
Using check boxes to select criteria to run query
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", "") -- Build a little, test a little. "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 |
#3
|
|||
|
|||
Using check boxes to select criteria to run query
Hello Natalie.
"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. 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 -- Regards, Wolfgang |
#4
|
|||
|
|||
Using check boxes to select criteria to run query
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 |
Thread Tools | |
Display Modes | |
|
|