Combo box reducing options

[jw1234]

Terrible title for this thread but can't think of how to say it.

Am working on my first true access db and need help to do the following task:

I have two combo boxes sourced from one table (alldata).

One is for Year where I have five years listed.
One is for weeks where I have the weeks 1 - 52.

I want it so that information can only be entered once for a specific year and week combination. e.g. once I have selected 2007 and week 35 from the respective combo boxes I wanted the following to happen:

ideally week 35 is no longer available for selection from the "Week" combo box when selected in conjunction with the 2007 year from the Year combo box.

OR

Upon save I get an error saying the combination of year and week has already been selected and therefore the record cannot be saved.

Thanks in anticipation of an answer.

cheers
Jamie

 

[ajetrumpet]

ideally week 35 is no longer available for selection from the "Week" combo box when selected in conjunction with the 2007 year from the Year combo box.

OR

Upon save I get an error saying the combination of year and week has already been selected and therefore the record cannot be saved.

Here is my thought on this JW:

*Look up the values you need in the tables that store the data. I assume you are entering records based on distinct combinations of Year & Week? If this is what I think it is, then you can probably use a subquery in both combo boxes (in the RowSource) to filter out what you've already used. The following thread has an example of just such a technique, but using two list boxes in a "list switch" scenario...

http://www.access-programmers.co.uk/forums/showthread.php?t=138180

Ideally, the code to filter combo values out in order to ensure that each value be used only once is:

Combo_AfterUpdate

Me.MyCombo.Rowsource = SELECT field
   FROM table WHERE field NOT IN
      (SELECT field FROM TableWhereRecordsAreBeingStored)

 

[Guus2005]

You can use one combobox instead of two. This combobox then should reflect the weeks for each year you have in your database.

Me.MyCombo.Rowsource = SELECT fieldYear, fieldWeek
   FROM table

perhaps with a group by or a distinct when you get more lines for a week in a year.