MS Access: Subform Datasheet - Variable RecordSources

Question

I have a subform datasheet whose RecordSource can be variable. My database constructs an SQL query based on user selections (a different collection of columns with each query). The resulting query is intended to be the RecordSource for a datasheet on a subform. (Read-only view for user)

Problem:

The various queries produce the desired results when run on their own
Setting a resulting query as the datasheet's RecordSource does not produce any result (no columns/rows)

I suspect I need to insert the query's attributes into the subform in order to see any results (much like "Add Existing Fields" in the menu strip).

Question:

Any pointers to get me off square one?

Thank you!

Answer 1

Remove the datasheet form from your subform object and leave the source object property empty. Create a new query (sql doesn't matter) and name it qryTemp (or whatever you like). Then whenever you want to set the source for the subform, use this

CurrentDb.QueryDefs("qryTemp").SQL = "<your new sql here>"
<yoursubformobject>.SourceObject = "Query.qryTemp".

Answer 2

Here is an example I use to populate a sub-form:

Private Sub cmdFind_DisplayName_Click()
    Dim dbs As Database, rstPatient As Recordset
    Dim txtDisplayName, strQuote As String
    strQuote = Chr$(34)
    On Error GoTo ErrorHandler

    Me.OrderBy = "DISPLAYNAME"
    Me.OrderByOn = True

    Set dbs = CurrentDb
    Set rstPatient = Me.RecordsetClone
    txtDisplayName = Trim(InputBox("Please Enter Patient Name ", "Patient Find By Name"))
    txtDisplayName = UCase(txtDisplayName) & "*"
    If IsNull(txtDisplayName) Then
        MsgBox ("No Patient Name Entered - Please Enter a Valid Patient Name")
    Else
        rstPatient.FindFirst "[DISPLAYNAME] Like " & strQuote & txtDisplayName & strQuote
        If Not (rstPatient.NoMatch) Then
            Me.Bookmark = rstPatient.Bookmark
            Me.Refresh
        Else
            MsgBox ("Patient Not Found - Please Enter a New Patient Name")
        End If
    End If

    GoTo Exit_cmdFind_Click

    ErrorHandler:
        MsgBox LTrim(RTrim(Me.NAME)) + "." + "Patient Find By Display Name - " + "Error: " + AccessError(Err.Number)

    Exit_cmdFind_Click:
      rstPatient.Close
      Set dbs = Nothing
      Set rstPatient = Nothing
End Sub