Filtering a subform using a combobox in the main form

[cafe_jay]

I am having trouble filtering my subform.

mainForm, subForm, cboBox

My cboBox has a list, "All, A, B, C"

When All is selected I want the subForm category to display A,B and C...

So when A is selected, i would like for the AfterUpdate() function to compare "A" to the Category field in the subForm.

I have been reading through forums for the last two days with out anyluck.

I found this online... but I can not apply it to my database

"Dim MySQL As String
MySQL = "SELECT [TblLessonList].[LessonID],[tblLessonList].[LessonName] FROM TblLessonList WHERE DivID = " & Me.cboDivSelect
Me.subfrm_LessonCardLessons.Form.cboLessonSelect.RowSource = MySQL"

Thanks for the help.

Jeff

 

[pbaldy]

If you're trying to filter the subform, you'd set its RecordSource property. What you have there would set a combo's row source, not filter the subform.

 

[cafe_jay]

I tried...

Private Sub cboBox_AfterUpdate()
Dim MySQL As String
MySQL = "Select * FROM Me.subForm WHERE field = " & Chr(34) & Me.cboBox & Chr(34)
Me.subForm.Form.RecordSource = MySQL


but i get "error 3024" saying it can not find "Me.subForm.Form.RecordSource"

 

[pbaldy]

You'd want the name of a table in the SQL. You'd want the name of the subform container where you have "subForm".

 

[cafe_jay]

can i use a query for the SQL?

 

[cafe_jay]

Also my cboBox is UNBOUND so in my code I have it as "field", I know this is wrong... How can I label the Control Source so that i can call it in VB.

 

[pbaldy]

You can certainly set the record source to the name of a saved query. I'm not clear on your second question. I thought your use of the combo in the WHERE clause looked fine. The reference to "Me.subForm" in the FROM clause was not.

 

[cafe_jay]

You can certainly set the record source to the name of a saved query. I'm not clear on your second question. I thought your use of the combo in the WHERE clause looked fine. The reference to "Me.subForm" in the FROM clause was not.

this is what i have now and it kinda works...

Private Sub cboBox_AfterUpdate()
Dim MySQL As String
MySQL = "Select * FROM myTable WHERE field = " & Chr(34) & Me.cboBox & Chr(34)
Me.subForm.Form.RecordSource = MySQL.

It doesnt fully work because my query shortens the table to less objects. but when I use "FROM myQuery" the subform displays 0 records.

Is there a way in my above code have more than one "WHERE" clause?

 

[pbaldy]

You can't have more than one WHERE clause, but you can have multiple conditions:

MySQL = "Select * FROM myTable WHERE field = " & Chr(34) & Me.cboBox & Chr(34) & " And SomeOtherField = 123"

 

[cafe_jay]

You can't have more than one WHERE clause, but you can have multiple conditions:

MySQL = "Select * FROM myTable WHERE field = " & Chr(34) & Me.cboBox & Chr(34) & " And SomeOtherField = 123"

Thank You so much i was finally able to get this to compile. My next step was to fix the "All" tab in the cboBox. So if "All" is clicked i want all filtering cleared.

This is what I did (It doesnt clear the filter it just includes all three, the point is that it works )....


"IF "All" = Me.cboBox Then (I include SQLs for A, B, and C)

Else UpdateCBOs (this is the function you helped me define)

end if"

Now I want to implement a secondary filter to the subForm, do you have anysuggestions before I begin?

 

[pbaldy]

Rather than include all three, I wouldn't even include that field in the criteria. I'm not clear on what you mean by a secondary filter. If you mean another field that the user can search on, I typically build SQL in code, adding on to it for any field the user has entered a criteria for. The sample db here demonstrates that method.

http://www.baldyweb.com/BuildSQL.htm

 

[cafe_jay]

pbaldy,

When I say a secondary filter, what I mean is...
Once the first filter is applied, the focus would be set to the secondary filter. Then you can use this next combo box to narrow the search even further. So the the first cboBox had A,B,C. Now cboBox2 has 1,2,3. Therefore you can search A1, A2, etc.

Currently cboBox2 does its on search with out the primary filter. so it searches all that have "1" not all that have "A" and "1"

 

[pbaldy]

That's what that sample does.

 

[cafe_jay]

I looked through your sample file. That helped a great deal. Not only did I learn how to implement this on my main form which has 3 subforms, I learned from your style of coding. I like how each new field added to the search of the previous field. At first i was just going to write a million if-then-else statements each with its own SQL output. Thanks to you this beginner has taken a great step forward.

 

[pbaldy]

Happy to help. In fairness, that's JasonM's sample, but I use that technique often. Sometimes I put it in a function and call the function from the after update event of all the search textboxes. That makes it update itself as the user enters things.

 

[cafe_jay]

Thats what did. I made a fxn, then when either cboBox is updated it calls the function. As opposed to having a search button.

 

[cafe_jay]

pbaldy,

I have improved my form in access. Now i would like to have a search bar that searches a string in multiple fields. At the moment im having problems adding the OR clause to the syntax. I can search on text in one field but not one text in multiple fields.

This is what i have so far...
-------
strOr = " OR "

strSQLWhere = "Where [FieldA] Like " & Chr$(39) "*" & Me.SearchBox & "*" & Chr$(39)
strSQL = strSQLWhere & strOr & [FieldB] Like " & Chr$(39) " * " & Me.SearchBox & " * " & Chr$(39)"
------

Notice how in VBA it automatically put an extra space in the parethesis for Asterisk then it automatically put a parenthesis after the second line of code. I tried to remove it but i had no luck

Thank You

 

[pbaldy]

You did not restart the string before [FieldB], so the asterisk is actually outside the quotes.

 

[cafe_jay]

pbaldy,

If i have a string of AND parameters and OR parameters how would i connect them. Im sure its something like
example
"Oranges AND Orange AND (Peeled or Rotten)"... so what gets displayed are both Peeled and Rotten Oranges...




strOr = " OR "
strAnd = " AND "

strSQLAnd= "WHERE [Field A] = " & Chr$(39) Me.cboBox & Chr$(39)
strSQLOr = "[FieldA] Like " & Chr$(39) "*" & Me.SearchBox & "*" & Chr$(39)
strSQLOr = strSQLOr & strOr & "[FieldB] Like " & Chr$(39) "*" & Me.SearchBox & "*" & Chr$(39)

strSQLWhere = strSQLAnd & strJoin & (strSQLOr)

Thanks

 

[pbaldy]

You would certainly want to add parentheses to clarify your desired logic.

(A And B) Or C

is different than

A And (B Or C)

I'm sure Access has built-in rules it will try to apply, but I wouldn't count on them doing what I wanted. By the way, you are going to have problems with missing ampersands, for instance:

strSQLAnd= "WHERE [Field A] = " & Chr$(39) & Me.cboBox & Chr$(39)