Pass a SQL Statement from forms->Recordsource for Reports- Help!

[AustinMan]

I have SQL statement based on comboboxes.

Can I pass that SQL Statement as the Recordsource for a Pre-defined Report.

Please Reply if it is possible, and if so, please provide a step - by - step method.


Thanks in advance!

 

[AccessAce]

Do the combo boxes specify the selected fields or criteria of the report?

 

[AustinMan]

Well, The SQL statement is created where the user is in the form. There are four scenarios.

Please read thread702-492764.

The SQL statement are providing Summary Totals run in form. Now, I would like to pass that SQL statement as the recordsource of a pre-defined designed report. Is this possible??

On my form, I have a command button for print,wanting to link the selected fields based on selection values in combobox. (the selected SQL statement - based on criteria)

Can I passed thru the SQL statement as the recordsource, or first need to create a temporary table or query first. If that is true, how does one do that, a brief example would be beneficial.

Thanks!

 

[PGTurner]

Here is an example of MakeQuery code. You can stick it in a command button on a form, then build the query string with data from the form. When you click the button you should get a query created with zQryFormName. It will overwrite whatever was in that query and present it as a table.

From that point a user can filter or whatever they want - or you can use it elsewhere. Just keep in mind that subsequent button pushes with replace the previous query.

The query that is here is only a clip of my real query so will contain syntax errors and of course you won't have my tables. The code is hacked out of one of my forms so there are probably several different variable naming schemes going.

Private Sub BuildQuery_Click()
On Error GoTo Err_BuildQuery_Click

    Dim stDocName As String
    Dim Tmpqry As QueryDef
    Dim db As Database
    Dim InList As String
    Dim qryName As String
    Set db = CodeDb()
    
    qryName = "zQry" & Me.Name

    stDocName = "SELECT TxnPerfPhysician, AccountNumber,"
    stDocName = stDocName & " Name, PtStatus, TxnSerDate "
    stDocName = stDocName & " From TSG_PatientInformation"
    stDocName = stDocName & " ORDER BY TxnSerDate;"


On Error Resume Next
   Set Tmpqry = db.CreateQueryDef(qryName, stDocName)
   Select Case Err.Number
     Case 0
     Case 3012  ' The query already existed but this will overwrite.
       Set Tmpqry = db.QueryDefs(qryName)
       Tmpqry.SQL = stDocName
     Case Else
       GoTo Err_BuildQuery_Click
   End Select
On Error GoTo Err_BuildQuery_Click
   
DoCmd.OpenQuery qryName, acViewNormal, acEdit
    

Exit_BuildQuery_Click:
    Exit Sub

Err_BuildQuery_Click:
    MsgBox Err.Description
    Resume Exit_BuildQuery_Click
    
End Sub

 

[cmmrfrds]

Make a string variable and put your sql in the variable and then equate the variable to the row source. It is that simple.

In Standard Module.
Public mySql as string

Where you build the sql.
mySql = "Select etc..... from table"

In Report OnOpen event.
Me.RowSource = mySql