Report based on a changing query

[jongoumd2005]

I am trying to create a form in which the user will build an SQL statement by selecting different checkboxes and filling in different criteria. The actual creation of the SQL statement is done on the back end after the user clicks the Submit button. When that happens, my code creates the SQL statement based on the user's selections, and then changes the SQL for one of my Queries. A have a report that uses this query, and in the code, I have the report using the new query, as shown below.

SQL_Statement = CreateSQL()
CurrentDb.QueryDefs("Submit_Query").SQL = SQL_Statement
DoCmd.OpenReport "Results", acViewDesign
Reports![Results].RecordSource = "Submit_Query"
DoCmd.OpenReport "Results", acViewPreview

Currently, my report is blank, because it could show different columns all the time, depending on what the user had selected. Is there any way that I can have the report create 'dynamically' based on what the results of the query are?

My end goal is to have the user select all the criteria, and what information to show, click Submit, and have the report with all the results appear.

If there is a better way to solve this than how I am currently doing so, I am open to suggestions.

Thank you,
Jon

 

[ProgramError]

I created this code for a search form which has the bases of a sql string builder. you may want to adapt it for your use. The important areas are the strSQL, strwhere, strorder elements and the removal of the final AND at the end of the code.
I have left it complete just in case someone found it useful as a search engine.

Private Sub cmdSearch_Click()
On Error GoTo Err_CMDSearch

Dim strSQL As String, strOrder As String, strwhere As String
Dim AllInputsEmpty As Boolean

'reset 'are all inputs empty' check
AllInputsEmpty = True

'Constant Select statement for the RowSource
strSQL = "SELECT * FROM tblOArequests"
strwhere = " WHERE"
strOrder = " ORDER BY tblOArequests.progno DESC;" ' default is progno order

'set the recordsource according to prognoinput if not empty
If (Not (IsNull(Me.TXTPrognoSearch)) And (TXTPrognoSearch > 0)) Then '<--If the textbox txtFName contains no data THEN do nothing
    strwhere = strwhere & " (tblOArequests.progno) LIKE " & Me.TXTPrognoSearch & " AND"
    AllInputsEmpty = False
End If

'Title
If Not IsNull(Me.TXTTitleSearch) Then
    strwhere = strwhere & " (tblOArequests.Title) Like '*" & Me.TXTTitleSearch & "*'  AND"
    AllInputsEmpty = False
End If

'Broadcast Date
If ((Not IsNull(Me.TXTDateSearch)) And (TXTDateSearch > 0)) Then
    strwhere = strwhere & " (tblOArequests.BCastDate) Like '*" & Me.TXTDateSearch & "*'  AND"
    AllInputsEmpty = False
    'list in date order
    strOrder = "ORDER BY tblOArequests.BcastDate, tblOArequests.progno DESC"
End If

'Between Broadcast Dates
If ((Not IsNull(Me.TXTStartDateSearch)) And (Me.TXTStartDateSearch > 0)) And ((Not IsNull(Me.TXTEndDateSearch)) And (Me.TXTEndDateSearch > 0)) Then
    strwhere = strwhere & " ((tblOArequests.BCastdate > Me.TXTStartDateSearch) AND (tblOArequests.BCastdate < me.TXTEndDateSearch)) AND"
    AllInputsEmpty = False
    'list in date order
    strOrder = "ORDER BY tblOArequests.BcastDate, tblOArequests.progno DESC;"
End If

'OU programmes
If Me.togOUsearch Then
    strwhere = strwhere & " (tblOArequests.OUno) And "
    AllInputsEmpty = False
End If

If AllInputsEmpty Then
    'calls an external function to reset the sql code to view all records    
    ResetToAllRecs
Else
    'Remove the last AND from the SQL statment
    strwhere = Mid(strwhere, 1, Len(strwhere) - 4)
    'open the form in datasheet view
    DoCmd.OpenForm "ListAllFRM", acFormDS
    'check what forms are open and Pass the SQL string to the RecordSource
Dim aobForm As AccessObject
For Each aobForm In Application.CurrentProject.AllForms
        If aobForm.IsLoaded Then
            If aobForm.Name = "listallfrm" Then
              Forms!ListAllFRM.RecordSource = strSQL & " " & strwhere & "" & strOrder
              Forms!ListAllFRM.Requery
            End If
            If aobForm.Name = "amendfrm" Then
              Forms!AmendFRM.RecordSource = strSQL & " " & strwhere & "" & strOrder
              Forms!AmendFRM.Requery
            End If
        End If
    Next aobForm

End If
RequeryForms

exit_CMDsearch:
Exit Sub

Err_CMDSearch:
End Sub

Ian Mayor (UK)
Program Error
Programming is 10% coding and 90% error checking.