I have a Unbound form with a Subform on the Unbound form. (frmProduct with fsubProduct) I display all my records on the subform using qryProduct.
I have a command button on the form that opens a form with several multi-list boxes. I can select data row(s) from the list boxes and from a command button, run code to retrieve a subset of records. These records are stored in a temp table and the main table tblProduct has a field that is updated with a Yes/No type field. When the form with List boxes closes, I want the subform to get updated with the "Yes" records ONLY displayed on the frmProduct when it gets the focus. How to apply a filter showing the "Yes" records only? I can always select the column and filter after the fact, but I wanted filtered with my subset On Focus. Tried many/many ways with no success. Any ideas appreciated.
[Code]
Private Sub OK_Click()
On Error GoTo OK_Click_Err
Dim varItem As Variant
Dim strWhere As String
Dim strWhere1 As String
Dim strWhere2 As String
Dim strWhere3 As String
Dim strWhere4 As String
Dim lngLen As Long
Dim strDelim As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
With Me!lstGroup
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & "'" & strDelim & .ItemData(varItem) & strDelim & "',"
End If
Next varItem
End With
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[Description] IN (" & Left$(strWhere1, lngLen) & ") "
End If
With Me!lstClass
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & "'" & strDelim & .ItemData(varItem) & strDelim & "',"
End If
Next varItem
End With
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[POP] IN (" & Left$(strWhere2, lngLen) & ") "
End If
With Me!lstBrand
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere3 = strWhere3 & "'" & strDelim & .ItemData(varItem) & strDelim & "' ,"
End If
Next varItem
End With
lngLen = Len(strWhere3) - 1
If lngLen > 0 Then
strWhere3 = "[Mfg Name] IN (" & Left$(strWhere3, lngLen) & ") "
End If
With Me!lstCode
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere4 = strWhere4 & "'" & strDelim & .ItemData(varItem) & strDelim & "' ,"
End If
Next varItem
End With
lngLen = Len(strWhere4) - 1
If lngLen > 0 Then
strWhere4 = "[Make] IN (" & Left$(strWhere4, lngLen) & ") "
End If
strWhere = strWhere1
If Len(strWhere) > 0 And Len(strWhere2) > 0 Then
strWhere = strWhere & " AND " & strWhere2
Else
strWhere = strWhere & strWhere2
End If
If Len(strWhere) > 0 And Len(strWhere3) > 0 Then
strWhere = strWhere & " AND " & strWhere3
Else
strWhere = strWhere & strWhere3
End If
If Len(strWhere) > 0 And Len(strWhere4) > 0 Then
strWhere = strWhere & " AND " & strWhere4
Else
strWhere = strWhere & strWhere4
End If
Set db = CurrentDb
'*** create the query based on the information on the form
strSQL = "SELECT qryProduct.* FROM qryProduct "
strSQL = strSQL & " WHERE " & strWhere
Set qdf = db.QueryDefs("qryProduct1")
qdf.SQL = strSQL
'*** open the query
strSQL1 = "SELECT qryProduct1.[Part Number], qryProduct1.Make, qryProduct1.[Mfg Name], qryProduct1.Description, qryProduct1.POP INTO tblTempMake " & vbCrLf & _
"FROM qryProduct1;"
strSQL2 = "UPDATE tblProduct SET tblProduct.Select = 0;"
strSQL3 = "UPDATE tblProduct INNER JOIN tblTempMake ON tblProduct.[Part Number] = tblTempMake.[Part Number] SET tblProduct.Select = -1;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2
DoCmd.RunSQL strSQL3
DoCmd.SetWarnings True
DoCmd.Close acForm, "frmProductCriteria"
'DoCmd.OpenForm "frmProduct", acNormal, "qryProduct1", , , acWindowNormal
DoCmd.ApplyFilter "qryProduct1", , "Forms!frmProduct.fsubProduct"
OK_Click_Exit:
Exit Sub
OK_Click_Err:
MsgBox Err.Description
Resume OK_Click_Exit
End Sub
/[code]