Alternative to 500+ lines of If, Then, Else... To Filter Form

[Vaulcul]

Hello,

I'm struggling w/ trying to get a form to filter another form through code. I know the code works on a smaller scale... but have a suspicion that there is a better more efficient way of writing it. In my current form I get the "Procedure too large" error for the filtering code (There are over 500 lines of If, ElseIf statements).

Currently, the filtering form has 9 control fields to use to filter another form. So for instance, one of the controls is a list of buildings. When a building is selected the associated form is filtered by buildings. When another control has an item selected in addition to the building filter... say room type, the filter would stack and show only buildings with rooms of that specific type (all filter controls stack the filter).

To give you an idea of the code that I've written:

If Nz(Forms!RM_Room_Filter.BuildingList) = "" And Nz(Forms!RM_Room_Filter.RoomList) = "" And Nz(Forms!RM_Room_Filter.SupervisorList) = "" And Nz(Forms!RM_Room_Filter.RMTypeList) = "" And Nz(Forms!RM_Room_Filter.AccountantList) = "" And Nz(Forms!RM_Room_Filter.AdminList) = "" And Nz(Forms!RM_Room_Filter.RMLowFilter) = "" And Nz(Forms!RM_Room_Filter.RMHighFilter) = "" Then
            Me.Filter = ""
            Me.FilterOn = False
 
    ElseIf Forms!RM_Room_Filter.BuildingList.ListIndex <> -1 And Nz(Forms!RM_Room_Filter.RoomList) = "" And Nz(Forms!RM_Room_Filter.SupervisorList) = "" And Nz(Forms!RM_Room_Filter.RMTypeList) = "" And Nz(Forms!RM_Room_Filter.AccountantList) = "" And Nz(Forms!RM_Room_Filter.AdminList) = "" And Nz(Forms!RM_Room_Filter.RMLowFilter) = "" And Nz(Forms!RM_Room_Filter.RMHighFilter) = "" Then
            Me.Filter = "[Building_ID] = " & Forms!RM_Room_Filter.BuildingList
            Me.FilterOn = True
 
    ElseIf Forms!RM_Room_Filter.BuildingList.ListIndex <> -1 And Forms!RM_Room_Filter.RoomList.ListIndex <> -1 And Nz(Forms!RM_Room_Filter.SupervisorList) = "" And Nz(Forms!RM_Room_Filter.RMTypeList) = "" And Nz(Forms!RM_Room_Filter.AccountantList) = "" And Nz(Forms!RM_Room_Filter.AdminList) = "" And Nz(Forms!RM_Room_Filter.RMLowFilter) = "" And Nz(Forms!RM_Room_Filter.RMHighFilter) = "" Then
            Me.Filter = "[Building_ID] = " & Forms!RM_Room_Filter.BuildingList & "And [RoomNumber] = " & Forms!RM_Room_Filter.RoomList
            Me.FilterOn = True
 
    ElseIf Nz(Forms!RM_Room_Filter.BuildingList) = "" And Nz(Forms!RM_Room_Filter.RoomList) = "" And Forms!RM_Room_Filter.SupervisorList.ListIndex <> -1 And Nz(Forms!RM_Room_Filter.RMTypeList) = "" And Nz(Forms!RM_Room_Filter.AccountantList) = "" And Nz(Forms!RM_Room_Filter.AdminList) = "" And Nz(Forms!RM_Room_Filter.RMLowFilter) = "" And Nz(Forms!RM_Room_Filter.RMHighFilter) = "" Then
            Me.Filter = "[Supervisor_ID] = " & Forms!RM_Room_Filter.SupervisorList
            Me.FilterOn = True
 

... Hope that all makes sense as to what I'm trying to accomplish.

Thanks for your help in advance.

[jimatqsi]

Vaulcul,
Thanks for an interesting question. Here is some code that will loop through all the list boxes on the form and dynamically make filter string. I haven't tested it, so it may be worth no more than the price charged.

Dim strC As String ' criteria
strC = ""
 
Dim ctl As Control
 
For Each ctl In Forms!RM_Room_Filter.Controls
    If ctl.Type = acListBox Then
        If ctl.ListIndex <> -1 Then             ' is anything selected?
                  strC = strC & " and " & ctl.Tag   ' the tag of the list control must contain the field name to be filtered
                  strC = strC & "= "      ' alternatively this could be part of the tag
                  strC = strC & ctl.Value  ' or ctl.Column(0)
                End If
    End If
Next
 
If strC <> "" Then
    strC = Mid(strC, 4) ' chop off first " and  "
     Me.Filter = strC
     Me.FilterOn = True
else
     Me.Filter = ""
     Me.FilterOn = False
End If
 
 

Your post makes it appear that all the fields to be filtered are numeric. If that is not the case you will have add some small bit of code to make sure the filtered values are wrapped in quote marks. Related to that, note that I am pulling filter's value from Column(0), you may need to change that depending on how the list box is defined.

Good luck. Let me know how it turns out.

Jim

[Vaulcul]

jimatqsi,

Wow! That's awesome code, I had no idea I could do that (still teaching my brain to think like a programmer). I made very few edits to what you provided.

The code I ended up with was:

Dim strC As String ' criteria
 strC = ""
 
 Dim ctl As Control
 
 For Each ctl In Forms!RM_Room_Filter.Controls
     If ctl.ControlType = acListBox Then
         If ctl.ListIndex <> -1 Then             ' is anything selected?
                   strC = strC & " and " & ctl.Tag   ' the tag of the list control must contain the field name to be filtered
                   strC = strC & "= "      ' alternatively this could be part of the tag
                   strC = strC & ctl.Value  ' or ctl.Column(0)
                 End If
     End If
 Next
 
 If strC <> "" Then
     strC = Mid(strC, 6) ' chop off first " and  "
      Me.Filter = strC
      Me.FilterOn = True
 Else
      Me.Filter = ""
      Me.FilterOn = False
 End If
 

I do have a question left over though.

So, this code covers all of the listboxes... I'm a little confused as to how I would also include text boxes in the filtering process. There are two text boxes RMHigh and RMLow so that the user could also filter by the highest and lowest room numbers, how would I incorporate those into this?

Thanks a ton for all of your help.

[zmbd]

Duplicate lines 6 thru 14 in your finished code
Alter duplicate line 7 to check for text box type
Alter duplicate line 8 to check for null or zerolength sring
(see this article: What is Null?)
Use duplicate lines 9 thru 11 basically as is.

Post back altered code if you run into issues along with the line where any error occurs and the exact number and description.

[jimatqsi]

The only reason for separating the treatment of the text boxes and the list boxes is that the properties are different. Textbox has no ListIndex property. So you could add similar code to handle the acTextBox type of control, in which you check for the empty text box with (len(nz([textboxname]))=0) instead of ListIndex=-1.

There's nothing to stop you from adding checkbox filters and whatever else you need. You just have to add the code to handle each filter, either with generalized code based on the object type or based on some specific filter names as you had originally. That gets out of hand if you have too many needing the specific name handling, as you found out.

About your High/Low Room requirement, look back at my comment about the "=". I said that could be part of the object's tag instead of hardcoded. You could put both the field name and the comparison operator in the tag; that way the program code doesn't need to know whether it is an = or < or > that is being used, it's all pre-ordained by the content of the filter object's tag.

NeoPa posted an article about filtering forms. It might be helpful to look that up, also.

[Vaulcul]

Thank you zmbd and jimatqsi for your help.

I think I'm close, but I'm having some problems.

I've been struggling with getting the code not to error out and I'm baffled at the error.

I'm getting a 'Runtime 2447 error "Invalid use of . (dot) or ! operator or invalid use of parentheses' (Marked error area w/ underline)

        Dim strC As String    ' criteria
        strC = ""
 
        Dim ctl As Control
 
        For Each ctl In Forms!RM_Room_Filter.Controls
            If ctl.ControlType = acListBox Then
                If ctl.ListIndex <> -1 Then             ' is anything selected?
                    strC = strC & " and " & ctl.Tag   ' the tag of the list control must contain the field name to be filtered
                    'strC = strC & "= "      ' alternatively this could be part of the tag
                    strC = strC & ctl.Value  ' or ctl.Column(0)
                End If
            End If
        Next
 
        For Each ctl In Forms!RM_Room_Filter.Controls
            If ctl.ControlType = acTextBox Then
                If Not (Len(Nz(ctl.Name)) = 0) Then            ' is anything selected?
                    strC = strC & " and " & ctl.Tag   ' the tag of the list control must contain the field name to be filtered
                    'strC = strC & "= "      ' alternatively this could be part of the tag
                    strC = strC & ctl.Value  ' or ctl.Column(0)
                End If
            End If
        Next
 
        If strC <> "" Then
            strC = Mid(strC, 6)    ' chop off first " and  "
            Me.Filter = strC
            Me.FilterOn = True
        Else
            Me.Filter = ""
            Me.FilterOn = False
        End If
 

I know I've had problems in the past due to data types not matching... I'm reluctant to believe this is the case here though, as the roomnumber field is a string field not a number field (some rooms have letters after them).

Again thanks for your help in advance.

[jimatqsi]

Change

If Not (Len(Nz(ctl.Name)) = 0) Then 

to

If Not (Len(Nz(ctl.Value)) = 0) Then 

My posting of this code misled you a bit. What you really want to know is "does this control have any value to filter with". Your code was actually checking the length of the name of the control, not the length of the value.

Jim

[Vaulcul]

Thanks jimatqsi,

I struggled with deciding whether to use .Name or .Value...

However, switching over to .Value moves the error (same error) from line 21 to line 18.

I thought that maybe this might be a corruption issue in my database, so I created a new database, reconnected all of the pertinent tables, copied the appropriate SQL statements and then copy/pasted the forms. After doing so and running the filter the error changed to: "Run-time error '0' Reserved Error" referencing the same line (line 18). Thinking that it might have been an issue with the form with the code in it, I recreated the form from scratch... getting the same error.

I have found very little on this particular error while Googling... The only answer that I've found (that I dislike) is to force Access to ignore the error.

When I debug the error I can see that it's passing the low value... but then doesn't grab any of the other filters (they all appear as X).

I also know that this has to do with the textboxes, because I can remove the textbox code and everything works well.

What am I missing?

Thanks again for all your help.

[Luk3r]

This may be a shot in the dark because I'm not VBA guru.. but I know a fair amount about VB.NET. That being said, there is no ".value" for textboxes in VB.NET, but there is ".name". So I can only assume the reason your code is erroring out is because you a providing a property that doesn't exist for the textbox control. Try changing line 18 and 21 both to .name. I'm not sure exactly what value you're hoping for from "ctl.Value" so I apologize if I don't fully understand the question but you look like you're having some trouble so I thought I'd chime in. :)


Edit: I went back and re-read the posts and it also looks like I spoke partially prematurely. It looks like on line 18 you'd actually want to get the length of the .text and not of the .name nor .value (since .value doesn't exist as a textbox property and .name is the name of the control). Again, this is just me throwing out an idea in hopes that it provokes a great thought from someone with real skills.

[Vaulcul]

Luk3r,

Thanks for your reply.

I think that .Value is a valid property in VBA...
at least I can find it on Microsoft's Textbox members page

(http://msdn.microsoft.com/en-us/libr...ffice.15).aspx)

I tried changing the code to the .Text property as you suggested. It still errors out on the same line, but with a different run-time error (2185) "You can't reference a property or method for a control unless the control has the focus"

I think this is happening for the .Text property because the code isn't giving explicit focus to the textbox controls... though I'm not positive.

Thanks for your input.

[jimatqsi]

Vaulcul,
Looking again at a portion of the code you posted:

If Not (Len(Nz(ctl.Name)) = 0) Then            ' is anything selected?
                    strC = strC & " and " & ctl.Tag   ' the tag of the list control must contain the field name to be filtered
                    'strC = strC & "= "      ' alternatively this could be part of the tag
                    strC = strC & ctl.Value  ' or ctl.Column(0)
                End If

The use of ctl.Name in the first line guarantees that test will never fail. The length of the name is always greater than zero. that means you will fall into and try to execute the following lines even when the value in the control is NULL. I suspect the NULL value is contributing to your problem.

.Value is the property for the contents of a textbox in VBA. Change the (Len(Nz(ctl.Name)) to (Len(Nz(ctl.VALUE ))and then report back as to results.

Jim

[Vaulcul]

jimatqsi,

I have changed the code from (Len(Nz(ctl.Name)) to (Len(Nz(ctl.Text )) and (Len(Nz(ctl.VALUE )) and all of them give me the run time errors that I've reported earlier.

... I'm not really sure what else to try at this point.

[Luk3r]

Ahhh. You are correct. Good thing I said I'm not great at VBA :). I do wonder a couple of things though:
1) If you put data in EVERY textbox, do you still get the error?
2) Have you tried creating a string from the control's value, then checking the length of that string instead of directly checking the length of the control value?
3) What's "Nz" in (Len(Nz(ctl.Name)) = 0)? All the documentation I see the use is just: Len(Str). This is more of a question for personal knowledge. I'm absolutely not calling someone out on what they know. I'm just curious.

[jimatqsi]

Vaulcul,
.Text should give you a runtime error 2185. That is not a valid textbox property.

If you are now testing the length of the .Value of the textboxes, and if the problem is related only to processing textbox filters, then you should get a successful test by using listbox filters only, but leaving all the textbox filters blank. Please start by proving that to be true.

If it is so, then try each text box, one at a time, to demonstrate that all the textbox filters cause a problem or, if not, which textbox filters cause the problem. It would be interesting to know what you have put into the .Tag properties of each textbox.

Since you are familiar with debugging this should not be too hard to figure out.

Jim

[jimatqsi]

Luk3r, the NZ() function is a NULL handler. You use a different method in the .Net world to deal with Nulls. NZ(something) just is a safe way to probe a string that might be NULL.

Jim

[Vaulcul]

Luk3r,

Thanks for your interest, the more heads the better. :)

jimatqsi,

I've found why I was getting the run-time errors mentioned above... I think.

So the code for setting the filter was set for this:

If strC <> "" Then
            strC = Mid(strC, 10)    ' chop off first " and  "
            Me.Filter = strC
            Me.FilterOn = True
 

When it should have been set to this:

If strC <> "" Then
            strC = Mid(strC, 6)    ' chop off first " and  "
            Me.Filter = strC
            Me.FilterOn = True
 

I think I changed the Mid setting to 10 when I first started getting errors with the .Name property and then I wasn't focusing on the line of code....

Then I remembered that I had 6 more text boxes on the form than what I was thinking, all with the letter "X" as their value and set as a hyperlink... these were used to clear the value of the listboxes... basically turning off the filter for the specific list box they were associated with.

These "X"'s were being included in the filter from what I was seeing in the debug... But when I first saw them my brain interpreted them as place holders for the listboxes.... Once I figured out that they weren't place holders, but rather the text from the 6 textboxes, I was able to get the filters to come out correctly by adjusting the code as follows:

        Dim strC As String    ' criteria
        strC = ""
 
        Dim ctl As Control
 
        For Each ctl In Forms!RM_Room_Filter.Controls
            If ctl.ControlType = acListBox Then
                If ctl.ListIndex <> -1 And Not (Len(Nz(ctl.Tag)) = 0) Then             ' is anything selected?
                    strC = strC & " and " & ctl.Tag & " "   ' the tag of the list control must contain the field name to be filtered
                    'strC = strC & "= "      ' alternatively this could be part of the tag
                    strC = strC & ctl.Value  ' or ctl.Column(0)
                End If
            End If
        Next
        For Each ctl In Forms!RM_Room_Filter.Controls
            If ctl.ControlType = acTextBox Then
                If Not (Len(Nz(ctl.Value)) = 0) And Not (Len(Nz(ctl.Tag)) = 0) Then            ' is anything selected?
                    strC = strC & " and " & ctl.Tag & " "  ' the tag of the list control must contain the field name to be filtered
                    'strC = strC & "= "      ' alternatively this could be part of the tag
                    strC = strC & ctl.Value  ' or ctl.Column(0)
                End If
            End If
        Next
 
        If strC <> "" Then
            strC = Mid(strC, 6)    ' chop off first " and  "
            Me.Filter = strC
            Me.FilterOn = True
        Else
            Me.Filter = ""
            Me.FilterOn = False
        End If
 

Now my only concern is... I still get a runtime error when a room number is present... but this time it's telling me that I have a type mismatch, which in my mind is progress (the error isn't so cryptic).

I'm assuming that I need a way of incorporating cbool() or something like that because the room numbers are still being interpreted as numbers and not a string (their set as strings in the table to allow for room numbers with letters)... somewhere... maybe...

FYI - I have three textboxes with tags
LowRoom tag is: roomnumber <=
HighRoom tag is: roomnumber >=
ArcDate tag is: arcyear =

Thanks again for your help.

[jimatqsi]

Vaulcul,
You may recall way back in my first post I say "Your post makes it appear that all the fields to be filtered are numeric." If you are comparing string values then you have to wrap the value to compare in quotes. So

strC = strC & ctl.Value 

should be

strC = strC & "'" & ctl.Value  & "' " 

Numeric compares don't need quotes around the value being compared.

Jim

[Vaulcul]

jimatqsi,

Honestly... I should have known that I needed to wrap the text in quotes... shame on me.

Thanks for all of your patience and help on this. I think I finally have the working code for this.

Here's what I finally ended up with:

    Dim strC As String    ' criteria
    strC = ""
 
    Dim ctl As Control
 
    For Each ctl In Forms!RM_Room_Filter.Controls
        If ctl.ControlType = acListBox Then
            If ctl.ListIndex <> -1 And Not (Len(Nz(ctl.Tag)) = 0) Then             ' is anything selected?
                strC = strC & " and " & ctl.Tag & " "   ' the tag of the list control must contain the field name to be filtered
                'strC = strC & "= "      ' alternatively this could be part of the tag
                strC = strC & ctl.Value  ' or ctl.Column(0)
            End If
        End If
    Next
    For Each ctl In Forms!RM_Room_Filter.Controls
        If ctl.ControlType = acTextBox Then
            If Not (Len(Nz(ctl.Value)) = 0) And Not (Len(Nz(ctl.Tag)) = 0) Then            ' is anything selected?
                If ctl = Forms!RM_Room_Filter.ArcDate Then
                    strC = strC & " and " & ctl.Tag & " "  ' the tag of the list control must contain the field name to be filtered
                    'strC = strC & "= "      ' alternatively this could be part of the tag
                    strC = strC & ctl.Value  ' or ctl.Column(0)
                Else
                    strC = strC & " and " & ctl.Tag & " "  ' the tag of the list control must contain the field name to be filtered
                    'strC = strC & "= "      ' alternatively this could be part of the tag
                    strC = strC & "'" & ctl.Value & "' "  ' or ctl.Column(0)
                End If
            End If
        End If
    Next
 
    If strC <> "" Then
        strC = Mid(strC, 6)    ' chop off first " and  "
        Me.Filter = strC
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
 

[jimatqsi]

So glad you got a good result. It is so empowering to know about that generalized access to controls. I'm sure you'll fun with that. I do.

Jim

[Seth Schrock]

Your code would run faster if you put both of your control type tests inside the first loop. That way you are only looping through your controls once. Personally, I would use the SELECT CASE statement, but there would be no problem just putting lines 16-28 between lines 13 and 14. You can then get rid of your second loop statement.

[Vaulcul]

Seth,

Thanks for the input.

After reading what you had to say I've updated my code to the following (and feeling really good about it):

    Dim strC As String    ' criteria
    Dim ctl As Control
 
    strC = ""
 
    For Each ctl In Forms!RM_Room_Filter.Controls
 
        If Not (Len(Nz(ctl.Tag)) = 0) Then
 
            Select Case ctl.ControlType
 
            Case acCheckBox
 
                If ctl.Value = 0 Then             ' is anything selected?
                    strC = strC & " and " & ctl.Tag & " "   ' the tag of the list control must contain the field name to be filtered
                    'strC = strC & "= "      ' alternatively this could be part of the tag
                    strC = strC & ctl.Value  ' or ctl.Column(0)
                End If
 
            Case acListBox
 
                If ctl.ListIndex <> -1 Then             ' is anything selected?
                    strC = strC & " and " & ctl.Tag & " "   ' the tag of the list control must contain the field name to be filtered
                    'strC = strC & "= "      ' alternatively this could be part of the tag
                    strC = strC & ctl.Value  ' or ctl.Column(0)
                End If
 
            Case acTextBox
 
                If Not (Len(Nz(ctl.Value)) = 0) Then            ' is anything selected?
                    If ctl = Forms!RM_Room_Filter.ArcDate Then
                        strC = strC & " and " & ctl.Tag & " "  ' the tag of the list control must contain the field name to be filtered
                        'strC = strC & "= "      ' alternatively this could be part of the tag
                        strC = strC & ctl.Value  ' or ctl.Column(0)
                    Else
                        strC = strC & " and " & ctl.Tag & " "  ' the tag of the list control must contain the field name to be filtered
                        'strC = strC & "= "      ' alternatively this could be part of the tag
                        strC = strC & "'" & ctl.Value & "' "  ' or ctl.Column(0)
                    End If
 
                End If
 
            End Select
 
        End If
 
    Next
 
    If strC <> "" Then
        strC = Mid(strC, 6)    ' chop off first " and  "
        Me.Filter = strC
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
 

@jimatsqi - It is very empowering... I'll be using this kind of logic from now on... Thanks again.

[GKJR]

I know this question was already answered, but here is an alternative approach created by Allen Browne (many thanks) - Search Criteria.
It isn't quite as elegant as the solution you have posted, but in some ways I feel it might be easier to troubleshoot and/or implement. Each control does need its own block of code, but it is easy to copy and paste from one and just modify it a little to accommodate the new control.
I've used a modified version of this that uses a pop up form to create a filter string and then uses that value to filter the original continuous form. Usually I just keep the unbound controls in the form header though. I've also created a sorting method based off of this format that can sort in asc or desc order on up to 3 fields in the underlying query. I use these flexible search forms for a lot of applications.
Just thought I'd share that with you - hope it may help.