Repopulating subform Recordset prompts for query parameters

0
votes

I have a Continuous subform that filters data based on a series of queries. I then need to populate a subform that I have placed in the footer based on some data, and I have crafted a query to accomplish this.

Private Sub UpdateXXXXX_Info()

    If (Me.FormFooter.Visible <> True) Then
        Me.FormFooter.Visible = True
    End If

    MsgBox "query start"

    LabelXXXXX_.Caption = "XXXXX for: " & Me.[XXXXX__NAME] & " " & Me.[XXXXX__NAME] & " " & Me.[XXXXX__CATEGORY_NAME]

    With CurrentDb.QueryDefs("XXXXX_Query")
        .Parameters("XXXXXParam") = Me.[XXXXX_NAME]
        .Parameters("XXXXXCategoryParam") = Me.[XXXXX_CATEGORY_NAME]
        Set Me.XXXXX__Subform.Form.Recordset = .OpenRecordset
        .Close

        MsgBox "query complete"

    End With
End Sub

Which gets called from Click events tied to each of the controls on the continuous form. This works great, and filters as I expect.

Due to the nature of the continuous form (which itself is a subform of another form), the entire continuous form may need to be refreshed:

Private Sub Form_Current()

    Me.FormFooter.Visible = False

End Sub

Which is causing an issue when refreshing the data. It works as-expected the first time around, however clicking on another record in the top continuous form causes the subform to prompt for query parameters. Based on testing, it is doing this when setting Me.FormFooter.Visible = True; although not setting Visible = False in the Current event circumvents this (which produces other, undesirable behavior).

Access will not let me .Close the Recordset, or otherwise set it to a blank or null value. I also cannot find any way to set the parameters for a Requery. The amount of data being presented also prohibits me from pulling the full table and applying a filter instead.

How can I prevent this behavior?

Edit: For clarification, I must use stored queries as a business requirement.

2
ms-access

2 Answers

1
votes

Use an SQL statement to (re)create the recordsource of the subform instead.

SQL = "SELECT * FROM xQuery WHERE xParam = '" & me!Name & "'" AND " & _
      "xCategoryParam = '" & me!CategoryName & """
me!subform.form.recordsource = SQL

This eliminates any requirements to open/close the recordset, and avoids having to change your original query definition. (i.e. create your parameterless query, and the WHERE command in the SQL does the heavy lifting for you.

0
votes

Aha!

geeFlo's answer pointed me to the RecordSource property of the form. Playing around with that, I was able to come up with the following:

Private Sub ClearXXXXXInfo()
    If Me.FormFooter.Visible = True Then
        Me.XXXXX_Subform.Form.RecordSource = ""
        Me.FormFooter.Visible = False
    End If
End Sub

Private Sub Form_Current()

    ClearXXXXXInfo

End Sub