Query on subform Issue

[chrisjames25]

HI

I have a main form with a category cbo, genus cbo and the na textbox to enter a vareity name in.

I have a subform that when the main form opens its shows a list of all the varieties on the database. When i choose a category form the category_cbo it filters the subform list to only show varieties within that category. When i then choose a genus from the genus_cbo it then further filters the variteis in the subform to show only varieties that are within the category and genus.

Please see attached query that makes this happen.

This worked great until i had a situation where a clematis id was 1 and genus id selected was 3 but it showed all the varieties with genus id 3 and 83.

How can i redo my query to eliminate this issue. Do i have to search the genus name rather than the genus id which is the second column in the cbo rather than first. If this is the solution how do i write that in the query

 

[plog]

Remove the LIKE portion of the criteria that you want to match exactly on.

 

[chrisjames25]

Sadly no joy with that approach as it then says criteria too complex to evaluate and crashes it.

If i remove the Like and "*" then it doesntr achieve what i want as it doesnt show all the varieties when form loads as i have selected a category or genus so it wont show anything.

I think the solution is to reference the second column in the cbo but i just dont know how to do that.

 

[plog]

I don't see how simplifying an expression can increase its complexity.

Further, my advice was to remove the LIKE for fields you want to exactly match on. If you don't want an enforce an exact match on a field....leave the LIKE.

Can you post a screenshot of what you tried that errored on you?

 

[Mark_]

Are your IDs text fields? If not, LIKE does behave differently than you would expect. LIKE treats everything as TEXT, so like "*1*" will give 1, 11, 21, 113, 155623, 59981333, ect... Like "*" & Your Field will return everything ENDING in your entered number.


If you are trying for a match, replace LIKE with

=[Forms]![Frm_VarietyAdd]![Cbo_Tier1]

 

[chrisjames25]

Hi Mark.
My IDs are numeric fields not text fields.

If i switch the code to what you have suggested i beleive when the form opens it will show nothing in the subform as nothing is selected or is my understanding incorrect?

THe idea is that at start all info is shown in the subform and as i make my selection it filters the information down. Will test your suggestion when home.

 

[chrisjames25]

I guess 1 alternative would be that if cbo_cat is empty i load one qry and if not refresh subform with another qry???

 

[Mark_]

I think you are actually looking for DoCmd.ApplyFilter.

DIM a string.
Fill the string with a valid WHERE clause.
Use DoCmd.ApplyFilter , YourString

That way you can work out IF you need any filter and, if so, what you want for your filter.