Option Groups

[Fee08]

Hi,
I'm a newbie with working with option groups and am trying to achieve the
following:

I have a form that asks users to enter the start and end date for the
various reports they need to create. The queries pick up these dates and run
the reports fine.

However, I have recently created a new report and need to be able to choose
the employee/s that will populate the report. What I have so far is an
option group that has two options 1) all staff and 2) select staff ... This
is where I run into trouble. What I want to happen is when the user chooses
option 2 a list box appears with check boxes beside all the active staff.
The user can then check the boxes of the staff they required and press the
run report button. This will then populate the report with only those staff
members checked.

I've looked through the posts and have been unable to find an answer to this
question, however, if you know of where the answer is, please direct me
there, or if you know of a better way to do this, please let me know.
Kind regards
Fee.

 

[Tom Wickerath]

I don't know that you can display a check box within a list box. I believe
you would need to use a subform instead.

...or if you know of a better way to do this, please let me know.

I won't claim that it is better, but here's how I would approach the issue.
I would use a multi-select list box to allow a user to pick one or more staff
people. If they made no selections in the list box, then all staff members
would be included. You do not need the option group to accomplish this
functionality. I have some QBF (Query by Form) examples that show how to
iterate the .ItemsSelected property of a multi-select list box. The items
selected become part of an IN clause in the WHERE part of a SQL statement. I
think you could use very similar logic for your report.

Easiest example:
http://www.accessmvp.com/TWickerath/downloads/elements.zip

A bit more involved example:
http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip

An example using the Northwind sample database
http://www.seattleaccess.org/downloads.htm
--->See download for Feb. 12, 2008. This sample includes a Word document
that attempts to explain how the technique works.

Also, Access MVP Armen Stein has a Report Selection Techniques example that
you might want to have a look at:

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[fredg]

Hi,
I'm a newbie with working with option groups and am trying to achieve the
following:

I have a form that asks users to enter the start and end date for the
various reports they need to create. The queries pick up these dates and run
the reports fine.

However, I have recently created a new report and need to be able to choose
the employee/s that will populate the report. What I have so far is an
option group that has two options 1) all staff and 2) select staff ... This
is where I run into trouble. What I want to happen is when the user chooses
option 2 a list box appears with check boxes beside all the active staff.
The user can then check the boxes of the staff they required and press the
run report button. This will then populate the report with only those staff
members checked.

I've looked through the posts and have been unable to find an answer to this
question, however, if you know of where the answer is, please direct me
there, or if you know of a better way to do this, please let me know.
Kind regards
Fee.

I just did something like this for my self the other day.
You cannot include a check box within the rowsource of a list box,
so...
Add a check box field to the underlying employees table.
Name this field "Selected".

As rowsource for the list box, use something like this (change the
field and table names as needed):

Select tblEmployees.[EmpID], tblEmployees.[EmpName] From tblEmployees
Order By tblEmployees.[EmpName];

Set the List box to Multiselect Extended.
Hide the first column (EmpID).
Set the List box Bound Column to 1.

Add a command button to the form. Code it's click event:
(watch out for word wrap)

Dim varItem As Variant
Dim strShow As String
strShow = "Update tblEmployee Set Selected = -1 Where
tblEmployee.EmpID = "

For Each varItem In Me.List0.ItemsSelected
strShow = strShow & List0.ItemData(varItem) & " or
tblEmployee.TestID = "
Next varItem

strShow = Left(strShow, Len(strShow) - 25)

CurrentDb.Execute strShow, dbFailOnError

DoEvents
DoCmd.OpenReport "ReportName", acViewPreview


Code the Form's Load event:

CurrentDb.Execute "Update tblEmployee Set tblEmployee.Selected = 0",
dbFailOnError


When the form loads it will clear all previously [Selected] records in
the table.

In your report query, add the Selected field and as criteria on this
field write:
-1

Then open the form and select the wanted employee's names.
When you multi-select names from the list box and click the command
button, it will set each EmpID record chosen to Selected = yes and run
the report.

However, it's quite possible that there may be more than one employee
with the same name.
I would suggest you include, in the list box rowsource, additional
fields as necessary to allow the user to select the correct employee,
such as department, or date of employment, etc.