If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
After School Attendance
Joe,
I would provide a calendar control that only displayed dates in your school year. You could even add make-up dates if needed. Steve "joestros" wrote in message ... Ok, I think I am getting it. Now do I need to create a table for calendar_date and another for attendance_code? It sounds like there will be two junction tables? StudentEnrollments and StudentAttendance? The calendar date is the hardest part for me to understand since all of the students and schools will be sharing the calendar date. If I use a calendar control where will this date get stored for running queries later? "Michael Gramelspacher" wrote: On Thu, 10 Jul 2008 08:44:01 -0700, joestros wrote: Michael, We are currently using an web-based service that is very costly. We are looking to save money, so they handed me the task of creating a student attendance database. Basically I will be building a database similar to the web-based service we currently use, although it won't be online. The way they have it setup on the current system makes it very easy to enter attendance. The managers print out a sign in sheet each day from the system, then use the sign in sheet to enter absent/present into their site. The students names from each site are displayed on one form with radio buttons for present/absent next to each name. This data is then saved for each day of attendance. We can then run reports based on student name, site name, program wide data, etc. Is there a way to set up a form in Access in a similar fashion? Since I will be the one entering attendance data each day I would like something easy as described above. I would like to not have to click on 800 records each day. I understand I will have that many records each day, but if I can avoid having to scroll through each student one-by-one and mark presernt/absent that would be great. I want to make sure I have my tables set up correctly as well. Thanks for your help so far. -Joe "Michael Gramelspacher" wrote: On Wed, 9 Jul 2008 13:38:32 -0700, joestros wrote: Thank you for the quick reply Michael. I'm a little confused as to what you meant by this: StudentAttendance student_num school_num calendar_date attendance_code PRIMARY KEY (student_num,school_num,calendar_date) Does this mean StudentAttendance is a junction table? Are student_num, school_num, calendar_num, all primary keys from their respective tables? Is attendance_code a field in the junction table? I understand there will be 800 attendance records per day. What I am trying to avoid is having to click on 800 students records each day, click to fill in an attendance date, and then click again to mark absent (that would be 2400 mouse clicks per day)! We currently use an expensive database service that allows us to choose a date and it brings up a list of students with radio buttons that default to present, and we go through and mark the absent students. I understand radio buttons are probably not the way to go, but is there a way to create a form from these tables that will allow a list of students to display with the attendance_code selector next to each one? And after selecting the value it would update their attendance record? Thank you again for your help. -Joe StudentAttendance would be a juntion table. I am a bit confused. It seems like you are saying that you keep attendance with another applicaion which generates an attendance roster every day. You then just have to change the presents to absences for those few students who are absent. That how it would also work with Access. Why do you need the second Access program? Maybe the roster you are describing is just for the normal school day. It that it? "Michael Gramelspacher" wrote: On Wed, 9 Jul 2008 12:22:00 -0700, joestros wrote: Hello, I am attempting to create a database that will track student daily student attendance at 11 different after school sites. My database needs to perform the following functions: Allow me to input daily student attendance for about 800 students. I would ideally like a form with the students listed in alphabetical order by site with a drop down yes/no, radio buttons, check box or something that will make it easy to enter Present/Absent for that many students. I am envisoning a form for a single date where I can filter student names by shcool site (I will be receiving sign in sheets from each site, so breaking them up by site will be the most logical way to enter attendance). Compile attendance data by school site, and by student. So, the form above needs to be able to calculate attendance data. Data such as average daily attendance by site, total attendance program-wide, total attendance per site, attendance per student, etc. I am pretty sure I can figure this out when the time comes to create it, but I want to make sure my initial table design allows for it. From what I have read so far I am going to need a many-to-many relationship between my tables. I am thinking I need: StudentTable ScheduleDatesTable SchoolTable and some sort of junction table that brings all of this together? I have tried this numerous times but am getting stumped after creating them all. I go to build a query and it won't display anything. What I need to know is what do you recommend as far as table design, and what is the next step in order to actually begin inputting attendance data from the tables? I know I will need to build some sort of form based on something, a query? the juntion table? I am not sure after building the tables. Also, I am confused as to what keys need to be in the junction table? Do you set them as primary keys, or foreign keys? Can a date/time field be used as a foreign key? Thank you for taking the time to read my question, and I hope someone can help. -Joe First off you should realize that you will have 800 attendance records for each school day. Each day you will need to generate an attendance record for each of the 800 students, probably with a default value of present. Then it is just a matter of the teacher at each school pulling up her roster and changing the present to absent for her missing stuents. StudentAttendance student_num school_num calendar_date attendance_code PRIMARY KEY (student_num,school_num,calendar_date) You probably need a calendar table with every school day date. You need a Students table. (student_num) You need a Schools table. (school_num) You need a relationship table for Students and Schools. lets call it StudentEnrolments. student_num, school_num, school_year, semester_num You need a StudentAttendance table, i.e., All fields from StudentEnrolments plus calendar_date and attendance_code . I would have a main table based on Schools and a subform based on StudentAttendance. I would have a combobox or calendar control in the form header to pick the date to see. I would have a command button in the header to run a query to generate a roster for today, if none exits already. I think this is the general idea. You can have a radio button to mark attendance in the subform or an option group with two buttons, present (default) and absent. I would say it is a matter of preference. The hard part is managing enrolments and disenrolments to keep your student table current. |
#12
|
|||
|
|||
After School Attendance
On Thu, 10 Jul 2008 09:54:02 -0700, joestros
wrote: Ok, I think I am getting it. Now do I need to create a table for calendar_date and another for attendance_code? It sounds like there will be two junction tables? StudentEnrollments and StudentAttendance? The calendar date is the hardest part for me to understand since all of the students and schools will be sharing the calendar date. If I use a calendar control where will this date get stored for running queries later? "Michael Gramelspacher" wrote: On Thu, 10 Jul 2008 08:44:01 -0700, joestros wrote: Michael, We are currently using an web-based service that is very costly. We are looking to save money, so they handed me the task of creating a student attendance database. Basically I will be building a database similar to the web-based service we currently use, although it won't be online. The way they have it setup on the current system makes it very easy to enter attendance. The managers print out a sign in sheet each day from the system, then use the sign in sheet to enter absent/present into their site. The students names from each site are displayed on one form with radio buttons for present/absent next to each name. This data is then saved for each day of attendance. We can then run reports based on student name, site name, program wide data, etc. Is there a way to set up a form in Access in a similar fashion? Since I will be the one entering attendance data each day I would like something easy as described above. I would like to not have to click on 800 records each day. I understand I will have that many records each day, but if I can avoid having to scroll through each student one-by-one and mark presernt/absent that would be great. I want to make sure I have my tables set up correctly as well. Thanks for your help so far. -Joe "Michael Gramelspacher" wrote: On Wed, 9 Jul 2008 13:38:32 -0700, joestros wrote: Thank you for the quick reply Michael. I'm a little confused as to what you meant by this: StudentAttendance student_num school_num calendar_date attendance_code PRIMARY KEY (student_num,school_num,calendar_date) Does this mean StudentAttendance is a junction table? Are student_num, school_num, calendar_num, all primary keys from their respective tables? Is attendance_code a field in the junction table? I understand there will be 800 attendance records per day. What I am trying to avoid is having to click on 800 students records each day, click to fill in an attendance date, and then click again to mark absent (that would be 2400 mouse clicks per day)! We currently use an expensive database service that allows us to choose a date and it brings up a list of students with radio buttons that default to present, and we go through and mark the absent students. I understand radio buttons are probably not the way to go, but is there a way to create a form from these tables that will allow a list of students to display with the attendance_code selector next to each one? And after selecting the value it would update their attendance record? Thank you again for your help. -Joe StudentAttendance would be a juntion table. I am a bit confused. It seems like you are saying that you keep attendance with another applicaion which generates an attendance roster every day. You then just have to change the presents to absences for those few students who are absent. That how it would also work with Access. Why do you need the second Access program? Maybe the roster you are describing is just for the normal school day. It that it? "Michael Gramelspacher" wrote: On Wed, 9 Jul 2008 12:22:00 -0700, joestros wrote: Hello, I am attempting to create a database that will track student daily student attendance at 11 different after school sites. My database needs to perform the following functions: Allow me to input daily student attendance for about 800 students. I would ideally like a form with the students listed in alphabetical order by site with a drop down yes/no, radio buttons, check box or something that will make it easy to enter Present/Absent for that many students. I am envisoning a form for a single date where I can filter student names by shcool site (I will be receiving sign in sheets from each site, so breaking them up by site will be the most logical way to enter attendance). Compile attendance data by school site, and by student. So, the form above needs to be able to calculate attendance data. Data such as average daily attendance by site, total attendance program-wide, total attendance per site, attendance per student, etc. I am pretty sure I can figure this out when the time comes to create it, but I want to make sure my initial table design allows for it. From what I have read so far I am going to need a many-to-many relationship between my tables. I am thinking I need: StudentTable ScheduleDatesTable SchoolTable and some sort of junction table that brings all of this together? I have tried this numerous times but am getting stumped after creating them all. I go to build a query and it won't display anything. What I need to know is what do you recommend as far as table design, and what is the next step in order to actually begin inputting attendance data from the tables? I know I will need to build some sort of form based on something, a query? the juntion table? I am not sure after building the tables. Also, I am confused as to what keys need to be in the junction table? Do you set them as primary keys, or foreign keys? Can a date/time field be used as a foreign key? Thank you for taking the time to read my question, and I hope someone can help. -Joe First off you should realize that you will have 800 attendance records for each school day. Each day you will need to generate an attendance record for each of the 800 students, probably with a default value of present. Then it is just a matter of the teacher at each school pulling up her roster and changing the present to absent for her missing stuents. StudentAttendance student_num school_num calendar_date attendance_code PRIMARY KEY (student_num,school_num,calendar_date) You probably need a calendar table with every school day date. You need a Students table. (student_num) You need a Schools table. (school_num) You need a relationship table for Students and Schools. lets call it StudentEnrolments. student_num, school_num, school_year, semester_num You need a StudentAttendance table, i.e., All fields from StudentEnrolments plus calendar_date and attendance_code . I would have a main table based on Schools and a subform based on StudentAttendance. I would have a combobox or calendar control in the form header to pick the date to see. I would have a command button in the header to run a query to generate a roster for today, if none exits already. I think this is the general idea. You can have a radio button to mark attendance in the subform or an option group with two buttons, present (default) and absent. I would say it is a matter of preference. The hard part is managing enrolments and disenrolments to keep your student table current. No, do not create a table for calendar_date. There is no table needed for attendance_code, since there are only two possibilities. Was a student present? Yes or No. I think this will create all the attendance records for today. Notice that calendar_date is just the Date() function, i.e., today's date. Attendance_code is not there, because the default value of True will be used. The table definition contains this default value, if that is what you want. I think I prefer 1 for yes and 0 for no, and then use an option group with two radio buttons. You do not need to do it this way. The calendar control is in the form header. Allen Browne has a nice one. It just makes it so much easier to select a date. The attendance subform has a query as its recordsource. Attendance links to Students and to Classes to get the student's name and school name. INSERT INTO StudentAtendance (student_num, school_num, school_year, semester_num, calendar_date) SELECT StudentEnrolments.student_num, StudentEnrolments.school_num, StudentEnrolments.school_year, StudentEnrolments.semester_num, DATE() AS Today FROM StudentEnrolments; |
#13
|
|||
|
|||
After School Attendance
Thanks again for your help Michael. I have the StudentTable, SchoolTable,
and EnrollmentTable built. I can now enter students names, school names, and then enroll students at a particular school. I am getting stuck when creating the table you suggested, StudentAttendance. You wrote in an earlier post: "I would have a main table based on Schools and a subform based on StudentAttendance. I would have a combobox or calendar control in the form header to pick the date to see." Did you mean you would have a main Form based on schools, or table based on schools? A form would make sense, so I think it was just a typo. I am confused how to create a form with a selectable date, and also display a roster from a specific site with an option group attached to each name. I understand how to create a sub form, but am unsure how to bring all of the data together. Thank you again. -Joe "Michael Gramelspacher" wrote: On Thu, 10 Jul 2008 09:54:02 -0700, joestros wrote: Ok, I think I am getting it. Now do I need to create a table for calendar_date and another for attendance_code? It sounds like there will be two junction tables? StudentEnrollments and StudentAttendance? The calendar date is the hardest part for me to understand since all of the students and schools will be sharing the calendar date. If I use a calendar control where will this date get stored for running queries later? "Michael Gramelspacher" wrote: On Thu, 10 Jul 2008 08:44:01 -0700, joestros wrote: Michael, We are currently using an web-based service that is very costly. We are looking to save money, so they handed me the task of creating a student attendance database. Basically I will be building a database similar to the web-based service we currently use, although it won't be online. The way they have it setup on the current system makes it very easy to enter attendance. The managers print out a sign in sheet each day from the system, then use the sign in sheet to enter absent/present into their site. The students names from each site are displayed on one form with radio buttons for present/absent next to each name. This data is then saved for each day of attendance. We can then run reports based on student name, site name, program wide data, etc. Is there a way to set up a form in Access in a similar fashion? Since I will be the one entering attendance data each day I would like something easy as described above. I would like to not have to click on 800 records each day. I understand I will have that many records each day, but if I can avoid having to scroll through each student one-by-one and mark presernt/absent that would be great. I want to make sure I have my tables set up correctly as well. Thanks for your help so far. -Joe "Michael Gramelspacher" wrote: On Wed, 9 Jul 2008 13:38:32 -0700, joestros wrote: Thank you for the quick reply Michael. I'm a little confused as to what you meant by this: StudentAttendance student_num school_num calendar_date attendance_code PRIMARY KEY (student_num,school_num,calendar_date) Does this mean StudentAttendance is a junction table? Are student_num, school_num, calendar_num, all primary keys from their respective tables? Is attendance_code a field in the junction table? I understand there will be 800 attendance records per day. What I am trying to avoid is having to click on 800 students records each day, click to fill in an attendance date, and then click again to mark absent (that would be 2400 mouse clicks per day)! We currently use an expensive database service that allows us to choose a date and it brings up a list of students with radio buttons that default to present, and we go through and mark the absent students. I understand radio buttons are probably not the way to go, but is there a way to create a form from these tables that will allow a list of students to display with the attendance_code selector next to each one? And after selecting the value it would update their attendance record? Thank you again for your help. -Joe StudentAttendance would be a juntion table. I am a bit confused. It seems like you are saying that you keep attendance with another applicaion which generates an attendance roster every day. You then just have to change the presents to absences for those few students who are absent. That how it would also work with Access. Why do you need the second Access program? Maybe the roster you are describing is just for the normal school day. It that it? "Michael Gramelspacher" wrote: On Wed, 9 Jul 2008 12:22:00 -0700, joestros wrote: Hello, I am attempting to create a database that will track student daily student attendance at 11 different after school sites. My database needs to perform the following functions: Allow me to input daily student attendance for about 800 students. I would ideally like a form with the students listed in alphabetical order by site with a drop down yes/no, radio buttons, check box or something that will make it easy to enter Present/Absent for that many students. I am envisoning a form for a single date where I can filter student names by shcool site (I will be receiving sign in sheets from each site, so breaking them up by site will be the most logical way to enter attendance). Compile attendance data by school site, and by student. So, the form above needs to be able to calculate attendance data. Data such as average daily attendance by site, total attendance program-wide, total attendance per site, attendance per student, etc. I am pretty sure I can figure this out when the time comes to create it, but I want to make sure my initial table design allows for it. From what I have read so far I am going to need a many-to-many relationship between my tables. I am thinking I need: StudentTable ScheduleDatesTable SchoolTable and some sort of junction table that brings all of this together? I have tried this numerous times but am getting stumped after creating them all. I go to build a query and it won't display anything. What I need to know is what do you recommend as far as table design, and what is the next step in order to actually begin inputting attendance data from the tables? I know I will need to build some sort of form based on something, a query? the juntion table? I am not sure after building the tables. Also, I am confused as to what keys need to be in the junction table? Do you set them as primary keys, or foreign keys? Can a date/time field be used as a foreign key? Thank you for taking the time to read my question, and I hope someone can help. -Joe First off you should realize that you will have 800 attendance records for each school day. Each day you will need to generate an attendance record for each of the 800 students, probably with a default value of present. Then it is just a matter of the teacher at each school pulling up her roster and changing the present to absent for her missing stuents. StudentAttendance student_num school_num calendar_date attendance_code PRIMARY KEY (student_num,school_num,calendar_date) You probably need a calendar table with every school day date. You need a Students table. (student_num) You need a Schools table. (school_num) You need a relationship table for Students and Schools. lets call it StudentEnrolments. student_num, school_num, school_year, semester_num You need a StudentAttendance table, i.e., All fields from StudentEnrolments plus calendar_date and attendance_code . I would have a main table based on Schools and a subform based on StudentAttendance. I would have a combobox or calendar control in the form header to pick the date to see. I would have a command button in the header to run a query to generate a roster for today, if none exits already. I think this is the general idea. You can have a radio button to mark attendance in the subform or an option group with two buttons, present (default) and absent. I would say it is a matter of preference. The hard part is managing enrolments and disenrolments to keep your student table current. No, do not create a table for calendar_date. There is no table needed for attendance_code, since there are only two possibilities. Was a student present? Yes or No. I think this will create all the attendance records for today. Notice that calendar_date is just the Date() function, i.e., today's date. Attendance_code is not there, because the default value of True will be used. The table definition contains this default value, if that is what you want. I think I prefer 1 for yes and 0 for no, and then use an option group with two radio buttons. You do not need to do it this way. The calendar control is in the form header. Allen Browne has a nice one. It just makes it so much easier to select a date. The attendance subform has a query as its recordsource. Attendance links to Students and to Classes to get the student's name and school name. INSERT INTO StudentAtendance (student_num, school_num, school_year, semester_num, calendar_date) SELECT StudentEnrolments.student_num, StudentEnrolments.school_num, StudentEnrolments.school_year, StudentEnrolments.semester_num, DATE() AS Today FROM StudentEnrolments; |
#14
|
|||
|
|||
After School Attendance
On Thu, 10 Jul 2008 17:43:01 -0700, joestros
wrote: I am confused how to create a form with a selectable date, and also display a roster from a specific site with an option group attached to each name. I understand how to create a sub form, but am unsure how to bring all of the data together. Thank you again. This is what I am looking at: http://www.psci.net/gramelsp/temp/Attendance%20Form.jpg The calendar control is by Allen Browne and is available on his web page. http://allenbrowne.com/ser-51.html I had never used it until just now. Allen's instructions are simple and clear. This is the code behind the combobox: Private Sub cbo_activity_name_AfterUpdate() Dim s As String Dim c As Variant If Not IsNull(Me.cbo_activity_name) Then ' add to Where clause c = (c + " AND ") & "activity_name = '" & _ Replace(Me.cbo_activity_name, "'", "''") & "'" End If If Not IsNull(Me.txt_calendar_date) Then ' add to Where clause c = (c + " AND ") & "calendar_date = #" & Me.txt_calendar_date & "#" End If ' eliminate initial AND If Left(c, 5) = " AND " Then c = Mid(c, 6) End If ' if c is empty then use zero, i.e., show no records If IsNull(c) Then c = "0" s = "SELECT * FROM [Attendance Query] WHERE " & c & ";" Debug.Print s Me.StudentAttendance.Form.RecordSource = s End Sub This is the code from the date text box. Allen's control puts the date in the text box and sets the focus to the text box. Private Sub txt_calendar_date_GotFocus() If Not IsNull(Me.txt_calendar_date) Then If IsDate(Me.txt_calendar_date) Then 'same as this cbo_activity_name_AfterUpdate End If End If End Sub There are other issues to consider. We need a way to prevent creating rosters for non-school days. We also have to filter for the current year and semester. |
#15
|
|||
|
|||
After School Attendance
I like the look of that attendance page. That is basically what I have
envisioned. What I do not understand is after markling all of the absences, how do you "save" the changes and move on to the next day? Does selecting the date simply create a new record in the attendance table? As far as non-program days I would think that simply not selecting or entering those dates would work. I think we can get away with not worrying about semester since we can simply run a query for a reporting period, say August-December 31 2008. Our attendnace data is due twice per year and currently we just run reports based on the given time period. As far as year, I think it might be the same as semester. All we really need to know is if a student at site "x" attended a day, and how many students attended site "x" each day. Basically we need to store daily attendance somewhere so we can run queries and reports for a given time period or date. I also forgot we are going to want to filter by grade as well. Should I create a grade table? What does the "select student" in the header do? Using this form do you have to select each student at each site each day and add them to the days roster? If so, this probably won't work since some sites have 100+ students and it would very time consuming to create a roster each site. Isn't there a way to pull in all active students per site each day using a query? "Michael Gramelspacher" wrote: On Thu, 10 Jul 2008 17:43:01 -0700, joestros wrote: I am confused how to create a form with a selectable date, and also display a roster from a specific site with an option group attached to each name. I understand how to create a sub form, but am unsure how to bring all of the data together. Thank you again. This is what I am looking at: http://www.psci.net/gramelsp/temp/Attendance%20Form.jpg The calendar control is by Allen Browne and is available on his web page. http://allenbrowne.com/ser-51.html I had never used it until just now. Allen's instructions are simple and clear. This is the code behind the combobox: Private Sub cbo_activity_name_AfterUpdate() Dim s As String Dim c As Variant If Not IsNull(Me.cbo_activity_name) Then ' add to Where clause c = (c + " AND ") & "activity_name = '" & _ Replace(Me.cbo_activity_name, "'", "''") & "'" End If If Not IsNull(Me.txt_calendar_date) Then ' add to Where clause c = (c + " AND ") & "calendar_date = #" & Me.txt_calendar_date & "#" End If ' eliminate initial AND If Left(c, 5) = " AND " Then c = Mid(c, 6) End If ' if c is empty then use zero, i.e., show no records If IsNull(c) Then c = "0" s = "SELECT * FROM [Attendance Query] WHERE " & c & ";" Debug.Print s Me.StudentAttendance.Form.RecordSource = s End Sub This is the code from the date text box. Allen's control puts the date in the text box and sets the focus to the text box. Private Sub txt_calendar_date_GotFocus() If Not IsNull(Me.txt_calendar_date) Then If IsDate(Me.txt_calendar_date) Then 'same as this cbo_activity_name_AfterUpdate End If End If End Sub There are other issues to consider. We need a way to prevent creating rosters for non-school days. We also have to filter for the current year and semester. |
#16
|
|||
|
|||
After School Attendance
On Mon, 14 Jul 2008 08:59:02 -0700, joestros
wrote: I like the look of that attendance page. That is basically what I have envisioned. What I do not understand is after markling all of the absences, how do you "save" the changes and move on to the next day? Does selecting the date simply create a new record in the attendance table? As far as non-program days I would think that simply not selecting or entering those dates would work. I think we can get away with not worrying about semester since we can simply run a query for a reporting period, say August-December 31 2008. Our attendnace data is due twice per year and currently we just run reports based on the given time period. As far as year, I think it might be the same as semester. All we really need to know is if a student at site "x" attended a day, and how many students attended site "x" each day. Basically we need to store daily attendance somewhere so we can run queries and reports for a given time period or date. I also forgot we are going to want to filter by grade as well. Should I create a grade table? What does the "select student" in the header do? Using this form do you have to select each student at each site each day and add them to the days roster? If so, this probably won't work since some sites have 100+ students and it would very time consuming to create a roster each site. Isn't there a way to pull in all active students per site each day using a query? "Michael Gramelspacher" wrote: On Thu, 10 Jul 2008 17:43:01 -0700, joestros wrote: I am confused how to create a form with a selectable date, and also display a roster from a specific site with an option group attached to each name. I understand how to create a sub form, but am unsure how to bring all of the data together. Thank you again. This is what I am looking at: http://www.psci.net/gramelsp/temp/Attendance%20Form.jpg The calendar control is by Allen Browne and is available on his web page. http://allenbrowne.com/ser-51.html I had never used it until just now. Allen's instructions are simple and clear. This is the code behind the combobox: Private Sub cbo_activity_name_AfterUpdate() Dim s As String Dim c As Variant If Not IsNull(Me.cbo_activity_name) Then ' add to Where clause c = (c + " AND ") & "activity_name = '" & _ Replace(Me.cbo_activity_name, "'", "''") & "'" End If If Not IsNull(Me.txt_calendar_date) Then ' add to Where clause c = (c + " AND ") & "calendar_date = #" & Me.txt_calendar_date & "#" End If ' eliminate initial AND If Left(c, 5) = " AND " Then c = Mid(c, 6) End If ' if c is empty then use zero, i.e., show no records If IsNull(c) Then c = "0" s = "SELECT * FROM [Attendance Query] WHERE " & c & ";" Debug.Print s Me.StudentAttendance.Form.RecordSource = s End Sub This is the code from the date text box. Allen's control puts the date in the text box and sets the focus to the text box. Private Sub txt_calendar_date_GotFocus() If Not IsNull(Me.txt_calendar_date) Then If IsDate(Me.txt_calendar_date) Then 'same as this cbo_activity_name_AfterUpdate End If End If End Sub There are other issues to consider. We need a way to prevent creating rosters for non-school days. We also have to filter for the current year and semester. Consider this form: http://www.psci.net/gramelsp/temp/Attendance%20Form.jpg After you make changes to a records, Access automatically saves the changes. The form is bound to a query and the record fields are bound to query columns. Selecting a new date causes the form to show the records for that date. If no records are shown, then you have not yet created them. Press the command button to run a query to insert the records for that date. If you create records for a non-program day, then you goofed. But really, you need a calender table for this. The insert query will simply not allow records to be created for a non-program date. This form is just an example. If semesters are not part of your school, then forget about semesters. Probably you do not need a table for grades. You need a validation rule like Between 1 and 8, or whatever range you need. A student's grade will change each year. I would have to think about this, but I think I would store a fixed date such as projected date of graduation and compute the grade level based on that date. Think of it as a student belonging to a year group based upon graduation year. The command button creates a master roster for all schools. Since the main form is based on schools, the subform only shows the records for the current school. I uploaded the example here. The only thing not obvious is that double-clicking in one of the select combo boxes clears it. Think of it as sort of a generic attendance example. http://www.psci.net/gramelsp/temp/St...Attendance.zip |
|
Thread Tools | |
Display Modes | |
|
|