Combo and Option Group

Topic

New Reply

Is it possible to filter a combo box (being used for finding records in a form)
according to the selection in an option group? In other words, when a user makes a selection in an option group which applies a filter, I want to restrict the choices in the combo box as well.

Reply | Quote

Replies

Sure, but how you do it depends on the rowsource for the combobox. If you use a saved query, you can reference the form’s option group in the criteria of the query and all you need to do is requery the combobox in the afterupdate event of the option group. If you use a select statement as the rowsource, you can modify it in code in the afterupdate event of the option group and set the combobox rowsource to the new select statement.

Reply | Quote

I used a query so your first suggestion worked great except I can’t get it to show all records. Following is the criteria statement I put in the query. What’s wrong with the ending?
IIf([Forms]![MusicbyLevel]![LevelOptions]=1,”A”,(IIf([Forms]![MusicbyLevel]![LevelOptions]=2,”B”,(IIf([Forms]![MusicbyLevel]![LevelOptions]=3,”C”,(IIf([Forms]![MusicbyLevel]![LevelOptions]=4,”D”,*)))))))

I also had difficulty getting it to requery using docmd but got that fixed (thus the edit).

Reply | Quote

Well, what’s wrong with the ending is the asterisk. When you use an asterisk with an equal sign, you’re telling it to return all fields that contain nothing but an asterisk. If you want to use it to return anything, you have to use the Like operator. You could also speed up your comparison by using the choose() function. I’d rewrite it like this:

Like Choose([Forms]![MusicbyLevel]![LevelOptions],”A”,”B,”C”,”*”)

This assumes that MusicbyLevel will always contain a number of 1 or greater. If it’s possible that it might contain a zero, change it to this:

Like Choose([Forms]![MusicbyLevel]![LevelOptions] + 1,”*”,”A”,”B,”C”,”*”)

Reply | Quote

I put the following into the criteria of the query but got the error “the expression is either typed incorrectly or too complex…”. Help.

Like Choose([Forms]![MusicbyLevel]![LevelOptions],”A”,”B”,”C”,”D”)

Reply | Quote

Assuming your form was actually open, want to bet you’ve got a zero or a null value in there somewhere in [Forms]![MusicbyLevel]![LevelOptions]? Choose can only handle numbers as its index, and they have to start with one, which is why my expression was:

Like Choose([Forms]![MusicbyLevel]![LevelOptions] + 1,”*”,”A”,”B,”C”,”*”)

… which allows it to handle zeros and anything outside the range of 1,2 or 3.

If the field is a string with a number in it, you could use this:

Like Choose(Val([Forms]![MusicbyLevel]![LevelOptions]) + 1,”*”,”A”,”B,”C”,”*”)

If it might contain a null (bad design, naughty), you could use this:

Like Choose(Val(nz([Forms]![MusicbyLevel]![LevelOptions],0)) + 1,”*”,”A”,”B,”C”,”*”)

That would allow it to handle numbers or numeric strings, including null and zero values.

Reply | Quote

Nove of the fields themselves contained a null value but the else statement in my options group code I wrote did contain a null statement (“gulp”). Putting the +1 at the beginning of the Choose statement fixed that. I don’t quite understand why it would not requery, why it worked for only the first option I picked, or what purpose the asterisk has. Please forgive my ignorance. I’m self taught and only been at it for a few months.

Nevertheless, my sincere thanks to charolotte for your patience and clear explanations. Everything is working fine now.

Reply | Quote

The Choose() function has a numeric index normally passed in as a field value or a variable and a list of alternative values to which the index is applied. Since there is no way to select item zero in a list or to select an absence of a value, which is what Null would be, you have to tweak the function a bit to handle zeros or nulls.

By adding one to the field value passed into Choose, you can force a zero field value to pick the first item in the list, but you need to shift your “normal” values over by one. If you put an asterisk in the first position, it gets evaluated by the Like operator as a wildcard, and the criteria returns any value for that field when the index value is zero + 1. Putting an asterisk at the last position in the list allows you to handle an index that is outside the values you want to specify. That one will just return all the possible values for that field the same as the zero does.

Reply | Quote