Error; report osn't opened or is misspelled.

[msreed]

I am asked to modify a form & report to allow for the selection of a client. There are functions already built in this already huge form so I would like to use what is already there. I am getting errors telling me that the report isn't opened or is misspelled. This error occurs on the Filter. I realize that I am referring to the report before its opened but I am not sure how else to do this. Here is what I have:

strRPT = "rptComprehensiveRevenueSummary_byClient"
Select Case [optCLIENT]
Case 1 'ALL Clients...
Case 2 'Single Client...
strFILTER = [Reports]![rptComprehensiveRevenueSummary_byClient]![ClientID] = [Forms]![frmHubReports]![cmbClientNumber] And ""
Case 3 'Negotiation Clients...
strFILTER = [Reports]![rptComprehensiveRevenueSummary_byClient].[ARTypeID] = 8
Case 4 'Multiple Clients...
strFILTER = [ClientPrint] = -1
End Select
DoCmd.OpenReport strRPT, acViewPreview, strFILTER
End

 

[danvlas]

1. The strFilter in your expressions should not attempt to get data from the report (that's what the notation Reports!Whatever does).

Think of the underlying recordsource of the report (table/query/SQL). Grab the name(s) of the field(s) by which you want to filter the report:

strFilter = "ClientID = " & [Forms]![frmHubReports]![cmbClientNumber]

2. Your logic leaves a trailing 'And' in case 2.

I'd do it like this:

strRPT = "rptComprehensiveRevenueSummary_byClient"
Select Case [optCLIENT]
Case 1 'ALL Clients...
strRpt = ""
Case 2 'Single Client...
strFilter = "ClientID = " & [Forms]![frmHubReports]![cmbClientNumber]
Case 3 'Negotiation Clients...
strFILTER = "ARTypeID = 8"
Case 4 'Multiple Clients...
strFILTER = [ClientPrint] = -1
End Select
DoCmd.OpenReport strRPT, acViewPreview, strFILTER
End

The bold green part can be probably replaced by 'Me'. The red part is what I didn't understand. Your line sets strFilter either to True or False...


HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[msreed]

Hey Dan thanks for the response. I understand what you are are telling me, unfortunately when I changed to code the report ran, but for all clients. I'm going to keep your logic and try and tweak it some. Thanks.

Missy