'Select' parameter query help

[DAW]

I need to passa parameter to a select query that shows info on a subform. I understand that I cannot execute a select query, such as qry.Parameters![ONo] = OrdNo so what is the alternative? How do I do this?

 

[DAW]

Hmm... am I getting close?

Private Sub cbxQV_Change()
    Dim SQL$
    Dim rs As DAO.Recordset
    '-------------------------
    SQL = "SELECT tbl_Extract.Arrival_Time, tbl_Extract.Case_ID_ AS [RT#], tbl_Extract.Status, " _
    & "tbl_Priorities.PriDef AS Priority, tbl_Extract.Requester_Name_ AS Requester, " _
    & "tbl_Extract.Assigned_To_Individual_ AS Owner, tbl_Extract.Description " _
    & "FROM tbl_Priorities INNER JOIN tbl_Extract ON tbl_Priorities.PriID = tbl_Extract.Priority " _
    & "WHERE (((tbl_Extract.Arrival_Time) Between Date() And Date()-2)) " _
    & "ORDER BY tbl_Extract.Arrival_Time DESC;"
    
    Set rs = CurrentDb.OpenRecordset(SQL)
    Me.cldSwitch.SourceObject = rs

    cldSwitch.Requery
    Set rs = Nothing
End Sub

Me.cldSwitch.SourceObject = rs doesn't work - type mismatch.

I can then manipulate the SQL string to accept my parameter

 

[DAW]

Sussed it. Should anyone else find this useful:

Private Sub txtNoDays_KeyDown(KeyCode As Integer, Shift As Integer)
    Dim SQL$
    Dim D%
    Dim QryDef As QueryDef
    '---------------------
    If KeyCode = 13 Then
        D = Val(txtNoDays.Text)
        SQL = "SELECT...[I]whatever[/I]"
        If FindQuery("qry_QD") = True Then  CurrentDb.QueryDefs.Delete "qry_QD"
        Set QryDef = CurrentDb.CreateQueryDef("qry_QD", SQL)
        Me.cldSwitch.SourceObject = "query.qry_QD"
        Me.cldSwitch.Requery
    End If
End Sub

Public Function FindQuery(QName) As Boolean
    Dim QryDef As QueryDef
    '----------
    For Each QryDef In CurrentDb.QueryDefs
        If LCase(QryDef.Name) = LCase(QName) Then
            FindQuery = True
            Exit For
        End If
    Next
End Function

Obviously in the SQL string parameters can be built in.

 

[LPurvis]

If you have a form which you're able to bind the query results to then you were almost there with your first stab at it.

Set rs = CurrentDb.OpenRecordset(SQL)
Set Me.cldSwitch.Form.Recordset = rs
Set rs = Nothing

It's not as if changing the WHERE clause will alter the fields selected (the usual reason for binding a subform control to a Query directly - unpredictable results).

Your current solution will work (and can be useful as mentioned) - but you do lose some control with a directly bound query rather than a form. (i.e. the raising of and responding to Events).