OpenReport method's SQL where condition (1 Viewer)

[Cynthia]

Hello!
I need help, please, with printing an Access report from VB5.

I need to accomplish two things before the report is printed: 1) I need to change the caption of a label (lblDetail) on the report based on a user's selection. 2) I need to append a where clause to the SQL statement that is the report's Record Source (where clause is based on user's selection).

I seem to be able to do one or the other, so I know that the syntax is alright. But the way it is now, the label caption is changed but the SQL where clause is ignored.

There must be a way!! Here's my code, followed by the SQL statement that the Report is based on.

Thanks for any help!
Cynthia

(dRequest is RequestNumber user chose.)
SQLwhereClause= "ILL.REQUEST = " & dRequest
Static accApp As New Access.Application
With accApp
.OpenCurrentDatabase (gsDatabase)
.Visible = False
.DoCmd.OpenReport "MyReport", acViewDesign, , SQLwhereClause
.Reports("MyReport").lblDetail.Caption = sDetail
.DoCmd.OpenReport "MyReport", acViewPreview, , SQLwhereClause
.DoCmd.Close
End With

Report.RecordSource's SQL statement:
SELECT ILL.REQUEST, ILL.PATRON, ILL.LIBRARY, LIBRARIES.ILLCODE, LIBRARIES.PHONE, LIBRARIES.FAX, FROM LIBRARIES INNER JOIN ILL ON LIBRARIES.ILLCODE = ILL.LIBRARY;

 

[dennyryan]

On thing you could consider is moving the statement where you set the caption value to the OnOpen event in the report

Me.lblDetail.Caption = me.sDetail

This requires that sDetail (or some synonymous field) needs to be on your report,perhaps as an Invisible control. That may not be possible in which case forget everything I wrote.

Denny

 

[Cynthia]

Hello all, Cynthia again.

Thank you Denny Ryan for your suggestion. Unfortunately I couldn't implement it as sMethodDetail was a string the user selects on the VB form and I couldn't figure out how to pass it to Access.

But, after days of thinking about it, I have FINALLY come up with a solution, and I wanted to SHARE it, just like in Kindergarten. Of course, once you find the solution, you think, "That's so SIMPLE!!"

Just to reiterate, my problem was that I could change the label on a Report before printing, or I could pass a Where clause to the Report, but not both, for some reason. The Report's RecordSource was a SQL statement that needed a where clause from the VB form at run-time.

Solution: Open the Report in design view from VB, pass the whole SQLstatement to the Report.RecordSource, including the Where clause, change the label's caption, and then open the report in normal view and print the Report. It works!!

(dRequestNo and sMethodDetail are variables I've extracted from user's choices on VB form)
Function ModifyAndPrintReport()
Static accApp As New Access.Application
With accApp
.OpenCurrentDatabase (gsDatabase)
.Visible = False
Dim SQLstatement As String
SQLstatement = "SELECT ILL.REQUEST, ILL.PATRON, ILL.LIBRARY, LIBRARIES.ILLCODE, LIBRARIES.PHONE, LIBRARIES.FAX, LIBRARIES.EMAIL FROM LIBRARIES INNER JOIN ILL ON LIBRARIES.ILLCODE = ILL.LIBRARY WHERE ILL.REQUEST = " & dRequestNo

.DoCmd.OpenReport "MyReport", acViewDesign
.Reports("MyReport").RecordSource = SQLstatement
.Reports("MyReport").lblDetail.Caption = sMethodDetail
.DoCmd.OpenReport "MyReport", acViewNormal
.DoCmd.Close
End With
accApp.CloseCurrentDatabase
accApp.DoCmd.Quit
'Close the instance of Access.
Set accApp = Nothing
End Function