How to fill in fields depending on the day

[phillsheen]

Hi guys,
I have a problem. I have 2 forms, 'frmClassList' and 'frmDailyCash'. 'frmClasslist' contains 7 records, monday to friday, with the classes that are on that day on each record:
clDay / Class1 / Class2 / Class3
monday / Aerobics / Spin / B&T
tuesday / Pilates / Tone / Circuits

'frmDailyCash' contains all the days cash takings. This will also contain a list of how many people have come into the days classes.
This is where I have the problem. In the 'frmDailyCash' I have the fields CsClass1, CsClass2, CsClass3 etc etc. What I would like to happen is if the Date field 'CsDate' in 'frmDailyCash' says Monday I would like the fields CsClass1, CsClass2 etc to automatically fill up with the days classes.
I also only want this to update when a new form is created. So that if I change the classes in one months time the old records will still show the classes from one month ago.

I thought about using the If Left statement in basic to match the first 3 letters of the date and the day on the class form. Didnt work though and I ended up more confused!

Hope this makes some kinda of sense?

Any help would be fantastic!!

Phill

 

[nschroeder]

This is a general suggestion without taking several pages to provide all the details. Try creating two class tables -- one for Class Definitions and one for the Daily Classes. In the Class Definitions table, have the class name, a boolean (yes/no) field for each day of the week, and any other general information about the class. On the corresponding Class Definition form, these will be checkboxes to indicate on which day or days that class occurs.

You will then need an Append query that should be set up to run once per day. It should add a record to the Daily Classes table for each Class Definition record that has the current day of the week checked. To accomplish this, create a query field with the weekday(date()) function in the field name and the numbers 2 through 6 in the criteria. For each number, put "True" in the corresponding boolean day of the week criteria.

Hopefully this is enough info to get you started.
-Nate

 

[nschroeder]

I thought some sample data might help clarify:

Class Definitions table:
ClassName Mon Tue Wed Thu Fri
Aerobics x
MS Access x

Daily Classes table:
ClassName ClassDate StudentName
Aerobics 5/5/2008 Joe
MS Access 5/6/2008 Mary

 

[nschroeder]

Sorry. Formatting on previous reply didn't come out right. "x"s should be under the Mon and Tue columns.