Filtering a report with multiple user-defined parameters

[Pharma Down]

I have asked this question before, but failed in implementing the answers!!

I have created a database to collect info via forms on (for example) errors/incidents that occur at a specified site and relate to other specified details (such as who is responsibile for the incident, who investigated and dates).

I would like to create a system so that a user (with no knowledge of Access) can open a form a, specify parameters, click a button and the report would print. The parameters that would commonly need to be defined before printing a report would be things such as:
- all errors between date 1 and date 2
- all errors relating to a selected site/person etc (between specified dates)

I envisage a system whereby the user completes fields in a form to set the parameters, then clicks a button and the form prints, or: they hit a button to print and then a box flashes up to ask for parameters to be set.

I tried an example and added this, via the 'On_Click':
DoCmd.OpenReport "Rpt_interventions_all", , , "FieldName = '" & Me.Name & "'"
(Because the report name is Rpt_interventions_all and the field name that I want to search with is Name)

I clicked on the button (to which I had added the code); I was asked for the field value that I wanted (not sure why it didn't automatically select the value that was showing in the field), but it threw up another window behind the one asking for the field value (couldn't read it)... it then printed a page from the report.

Not understanding why it printed rather than opened the report I thought I could be clever and added a little (copying from elsewhere, not understanding what I am doing):

DoCmd.OpenReport, acPreview, "Rpt_interventions_all", , , "FieldName = '" & Me.Name & "'"

Something in the code builder turned bright yellow and Access locked up until I deleted the code!

I have looked at http://www.baldyweb.com/multiselect.htm - a multiselect box to filter a report and I imagine this might do part of what I want, but I have no idea of what a multiselect box is or how to add one. I think that I would want a multiselect box, to select one or multiple sites/people, but I would also want to filter the report based on a timeframe - is this possible (ie multiselect box plus filtering for records between user-defined dates Date 1 and Date 2)?

Please can someone advise me (considering I don't understand coding!)?

Thank you

Andy

 

[pbaldy]

If you got a parameter prompt, something is likely spelled wrong. The prompt is Access telling you it can't find something.

The problem you had trying to stop the print is that you put the preview argument in the wrong place. If you look at help for OpenReport, you should see where it goes.

You can certainly filter on multiple criteria, you simply include AND or OR between them as appropriate to the desired logic.

 

[Pharma Down]

Thanks...
OK - in as much as you were using English I understand the words you are using, it's just that the way they are combined makes no sense to me.

I am not trying to be modest when I say that I don't know what I am doing with the coding! If I hit Alt+F11 it opens up a window that pretty much scares me. I have thrown bits of code together in a very Frankenstein manner so that two buttons created using the wizard can be combined in one to do both jobs (eg save record + open new form), but this has been by luck rather than any knowledge of what I am doing. It's like going to a gun fight, poking everything on the gun and and shooting the other guy rather than myself out of sheer luck!

Is a 'parameter prompt' the yellow arrow and yellow highlighted text (whilst everything else doesn't work)?

I put the preview argument (ac Preview?) where it seemed to mimic other coding. Where should it go in my example? I certainly don't understand why there is either one comma, two or three between code pieces (arguments?) such as ac Preview.

Where do I put AND or OR... and what do I put after it?

All of the coding I have 'done' has been tinkering with bits created by the button wizard!

Andy

 

[dstone10]

Not sure if this helps, but I have a report based on a query, in the query that is where I put in the parameters I want to view. See query below:

SELECT [tbl1].[datecolumn], [tbl1].[namecolumn] 
FROM tbl1 WHERE (((tbl1.datecolumn) BETWEEN [Start Date - FORMAT EX: 11/1/12] AND
[End Date FORMAT EX: 11/30/12]) AND [tbl1].[namecolumn] = [Person Responsible?]);

That worked on my test database. It asked for a start date, end date, and person responsible. You must type in that value exactly like it is in the namecolumn though.

 

[Pharma Down]

Wow, if I could combine this with the multiselect box, so that I could select one or more people/places and then be prompted for the dates... I think that this could do exactly what I am after!!

I can see that I would alter
[tbl1] to say [whatever my table/query is called]
and
[datecolumn] to say [whatever field in that table/query]

But, what do I do to this bit:
[Start Date - FORMAT EX: 11/1/12] ?

Or... does this throw up a window requesting input with the message: "Start Date - FORMAT EX: 11/1/12"

And, where do I put the code?

All of my reports are based on queries, but my understanding is that I can collect up fields from tables and other queries, with the option of displaying (or not) each field and maybe adding a condition such as ="yes", ="blue", "red" etc.

If I have a button on a form that opens/previews the report (which is based on the query), where do I put the code that you have suggested?

 

[Pharma Down]

I have played around with the Multiselect box example from http://www.baldyweb.com/multiselect.htm -
1) added a yes/no field in the employee table to show employees that are 'active';
2) created a query to display only 'active' employees
3) altered the code in Row Source for the multiselect box to: SELECT tblEmployees.EmpID, tblEmployees.EmpLName FROM qryAllEmployees_active;

where qryAllEmployees_active is my new query. It seems to work, limiting displayed options to only active employees, but have I amended the code correctly or should I be altering it differently/more appropriately?

 

