Creating Dynamic Report?

[swk003]

I am trying to pass a txt box value to a report so that I can dynamically print a report that displays the records that are equal to SQLtest in the below code. I presume I can use a standard report, and then pass field values so that only certain values from table are reported on. No luck yet with my shoddy code, any ideas??

Private Sub cmd_PrintRGCBoxReport_Click()

SQLtest = "Where RGCBoxNumber =" & Me.[txt_RGC_BoxNumber_unbound] & ";"
Dim rsreport As DAO.Recordset ' requires DAO reference
'assumes number data type for Abbot_PID
Set rsreport = CurrentDb.OpenRecordset( _
"Select * From tbl_Validation_data " & SQLtest)

DoCmd.OpenReport "report_tbl_Validation_data", acNormal, rsreport
rsreport.Close
Set rsreport = Nothing
End Sub

 

[RoyVidar]

Bind the report to the select part, then in the open command, pass the where condition:

[tt]DoCmd.OpenReport "report_tbl_Validation_data", acViewNormal,, SQLtest [/tt]

Roy-Vidar

 

[swk003]

hi Roy

Hope I'm not being really dumb but how do I Bind the report to the select part??

swk003

 

[RoyVidar]

The reports recordsource, set it to "Select * From tbl_Validation_data", or for better efficiency, a stored query.

And - for the where condition, I forgot to mention, remove the keyword "Where" leaving

[tt]SQLtest = "RGCBoxNumber =" & Me.[txt_RGC_BoxNumber_unbound] [/tt]

- i e, drop of the semicolon too

See the help topic for openreport (hit F1 when the cursor is wihtin the method name)

Roy-Vidar

 

[PHV]

By setting its RowSourceType and RowSource properties

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[swk003]

Hi Roy

I have the following code

SQLtest = "RGCBoxNumber =" & Me.[txt_RGC_BoxNumber_unbound]
DoCmd.OpenReport "report_tbl_Validation_data", acViewNormal, , SQLtest


and the record source of the report is bound to Query_Validation_data (which selects all records from tbl_Validation_data.

BUT I am still printing all the records from the table and not the SQLtest where clause records that I want to retrieve, any ideas?

 

[swk003]

sussed it:

SQLtest = "RGCBoxNumber =(" & Me.txt_RGC_BoxNumber_unbound & ")"

DoCmd.OpenReport "report_tbl_Validation_data", acViewNormal, , SQLtest

I need to get my brackets/ data types up to scratch.

thanks for all your help