user input form to modify a query

[Lila]

I have a database with about 6000 names, each name is associated with a
certain named event. There are a group of people all associated to the same
named event. For example, contacts 1-100 might be associated with "spring
training" and users 101-250 might be associated with "fall review". There are
about 30 named events.

My reporting is getting complicated because I need to run reports that a)
only show people associated with certain events b) show all contacts or c)
show contacts which are associated with events 1, 4 and 8 or any combination
of the 30 events.

My idea was to create a form which lists these events and have a check mark
next to them. When I run a certain report, this form would pop up and ask
which events I want to see. I would put a check next to each event I want to
see, and a contact list for that event would be shown.

So, I created a new query
SELECT Contacts.Info_FirstName, Contacts.Info_LastName, Contacts.Info_Event
FROM Contacts
WHERE ((([Contacts.Info_Event])=Nz([enter event],[Contacts.Info_Event])));

But this only allows me to type in the event name. Because there are over 30
events, this can be cumbersome, not to mention UN-user-friendly.

Is there a tutorial somewhere that can help walk me through this process???

 

[Clifford Bass]

Hi Lila,

The way I have done this is to populate a list box on the selection
form. The list box will allow for multiple selections. Jumping slightly.
The report's query is designed to display all rows of data without any
parameters (i.e. [enter this:] or [enter that:]). Back to the form. When
the user clicks the display report button, check to see how many rows are
selected in the list box. If none or all, just open the report to get all
rows. Here I am defining no rows selected as meaning all rows. If some rows
selected, create a where clause using the in operator. Then open the report,
passing it the constructed where clause. In semicode/English:

Dim strWhereClause As String

if listbox has some rows selected, but not all
code to construct the where clause which might look like this:
Event_Code in ("spring training", "fall review", "etc.")
end if

DoCmd.OpenReport "rptMy Report", acViewPreview, , strWhereClause

Hope this helps,

Clifford Bass

 

[Lila]

When I tried using a list box, it only allowed me to select one item. Is
there a setting that I'm missing?

Also, to add to the complexity, I was also hoping to have a Toggle button to
select/deselect certain preselected events. For example, a Toggle button to
select all 2008 events, another for 2007 events, or other pre-selected groups
of events.

 

[Lila]

I just found this free download which includes instructions on how to do what
you mention and an example database.
http://downloads.techrepublic.com.com/abstract.aspx?docid=295624

Hi Lila,

The way I have done this is to populate a list box on the selection
form. The list box will allow for multiple selections. Jumping slightly.
The report's query is designed to display all rows of data without any
parameters (i.e. [enter this:] or [enter that:]). Back to the form. When
the user clicks the display report button, check to see how many rows are
selected in the list box. If none or all, just open the report to get all
rows. Here I am defining no rows selected as meaning all rows. If some rows
selected, create a where clause using the in operator. Then open the report,
passing it the constructed where clause. In semicode/English:

Dim strWhereClause As String

if listbox has some rows selected, but not all
code to construct the where clause which might look like this:
Event_Code in ("spring training", "fall review", "etc.")
end if

DoCmd.OpenReport "rptMy Report", acViewPreview, , strWhereClause

Hope this helps,

Clifford Bass

I have a database with about 6000 names, each name is associated with a
certain named event. There are a group of people all associated to the same
named event. For example, contacts 1-100 might be associated with "spring
training" and users 101-250 might be associated with "fall review". There are
about 30 named events.

My reporting is getting complicated because I need to run reports that a)
only show people associated with certain events b) show all contacts or c)
show contacts which are associated with events 1, 4 and 8 or any combination
of the 30 events.

My idea was to create a form which lists these events and have a check mark
next to them. When I run a certain report, this form would pop up and ask
which events I want to see. I would put a check next to each event I want to
see, and a contact list for that event would be shown.

So, I created a new query
SELECT Contacts.Info_FirstName, Contacts.Info_LastName, Contacts.Info_Event
FROM Contacts
WHERE ((([Contacts.Info_Event])=Nz([enter event],[Contacts.Info_Event])));

But this only allows me to type in the event name. Because there are over 30
events, this can be cumbersome, not to mention UN-user-friendly.

Is there a tutorial somewhere that can help walk me through this process???

 

[Clifford Bass]

Hi Lila,

Sounds good! Glad to get you pointed in the right direction.

Clifford Bass