Form to enter report criteria

[davea300]

I have a report which is opened via a button on a form. It opens the report with linkcriteria using an ID number. When the report is opened it prompts the user to input 3 fields (free text) which appear in the wording of the report. These prompts appear one after the other and then after the last one the report opens.

What I would like to do is use a form to allow the users to enter the text for these fields in one go, click ok and then preview the final report. I don't need to store the data in the fields, just to print them in the report.

I have a form set up with the fields I require but I'm not sure how to get these to transfer over to the report once OK is clicked. The fields don't appear in the underlying query for my report as they are not stored in any table.

Field names are [Information Required] [Officer Name] and [Area]

Any help would be appriciated.

Thanks

 

[ErikSnoek]

Hi davea300,

You could do this by using the OpenArgs variable of a report. Currently the code on your button is probably something like this:
DoCmd.OpenReport "name of your report", acPreview

The definition of the OpenReport sub is:

OpenReport(ReportName, [View As AcView = acViewNormal], [FilterName], [WhereCondition], [WindowMode As AcWindowMode = acWindowNormal], [OpenArgs])

what we need is the last one (OpenArgs), so we just have to add some comma's until we reach the OpenArgs parameter. Let's say we want to send the text "Hello" as OpenArgs, we would use the following piece of code:

DoCmd.OpenReport "name of your report", acPreview, , , , "Hello"

This text could then be read by the report in the "OnOpen" event of the report (or any other event, but I will use OnOpen). The code to read it would simply be:

If IsNull(OpenArgs) Then
     Exit Sub
 End If
MsgBox OpenArgs

First we check if OpenArgs is empty because we do not want any errors, then we show a messagebox showing the value in OpenArgs.

Now to send multiple values as OpenArgs, we have put all the values in one string and divide them with some symbol. I will use a colon in my example.

DoCmd.OpenReport "name of your report", acPreview, , , , "Hello:How:Are:You"

We can then split the values with the Split function. For example, I would put this code in the OnOpen event of the report:

    text1 = Split(OpenArgs, ";")(0)
    text2 = Split(OpenArgs, ";")(1)
    text3 = Split(OpenArgs, ";")(2)
    text4 = Split(OpenArgs, ";")(3)

So now we can use the 4 separate values on the report.

Hope this helps,

Erik Snoek

 

[davea300]

Thanks but that isn't going to allow the user to enter the criteria themselves via a form?

 

[Michael J Ross]

Set the Control Source for each of the fields in your report to [Forms]![NameofForm]![NameofTextBox].

 

[ErikSnoek]

Thanks but that isn't going to allow the user to enter the criteria themselves via a form?

You can create the OpenArgs string like this:
stringtosend = Textbox1.Value & ";" & Textbox2.Value & ";" etc.
and then use that string like this:

DoCmd.OpenReport "name of your report", acPreview, , , , stringtosend