[pbaldy]

Sounds like you're on the right track. To continue to get a date range you'd add to the wherecondition:

DoCmd.OpenReport "rptEmployees", acPreview, , "EmpID IN(" & strWhere & ") AND DateField >= #" & Forms!FormName.txtFromDate & "# AND DateField <=#" & Forms!FormName.txtToDate & "#"

changing the field, form and control names to yours.

 

[Pharma Down]

Hi

Based on what you have suggested, I have in my mind a vision of a form with four 'bits':
1) multiselect box based on active employees from a query
2) text entry box to enter a date (typed or date picker) for From Date
3) text entry box to enter a date (typed or date picker) for To Date
4) button to open report (where all fields are defined in a separate query)

- I create '1' in the same way as the example at the website
- Do the text boxes need to be based on a table or can they exist without actually linking to anything - ie the only function that they ever serve is to provide 'guidance' for filtering the report?
- I create a button (that doesn't do anything), open the properties pane and in the On Click field I click the elipsis and paste this code:

DoCmd.OpenReport "My_report", acPreview, , "My_ID_for_multiselectbox_options IN(" & strWhere & ") AND DateField >= #" & Forms!FormName.txtFromDate & "# AND DateField <=#" & Forms!FormName.txtToDate & "#"

But I'm not sure what to alter. I hope I have got the first 2 red bits right. I assume that
& Forms!FormName.txtFromDate
becomes
& Forms!This_new_FormName.txtFromDate
but what does .txtFromDate become? If I have created text boxes for the dates that aren't linked to a table then what are they called - how do I identify them?
I have just tried to create this unlinked date box - and the Property pane says:
Name: Text0
Control Source:
Format: Short Date

Does that mean that
.txtFromDate
becomes
.txtText0
or
.Text0 ?

Thanks you, again for all your help.

Andy

 

[pbaldy]

It would be the name of the textbox, Text0. I always give the textboxes meaningful names.

 

[Pharma Down]

Cool. Thanks.

Am I essentially correct in the rest of how I create it - fields not linked to a table etc?

A

 

[pbaldy]

Yes; I usually don't have criteria forms bound to a table.

 

[Pharma Down]

I have amended the code for the button on the multiselect box form to say:

DoCmd.OpenReport "rptEmployees", acPreview, , "EmpID IN(" & strWhere & ")" AND DateField >= #" & Forms!frmOpenReport.txtFromDate & "# AND DateField <=#" & Forms!frmOpenReport.txtToDate & "#"

This turns the text red and throws up the message:
Compile Error:
Expected: expression
then highlights the first #

As I added the code it also displayed a line of code - a help line(?)... like an alt="" tag for an image in HTML. This showed what I assume was a suggestion for the layout of code but it differed from your suggestion, with added brackets etc

Eh?

 

[Pharma Down]

Here is an image to show the problem.

Also, when creating the multiselect list box, what is the difference between multiselect=simple or =extended; and what on Earth is IME Sentence Mode, which I notice you have set to Phrase Predict?

I couldn't immediately see that anything else has been done differently to the automatic parameters that Access sets when I create the list box.

 

[Pharma Down]

Now, when attempting to leave the coding window this line
Private Sub cmdOpenReport_Click()
turns yellow, gains a yellow arrow pointing at it and I can't use the form

A

 

[pbaldy]

Can you post the db here?

 

[Pharma Down]

OK... I will try.

Andy

 

[pbaldy]

Try

DoCmd.OpenReport "rptEmployees", acPreview, , "EmpID IN(" & strWhere & ") AND DateOfBirth >= #" & Forms!frmOpenReport.txtFromDate & "# AND DateOfBirth <=#" & Forms!frmOpenReport.txtToDate & "#"

 

[Pharma Down]

I tried!

It did this - attached.

Also, once I have replaced the code (as you suggested) how do I get rid of the yellow arrow and highlighting?

Andy

 

[pbaldy]

The error would imply that there are no date values entered. I tested what I posted and it worked as expected.

 

[Pharma Down]

Aha! Now it works! Thank you.

So, the code that I have is:

Option Compare Database
Option Explicit
----------------
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.lstEmployees.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 employee"
Exit Sub
End If
'add selected values to string
Set ctl = Me.lstEmployees
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "rptEmployees", acPreview, , "EmpID IN(" & strWhere & ") AND DateOfBirth >= #" & Forms!frmOpenReport.txtFromDate & "# AND DateOfBirth <=#" & Forms!frmOpenReport.txtToDate & "#"
Exit_cmdOpenReport_Click:
Exit Sub
Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click
End Sub

You are aware that I don't really understand what most (any) of this means, but I can swap bits so that is relates to my forms and reports! So, please can you highlight for me what bits relate to making the list etc.

Is there anything that I can add to generate an error message (that makes sense to people like me) when the dates are not entered?

Would it be something along the lines of:
If Me.txtFromDate = 0 Then
MsgBox "Please enter a start date for the report period"
Exit Sub
End If
If Me.txtToDate = 0 Then
MsgBox "Please enter an end date for the report period"
Exit Sub
End If

Where would I stick it (and does it matter)?