I am building a daily attendence database and need help creating the desired behavior for the form.
pk TransactionID (autonumber)
AttendanceCode (x = attended / A = absent / L = late)
I would like to create a form to make the data entry as easy as possible. Have the the attendence date appear at the top of the form. Then the student list to appear as a datasheet, so the data entry person can update attendance code easily.
I think you need a template table and a data table in the following format:
Name Year Month 1 2 3 4 5 6 7 .......... 31 (date number as fields, pure number as field names is not advisable, you may use D1,D2,D3 .....D31)
John 2011 09 A X X X L
The Template table will only have the students' names, other fields will be kept blank. Each Month run an append Query to add the students' name from the template table with required Year and Month values to the data table.
Create a Temporary Table with the fields Year & Month fields and with only one record.
Design a Main Form with the temporary table (if the database is shared on a Network then use only Unbound Textboxes, don't use temporary table fields) with the Year & Month fields placed on the Header of the Form.
Design a Datasheet Form using the datatable and insert it as a sub-form in the Detail Section of the Main Form.
Select the sub-form and display it's Property Sheet (ALT+ENTER).
Set the Link Master Field property value to Year;Month
Set the Link Child Field property value to Year;Month
When you set Year and Month values in the header field of the Form it's corresponding Students' list will appear in the Datasheet below.
When new students are joined (if joined) in the middle of the month their names can be added to the template table and the data table.