ACCESS FEED: [MS_AccessPros] Reference objects in footer

Senin, 26 Agustus 2013

[MS_AccessPros] Reference objects in footer

I have a form called UnitList which is defined as a continuous form. I have added the form header and form footer to it. In the form footer I have created a 4 unbound combo and a command button called cmdApplyFilter. This is the click code:

Private Sub cmdApplyFilter_Click()
Dim stFilter As String
Dim stClass As String
Dim stRoomCount As String
Dim stStatus As String
Dim stCommunity As String

On Error GoTo HandleError
DoCmd.ShowAllRecords

'First I convert any Nulls to Strings of "" in the variables
'----------------------------------------------------------
stClass = Nz(Me.cboFilterClass, "")
stRoomCount = Nz(Me.cboFilterRoomCount, "")
stStatus = Nz(Me.cboFilterStatus, "")
stCommunity = Nz(Me.cboFilterCommunity, "")

'If the variables are not "" then I define the variable as that
'portion of the WHERE statement containing the field and the variables.
'----------------------------------------------------------
If stClass <> "" Then stClass = "[ClassType] = " & stClass & " "

If stRoomCount <> "" Then stRoomCount = " [BedroomCount] = '" & stRoomCount & "' "

If stStatus <> "" Then stStatus = " [UnitStatus] = '" & stStatus & "' "

If stCommunity <> "" Then stCommunity = " [CommunityName] = '" & stCommunity & "' " ' This is the Community

'Now I apply the filters if all or none are populated.
'----------------------------------------------------------

If stClass = "" And stRoomCount = "" And stStatus = "" And stCommunity = "" Then
DoCmd.ShowAllRecords
Me.Requery
GoTo EndCode
End If

If stClass <> "" And stRoomCount <> "" And stStatus <> "" And stCommunity <> "" Then
stFilter = stClass & " AND " & stRoomCount & " AND " & stStatus & " AND " & stCommunity
DoCmd.ApplyFilter , stFilter
DoCmd.Requery
GoTo EndCode
End If

'Since the above 2 conditions were not meet,
'the var - stFilter must be edited as follows:
'the applied as the filter
'----------------------------------------------------------
stFilter = stClass & " AND " & stRoomCount & " AND " & stStatus & " AND " & stCommunity

stFilter = Replace(stFilter, " AND AND ", " AND ", , , vbTextCompare)
stFilter = Replace(stFilter, " AND AND ", " AND ", , , vbTextCompare)
stFilter = Replace(stFilter, " AND AND ", " AND ", , , vbTextCompare)
If Left(stFilter, 5) = " AND " Then stFilter = Mid(stFilter, 6)
If Right(stFilter, 5) = " AND " Then stFilter = Left(stFilter, Len(stFilter) - 5)
stFilter = Trim(stFilter)

'Used to test the above sequence
'MsgBox stFilter & "|", vbOKOnly

DoCmd.ApplyFilter , stFilter
DoCmd.Requery
GoTo EndCode

EndCode:
Exit Sub

HandleError:
If Err.Number <> 2501 And Err.Number <> 0 Then
MsgBox Err.Number & " " & Err.Description
Else: Err.Clear
End If
Resume EndCode

End Sub

I have double checked the names of the combo boxes and they are correct. When I select a item out of the first combo (cboFilterClass) and click the cmdApply Filter it tells me cboFilterClass is not found. I know its probably a referencing issue but how do I fix it?

Thanks

Art Lorenzini
Sioux Falls, SD