Create report using parameters defined by a form user

[Pharma Down]

Hi
I apologise if this is very simple task, but it is something that I have never had to do using Access before.

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.

Is this possible? I'd appreciate any help or advice.

Thanks

Andy

 

[vbaInet]

Yes it is possible and it's quite easy. Have you created the form?

Have a look at this:

http://www.baldyweb.com/wherecondition.htm

Substitute OpenForm for OpenReport.

 

[Pharma Down]

Thank you... but I am fairly ignorant when it comes to the 'coding bit!' Where do I put this piece of code?

If you can tell me, I'll go away and try to build the bits that need it and see how it works.

Andy

 

[vbaInet]

Which is why I asked you if you have created the form? It will go in the click event of the button that opens up the report.

 

[Pharma Down]

Ah! OK. I will attempt to build the form and come back to you!

 

[vbaInet]

Let us know how it goes.

 

[Pharma Down]

I have just been kicked out twice, having written a huge reply!!

I am struggling with this problem…

My understanding of Access if very simplistic: you collect info in tables; forms are a user-friendly way of collecting the info that goes into tables; reports display the info from the tables in a nice way; queries do clever stuff to filter the info to limit what displays in forms ad reports…

As an example of what I am trying to achieve…
I have created a form to collect info on clinical interventions (something done to prevent harm to a patient) – this records details of what was done, when, where, by whom, drugs involved etc. The intervention form is a subform within a form that displays patient details – in this way you can link multiple interventions to a single patient and search/view them easily without having to search through (and compromise the confidentiality of) other patients’ details.

I have created a new form that displays all interventions and a report that displays all interventions. I have added a button to the form and, when opened in design view, in the Property Sheet, under the Even tab, at ‘On_Click’ I have pasted: DoCmd.OpenReport "Rpt_interventions_all", , , "Location = '" & Me.Location & "'" – hoping that this would filter the report to show only intervention records for a given location.

I was presented with a pop-up window that read: Microsoft Access cannot find the object ‘DoCmd.’

It should be fairly obvious that I don’t know what I’m doing, but what I ultimately want to achieve is to be able to filter the report so that it displays (eg) all interventions for Location A between Date 1 and Date 2, or all interventions by Staff Member 1 between Date 1 and Date 2 etc.

 

[vbaInet]

I have added a button to the form and, when opened in design view, in the Property Sheet, under the Even tab, at ‘On_Click’ I have pasted: DoCmd.OpenReport "Rpt_interventions_all", , , "Location = '" & Me.Location & "'" – hoping that this would filter the report to show only intervention records for a given location.

You enter or select properties in the Property Sheet. What you have there is not a property, it's code so it needs to go in the VBA code window and you get there by clicking on the elipsis button which is beside to the On Click event, select Code Builder and click OK. Then paste your code there.

From the same site, see here:

http://baldyweb.com/FirstVBA.htm

 

[Pharma Down]

You'll note that it is a long time since I asked this question... but I have finally tried to follow your instructions again, thinking that I had a better understanding. I posted this:
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 through 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 the whole form locked up until I deleted the code!

Help?!!