Hi

I have 2 reports; 'rptEnquiryType' and 'rptCustomerType' I'm looking for Access to automatically choose the correct report based on a field value.

i.e. in tblContacts the field 'Type' may be set to 'Enquiry' or 'Customer'

If the field says 'Enquiry' I need it to run the report 'rptEnquiryType' and if it says 'Customer' I need it to run the report 'rptCustomerType'

Is this possible by clicking a control button in a form?

Many thanks in advance :):):):)

Is the tblContacts at least part of the RecordSource where the "Report" button resides? If not, how do we locate the correct record in the tblContacts table?

Yes, I have a query that is used as the RecordSource as it pulls data from tblContacts and from tblPricing
It looks at the primary key [CustRef] and pulls the information related to that customer reference number

Hopefully you are using code to run your report. If so then post the code here and we'll modify it to be a little smarter about the report. Start with Private Sub...

I haven't written any code for it yet... this is where I'm struggling
I'm sort of new to Access but have a lot of 'basic' knowledge and understand a little VBA.

The code for your "Report" button will look something like:

Private Sub YourButton_Click()

Dim strReport As String

Select Case Me.txtType
Case "Enquiry"
strReport = "rptEnquiryType"
Case "Customer"
strReport = "rptCustomerType"
Case Else
MsgBox "Invalid Report Type"
Exit Sub
End Select

DoCmd.OpenReport strReport, acViewPreview

End Sub...using your control names of course.

BTW, Type is a reserved word (http://www.allenbrowne.com/AppIssueBadWord.html#T) and should *not* be use for a name for anything! Post back here if you need additional assistance.

Brilliant
That works perfect

Thanks very much for all your help :-)

You're very welcome. Glad I could help.