CODEWITHSUNDEEP
vbams-accessms-access-2010
Xcelrate
Xcelrate

Reputation: 41

Filter a Report based on user input

I created a report template which I want to open a single page report based on a specific IDENT NO. All of the fields in the report are then generated from the associated IDENT NO.

Currently, when I open the report, it will create a single page report for each ID number in the Report Data table when opened.

Instead, when the user is attempting to open the report, I want to prompt the user to enter the identification number of the specific report they are looking for so that it only opens that single individual 1 page report associated with the entered IDENT NO.

How could I achieve this filtering?

Upvotes: 0

Views: 6147

Answers (2)

johnDanger
johnDanger

Reputation: 2333

An alternative to using InputBox is to use a "parameter query" (documentation here)

Anywhere in a query criteria or report design view you may enter [IdentNoVariable] and a dialog box will appear (when the report or query is run) asking you to "Enter Parameter Value" for IdentNoVariable.

For example, if you wanted to restrict an ID # during a query (say the query that your report calls):

enter image description here

The dialog box seen appeared upon running the query and has the text of the variable seen in the query criteria. This same thing can be done in a report by entering [IdentNoVariable] into the part of the report where the entered value is desired.

Upvotes: 1

Kostas K.
Kostas K.

Reputation: 8518

You can display an InputBox for the user to insert the ID and then open and filter the report.

Dim id As String
    id = InputBox("Enter the identification number:", "YourInputBoxTitle")

'if a value was entered, open and filter report
If Len(Trim(id)) > 0 Then
    DoCmd.OpenReport "ReportName", acViewPreview, , "[IDENT NO] = " & id, acWindowNormal
End If

Upvotes: 0

Related Questions