Ok I have to ask this question, is this database in the process of being built or has it been in use for a while. What I'm getting at here is that your structure is not normalized so you are going to have a lot of issues with updating queries, reports, forms etc. Basically every term you will need to design a new form, granted it will be very similar to existing forms but it's still going to need continual maintenance. I'd really recommend you look at how to normalize your structure if you're allowed to/able to at this point in your development.
What you should have is a table that handles your terms and any items directly related to a specific term (i.e. tuition expense, etc.)
Code:
TermID Term Desc
1 Spring 2012
2 Summer 2012
3 Fall 2012
A table for classes offered and any data specifically related to the class (i.e. which department administers the class, history dept, psychology dept, is the class active or inactive, etc.)
Code:
ClassID ClassName
1 History 101
2 History 102
3 Algebra 101
4 Algebra 102
Then a table that contains your actual scheduling information and any information directly related to a specific class during a specific term (i.e. a particular room number the class is held, the teacher responsible for the class, etc)
Code:
ClassSchedID ClassID TermID ....
1 1 1 (History 101, Spring 2012)
2 1 2 (History 101, Summer 2012)
and so on
Then if you made a specific class inactive you wouldn't see it in your list of available classes for a specific term unless you reactivated it.
That's my advice, but for your specific request you do not have an easy way to update your records you would specifically have to have something in your ON EXIT or AFTER UPDATE or ON CHANGE event (I usually use on exit so that's what I'm going to go with here)
If you mark a STATUS field as INACTIVE you will have to modify your code to not only look on the existing form but for everything in the source table that
This is what I mean about normalizing your structure and why it will be a pain, you'll have to have code or a query for every possible combination of updates starting with your earliest term, in your case your current earliest item is Fall2011 (let's pretend that we're in the fall of 2011 just for the purposes of this example) You would have to have a query or write code that said IF the FA11 status equals 'Inactive' run an update query that marks the following fields inactive as well SP12 Status, SU12 Status, FA12 Status, SP13 Status, SU13 Status, FA13 Status. If however you marked SP12 Status as 'Inactive' you would only want to update the last five 'status' fields of your table.
So, for the sake of this example let's say you're updating your FA12 Status to Inactive, you are going to want to change the following fields to 'Inactive' SP13 Status, SU13 Status, FA13 Status. Create a query that will update all of those fields to 'Inactive'
Then in your ON EXIT property of your FA12 Status put in this code
Code:
me.refresh
if [fa12 status] = "Inactive" Then
docmd.setwarnings false
docmd.openquery ("Name of Update Query")
docmd.setwarnings true
endif
you just insert the name of the query you created to perform the update where I have NAME OF UPDATE QUERY
One other thing to keep in mind is if you keep building your database this way you are, at some point run out of room. Access tables can only handle up to 255 fields and you will rapidly run out of room, you've got 3 years worth of terms in there and you're already at 50 fields. Please consider trying to normalize the structure of your database, it will save you a lot of issues and headaches in the future.
Windows 7 64bit 
Access 2010 64bit
Reply With Quote
