Clearing criteria list or conditional criteria

[Gastrifitis]

I have 8 different fields tracking whether each job is done on that particular week or not. (It's an 8 week cycle.) If a particular job is done on week 5, for example, the field '5' would contain the number "5", otherwise it's blank.

I need to set up a query that only lists the particular week chosen from an option group. But there are 8 different fields, and if one already is set to filter by that field, it needs to stop. Only one of the 8 fields should be filtering at a time.

So, I either need to clear the criteria list for the query somehow each time it runs, or set criteria that's conditional based on the output from the option group. The last one I think would be better, but I have no idea how to do it. I'm sure there's a simple solution, but because I'm confused on which direction to go, I'm just not seeing it. Any ideas?

 

[Gastrifitis]

Maybe I was a little wordy to be clear. The datasheet view, in part, looks a little like this:

1 2 3 4 5 6 7 8
1 _ 3 _ 5 _ 7
_ 2 _ _ _ 6
1 _ _ _ 5
_ 2 _ 4 _ 6 _ 8
_ _ _ 4

Except there aren't any underscore characters in the table. When week 4 comes around, I want to go to this query, and column 4 to be filtered so that only the entries with a 4 in them show up. No other of these numbered columns should be getting filtered at that time. I can't absolutely guarantee that the weeks will be done in order, either. Most of the time, yeah, but it's not a lock. And while this wouldn't be too bad manually, I'm trying to get it done based on a choice made in an option box.

While I'm on the subject, is there any way to get the option box to set the default to the last choice made, instead of always reverting to 1?

 

[Gastrifitis]

OK, I think I have part of it figured out now. I can make a macro and use the ShowAllRecords command which will hopefully eliminate any criteria that are currently there. Then I can set up eight different conditional checks, and when the option box has the correct radio button chosen I can put in the Where line for ApplyFilter something like [4] = "4". Sound good?

I still don't know how to automatically change the default radio button to last chosen, though.

 

[Gastrifitis]

OK, that's not working. The ShowAllRecords and ApplyFilter actions don't seem to work when I choose the Design view of the query, and they don't seem to have any effect if they're run anywhere else. Each option halts the macro when I try to run them with the Query open in design view, saying "Action Failed." The ShowAllRecords command doesn't have any arguments, and the argument I'm trying to run for the Where Condition in ApplyFilter looks like:

[4] Like [Forms]![CustList]![WeekFrame]

This is the fourth post I've put on this topic so far, and so far I'm talking to myself. If anyone would like to chime in and point me in the right direction, that'd be much appreciated.