OpenArgs function preventing reports from launching after splitting database

[TheChairman]

Hello, I have a database that contains a search form (frmPanelSearch) that builds a custom SQL string (sSQL) that selects records from a query (qryPanelSearch) based on user input. When the user clicks the "search" button, the form builds sSQL and passes it via OpenArgs to two reports (rptPanelSearch and rptPanelEmails) that are launched with the following commands:

DoCmd.OpenReport "rptPanelSearch", View:=acViewReport, OpenArgs:=sSQL

DoCmd.OpenReport "rptPanelEmails", View:=acViewReport, OpenArgs:=sSQL

This was all working fine when my database was a single unit. However, after I split the database into a front and back end (with both databases residing in the same folder on my C: drive), the reports would no longer open. I experimented a bit and found that they would launch only when I removed the OpenArgs parameter, but of course this meant that sSQL would not get passed and I got a blank report.



Any idea on what's going on here? My tables all seemed to be properly linked and qryPanelSearch runs fine when I double-click it in the Navigation Pane. I also have another form with a similar search capability that also selects from a query and opens up another report through OpenArgs using the exact same syntax, so I'm really scratching my head. Thanks!

[June7]

I use this syntax with split project:

DoCmd.OpenReport "rptPanelEmails", acViewReport, , , , sSQL

You don't show how the sSQL variable is built or how the RecordSource of opening form/report is set.

An alternative to OpenArgs is WHERE argument of OpenForm/OpenReport. Set up form/report with a basic unfiltered RecordSource. Then pass the filter criteria with WHERE argument. Use report Grouping & Sorting functionality to set up report structure.

[RuralGuy]

What are you doing with the OpenArgs in the reports? I would not expect the results you are getting.

[TheChairman]

In both reports reports, I have the following code set to run on Report_Open.

Option Compare Database
Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = Nz(Me.OpenArgs, "")
    Me.Filter = Forms!frmPanelSearch.Filter
    Me.FilterOn = True
End Sub

The sSQL variable is built through a rather tedious process. I'll post the whole code here, but the relevant part is only the last few lines starting from "Set rs = db.OpenRecordset(sSQL, dbReadOnly)".

