A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using check boxes to select criteria to run query



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2010, 03:36 PM posted to microsoft.public.access.queries
Natalie
external usenet poster
 
Posts: 212
Default 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  
Old May 28th, 2010, 05:26 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old May 30th, 2010, 11:01 AM posted to microsoft.public.access.queries
Wolfgang Kais[_4_]
external usenet poster
 
Posts: 18
Default 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  
Old May 31st, 2010, 01:10 AM posted to microsoft.public.access.queries
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:52 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.