Dynamic Report RecordSource

[hi there]

Hi all,

i am trying to create a report based on some dynamic SQL and i'm getting stuck. here's what i've got so far:


Public Function CreateDynamicRpt() As ADODB.Recordset

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sSQL As String

Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset

Set cnn = CurrentProject.Connection


If (Forms!frmFindingParam.ListBoxReqType = "All") Then
sSQL = "SELECT * FROM tblFindings;"
Else:
sSQL = "SELECT * FROM tblFindings WHERE tblFindings.ReqType = Forms!frmFindingParm.ListBoxReqType;"
End If

rst.Open sSQL, cnn

CreateDynamicRpt = rst

Set rst = Nothing
Set cnn = Nothing


End Function


sorry about the weird code formatting, but there are no line continuation marks in the code. all statement are on a single line. i saved this into a separate module named "mCreateDynamicRpt". I tried calling this function in the report's RecordSource property, but it isn't working. it says it doesn't recognize a macro named "mCreateDynamicRpt". to launch the report i created a command button. what am i doing wrong? is there a better way to do this? any help would be greatly appreciated.

Thanks a lot

 

[WayneRyan]

hi there,

Just put this on your Command Button to print the report:

If Me.ListBoxReqType = "All" Then
   DoCmd.OpenReport "YourReport"
Else
   DoCmd.OpenReport "YourReport",,,"[ReqType] = '" & Me.ListBoxReqType & "'"
End If

Wayne

 

[hi there]

thanks WayneRyan for the response. that worked perfectly, however the example i presented was a little simplified. in the actual application i have several list boxes, radio buttons,..etc that will be criteria, so i'd like to learn how write the dynamic SQL and use it as the recordsource for the report. could you explain how i could modify my code/approach in order to get this to work by dynamically creating a report's recordsource at runtime?

thanks again for all the help

 

[WayneRyan]

hi there,

You can substantially up the ante, as far as criteria are
concerned. Your next step will be to base your report
on a query. The query will reference things like:

Forms![YourForm]![YourListBox]

in its criteria section.

Wayne