Using Macro to open specific records

[mondeoman]

I have a search form. It is a main form with three text boxes and a sub form to show results from a table. The user can search for records that are based on filds with values - Job_Reference; Employer_Code or Serial_No. The results are shown in datasheet view in the sub form. As data relating to an Employer_Code or Job_Reference may not be unique (Serial_No is unique) thena number of records are returned. What I want is for the user then to be able to double click in the datasheet a record and that record to be opened in a related report called rpt_Interim_Cert. I only want this record inthe report not all records. At present I have a macro that opens the report fine but I don't know how to filter or use the where clause to only return the one report record. I am using the Access 2010 (beta) version. Any help would be appreciated.

 

[dhookom]

I seem to recall answering this question earlier today. Did you get my answer and try it?

You can set the Double-Click event code of one or more text boxes in the subform to open your report to the appropriate record using the WHERE CONDITION of the DoCmd.OpenReport method.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

I found my earlier reply still in an edit window :-(

You could try set the double-click event of a field or fields in the datasheet subform to open the report with a WHERE CONDITION that filters the report. The code might look like:

   DoCmd.OpenReport "rpt_Interim_Cert", acViewPreview, , _
      "Serial_No=""" & Me.Serial_No & """ "

The above assumes Serial_No is text.

Duane
Hook'D on Access
MS Access MVP

 

[mondeoman]

Many thanks dhookom

That is exactly what I wanted. I had tried something very similar but the syntax was obviously wrong so you input was timely and helpful.