UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Search Subform From Multiple Combo Boxes And Text Box, Access 2013    
 
   
admatura
post Sep 8 2017, 07:51 AM
Post#1



Posts: 25
Joined: 15-August 16



Good day,

I have a mainform with a subform and would like the unbounded comboboxes and textbox (As You Type) to filter the subform. I have the following function set to the On Change event of the unbounded controls on the main form. The problem I and having is that the subform filter either by BA or Keyword but not the combined criterias, by BA and Keyword.

Function flt()
Dim qry As String

If Nz(Me.BASearch, "") <> "" Then
Me.Review.Form.FilterOn = False
Else
qry = qry & "[BA] = '" & Me.BASearch & "' AND "
End If

If Nz(Me.Keyword, "") <> "" Then
Me.Review.Form.FilterOn = False
Else
qry = qry & "[Vendor] LIKE *'" & Me.Keyword & "'* AND "
End If


Me.Review.Form.Filter = qry
Me.Review.Form.FilterOn = True

End Function

Keyword is a text box on the main form that filters Vendor on the subform (Review),
BASearch is a combo box on the main form that filters BA on the subform

Thanks in advance.
Go to the top of the page
 
moke123
post Sep 8 2017, 08:43 AM
Post#2



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



I would add a - debug.print qry - so you can see exactly what your filter criteria returns.
it also appears that you would have an extra "And" at the end of qry.
add something like
CODE
if qry <> "" then
qry = left(qry,Len(qry)-4)
end if

also why use a textbox to find as type? if the data is in the db then why not use a combobox for that also.
Go to the top of the page
 
admatura
post Sep 8 2017, 11:19 AM
Post#3



Posts: 25
Joined: 15-August 16



Not working. I looked around and found this but now the Keyword filter is not working. I want to search part of the vendor field that is why i'm using the textbox. So users can type "tain" and would return something like "container"

If IsNull(Me.BASearch) Then
Me.Review.Form.FilterOn = False
Else
qry = qry & "BA = """ & Me.BASearch & """ AND "
End If


If Nz(Me.Keyword, "") <> "" Then
Me.Review.Form.FilterOn = False
Else
qry = qry & "Vendor Like '*" & Me.Keyword & "*'"
End If
Go to the top of the page
 
moke123
post Sep 8 2017, 12:56 PM
Post#4



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



"not working" doesnt tell me much.
try the code below and check the debug.print strFilter in the immediate window to be sure its valid
this assumes both BA and Vendor are string datatypes.

CODE
Dim strFilter as String

if NZ(Me.BASearch,"")<> "" then
strFilter = strFilter & "BA = '" &  Me.BASearch & "' And "
end if

If Nz(Me.Keyword, "") <> "" Then
strFilter = strFilter & "Vendor Like '*" & Me.Keyword & "*' And "
end if

if strFilter <> "" then
strFilter = left(strFilter,Len(strFilter)-4)
end if

debug.Print strFilter
Go to the top of the page
 
admatura
post Sep 8 2017, 03:15 PM
Post#5



Posts: 25
Joined: 15-August 16



It's working sort of. I need to put a value in BASearch first before Keyword, I want the user to be able to enter in any box they want. Also the Keyword field it is not filtering 'as-you-type' . Like if i enter 'ab' all the words with 'ab' are left in the subform.
Go to the top of the page
 
moke123
post Sep 8 2017, 06:51 PM
Post#6



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



see if this gives you any ideas on constructing the filter string
Attached File  ex_search.zip ( 26.39K )Number of downloads: 15
Go to the top of the page
 
admatura
post Sep 8 2017, 10:00 PM
Post#7



Posts: 25
Joined: 15-August 16



Almost there.....Works good but it's not filtering by Keyword alone. I want the user to be able to choose to search by Keyword only or BA only or even a combination of BA and Keyword. As it is now the subform filters the Keyword only after the BASearch field is changed.
Go to the top of the page
 
moke123
post Sep 9 2017, 06:04 AM
Post#8



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



You will have to upload a stripped down version of your db with enough dummy data for it to be functional.

since i cant see what you have and have no idea what your data looks like or represents all i can do is construct a string for you to use as a filter.
the sample i uploaded will construct a filter string using either control individually or both together. Are you sure you want an "And" and not an "Or" in the filter?
Go to the top of the page
 
admatura
post Sep 9 2017, 08:21 AM
Post#9



Posts: 25
Joined: 15-August 16



Ok I've attached the db. The Search form is the form I'm using to look for records. After the form should be able to:

1. When a Keyword is typed the subform filters records "as you are typing" also it re-queries the comboboxes to dynamically update with only items that is associated to the keyword.
2. When searching by the comboboxes each one will update their list based on the selection of the previous combobox.
Go to the top of the page
 
admatura
post Sep 13 2017, 07:21 PM
Post#10



Posts: 25
Joined: 15-August 16



Anyone help please.
Go to the top of the page
 
moke123
post Sep 14 2017, 04:34 AM
Post#11



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



hi admatura,
one mistake I made in the sample is using keydown for search as you type. Switch it to keyUp event. I think keydown leaves you one letter behind.

With the other combboxes you should be able to add them to the search string the same as the BASearch combo.
As far as making them cascading I dont think I understand your data well enough to attempt it.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 03:15 AM