How to run a Query in a Subform?

[SteelFire via AccessMonster.com]

I have a subform that is showing a Query. The Query when run works just fine.
The query is being filtered by a VBA string acting as the SQL WHERE. The only
thing is how do I get the Query that is shown in the subform to undergo the
filtering that the the Query does.

 

[John W. Vinson]

I have a subform that is showing a Query. The Query when run works just fine.
The query is being filtered by a VBA string acting as the SQL WHERE. The only
thing is how do I get the Query that is shown in the subform to undergo the
filtering that the the Query does.

Just set the subform's Recordsource property to the SQL string and open the
form. It's not necessary nor appropriate to actually "run" the query.

 

[SteelFire via AccessMonster.com]

Thats the thing, I don't have an SQL string. Heres how the Query works:
=========================================================================
Private Sub cmdOK_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to
append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in
a JET query string.

On Error GoTo Err_Handler

'Start of Body of Procedure***********************************
If Not IsNull(Me.cboCategory) Then
strWhere = strWhere & "(tblPartTable.CategoryName = """ & Me.
cboCategory & """) AND "
End If

If Not IsNull(Me.cboType) Then
strWhere = strWhere & "(tblPartTable.CategoryType = """ & Me.cboType
& """) AND "
End If

If Not IsNull(Me.txtSDate) Then
strWhere = strWhere & "(tblPartTable.RevDate >= " & Format(Me.
txtSDate, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEDate) Then
strWhere = strWhere & "(tblPartTable.RevDate < " & Format(Me.txtEDate
+ 1, conJetDate) & ") AND "
End If

If Not IsNull(Me.cboDrawn) Then
strWhere = strWhere & "(tblPartTable.DrawnBy = """ & Me.cboDrawn &
""") AND "
End If

If Not IsNull(Me.cboPart) Then
strWhere = strWhere & "(tblPartTable.PartNum = """ & Me.cboPart & """)
AND "
End If

If Not IsNull(Me.cboJob) Then
strWhere = strWhere & "(tblPartTable.JobNum = """ & Me.cboJob & """)
AND "
End If

If Not IsNull(Me.cboLocation) Then
strWhere = strWhere & "(tblPartTable.CustomerID In (""SELECT DISTINCT
CustomerID FROM qryCustomers"")) AND"
End If

If Not IsNull(Me.txtDesc) Then
strWhere = strWhere & "(tblPartTable.PartDescription Like ""*" & Me.
txtDesc & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
DoCmd.Minimize
DoCmd.OpenForm "frmPartList", acNormal, , strWhere
strWhere = ""
End If
'End of Body of Procedure***********************************

Exit_Handler:
Exit Sub
Err_Handler:
Call LogError(Err.Number, Err.Description, "cmdOK_Click from
Form_frmPartFinder")
Resume Exit_Handler
End Sub
=========================================================================
The Queries results are shown on the form inside of text boxes. Only one
record is shown at a time. I also have the SubForm at the bottom that shows
multiple records at a time in a Datasheet View. The SubForm's Source Object
is Query.qryPartTable. That Query is also the Record Source for the Form.
In the code line *****{DoCmd.OpenForm "frmPartList", acNormal, , strWhere}
***** the form is filtered, but I don't know how to get the same filtering to
happen in the SubForm.
Oh, the SQL for the Query is:
=========================================================================
SELECT tblPartTable.ID, tblPartTable.ProjectName, tblPartTable.
PartDescription, tblPartTable.PartNum, tblPartTable.JobNum, tblPartTable.
CategoryName, tblPartTable.CategoryType, tblPartTable.Rev, tblPartTable.
RevDate, tblPartTable.CustomerID, tblPartTable.FilePath, tblPartTable.
PartDrawingAtt, tblPartTable.Notes
FROM tblPartTable;
=========================================================================

Does this clear some things up?