Private Sub cmdSubmit_Click()
'This function dynamically generates a SQL string ("sSQL") based on the data the user enters into the text and combo boxes in the form.
'It then plugs the SQL string into the query qryPanelSearch and displays the results in two reports, rptPanelSearch and rptPanelEmails.

    On Error Resume Next
    
    Dim ctl As Control
    Dim sSQL As String
    Dim sWhereClause As String
    Dim db As Database
    Set db = CurrentDb
    
    'Start the first part of the select statement. If nothing is in any of the fields, return all records in tblContactsContactInfo.
    sSQL = "SELECT * FROM qryPanelSearch "
    sWhereClause = " WHERE "
    
    'Loop through each control on the form to get its value.
    For Each ctl In Me.Controls
        With ctl
            Select Case .ControlType
    
    'If the function finds a text box, it checks to see if there is data. If there is data, then it checks to see which text box it is, runs the
    'code for that text box, and then iterates to the next control. Within each block of code for a particular control, there are two versions of the
    'search string. The first is if the criterion is the FIRST criterion in the search string. The second is if the criterion is the second criteron or later,
    'meaning that an "AND" has to be placed in front of it.
            
            Case acTextBox
                 .SetFocus
                If Not ctl.Value = "" Then
                    
                    'Checks the date range. Date range should be entered using proper search syntax.
                    If .Name = "txtDateRange" Then
                        If sWhereClause = " WHERE " Then
                            sWhereClause = sWhereClause & " (" & BuildCriteria("dtePanelDate", dbDate, txtDateRange.Value) & ")"
                        Else
                            sWhereClause = sWhereClause & "AND (" & BuildCriteria("dtePanelDate", dbDate, txtDateRange.Value) & ")"
                        End If
                    End If
                    
                    'Generates a string that searches all presentation titles for the criteria entered.
                    If .Name = "txtPresentationTitle" Then
                        If sWhereClause = " WHERE " Then
                            sWhereClause = sWhereClause & " ((" & BuildCriteria("txtPresentationTitle1", dbText, txtPresentationTitle.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("txtPresentationTitle2", dbText, txtPresentationTitle.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("txtPresentationTitle3", dbText, txtPresentationTitle.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("txtPresentationTitle4", dbText, txtPresentationTitle.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("txtPresentationTitle5", dbText, txtPresentationTitle.Value & "*") & "))"
                        Else
                            sWhereClause = sWhereClause & " AND ((" & BuildCriteria("txtPresentationTitle1", dbText, txtPresentationTitle.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("txtPresentationTitle2", dbText, txtPresentationTitle.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("txtPresentationTitle3", dbText, txtPresentationTitle.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("txtPresentationTitle4", dbText, txtPresentationTitle.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("txtPresentationTitle5", dbText, txtPresentationTitle.Value & "*") & "))"
                        End If
                    End If
                    
                    'Searches Panel Chair, Discussants 1 and 2, and Panelists 1-5, respectively for the string entered. See qryPanelSearch for more details.
                    If .Name = "txtParticipantName" Then
                        If sWhereClause = " WHERE " Then
                            sWhereClause = sWhereClause & " ((" & BuildCriteria("qryGetAllNames.txtFullNameWithKorean", dbText, "*" & txtParticipantName.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_1.txtFullNameWithKorean", dbText, "*" & txtParticipantName.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_2.txtFullNameWithKorean", dbText, "*" & txtParticipantName.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_3.txtFullNameWithKorean", dbText, "*" & txtParticipantName.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_4.txtFullNameWithKorean", dbText, "*" & txtParticipantName.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_5.txtFullNameWithKorean", dbText, "*" & txtParticipantName.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_6.txtFullNameWithKorean", dbText, "*" & txtParticipantName.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_7.txtFullNameWithKorean", dbText, "*" & txtParticipantName.Value & "*") & "))"
                        Else
                            sWhereClause = sWhereClause & " AND ((" & BuildCriteria("qryGetAllNames.txtFullNameWithKorean", dbText, "*" & txtParticipantName.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_1.txtFullNameWithKorean", dbText, "*" & txtParticipantName.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_2.txtFullNameWithKorean", dbText, "*" & txtParticipantName.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_3.txtFullNameWithKorean", dbText, "*" & txtParticipantName.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_4.txtFullNameWithKorean", dbText, "*" & txtParticipantName.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_5.txtFullNameWithKorean", dbText, "*" & txtParticipantName.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_6.txtFullNameWithKorean", dbText, "*" & txtParticipantName.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_7.txtFullNameWithKorean", dbText, "*" & txtParticipantName.Value & "*") & "))"
                        End If
                    End If
                    
                    'Searches the e-mail fields for Panel Chair, Discussants 1 and 2, and Panelists 1-5 for the criterion entered.
                    If .Name = "txtParticipantEmail" Then
                        If sWhereClause = " WHERE " Then
                            sWhereClause = sWhereClause & " ((" & BuildCriteria("qryGetAllNames.txtEmail", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames.txtEmail2", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_1.txtEmail", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_1.txtEmail2", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_2.txtEmail", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_2.txtEmail2", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_3.txtEmail", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_3.txtEmail2", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_4.txtEmail", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_4.txtEmail2", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_5.txtEmail", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_5.txtEmail2", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_6.txtEmail", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_6.txtEmail2", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_7.txtEmail", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_7.txtEmail2", dbText, "*" & txtParticipantEmail.Value & "*") & "))"
                        Else
                            sWhereClause = sWhereClause & " AND ((" & BuildCriteria("qryGetAllNames.txtEmail", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames.txtEmail2", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_1.txtEmail", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_1.txtEmail2", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_2.txtEmail", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_2.txtEmail2", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_3.txtEmail", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_3.txtEmail2", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_4.txtEmail", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_4.txtEmail2", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_5.txtEmail", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_5.txtEmail2", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_6.txtEmail", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_6.txtEmail2", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_7.txtEmail", dbText, "*" & txtParticipantEmail.Value & "*") & ")"
                            sWhereClause = sWhereClause & " OR (" & BuildCriteria("qryGetAllNames_7.txtEmail2", dbText, "*" & txtParticipantEmail.Value & "*") & "))"
                        End If
                    End If
                End If
        
            
            Case acComboBox
                .SetFocus
                If Not ctl.Value = "" Then
                    
                    'The user can select an event from a dropdown box. This searches for the event name specified in the dropdown box
                    'and compares it with the event name specified in the query.
                    If .Name = "cboEventName" Then
                        If sWhereClause = " WHERE " Then
                            sWhereClause = sWhereClause & " (" & BuildCriteria("txtEventName", dbText, cboEventName.Column(1)) & ")"
                        Else
                            sWhereClause = sWhereClause & " AND (" & BuildCriteria("txtEventName", dbText, cboEventName.Column(1)) & ")"
                        End If
                    End If
                    
                    'The user can select the Project Lead from a dropdown box. Searches for the Project Lead's EMPLOYEE ID (contained in a hidden
                    'column) and compares it with the value in qryPanelSearch.
                    If .Name = "cboProjectLead" Then
                        If sWhereClause = " WHERE " Then
                            sWhereClause = sWhereClause & " (" & BuildCriteria("txtProjectLeadID", dbText, cboProjectLead.Value) & ")"
                        Else
                            sWhereClause = sWhereClause & " AND (" & BuildCriteria("txtProjectLeadID", dbText, cboProjectLead.Value) & ")"
                        End If
                    End If
                    
                    'The user can select the Event Coordinator from a dropdown box. Searches for the Event Coordinator's EMPLOYEE ID (contained in a hidden
                    'column) and compares it with the value in qryPanelSearch.
                    If .Name = "cboEventCoordinator" Then
                        If sWhereClause = " WHERE " Then
                            sWhereClause = sWhereClause & " (" & BuildCriteria("txtEventCoordID", dbText, cboEventCoordinator.Value) & ")"
                        Else
                            sWhereClause = sWhereClause & " AND (" & BuildCriteria("txtEventCoordID", dbText, cboEventCoordinator.Value) & ")"
                        End If
                    End If
                End If
            End Select
        End With
    Next ctl
    
    'Set the form's recordsource equal to the new select statement.
    If Not sWhereClause = " WHERE " Then
        sSQL = sSQL & sWhereClause
    End If
    
    'memoSQL is a temporary field for testing only, used to verify the syntax of the SQL string. This should only be enabled for diagnostic purposes.
    memoSQL = sSQL
    
    Set rs = db.OpenRecordset(sSQL, dbReadOnly)
    
    'This command opens the report rptContactSearch and passes the query string sSQL via the Open Arguments parameter.
    'The On_Open function in rptContactSearch sets sSQL as the Record Source.
    DoCmd.OpenReport "rptPanelSearch", View:=acViewReport, OpenArgs:=sSQL
    DoCmd.OpenReport "rptPanelEmails", View:=acViewReport, OpenArgs:=sSQL
    
End Sub

[June7]

Looks like that should work but some aspects confuse me.

If you are setting the report's RecordSource why do you Set a recordset variable? You don't open the recordset.

You state code 'then plugs the SQL string into the query qryPanelSearch' when actually this code does not modify any query.

How many controls (including Labels) are on this form? Why cycle through all controls when you know exactly which controls need to be referenced?

[TheChairman]

Hi June7, sSQL is selecting all of its fields from qryPanelSearch. The whole code just adds to the "WHERE" clause. I basically modified an example code from somewhere else, which explains why I'm cycling through the controls. There are 4 text boxes and 3 combo boxes. This is the first time I've coded anything remotely of this scale before, so I'm very much a newbie. I guess I don't need to set the recordset since I already specify the recordsource, but I'm not sure if that will solve the problem when I split the database.

[TheChairman]

OK, I commented out the set rs = db.OpenRecordSet (sSQL,dbReadOnly) line and ONE of my reports now opens when I hit the submit button. But the second one doesn't. It doesn't matter which report it is. Whichever is listed first opens.

[June7]

No, doubt will fix, it is just clutter.

Maybe the issue is in the BuildCriteria function. Have you step debugged? Is the sSQL built as expected?

[TheChairman]

So, I'm still stuck on this, but made a crude workaround. Basically, I've discovered that I can only have ONE search form-generated report open at a time from ANY of my search forms.

I have three search forms, A, B and C.
Search Forms A and B can generate two reports (1 and 2) based on the user-generated SQL query. Search Form C generates one report.

If I open Search Form A, generate Report A1, then try to generate Report A2, A2 will not open unless I first close report A1. The same goes for every other report and search form.

If I open Search Form A, generate report A1, then open Search Form B and try to generate Reports B1 or B2, or open Search Form C and try to generate Report C1, none will open until I first close A1.

And the weirdest part:

If I open Search Form C, generate report C1, then open Search Form A (or B) and try to generate Report A1 (or A2 or B1 or B2), the report will not open until after I've closed BOTH C1 AND Search Form C.

I should point out that when the reports DO run, the results are just what I expected. I should also note that none of this happened until AFTER I split the database into a front end and a back end. I could have as many reports and search forms opened as I wanted before I split the database.

Still scratching my head...

[June7]

Sorry, no ideas! This is beyond my experience. My spilt database doesn't allow for multiple search forms and reports to be open.