Report to display records based on form text box

[krutoigoga]

Form "IAR_Entry" with multiple fields is being used to enter data into "IAR" Table.
I am trying to a way to enter a field value (ID) on a form and base the report on the specific field value in the form, i.e. only show the records for that ID.
I am able to do the above in the form using a combo box. However the report does not have an "afterupdate" event to use

], , First, ="[IARnum] = " & Str(Nz(Screen.ActiveControl,0))[/CODE.
I have tried a Dlookup however that returns the first entry in the query.
Also tried creating another form and tried dlookup [CODE]=DLookUp("[IARnum]","IARQa" & [Forms]![IAR_REPORT]![text0])

to no avail.
Can anyone suggest a way to enter a field value on a form and base the report on the lookup value in the form?
Thank you in advance.

 

[apr pillai]

You can use the Form ID field reference as criteria in the Report Source Query to filter the data.

Type the Form field reference as shown below in the criteria row of the Id column:

Forms![myForm]![ID]

The sampe SQL in the Query will look like the following:

SELECT myTable.* FROM myTable WHERE (myTable.ID = Forms![myForm]![ID]);

Use a Command Button on the Form to open the Report, with a Command Button Click Event procedure as given below:

Sub cmdRunRpt_Click()
   me.refresh 'this will update the underlying query with the change of ID
   Docmd.Open "myReport", acViewPreview
End Sub

If you don't like this idea then use the following method taken from Microsoft Access2007 help document:

Access 2007 Developer Reference > How Do I... in Access 2007 > Reports > Events
Access Developer Reference
How to: Filter a Report Using a Form's Filter

The following example illustrates how to open a report based on the filtered contents of a form. To do this, specify the form's Filter property as the value of the OpenReport method's WhereCondition argument.

Private Sub cmdOpenReport_Click()
If Me.Filter = "" Then
MsgBox "Apply a filter to the form first."
Else
DoCmd.OpenReport "rptCustomers", acViewReport, , Me.Filter
End If
End Sub