Filtering Unbound Subform from Main form with combo boxes HELP

  • Thread starter Thread starter heidii
  • Start date Start date
H

heidii

Hello All:

Below I have pasted the filter code that I have been using on a lot of
my continious forms. It works great, and thank you to who ever
created this code. I found it here on this site.

I would now like to use the same code to filter a subform that is in
datasheet view. My subform is not related to the main form. There is
no records source on the main form. It is just the frame holding my
subform so that I can have combo boxes on the form to use on the
subform. I can not use the combo boxes on the subform as I usually do
as I want this subform to remain in datasheet view so the users can
hide and unhide columns as they wish.

My question is this: How can I use the code I have below to reference
my fields on the subform so they will filter correctly.

I have been trying to implement forms![FRM-RANCH_INFO_SEARCH].[QRY-
RANCH_INFO_RANCH_VIEW1].FORM.[LOT#] but this does not seem to work.
Can anyone help me implement this with the code I have.

Thanks much

Heidi

Private Sub cmdsearch_Click()

Dim blnLOTNUMBER As Boolean
blnLOTNUMBER = IsNull(Me.CBOLOTNUMBER)

Dim blnRANCH As Boolean
blnRANCH = IsNull(Me.CBORANCH)

Dim blnWSDACERTNUMBER As Boolean
blnWSDACERTNUMBER = IsNull(Me.CBOWSDACERTNUMBER)

Dim blnBLOCKTYPE As Boolean
blnBLOCKTYPE = IsNull(Me.cboBLOCKTYPE)

Dim blnSTATUS As Boolean
blnSTATUS = IsNull(Me.cbostatus)

Dim blnCOMMODITY As Boolean
blnCOMMODITY = IsNull(Me.cboCOMMODITY)

Dim blnWSDASITENUMBER As Boolean
blnWSDASITENUMBER = IsNull(Me.CBOWSDASITENUMBER)

Dim blnMASTERVARIETY As Boolean
blnMASTERVARIETY = IsNull(Me.cboMASTERVARIETY)

Dim blnVARIETY As Boolean
blnVARIETY = IsNull(Me.cboVARIETY)

Dim blnYEARPLANTED As Boolean
blnYEARPLANTED = IsNull(Me.cboYEARPLANTED)

Dim blnYEARGRAFTED As Boolean
blnYEARGRAFTED = IsNull(Me.cboYEARGRAFTED)


Dim strLOTNUMBER As String

If blnLOTNUMBER = False Then
strLOTNUMBER = "[LOT#]=" & """" & Me.CBOLOTNUMBER & """"
Else
strLOTNUMBER = ""
End If

Dim strTYPE As String

If blnRANCH = False Then
strRANCH = " [RANCH]=" & """" & Me.CBORANCH & """"
Else
strRANCH = ""
End If

Dim strWSDACERTNUMBER As String

If blnWSDACERTNUMBER = False Then
strWSDACERTNUMBER = " [WSDACERT#]=" & """" & Me.CBOWSDACERTNUMBER &
""""
Else
strWSDACERTNUMBER = ""
End If

Dim strBLOCKTYPE As String

If blnBLOCKTYPE = False Then
strBLOCKTYPE = " [BLOCKTYPE]=" & """" & Me.cboBLOCKTYPE & """"
Else
strBLOCKTYPE = ""
End If

Dim strSTATUS As String

If blnSTATUS = False Then
strSTATUS = " [STATUS]=" & """" & Me.cbostatus & """"
Else
strSTATUS = ""
End If

Dim strCOMMODITY As String

If blnCOMMODITY = False Then
strCOMMODITY = " [COMMODITY]=" & """" & Me.cboCOMMODITY & """"
Else
strCOMMODITY = ""
End If

Dim strWSDASITENUMBER As String

If blnWSDASITENUMBER = False Then
strWSDASITENUMBER = " [WSDA SITE NUMBER]=" & """" &
Me.CBOWSDASITENUMBER & """"
Else
strWSDASITENUMBER = ""
End If

Dim strMASTERVARIETY As String

If blnMASTERVARIETY = False Then
strMASTERVARIETY = " [MASTER VARIETY]=" & """" & Me.cboMASTERVARIETY
& """"
Else
strMASTERVARIETY = ""
End If

Dim strVARIETY As String

If blnVARIETY = False Then
strVARIETY = " [VARIETY]=" & """" & Me.cboVARIETY & """"
Else
strVARIETY = ""
End If

Dim strYEARPLANTED As String

If blnYEARPLANTED = False Then
strYEARPLANTED = " [PLANTED]=" & """" & Me.cboYEARPLANTED & """"
Else
strYEARPLANTED = ""
End If

Dim strYEARGRAFTED As String

If blnYEARGRAFTED = False Then
strYEARGRAFTED = " [GRAFTED]=" & """" & Me.cboYEARGRAFTED & """"
Else
strYEARGRAFTED = ""
End If


Dim strFilter As String
strFilter = ""

If strLOTNUMBER <> "" Then
strFilter = strFilter & Chr(32) & "AND" & Chr(32) & strLOTNUMBER
Else
strFilter = strFilter
End If

If strRANCH <> "" Then
strFilter = strFilter & Chr(32) & "AND" & Chr(32) & strRANCH
Else
strFilter = strFilter
End If

If strWSDACERTNUMBER <> "" Then
strFilter = strFilter & Chr(32) & "AND" & Chr(32) & strWSDACERTNUMBER
Else
strFilter = strFilter
End If

If strBLOCKTYPE <> "" Then
strFilter = strFilter & Chr(32) & "AND" & Chr(32) & strBLOCKTYPE
Else
strFilter = strFilter
End If

If strSTATUS <> "" Then
strFilter = strFilter & Chr(32) & "AND" & Chr(32) & strSTATUS
Else
strFilter = strFilter
End If

If strCOMMODITY <> "" Then
strFilter = strFilter & Chr(32) & "AND" & Chr(32) & strCOMMODITY
Else
strFilter = strFilter
End If

If strWSDASITENUMBER <> "" Then
strFilter = strFilter & Chr(32) & "AND" & Chr(32) & strWSDASITENUMBER
Else
strFilter = strFilter
End If

If strMASTERVARIETY <> "" Then
strFilter = strFilter & Chr(32) & "AND" & Chr(32) & strMASTERVARIETY
Else
strFilter = strFilter
End If

If strVARIETY <> "" Then
strFilter = strFilter & Chr(32) & "AND" & Chr(32) & strVARIETY
Else
strFilter = strFilter
End If

If strYEARPLANTED <> "" Then
strFilter = strFilter & Chr(32) & "AND" & Chr(32) & strYEARPLANTED
Else
strFilter = strFilter
End If

If strYEARGRAFTED <> "" Then
strFilter = strFilter & Chr(32) & "AND" & Chr(32) & strYEARGRAFTED
Else
strFilter = strFilter
End If

Dim lngLenghOfFilter As Long
lngLenghOfFilter = Len(strFilter)

strFilter = Mid(strFilter, 5, lngLenghOfFilter)

MsgBox strFilter 'you can use it for check



Me.Filter = strFilter
Me.FilterOn = True
End Sub
 
I do this a little differently:

Private Sub cmdsearch_Click()
Dim strFilter As String

If Not IsNUll(Me.CBOLOTNUMBER) Then
strFilter = "[LOT#]=" & """" & Me.CBOLOTNUMBER & """"
End If

If Not IsNull(Me.CBORANCH) Then
strFilter = AddAnd(strFilter)
strFliter = strFilter & "[RANCH]=" & """" & Me.CBORANCH & """"
End If

If Not IsNull(M.CBOWSDACERTNUMBER) Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[WSDACERT#]=" & """" & Me.CBOWSDACERTNUMBER &
""""
End If

If Not IsNull(Me.cboBLOCKTYPE) Then
strFilter = AddAnd(strFilter)
strFliter = strFilter & "[BLOCKTYPE]=" & """" & Me.cboBLOCKTYPE & """"
End If

If Not IsNull(Me.cbostatus) Then
strFilter = AddAnd(strFilter)
strFliter = strFilter & "[STATUS]=" & """" & Me.cbostatus & """"
End If

If Not IsNull( Me.cboCOMMODITY ) Then
strFilter = AddAnd(strFilter)
strFliter = strFilter & "[COMMODITY]=" & """" & Me.cboCOMMODITY & """"
End If

If Not IsNull( Me.CBOWSDASITENUMBER ) Then
strFilter = AddAnd(strFilter)
strFliter = strFilter & "[WSDA SITE NUMBER]=" & """" &
e.CBOWSDASITENUMBER & """"
End If


If Not IsNull( Me.cboMASTERVARIETY) Then
strFilter = AddAnd(strFilter)
strFliter = strFilter & "[MASTER VARIETY]=" & """" &
Me.cboMASTERVARIETY
& """"
End If

If Not IsNull( Me.cboVARIETY) Then
strFilter = AddAnd(strFilter)
strFliter = strFilter & "[VARIETY]=" & """" & Me.cboVARIETY & """"
End If

If Not IsNull( Me.cboYEARPLANTED) Then
strFilter = AddAnd(strFilter)
strFliter = strFilter & "[PLANTED]=" & """" & Me.cboYEARPLANTED & """"
End If

If Not IsNull( Me.cboYEARGRAFTED) Then
strFilter = AddAnd(strFilter)
strFliter = strFilter & "[GRAFTED]=" & """" & Me.cboYEARGRAFTED & """"
End If


Me.SubformControlName.Form.Filter = strFilter
Me.SubformControlName.Form.FilterOn = True
End Sub

I don't know the name of your subform control, so you will need to replace
SubFormControlName with the name of your control. It is not the name of the
form being used as the subform, but the name of the subform control on the
main form.

I did this pretty quickly, so it is possible there will be syntax errors,
but I'm sure you can clean them up. Notice this takes a lot less code and
will be faster. Below is the code for the AddAnd function. What is does is
add the AND to your filter string when it is needed.

'---------------------------------------------------------------------------------------
' Procedure : AddAnd
' DateTime : 2/9/2008 17:26
' Author : Dave Hargis
' Purpose : Adds the word AND to a filtering string when neede
'---------------------------------------------------------------------------------------
'
Private Function AddAnd(strFilterString) As String
On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If


AddAnd_Exit:

Exit Function
On Error GoTo 0

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of VBA Document Form_frmDashBoard"
GoTo AddAnd_Exit

End Function
[/QUOTE]
 
Back
Top