Art (second attempt to answer you) Instead of using Nz(), try testing this way: If Len(Me.cboFilterClass & vbNullString)<>0 Then stClass = Me.cboFilterClass End If Also, when a control can't be found and you KNOW you spelled it right, you have some corruption going on. Try compiling and comment out every line that blows up. Then compact and uncomment those lines out one at a time and compile. Bill --- In MS_Access_Professionals@yahoogroups.com, <dbalorenzini@yahoo.com> wrote: 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
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar