Filter by ComboBox

[Eddy Sincere]

I've created a very basic filter using the code form Allen Browne's website. Every field except the combobox field works great. When I enter criteria into the combobox it's like the filter doesn't even recognize the VBA. (Attachment 1).

Attachment 2 has the code I'm using. Can someone tell me what I'm doing wrong? I'll post the DB if you need. Thanks!

Attachment 1


Attachment 2

[June7]

The combobox shows a text value but your code is looking for Boolean values. What is the combobox RowSource?

Your filter will fail if the last parameter is not included in the concatenation. That's because if any of the other parameters are used the filter will end with " And ".

Look at Allen's example again. It has the string always end with " And " and then chops it off.

[ssanfu]

Since you are familiar with Allen's site, look at
Problem names and reserved words in Access
http://www.allenbrowne.com/AppIssueBadWord.html

"Type" is a reserved word in Access.

"Type" as a caption of a label is OK.
"Type" as an object name... not so much...(aka bad)

[Eddy Sincere]

Steve -

Thanks again for the naming correction. I just went through and renamed everything (fields, tables, queries, etc) to avoid this problem in the future. I believe you gave me this advice on an earlier post, it's just taken me a while to get around to fixing it. :-)

It didn't really fix my problem but I'll keep trying. I'm guessing that filtering via the combo box requires some specific VBA because even when I use a standard text search (opposed to a Boolean as mentioned by June7) it still doesn't return any values.

On another note, I'm incorporating most of your other recommendations from our previous interaction and the relationships, etc. seem to be working better. Thanks again for the honest criticism, they're great opportunities for me to learn.

[Eddy Sincere]

The combobox shows a text value but your code is looking for Boolean values.

True, the string is looking for Boolean values. I just changed the string to resemble the text strings and, unfortunately, it didn't work. Whether I eliminate the "like" statement or leave it in the filter simply returns no records.

What is the combobox RowSource?

The RowSource is another table named, DcmntTp. It contains all the types of documents, i.e. Forms, SOP, etc.

Your filter will fail if the last parameter is not included in the concatenation. That's because if any of the other parameters are used the filter will end with " And ".

Look at Allen's example again. It has the string always end with " And " and then chops it off.

Yes, I understand that which is why I left the And " off the last string only. Am I incorrect? Should it be added to the last string as well? It seems that whenever I leave it on I get an error message.

[June7]

Look at Allen's example again. Include the " And " even with the last parameter. Then code chops off the unnecessary " And ". http://www.allenbrowne.com/ser-62code.html

If the combobox RowSource is just a reference to table, all fields will be retrieved. What fields are in the table? Is there an ID field? Is it the first field? Is the combobox BoundColumn set to 1?

[Eddy Sincere]

ook at Allen's example again. Include the " And " even with the last parameter. Then code chops off the unnecessary " And ".

Whenever I do this I get an error code (attachment 3) and when I start the debugger it stops at the str.where (attachment 4). However, when I drop the And off the last string it works just fine and I get no error messages.

Attachment 3


Attachment 4

What fields are in the table?

2 Fields. A record number (RcdNmbrDT) and text field (Tp). I made sure my naming conventions don't use the reserved words.

[QUOTE]Is there an ID field? Is it the first field? Is the combobox BoundColumn set to 1?[QUOTE]
Yes, yes and yes.

The code in Attachment 3 uses only "Like" statements to search. I stopped trying to get the combo box to work because it kept messing up the searches.

[June7]

You are not using the code that chops off the " And ". It is clearly present in Allen's example. Why are you not including that code?

Then the value of combobox is the ID, not the descriptive text. Why would you use True and False parameters?

[Eddy Sincere]

LOL! I'm so sorry. You are exactly right. I was confused and didn't understand that the piece of code was what "chopped" the And. I inserted it and it's running fine.

I'm using True/False parameters because it was on Allen's code . He used a combobox for one of the examples so I just copied that not realizing it was +/-. After reading your comment I see the error. However, even after correcting the error and using a text string I'm still getting errors. I inserted the following string (where txtTp = Combobox and Tp = Field ID):

If Not IsNull(Me.txtTp) Then
strWhere = strWhere & "([Tp] = """ & Me.txtTp & """) AND "
End If

But when it runs I get error 3075. I thought that a combobox was always recognized as text. I'm obviously missing something so I'm going to let this percolate over the weekend and try again next week. Thanks for the help.

[June7]

It's the field type that drives the criteria syntax.

What data type is [Tp]? Do you have Lookup on this field in table? I NEVER set lookup in table just because of the confusion it causes.

As I already said, if the combobox BoundColumn is the ID field then that is the value of the combobox, not the descriptive text.

[Eddy Sincere]

Alright, I got it figured out with your help. You correct about the bound column being the source of the issue. It needed to be "2" instead of "1" because the primary key is a number. I had the combobox displaying column "2" but the bound column was looking at column "1". Once I corrected that and made a small correction in the VBA string it works fine.

My VBA code was:

If Not IsNull(Me.txtTp) Then
strWhere = strWhere & "([Tp] Like ""*" & Me.txtTp & "*"") And "
End If

It is now:
If Not IsNull(Me.cboTp) Then
strWhere = strWhere & "([Tp] Like '" & Me.cboTp & "') And "
End If

The primary differences being: a) Renamed the field to cbTp; b) Removed Wildcard asterix (*); c) Closed the statement in a quote mark (').

Thanks for the help! It took me a while to understand what you meant so thanks for the patience. My filter form looks tons better and actually does what i want it to. I'm not sure how to mark as closed but this thread should be.

[June7]

Glad it works.

Mark solved with Thread Tools drop down above first post.