Creating report from user entered parameters

[TheB]

Hi there,

Forgive me but I am quite new to Access. I was hoping I could get some direction. I wish to build a dynamic report based on a table. My intention is to have the user enter information for each field on a record. For example, if wants report for a specific Sales Rep for a specific date for a specific amount I would like user to enter these values on a form. The form would have text boxes for each field in the table whereby the user has flexibility to filter the data by entering data in any one of the fields. Based on this I would somehow have to build my query for the report and generate the report. Can someone tell me the steps involved and how I would go about achieving this. Better yet if you could also lead me to a similar example that would be great.

I look forward to your reply as I am kinda stumped as to how to go about doing this in Access.

Thanks in advance...TheB

 

[Robert Dunstan]

Hi TheB,

Ok first create your form that will display all the text boxes needed for your users to enter the required parameters. These text boxes can remain unbound i.e. they don't need to be linked to an underlying field on your tables.

Secondly create your query and include all the fields you need to be displayed in your report. Make sure you include the fields that are going to be used to set criteria. In these fields set the criteria to point to the data entered by the users in your form. The syntax for this is:

[Forms]![yourFormNameHere]![yourControlNameHere]

Save the query and then create your report based on the query.

Finally, on your parameters form, use the wizard to create a command button that will run the report.

HTH
Rob

 

[TheB]

Thanks Rob for your help. I will give it a try. I do have a question however with respect to the criteria in the query. Sometimes the user will choose a criteria for a field but sometimes they will not. How would I specify that in the criteria portion of the query.

Thanks gain and look forward to your reply.

...TheB

 

[Robert Dunstan]

Hi there,

OK on your paramters form will you need to perform a selection on all of the criteria or just some of them?

 

[TheB]

Answer to your question

Hi Rob,

Thanks for getting back to me. In answer to yuor question I could be doing a selection on all the criteria but my guess is most of the time it will not not be. However, like I said there is a chance it could be all.


Look forward to your reply.

...TheB

 

[Robert Dunstan]

Hi TheB

If you have a scenario whereby you have a report based on no criteria or on all of the criteria then I would suggest the following:

1. Create the report / query as in my previous post. This report will be the one that requires all the criteria from your parameters form. We'll call it in this instance ReportOne.

2. Make a copy of the report & query and we'll call them ReportTwo and QueryTwo. Remove all the criteria in QueryTwo and set the RecordSource property of ReportTwo to QueryTwo. This report will print all records as defined.

3. On your parameters form you could create a second command button to print ReportTwo and in your other command button for printing ReportOne you would need to check that a selection was made by using code along these lines in the buttons On_Click event.

cmdButton On_Click()

If IsNull(yourControlNameHere) = True Then
   MsgBox "Please make a selection",vbExclamation,"No Selection"
   yourControlName.SetFocus
   Exit Sub
End if

DoCmd.OpenReport "ReportOne", acPreview

End Sub

This is a very simple solution. However if you need to report on some but not all cirteria then you're getting into the realms of creating QueryDefs in code which is probably a much better way of handling this kind of situation. But alas I'm sadly lacking the knowledge in the area

Hopefully what I've suggested you will find useful.

Rob

 

[TheB]

Thank you

Rob,

Thanks so much for your suggestions. I will give them a try.

You have been most helpful.

Have a great day.

...TheB(aka Bruce)

 

[Robert Dunstan]

No problemo

If you need more help just post again