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
|
|||
|
|||
Sprinks -
Alright, I have finished everything you have listed here, and now the only problem I'm having is with the VB code. To prevent any confusion I've made my tables/queries/properties the same as your names, I can change them later if need be. But I have taken your VB code and query and copied it into my command button's click event exactly the way it appears in your message. The query is coming up red thus meaning that it must be wrong somehow...any ideas? I have tested the Attedance form and it works great when I put the appropriate information manually into the tables. But without the VB code, I can't have the data updated automatically. Thank you Sprinks, ivalum21 "Sprinks" wrote: Hi, ivalum. This took a little thought. It always helps me to think about the “things” (tables) and the relationships. In this case, there is really a new “thing”—i.e., an “instance” of one of the sessions. Each SessionID will have 16 or so “instances” in a semester. Each instance will have many students, and, in fact, the same students that are associated with that session. This relationship suggests the following tables: SessionDays: SessionDayID AutoNumber (PK) SessionID Integer (FK to Sessions) SessionDate Date/Time Attendance: AttendanceID AutoNumber (PK) SessionDayID Integer (FK to SessionDays) StudentID Integer (FK to Students) Present Yes/No To enter the instance, create a main form based on SessionDays that includes a combo box for the SessionID (with the same properties as the one on your enrollment form), the same textboxes displaying the day & time via the Column property, and a textbox for the SessionDate. The students are a little trickier. Since StudentName is not a field in Attendance, you could use a combo box to display the student name based on the StudentID in the Attendance table, but a combo box implies user choice, and you really don’t want the user to change the name, but rather just check off if he/she was there. So I suggest a continuous subform based on a query, linked by the SessionDayID: SELECT Attendance.AttendanceID, Attendance.SessionDayID, Attendance.StudentID, Students.StudentName, Attendance.Present FROM Attendance INNER JOIN Students ON Attendance.StudentID = Students.StudentID; Include a textbox for the StudentName and a checkbox for Present. Set the StudentName’s Locked property to Yes (Data tab) and Tab Stop property (Other tab) to No to prevent changes to and tabbing into the textbox. To “preload” Attendance with records for all of the students for this particular SessionDayID, create a command button to insert the records after you’ve entered the main form record. The button will execute an Append query and requery the subform to display the added records, ready for you to check off the attendance. Dim stDocName As String stDocName = "AppendAttendanceRecords" DoCmd.OpenQuery stDocName, acNormal ' Requery subform Me!AttendanceQuery.Requery ' AppendAttendanceRecords Query SQL: INSERT INTO Attendance ( SessionDayID, StudentID ) SELECT SessionDays.SessionDayID, Enrollments.StudentID FROM SessionDays INNER JOIN Enrollments ON SessionDays.SessionID = Enrollments.SessionID WHERE (((SessionDays.SessionID)=[Forms]![SessionDays]![SessionID])); To prevent adding duplicates by pressing the command button more than once, add a multiple field index to the Attendance table. Open Attendance in Design View, and choose View, Indexes. Leave the indexes that Access has already created for you. On a new line at the bottom, give the index a name, such as SingleDayStudent, and choose the SessionDayID from the pick box. On the next line, enter nothing in the Name column, but choose StudentID in the FieldName pick box. Go back up to the previous line, and three fields will show at the bottom of the window. Set Unique to Yes, save & exit. Access will now prevent you from adding duplicates, and will display a message to the user. Please note that the code above is tested but in many cases refers to other objects. If you use different names for your queries, fields, tables, and forms, you will need to adjust the code accordingly. Hope that helps. Sprinks "ivalum21" wrote: Sprinks - You're awesome. Works great. But now that I have the ability to sign students up for a class, I would like to be able to keep track of their attendence. I have created an Attendence table using SessionID, StudentID, and Date. And I'm going to want my form based on the Classes table, then have a subform based on the Attendence table....?? This is where I get lost, because I want it to display the ClassName, and then list all the students that are signed up for that class, then have a Date field to distinguish between classes, then next to the students' name have a checkbox to verify if they were there on that particular class. Can you help me out with this last little feature of my database?? Thank you very much for all your help! ivalum21 "Sprinks" wrote: I believe the ControlSource of the combo box is the problem. Access is interpreting it as an expression, rather than your SessionID field. A control can be bound either to a field or an expression, but not both. Open your form in design view and double-click on the subform to edit the subform itself. Show the properties for the combo box, click the pick box to the right of the ControlSource window, and select SessionID. Hope that helps. Sprinks "ivalum21" wrote: Here is the information you requested... Combo Box --------------------- ControlSource: =Enrollments!SessionID BoundColumn: 1 RowSource: SELECT Sessions.SessionID, Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime FROM Classes INNER JOIN Sessions ON Classes.ClassID=Sessions.ClassID ORDER BY Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime; ColumnCount: 4 Subform (Control) ---------------------- LinkMasterFields: StudentID LinkChildFields: StudentID Subform (Form) ------------------- RecordSource: SELECT Enrollments.* FROM Enrollments; ivalum21 "Sprinks" wrote: Hi, ivalum. Please post the following properties: Combo Box --------------------- ControlSource BoundColumn RowSource ColumnCount Subform (Control) ---------------------- LinkMasterFields LinkChildFields Subform (Form) ------------------- RecordSource "ivalum21" wrote: Sprinks - Everything seems to have worked great with one exception. I got the ClassName, SessionDay, SessionTime to display within my combobox, I've done everything you've listed, but when I run my form and try to select a class, I get a message in the status bar that says "Control cannot be edited. It is bound to the expression 'Enrollments!SessionID'". So I can't select a class for a student... I appreciate all of your help, thank you. ivalum21 "Sprinks" wrote: OK. So the Session table already has its own primary key. This is good. However, you currently do not have a way of capturing which session a student is enrolled in, so you need a SessionID field in Enrollments. Moreover, since the SessionID determines the ClassID as well, you do not need the ClassID in the Enrollments table. Also, Day is a reserved word. Using them as field names can cause unpredictable behavior. I suggest the following changes: Enrollments: EnrollmentID (AutoNumber PK), StudentID (FK), SessionID (FK) Sessions: SessionID (PK), ClassID (FK), SessionDay, SessionTime Your subform can be based on Enrollments only, linked to the main form by the StudentID. Although there may be many Sessions records, if we make the Session combo box sorted by class name, then by session day and time, it should be easy to navigate to the correct class and session. SessionID Combo Box: ControlSource: SessionID in Enrollments table RowSource: SELECT Sessions.SessionID, Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime FROM Classes INNER JOIN Sessions ON Classes.ClassID = Sessions.ClassID ORDER BY Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime; Column Count: 4 BoundColumn: 1 ColumnWidths: 0";1";1";1" The combo box will display the courses, days & times. You may need to adjust the column widths. When you select a row, the class will be displayed, but the SessionID will be stored in the SessionID field. To display the day and time in other textboxes, use the Column property of the combo box: SessionDay: Me!YourComboBox.Column(2) SessionTime: Me!YourComboBox.Column(3) Hope that helps. Sprinks "ivalum21" wrote: Here is the info on all my tables: Students: StudentID (PK), FirstName, LastName Classes: ClassID (PK), ClassName Sessions: SessionID (PK), ClassID (FK), Day, Time Enrollments: ClassID (FK), StudentID (FK) The form I'm talking about is named Assign, and it assigns a student to classes. The form is based off of the Students table, then the subform I have based on the Classes, Enrollments and Sessions tables. I'm not sure if that is right. Thanks for your help. ivalum21 "Sprinks" wrote: Hi, ivalum. You must have a non-updateable query. If you open the query, you will likely not be able to change data in it, either. Does ClassID define a session? What are the primary keys of Classes and Sessions? If there are multiple sessions of a given ClassID you will need a different primary key for Sessions. A multiple field one of ClassID, Day, and Time would work, but it's easier to just use an AutoNumber one. You'll never see it anyway. Post back and I'll be able to help you. Sprinks "ivalum21" wrote: I have a form based on a table called Students, it just displays their first name. I have a subform where I want to display the classes the student is signed up for. I have the following tables: Classes, Enrollments (StudentID, ClassID), and Sessions (ClassID, Day, Time). I'm using all three of these tables for my subform's record source and when I run my form it won't let me input any data for a student. I also want in my subform to have the ClassName be a combo box that displays all classes from the Classes table. Please help! |
#12
|
|||
|
|||
Ivalum,
Sorry you're having trouble, but I think you're almost there. - Did you create a query by cutting & pasting the INSERT SQL statement into the SQL View window, and save it by the name “AppendAttendanceRecords”? Check the spelling carefully, because I noticed you were spelling “attendance” “attendence” in earlier posts. - The command button code requeries the subform, and assumes that the name of the subform control is “AttendanceQuery”. Change it to the name of your control if not. - If the form and subform are working manually, then I presume the query of the subform is correct. - Is your enrollment table called “Enrollments” and the attendance table “Attendance”? Check the spelling carefully. - With the Attendance form open, with a SessionID and SessionDate entered, try executing the AppendAttendanceRecords query from the query window. After executing it, close the Attendance form, reopen it, and go the same record. Did it insert the student records? If none of these tests resolve your code, post the non-null data properties and name of your subform, and the names of the subform and insert queries. Sprinks "ivalum21" wrote: Sprinks - Alright, I have finished everything you have listed here, and now the only problem I'm having is with the VB code. To prevent any confusion I've made my tables/queries/properties the same as your names, I can change them later if need be. But I have taken your VB code and query and copied it into my command button's click event exactly the way it appears in your message. The query is coming up red thus meaning that it must be wrong somehow...any ideas? I have tested the Attedance form and it works great when I put the appropriate information manually into the tables. But without the VB code, I can't have the data updated automatically. Thank you Sprinks, ivalum21 "Sprinks" wrote: Hi, ivalum. This took a little thought. It always helps me to think about the “things” (tables) and the relationships. In this case, there is really a new “thing”—i.e., an “instance” of one of the sessions. Each SessionID will have 16 or so “instances” in a semester. Each instance will have many students, and, in fact, the same students that are associated with that session. This relationship suggests the following tables: SessionDays: SessionDayID AutoNumber (PK) SessionID Integer (FK to Sessions) SessionDate Date/Time Attendance: AttendanceID AutoNumber (PK) SessionDayID Integer (FK to SessionDays) StudentID Integer (FK to Students) Present Yes/No To enter the instance, create a main form based on SessionDays that includes a combo box for the SessionID (with the same properties as the one on your enrollment form), the same textboxes displaying the day & time via the Column property, and a textbox for the SessionDate. The students are a little trickier. Since StudentName is not a field in Attendance, you could use a combo box to display the student name based on the StudentID in the Attendance table, but a combo box implies user choice, and you really don’t want the user to change the name, but rather just check off if he/she was there. So I suggest a continuous subform based on a query, linked by the SessionDayID: SELECT Attendance.AttendanceID, Attendance.SessionDayID, Attendance.StudentID, Students.StudentName, Attendance.Present FROM Attendance INNER JOIN Students ON Attendance.StudentID = Students.StudentID; Include a textbox for the StudentName and a checkbox for Present. Set the StudentName’s Locked property to Yes (Data tab) and Tab Stop property (Other tab) to No to prevent changes to and tabbing into the textbox. To “preload” Attendance with records for all of the students for this particular SessionDayID, create a command button to insert the records after you’ve entered the main form record. The button will execute an Append query and requery the subform to display the added records, ready for you to check off the attendance. Dim stDocName As String stDocName = "AppendAttendanceRecords" DoCmd.OpenQuery stDocName, acNormal ' Requery subform Me!AttendanceQuery.Requery ' AppendAttendanceRecords Query SQL: INSERT INTO Attendance ( SessionDayID, StudentID ) SELECT SessionDays.SessionDayID, Enrollments.StudentID FROM SessionDays INNER JOIN Enrollments ON SessionDays.SessionID = Enrollments.SessionID WHERE (((SessionDays.SessionID)=[Forms]![SessionDays]![SessionID])); To prevent adding duplicates by pressing the command button more than once, add a multiple field index to the Attendance table. Open Attendance in Design View, and choose View, Indexes. Leave the indexes that Access has already created for you. On a new line at the bottom, give the index a name, such as SingleDayStudent, and choose the SessionDayID from the pick box. On the next line, enter nothing in the Name column, but choose StudentID in the FieldName pick box. Go back up to the previous line, and three fields will show at the bottom of the window. Set Unique to Yes, save & exit. Access will now prevent you from adding duplicates, and will display a message to the user. Please note that the code above is tested but in many cases refers to other objects. If you use different names for your queries, fields, tables, and forms, you will need to adjust the code accordingly. Hope that helps. Sprinks "ivalum21" wrote: Sprinks - You're awesome. Works great. But now that I have the ability to sign students up for a class, I would like to be able to keep track of their attendence. I have created an Attendence table using SessionID, StudentID, and Date. And I'm going to want my form based on the Classes table, then have a subform based on the Attendence table....?? This is where I get lost, because I want it to display the ClassName, and then list all the students that are signed up for that class, then have a Date field to distinguish between classes, then next to the students' name have a checkbox to verify if they were there on that particular class. Can you help me out with this last little feature of my database?? Thank you very much for all your help! ivalum21 "Sprinks" wrote: I believe the ControlSource of the combo box is the problem. Access is interpreting it as an expression, rather than your SessionID field. A control can be bound either to a field or an expression, but not both. Open your form in design view and double-click on the subform to edit the subform itself. Show the properties for the combo box, click the pick box to the right of the ControlSource window, and select SessionID. Hope that helps. Sprinks "ivalum21" wrote: Here is the information you requested... Combo Box --------------------- ControlSource: =Enrollments!SessionID BoundColumn: 1 RowSource: SELECT Sessions.SessionID, Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime FROM Classes INNER JOIN Sessions ON Classes.ClassID=Sessions.ClassID ORDER BY Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime; ColumnCount: 4 Subform (Control) ---------------------- LinkMasterFields: StudentID LinkChildFields: StudentID Subform (Form) ------------------- RecordSource: SELECT Enrollments.* FROM Enrollments; ivalum21 "Sprinks" wrote: Hi, ivalum. Please post the following properties: Combo Box --------------------- ControlSource BoundColumn RowSource ColumnCount Subform (Control) ---------------------- LinkMasterFields LinkChildFields Subform (Form) ------------------- RecordSource "ivalum21" wrote: Sprinks - Everything seems to have worked great with one exception. I got the ClassName, SessionDay, SessionTime to display within my combobox, I've done everything you've listed, but when I run my form and try to select a class, I get a message in the status bar that says "Control cannot be edited. It is bound to the expression 'Enrollments!SessionID'". So I can't select a class for a student... I appreciate all of your help, thank you. ivalum21 "Sprinks" wrote: OK. So the Session table already has its own primary key. This is good. However, you currently do not have a way of capturing which session a student is enrolled in, so you need a SessionID field in Enrollments. Moreover, since the SessionID determines the ClassID as well, you do not need the ClassID in the Enrollments table. Also, Day is a reserved word. Using them as field names can cause unpredictable behavior. I suggest the following changes: Enrollments: EnrollmentID (AutoNumber PK), StudentID (FK), SessionID (FK) Sessions: SessionID (PK), ClassID (FK), SessionDay, SessionTime Your subform can be based on Enrollments only, linked to the main form by the StudentID. Although there may be many Sessions records, if we make the Session combo box sorted by class name, then by session day and time, it should be easy to navigate to the correct class and session. SessionID Combo Box: ControlSource: SessionID in Enrollments table RowSource: SELECT Sessions.SessionID, Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime FROM Classes INNER JOIN Sessions ON Classes.ClassID = Sessions.ClassID ORDER BY Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime; Column Count: 4 BoundColumn: 1 ColumnWidths: 0";1";1";1" The combo box will display the courses, days & times. You may need to adjust the column widths. When you select a row, the class will be displayed, but the SessionID will be stored in the SessionID field. To display the day and time in other textboxes, use the Column property of the combo box: SessionDay: Me!YourComboBox.Column(2) SessionTime: Me!YourComboBox.Column(3) Hope that helps. Sprinks "ivalum21" wrote: Here is the info on all my tables: Students: StudentID (PK), FirstName, LastName Classes: ClassID (PK), ClassName Sessions: SessionID (PK), ClassID (FK), Day, Time Enrollments: ClassID (FK), StudentID (FK) The form I'm talking about is named Assign, and it assigns a student to classes. The form is based off of the Students table, then the subform I have based on the Classes, Enrollments and Sessions tables. I'm not sure if that is right. Thanks for your help. ivalum21 "Sprinks" wrote: Hi, ivalum. You must have a non-updateable query. If you open the query, you will likely not be able to change data in it, either. Does ClassID define a session? What are the primary keys of Classes and Sessions? If there are multiple sessions of a given ClassID you will need a different primary key for Sessions. A multiple field one of ClassID, Day, and Time would work, but it's easier to just use an AutoNumber one. You'll never see it anyway. Post back and I'll be able to help you. Sprinks "ivalum21" wrote: I have a form based on a table called Students, it just displays their first name. I have a subform where I want to display the classes the student is signed up for. I have the following tables: Classes, Enrollments (StudentID, ClassID), and Sessions (ClassID, Day, Time). I'm using all three of these tables for my subform's record source and when I run my form it won't let me input any data for a student. I also want in my subform to have the ClassName be a combo box that displays all classes from the Classes table. Please help! |
#13
|
|||
|
|||
Sprinks -
I created a query called AppendAttendanceRecords and used the INSERT SQL statement you wrote below (and I did notice the attendance spelling thing a couple posts ago...got that fixed...). However, when I have the Attendance form open, I then run the INSERT query and I get an Enter Parameter Value window that pops saying "Forms!SessionDay!SessionID" and wants me to type in a value. I thought the only thing that was wrong is that it should be "Form" instead of "Forms", but that didn't work either. Any ideas?? ivalum21 Here is the code in my command buttons click event: Private Sub Command5_Click() Dim stDocName As String stDocName = "AppendAttendanceRecords" DoCmd.OpenQuery stDocName, acNormal ' Requery subform Me!Attendance_subform.Requery End Sub Everything else is okay. "Sprinks" wrote: Ivalum, Sorry you're having trouble, but I think you're almost there. - Did you create a query by cutting & pasting the INSERT SQL statement into the SQL View window, and save it by the name “AppendAttendanceRecords”? Check the spelling carefully, because I noticed you were spelling “attendance” “attendence” in earlier posts. - The command button code requeries the subform, and assumes that the name of the subform control is “AttendanceQuery”. Change it to the name of your control if not. - If the form and subform are working manually, then I presume the query of the subform is correct. - Is your enrollment table called “Enrollments” and the attendance table “Attendance”? Check the spelling carefully. - With the Attendance form open, with a SessionID and SessionDate entered, try executing the AppendAttendanceRecords query from the query window. After executing it, close the Attendance form, reopen it, and go the same record. Did it insert the student records? If none of these tests resolve your code, post the non-null data properties and name of your subform, and the names of the subform and insert queries. Sprinks "ivalum21" wrote: Sprinks - Alright, I have finished everything you have listed here, and now the only problem I'm having is with the VB code. To prevent any confusion I've made my tables/queries/properties the same as your names, I can change them later if need be. But I have taken your VB code and query and copied it into my command button's click event exactly the way it appears in your message. The query is coming up red thus meaning that it must be wrong somehow...any ideas? I have tested the Attedance form and it works great when I put the appropriate information manually into the tables. But without the VB code, I can't have the data updated automatically. Thank you Sprinks, ivalum21 "Sprinks" wrote: Hi, ivalum. This took a little thought. It always helps me to think about the “things” (tables) and the relationships. In this case, there is really a new “thing”—i.e., an “instance” of one of the sessions. Each SessionID will have 16 or so “instances” in a semester. Each instance will have many students, and, in fact, the same students that are associated with that session. This relationship suggests the following tables: SessionDays: SessionDayID AutoNumber (PK) SessionID Integer (FK to Sessions) SessionDate Date/Time Attendance: AttendanceID AutoNumber (PK) SessionDayID Integer (FK to SessionDays) StudentID Integer (FK to Students) Present Yes/No To enter the instance, create a main form based on SessionDays that includes a combo box for the SessionID (with the same properties as the one on your enrollment form), the same textboxes displaying the day & time via the Column property, and a textbox for the SessionDate. The students are a little trickier. Since StudentName is not a field in Attendance, you could use a combo box to display the student name based on the StudentID in the Attendance table, but a combo box implies user choice, and you really don’t want the user to change the name, but rather just check off if he/she was there. So I suggest a continuous subform based on a query, linked by the SessionDayID: SELECT Attendance.AttendanceID, Attendance.SessionDayID, Attendance.StudentID, Students.StudentName, Attendance.Present FROM Attendance INNER JOIN Students ON Attendance.StudentID = Students.StudentID; Include a textbox for the StudentName and a checkbox for Present. Set the StudentName’s Locked property to Yes (Data tab) and Tab Stop property (Other tab) to No to prevent changes to and tabbing into the textbox. To “preload” Attendance with records for all of the students for this particular SessionDayID, create a command button to insert the records after you’ve entered the main form record. The button will execute an Append query and requery the subform to display the added records, ready for you to check off the attendance. Dim stDocName As String stDocName = "AppendAttendanceRecords" DoCmd.OpenQuery stDocName, acNormal ' Requery subform Me!AttendanceQuery.Requery ' AppendAttendanceRecords Query SQL: INSERT INTO Attendance ( SessionDayID, StudentID ) SELECT SessionDays.SessionDayID, Enrollments.StudentID FROM SessionDays INNER JOIN Enrollments ON SessionDays.SessionID = Enrollments.SessionID WHERE (((SessionDays.SessionID)=[Forms]![SessionDays]![SessionID])); To prevent adding duplicates by pressing the command button more than once, add a multiple field index to the Attendance table. Open Attendance in Design View, and choose View, Indexes. Leave the indexes that Access has already created for you. On a new line at the bottom, give the index a name, such as SingleDayStudent, and choose the SessionDayID from the pick box. On the next line, enter nothing in the Name column, but choose StudentID in the FieldName pick box. Go back up to the previous line, and three fields will show at the bottom of the window. Set Unique to Yes, save & exit. Access will now prevent you from adding duplicates, and will display a message to the user. Please note that the code above is tested but in many cases refers to other objects. If you use different names for your queries, fields, tables, and forms, you will need to adjust the code accordingly. Hope that helps. Sprinks "ivalum21" wrote: Sprinks - You're awesome. Works great. But now that I have the ability to sign students up for a class, I would like to be able to keep track of their attendence. I have created an Attendence table using SessionID, StudentID, and Date. And I'm going to want my form based on the Classes table, then have a subform based on the Attendence table....?? This is where I get lost, because I want it to display the ClassName, and then list all the students that are signed up for that class, then have a Date field to distinguish between classes, then next to the students' name have a checkbox to verify if they were there on that particular class. Can you help me out with this last little feature of my database?? Thank you very much for all your help! ivalum21 "Sprinks" wrote: I believe the ControlSource of the combo box is the problem. Access is interpreting it as an expression, rather than your SessionID field. A control can be bound either to a field or an expression, but not both. Open your form in design view and double-click on the subform to edit the subform itself. Show the properties for the combo box, click the pick box to the right of the ControlSource window, and select SessionID. Hope that helps. Sprinks "ivalum21" wrote: Here is the information you requested... Combo Box --------------------- ControlSource: =Enrollments!SessionID BoundColumn: 1 RowSource: SELECT Sessions.SessionID, Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime FROM Classes INNER JOIN Sessions ON Classes.ClassID=Sessions.ClassID ORDER BY Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime; ColumnCount: 4 Subform (Control) ---------------------- LinkMasterFields: StudentID LinkChildFields: StudentID Subform (Form) ------------------- RecordSource: SELECT Enrollments.* FROM Enrollments; ivalum21 "Sprinks" wrote: Hi, ivalum. Please post the following properties: Combo Box --------------------- ControlSource BoundColumn RowSource ColumnCount Subform (Control) ---------------------- LinkMasterFields LinkChildFields Subform (Form) ------------------- RecordSource "ivalum21" wrote: Sprinks - Everything seems to have worked great with one exception. I got the ClassName, SessionDay, SessionTime to display within my combobox, I've done everything you've listed, but when I run my form and try to select a class, I get a message in the status bar that says "Control cannot be edited. It is bound to the expression 'Enrollments!SessionID'". So I can't select a class for a student... I appreciate all of your help, thank you. ivalum21 "Sprinks" wrote: OK. So the Session table already has its own primary key. This is good. However, you currently do not have a way of capturing which session a student is enrolled in, so you need a SessionID field in Enrollments. Moreover, since the SessionID determines the ClassID as well, you do not need the ClassID in the Enrollments table. Also, Day is a reserved word. Using them as field names can cause unpredictable behavior. I suggest the following changes: Enrollments: EnrollmentID (AutoNumber PK), StudentID (FK), SessionID (FK) Sessions: SessionID (PK), ClassID (FK), SessionDay, SessionTime Your subform can be based on Enrollments only, linked to the main form by the StudentID. Although there may be many Sessions records, if we make the Session combo box sorted by class name, then by session day and time, it should be easy to navigate to the correct class and session. SessionID Combo Box: ControlSource: SessionID in Enrollments table RowSource: SELECT Sessions.SessionID, Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime FROM Classes INNER JOIN Sessions ON Classes.ClassID = Sessions.ClassID ORDER BY Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime; Column Count: 4 BoundColumn: 1 ColumnWidths: 0";1";1";1" The combo box will display the courses, days & times. You may need to adjust the column widths. When you select a row, the class will be displayed, but the SessionID will be stored in the SessionID field. To display the day and time in other textboxes, use the Column property of the combo box: SessionDay: Me!YourComboBox.Column(2) SessionTime: Me!YourComboBox.Column(3) Hope that helps. Sprinks "ivalum21" wrote: Here is the info on all my tables: Students: StudentID (PK), FirstName, LastName Classes: ClassID (PK), ClassName Sessions: SessionID (PK), ClassID (FK), Day, Time Enrollments: ClassID (FK), StudentID (FK) The form I'm talking about is named Assign, and it assigns a student to classes. The form is based off of the Students table, then the subform I have based on the Classes, Enrollments and Sessions tables. I'm not sure if that is right. Thanks for your help. ivalum21 "Sprinks" wrote: Hi, ivalum. You must have a non-updateable query. If you open the query, you will likely not be able to change data in it, either. |
#14
|
|||
|
|||
Forms!FormName!FormControlName is the proper syntax for referring to a
control on an open form. The term Forms! refers to the collection of all open forms. The error message means that Access cannot find the control you're specifying, and so is asking you to provide a value. Since you know the form is open, this could only be because either: - Your form is not named SessionDay - Your control is not named SessionID Hope that resolves it. Sprinks "ivalum21" wrote: Sprinks - I created a query called AppendAttendanceRecords and used the INSERT SQL statement you wrote below (and I did notice the attendance spelling thing a couple posts ago...got that fixed...). However, when I have the Attendance form open, I then run the INSERT query and I get an Enter Parameter Value window that pops saying "Forms!SessionDay!SessionID" and wants me to type in a value. I thought the only thing that was wrong is that it should be "Form" instead of "Forms", but that didn't work either. Any ideas?? ivalum21 Here is the code in my command buttons click event: Private Sub Command5_Click() Dim stDocName As String stDocName = "AppendAttendanceRecords" DoCmd.OpenQuery stDocName, acNormal ' Requery subform Me!Attendance_subform.Requery End Sub Everything else is okay. "Sprinks" wrote: Ivalum, Sorry you're having trouble, but I think you're almost there. - Did you create a query by cutting & pasting the INSERT SQL statement into the SQL View window, and save it by the name “AppendAttendanceRecords”? Check the spelling carefully, because I noticed you were spelling “attendance” “attendence” in earlier posts. - The command button code requeries the subform, and assumes that the name of the subform control is “AttendanceQuery”. Change it to the name of your control if not. - If the form and subform are working manually, then I presume the query of the subform is correct. - Is your enrollment table called “Enrollments” and the attendance table “Attendance”? Check the spelling carefully. - With the Attendance form open, with a SessionID and SessionDate entered, try executing the AppendAttendanceRecords query from the query window. After executing it, close the Attendance form, reopen it, and go the same record. Did it insert the student records? If none of these tests resolve your code, post the non-null data properties and name of your subform, and the names of the subform and insert queries. Sprinks "ivalum21" wrote: Sprinks - Alright, I have finished everything you have listed here, and now the only problem I'm having is with the VB code. To prevent any confusion I've made my tables/queries/properties the same as your names, I can change them later if need be. But I have taken your VB code and query and copied it into my command button's click event exactly the way it appears in your message. The query is coming up red thus meaning that it must be wrong somehow...any ideas? I have tested the Attedance form and it works great when I put the appropriate information manually into the tables. But without the VB code, I can't have the data updated automatically. Thank you Sprinks, ivalum21 "Sprinks" wrote: Hi, ivalum. This took a little thought. It always helps me to think about the “things” (tables) and the relationships. In this case, there is really a new “thing”—i.e., an “instance” of one of the sessions. Each SessionID will have 16 or so “instances” in a semester. Each instance will have many students, and, in fact, the same students that are associated with that session. This relationship suggests the following tables: SessionDays: SessionDayID AutoNumber (PK) SessionID Integer (FK to Sessions) SessionDate Date/Time Attendance: AttendanceID AutoNumber (PK) SessionDayID Integer (FK to SessionDays) StudentID Integer (FK to Students) Present Yes/No To enter the instance, create a main form based on SessionDays that includes a combo box for the SessionID (with the same properties as the one on your enrollment form), the same textboxes displaying the day & time via the Column property, and a textbox for the SessionDate. The students are a little trickier. Since StudentName is not a field in Attendance, you could use a combo box to display the student name based on the StudentID in the Attendance table, but a combo box implies user choice, and you really don’t want the user to change the name, but rather just check off if he/she was there. So I suggest a continuous subform based on a query, linked by the SessionDayID: SELECT Attendance.AttendanceID, Attendance.SessionDayID, Attendance.StudentID, Students.StudentName, Attendance.Present FROM Attendance INNER JOIN Students ON Attendance.StudentID = Students.StudentID; Include a textbox for the StudentName and a checkbox for Present. Set the StudentName’s Locked property to Yes (Data tab) and Tab Stop property (Other tab) to No to prevent changes to and tabbing into the textbox. To “preload” Attendance with records for all of the students for this particular SessionDayID, create a command button to insert the records after you’ve entered the main form record. The button will execute an Append query and requery the subform to display the added records, ready for you to check off the attendance. Dim stDocName As String stDocName = "AppendAttendanceRecords" DoCmd.OpenQuery stDocName, acNormal ' Requery subform Me!AttendanceQuery.Requery ' AppendAttendanceRecords Query SQL: INSERT INTO Attendance ( SessionDayID, StudentID ) SELECT SessionDays.SessionDayID, Enrollments.StudentID FROM SessionDays INNER JOIN Enrollments ON SessionDays.SessionID = Enrollments.SessionID WHERE (((SessionDays.SessionID)=[Forms]![SessionDays]![SessionID])); To prevent adding duplicates by pressing the command button more than once, add a multiple field index to the Attendance table. Open Attendance in Design View, and choose View, Indexes. Leave the indexes that Access has already created for you. On a new line at the bottom, give the index a name, such as SingleDayStudent, and choose the SessionDayID from the pick box. On the next line, enter nothing in the Name column, but choose StudentID in the FieldName pick box. Go back up to the previous line, and three fields will show at the bottom of the window. Set Unique to Yes, save & exit. Access will now prevent you from adding duplicates, and will display a message to the user. Please note that the code above is tested but in many cases refers to other objects. If you use different names for your queries, fields, tables, and forms, you will need to adjust the code accordingly. Hope that helps. Sprinks "ivalum21" wrote: Sprinks - You're awesome. Works great. But now that I have the ability to sign students up for a class, I would like to be able to keep track of their attendence. I have created an Attendence table using SessionID, StudentID, and Date. And I'm going to want my form based on the Classes table, then have a subform based on the Attendence table....?? This is where I get lost, because I want it to display the ClassName, and then list all the students that are signed up for that class, then have a Date field to distinguish between classes, then next to the students' name have a checkbox to verify if they were there on that particular class. Can you help me out with this last little feature of my database?? Thank you very much for all your help! ivalum21 "Sprinks" wrote: I believe the ControlSource of the combo box is the problem. Access is interpreting it as an expression, rather than your SessionID field. A control can be bound either to a field or an expression, but not both. Open your form in design view and double-click on the subform to edit the subform itself. Show the properties for the combo box, click the pick box to the right of the ControlSource window, and select SessionID. Hope that helps. Sprinks "ivalum21" wrote: Here is the information you requested... Combo Box --------------------- ControlSource: =Enrollments!SessionID BoundColumn: 1 RowSource: SELECT Sessions.SessionID, Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime FROM Classes INNER JOIN Sessions ON Classes.ClassID=Sessions.ClassID ORDER BY Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime; ColumnCount: 4 Subform (Control) ---------------------- LinkMasterFields: StudentID LinkChildFields: StudentID Subform (Form) ------------------- RecordSource: SELECT Enrollments.* FROM Enrollments; ivalum21 "Sprinks" wrote: Hi, ivalum. Please post the following properties: Combo Box --------------------- ControlSource BoundColumn RowSource ColumnCount Subform (Control) ---------------------- LinkMasterFields LinkChildFields Subform (Form) ------------------- RecordSource "ivalum21" wrote: Sprinks - Everything seems to have worked great with one exception. I got the ClassName, SessionDay, SessionTime to display within my combobox, I've done everything you've listed, but when I run my form and try to select a class, I get a message in the status bar that says "Control cannot be edited. It is bound to the expression 'Enrollments!SessionID'". So I can't select a class for a student... I appreciate all of your help, thank you. ivalum21 "Sprinks" wrote: OK. So the Session table already has its own primary key. This is good. However, you currently do not have a way of capturing which session a student is enrolled in, so you need a SessionID field in Enrollments. Moreover, since the SessionID determines the ClassID as well, you do not need the ClassID in the Enrollments table. Also, Day is a reserved word. Using them as field names can cause unpredictable behavior. I suggest the following changes: Enrollments: EnrollmentID (AutoNumber PK), StudentID (FK), SessionID (FK) Sessions: SessionID (PK), ClassID (FK), SessionDay, SessionTime Your subform can be based on Enrollments only, linked to the main form by the StudentID. Although there may be many Sessions records, if we make the Session combo box sorted by class name, then by session day and time, it should be easy to navigate to the correct class and session. SessionID Combo Box: ControlSource: SessionID in Enrollments table RowSource: SELECT Sessions.SessionID, Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime FROM Classes INNER JOIN Sessions ON Classes.ClassID = Sessions.ClassID ORDER BY Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime; Column Count: 4 BoundColumn: 1 ColumnWidths: 0";1";1";1" The combo box will display the courses, days & times. You may need to adjust the column widths. When you select a row, the class will be displayed, but the SessionID will be stored in the SessionID field. To display the day and time in other textboxes, use the Column property of the combo box: SessionDay: Me!YourComboBox.Column(2) |
#15
|
|||
|
|||
Sprinks -
I'm having trouble getting the command button to work properly. I know exactly what the problem is, I just don't know how to fix it. When I put the VB code into my database, I used what I believe is the name for my subform (when I have my subform selected, my properties window says "Attendance subform", but when I try to put that into the VB code you have it won't let me use a space. So then I tried to do "Attendance_subform", but that is where my problem comes in. When I click on my control button, it comes up and says its going to run my append query, I click Yes, it tells me how many rows I'm going to append, I click Yes, and then it tells me it can't find the Attendance_subform referred to in my expression. Do I have the wrong subform name? I don't think I do because when my subform is selected, the name property under the Other tab says Attendance subform. Help? ivalum21 "Sprinks" wrote: Forms!FormName!FormControlName is the proper syntax for referring to a control on an open form. The term Forms! refers to the collection of all open forms. The error message means that Access cannot find the control you're specifying, and so is asking you to provide a value. Since you know the form is open, this could only be because either: - Your form is not named SessionDay - Your control is not named SessionID Hope that resolves it. Sprinks "ivalum21" wrote: Sprinks - I created a query called AppendAttendanceRecords and used the INSERT SQL statement you wrote below (and I did notice the attendance spelling thing a couple posts ago...got that fixed...). However, when I have the Attendance form open, I then run the INSERT query and I get an Enter Parameter Value window that pops saying "Forms!SessionDay!SessionID" and wants me to type in a value. I thought the only thing that was wrong is that it should be "Form" instead of "Forms", but that didn't work either. Any ideas?? ivalum21 Here is the code in my command buttons click event: Private Sub Command5_Click() Dim stDocName As String stDocName = "AppendAttendanceRecords" DoCmd.OpenQuery stDocName, acNormal ' Requery subform Me!Attendance_subform.Requery End Sub Everything else is okay. "Sprinks" wrote: Ivalum, Sorry you're having trouble, but I think you're almost there. - Did you create a query by cutting & pasting the INSERT SQL statement into the SQL View window, and save it by the name “AppendAttendanceRecords”? Check the spelling carefully, because I noticed you were spelling “attendance” “attendence” in earlier posts. - The command button code requeries the subform, and assumes that the name of the subform control is “AttendanceQuery”. Change it to the name of your control if not. - If the form and subform are working manually, then I presume the query of the subform is correct. - Is your enrollment table called “Enrollments” and the attendance table “Attendance”? Check the spelling carefully. - With the Attendance form open, with a SessionID and SessionDate entered, try executing the AppendAttendanceRecords query from the query window. After executing it, close the Attendance form, reopen it, and go the same record. Did it insert the student records? If none of these tests resolve your code, post the non-null data properties and name of your subform, and the names of the subform and insert queries. Sprinks "ivalum21" wrote: Sprinks - Alright, I have finished everything you have listed here, and now the only problem I'm having is with the VB code. To prevent any confusion I've made my tables/queries/properties the same as your names, I can change them later if need be. But I have taken your VB code and query and copied it into my command button's click event exactly the way it appears in your message. The query is coming up red thus meaning that it must be wrong somehow...any ideas? I have tested the Attedance form and it works great when I put the appropriate information manually into the tables. But without the VB code, I can't have the data updated automatically. Thank you Sprinks, ivalum21 "Sprinks" wrote: Hi, ivalum. This took a little thought. It always helps me to think about the “things” (tables) and the relationships. In this case, there is really a new “thing”—i.e., an “instance” of one of the sessions. Each SessionID will have 16 or so “instances” in a semester. Each instance will have many students, and, in fact, the same students that are associated with that session. This relationship suggests the following tables: SessionDays: SessionDayID AutoNumber (PK) SessionID Integer (FK to Sessions) SessionDate Date/Time Attendance: AttendanceID AutoNumber (PK) SessionDayID Integer (FK to SessionDays) StudentID Integer (FK to Students) Present Yes/No To enter the instance, create a main form based on SessionDays that includes a combo box for the SessionID (with the same properties as the one on your enrollment form), the same textboxes displaying the day & time via the Column property, and a textbox for the SessionDate. The students are a little trickier. Since StudentName is not a field in Attendance, you could use a combo box to display the student name based on the StudentID in the Attendance table, but a combo box implies user choice, and you really don’t want the user to change the name, but rather just check off if he/she was there. So I suggest a continuous subform based on a query, linked by the SessionDayID: SELECT Attendance.AttendanceID, Attendance.SessionDayID, Attendance.StudentID, Students.StudentName, Attendance.Present FROM Attendance INNER JOIN Students ON Attendance.StudentID = Students.StudentID; Include a textbox for the StudentName and a checkbox for Present. Set the StudentName’s Locked property to Yes (Data tab) and Tab Stop property (Other tab) to No to prevent changes to and tabbing into the textbox. To “preload” Attendance with records for all of the students for this particular SessionDayID, create a command button to insert the records after you’ve entered the main form record. The button will execute an Append query and requery the subform to display the added records, ready for you to check off the attendance. Dim stDocName As String stDocName = "AppendAttendanceRecords" DoCmd.OpenQuery stDocName, acNormal ' Requery subform Me!AttendanceQuery.Requery ' AppendAttendanceRecords Query SQL: INSERT INTO Attendance ( SessionDayID, StudentID ) SELECT SessionDays.SessionDayID, Enrollments.StudentID FROM SessionDays INNER JOIN Enrollments ON SessionDays.SessionID = Enrollments.SessionID WHERE (((SessionDays.SessionID)=[Forms]![SessionDays]![SessionID])); To prevent adding duplicates by pressing the command button more than once, add a multiple field index to the Attendance table. Open Attendance in Design View, and choose View, Indexes. Leave the indexes that Access has already created for you. On a new line at the bottom, give the index a name, such as SingleDayStudent, and choose the SessionDayID from the pick box. On the next line, enter nothing in the Name column, but choose StudentID in the FieldName pick box. Go back up to the previous line, and three fields will show at the bottom of the window. Set Unique to Yes, save & exit. Access will now prevent you from adding duplicates, and will display a message to the user. Please note that the code above is tested but in many cases refers to other objects. If you use different names for your queries, fields, tables, and forms, you will need to adjust the code accordingly. Hope that helps. Sprinks "ivalum21" wrote: Sprinks - You're awesome. Works great. But now that I have the ability to sign students up for a class, I would like to be able to keep track of their attendence. I have created an Attendence table using SessionID, StudentID, and Date. And I'm going to want my form based on the Classes table, then have a subform based on the Attendence table....?? This is where I get lost, because I want it to display the ClassName, and then list all the students that are signed up for that class, then have a Date field to distinguish between classes, then next to the students' name have a checkbox to verify if they were there on that particular class. Can you help me out with this last little feature of my database?? Thank you very much for all your help! ivalum21 "Sprinks" wrote: I believe the ControlSource of the combo box is the problem. Access is interpreting it as an expression, rather than your SessionID field. A control can be bound either to a field or an expression, but not both. Open your form in design view and double-click on the subform to edit the subform itself. Show the properties for the combo box, click the pick box to the right of the ControlSource window, and select SessionID. Hope that helps. Sprinks "ivalum21" wrote: Here is the information you requested... Combo Box --------------------- ControlSource: =Enrollments!SessionID BoundColumn: 1 RowSource: SELECT Sessions.SessionID, Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime FROM Classes INNER JOIN Sessions ON Classes.ClassID=Sessions.ClassID ORDER BY Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime; ColumnCount: 4 Subform (Control) ---------------------- LinkMasterFields: StudentID LinkChildFields: StudentID Subform (Form) ------------------- RecordSource: SELECT Enrollments.* FROM Enrollments; ivalum21 "Sprinks" wrote: Hi, ivalum. Please post the following properties: Combo Box --------------------- ControlSource BoundColumn RowSource ColumnCount Subform (Control) ---------------------- LinkMasterFields LinkChildFields Subform (Form) ------------------- RecordSource "ivalum21" wrote: Sprinks - Everything seems to have worked great with one exception. I got the ClassName, SessionDay, SessionTime to display within my combobox, I've done everything you've listed, but when I run my form and try to select a class, I get a message in the status bar that says "Control cannot be edited. It is bound to the expression 'Enrollments!SessionID'". So I can't select a class for a student... I appreciate all of your help, thank you. ivalum21 "Sprinks" wrote: OK. So the Session table already has its own primary key. This is good. However, you currently do not have a way of capturing which session a student is enrolled in, so you need a SessionID field in Enrollments. Moreover, since the SessionID determines the ClassID as well, you do not need the ClassID in the Enrollments table. Also, Day is a reserved word. Using them as field names can cause unpredictable behavior. I suggest the following changes: Enrollments: EnrollmentID (AutoNumber PK), StudentID (FK), SessionID (FK) Sessions: SessionID (PK), ClassID (FK), SessionDay, SessionTime Your subform can be based on Enrollments only, linked to the main form by the StudentID. Although there may be many Sessions records, if we make the Session combo box sorted by class name, then by session day and time, it should be easy to navigate to the correct class and session. SessionID Combo Box: ControlSource: SessionID in Enrollments table |
#16
|
|||
|
|||
Hi, ivalum.
You have the right name. This problem is one reason most developers use names that don't contain spaces--they make the delimiting brackets around object names optional. Me!MySubform.Requery & Me![MySubform].Requery are equivalent but Me!My Subform.Requery won't work. Change the line to: Me![Attendance subform].Requery Also, it sounds like the insert query is running, but because the requery doesn't happen, it wouldn't show the new records. If you exit the form, and come back to the same record, are the student records there? Sprinks "ivalum21" wrote: Sprinks - I'm having trouble getting the command button to work properly. I know exactly what the problem is, I just don't know how to fix it. When I put the VB code into my database, I used what I believe is the name for my subform (when I have my subform selected, my properties window says "Attendance subform", but when I try to put that into the VB code you have it won't let me use a space. So then I tried to do "Attendance_subform", but that is where my problem comes in. When I click on my control button, it comes up and says its going to run my append query, I click Yes, it tells me how many rows I'm going to append, I click Yes, and then it tells me it can't find the Attendance_subform referred to in my expression. Do I have the wrong subform name? I don't think I do because when my subform is selected, the name property under the Other tab says Attendance subform. Help? ivalum21 "Sprinks" wrote: Forms!FormName!FormControlName is the proper syntax for referring to a control on an open form. The term Forms! refers to the collection of all open forms. The error message means that Access cannot find the control you're specifying, and so is asking you to provide a value. Since you know the form is open, this could only be because either: - Your form is not named SessionDay - Your control is not named SessionID Hope that resolves it. Sprinks "ivalum21" wrote: Sprinks - I created a query called AppendAttendanceRecords and used the INSERT SQL statement you wrote below (and I did notice the attendance spelling thing a couple posts ago...got that fixed...). However, when I have the Attendance form open, I then run the INSERT query and I get an Enter Parameter Value window that pops saying "Forms!SessionDay!SessionID" and wants me to type in a value. I thought the only thing that was wrong is that it should be "Form" instead of "Forms", but that didn't work either. Any ideas?? ivalum21 Here is the code in my command buttons click event: Private Sub Command5_Click() Dim stDocName As String stDocName = "AppendAttendanceRecords" DoCmd.OpenQuery stDocName, acNormal ' Requery subform Me!Attendance_subform.Requery End Sub Everything else is okay. "Sprinks" wrote: Ivalum, Sorry you're having trouble, but I think you're almost there. - Did you create a query by cutting & pasting the INSERT SQL statement into the SQL View window, and save it by the name “AppendAttendanceRecords”? Check the spelling carefully, because I noticed you were spelling “attendance” “attendence” in earlier posts. - The command button code requeries the subform, and assumes that the name of the subform control is “AttendanceQuery”. Change it to the name of your control if not. - If the form and subform are working manually, then I presume the query of the subform is correct. - Is your enrollment table called “Enrollments” and the attendance table “Attendance”? Check the spelling carefully. - With the Attendance form open, with a SessionID and SessionDate entered, try executing the AppendAttendanceRecords query from the query window. After executing it, close the Attendance form, reopen it, and go the same record. Did it insert the student records? If none of these tests resolve your code, post the non-null data properties and name of your subform, and the names of the subform and insert queries. Sprinks "ivalum21" wrote: Sprinks - Alright, I have finished everything you have listed here, and now the only problem I'm having is with the VB code. To prevent any confusion I've made my tables/queries/properties the same as your names, I can change them later if need be. But I have taken your VB code and query and copied it into my command button's click event exactly the way it appears in your message. The query is coming up red thus meaning that it must be wrong somehow...any ideas? I have tested the Attedance form and it works great when I put the appropriate information manually into the tables. But without the VB code, I can't have the data updated automatically. Thank you Sprinks, ivalum21 "Sprinks" wrote: Hi, ivalum. This took a little thought. It always helps me to think about the “things” (tables) and the relationships. In this case, there is really a new “thing”—i.e., an “instance” of one of the sessions. Each SessionID will have 16 or so “instances” in a semester. Each instance will have many students, and, in fact, the same students that are associated with that session. This relationship suggests the following tables: SessionDays: SessionDayID AutoNumber (PK) SessionID Integer (FK to Sessions) SessionDate Date/Time Attendance: AttendanceID AutoNumber (PK) SessionDayID Integer (FK to SessionDays) StudentID Integer (FK to Students) Present Yes/No To enter the instance, create a main form based on SessionDays that includes a combo box for the SessionID (with the same properties as the one on your enrollment form), the same textboxes displaying the day & time via the Column property, and a textbox for the SessionDate. The students are a little trickier. Since StudentName is not a field in Attendance, you could use a combo box to display the student name based on the StudentID in the Attendance table, but a combo box implies user choice, and you really don’t want the user to change the name, but rather just check off if he/she was there. So I suggest a continuous subform based on a query, linked by the SessionDayID: SELECT Attendance.AttendanceID, Attendance.SessionDayID, Attendance.StudentID, Students.StudentName, Attendance.Present FROM Attendance INNER JOIN Students ON Attendance.StudentID = Students.StudentID; Include a textbox for the StudentName and a checkbox for Present. Set the StudentName’s Locked property to Yes (Data tab) and Tab Stop property (Other tab) to No to prevent changes to and tabbing into the textbox. To “preload” Attendance with records for all of the students for this particular SessionDayID, create a command button to insert the records after you’ve entered the main form record. The button will execute an Append query and requery the subform to display the added records, ready for you to check off the attendance. Dim stDocName As String stDocName = "AppendAttendanceRecords" DoCmd.OpenQuery stDocName, acNormal ' Requery subform Me!AttendanceQuery.Requery ' AppendAttendanceRecords Query SQL: INSERT INTO Attendance ( SessionDayID, StudentID ) SELECT SessionDays.SessionDayID, Enrollments.StudentID FROM SessionDays INNER JOIN Enrollments ON SessionDays.SessionID = Enrollments.SessionID WHERE (((SessionDays.SessionID)=[Forms]![SessionDays]![SessionID])); To prevent adding duplicates by pressing the command button more than once, add a multiple field index to the Attendance table. Open Attendance in Design View, and choose View, Indexes. Leave the indexes that Access has already created for you. On a new line at the bottom, give the index a name, such as SingleDayStudent, and choose the SessionDayID from the pick box. On the next line, enter nothing in the Name column, but choose StudentID in the FieldName pick box. Go back up to the previous line, and three fields will show at the bottom of the window. Set Unique to Yes, save & exit. Access will now prevent you from adding duplicates, and will display a message to the user. Please note that the code above is tested but in many cases refers to other objects. If you use different names for your queries, fields, tables, and forms, you will need to adjust the code accordingly. Hope that helps. Sprinks "ivalum21" wrote: Sprinks - You're awesome. Works great. But now that I have the ability to sign students up for a class, I would like to be able to keep track of their attendence. I have created an Attendence table using SessionID, StudentID, and Date. And I'm going to want my form based on the Classes table, then have a subform based on the Attendence table....?? This is where I get lost, because I want it to display the ClassName, and then list all the students that are signed up for that class, then have a Date field to distinguish between classes, then next to the students' name have a checkbox to verify if they were there on that particular class. Can you help me out with this last little feature of my database?? Thank you very much for all your help! ivalum21 "Sprinks" wrote: I believe the ControlSource of the combo box is the problem. Access is interpreting it as an expression, rather than your SessionID field. A control can be bound either to a field or an expression, but not both. Open your form in design view and double-click on the subform to edit the subform itself. Show the properties for the combo box, click the pick box to the right of the ControlSource window, and select SessionID. Hope that helps. Sprinks "ivalum21" wrote: Here is the information you requested... Combo Box --------------------- ControlSource: =Enrollments!SessionID BoundColumn: 1 RowSource: SELECT Sessions.SessionID, Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime FROM Classes INNER JOIN Sessions ON Classes.ClassID=Sessions.ClassID ORDER BY Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime; ColumnCount: 4 Subform (Control) ---------------------- LinkMasterFields: StudentID LinkChildFields: StudentID Subform (Form) ------------------- RecordSource: SELECT Enrollments.* FROM Enrollments; ivalum21 "Sprinks" wrote: Hi, ivalum. Please post the following properties: Combo Box --------------------- ControlSource BoundColumn RowSource ColumnCount Subform (Control) ---------------------- LinkMasterFields LinkChildFields Subform (Form) ------------------- RecordSource "ivalum21" wrote: Sprinks - Everything seems to have worked great with one exception. I got the ClassName, SessionDay, SessionTime to display within my combobox, I've done everything you've listed, but when I run my form and try to select a class, I get a message in the status bar that says "Control cannot be edited. It is bound to the expression 'Enrollments!SessionID'". So I can't select a class for a student... |
#17
|
|||
|
|||
Sprinks -
When I exited the form and came back in, the old records stay in there, so previous dates appear first, but when I went into add new students into other classes, those wouldn't appear until the VB code ran correctly. I made the change to add bracekts around my subform name. I believe everything is running correctly, I'm going to delete my fake data and start using actual data we have available and see how it runs. The next step is creating some reports displaying student attendance, class lists and stuff like that. Thank you very much for all of your help, I really appreciate it. ivalum21 "Sprinks" wrote: Hi, ivalum. You have the right name. This problem is one reason most developers use names that don't contain spaces--they make the delimiting brackets around object names optional. Me!MySubform.Requery & Me![MySubform].Requery are equivalent but Me!My Subform.Requery won't work. Change the line to: Me![Attendance subform].Requery Also, it sounds like the insert query is running, but because the requery doesn't happen, it wouldn't show the new records. If you exit the form, and come back to the same record, are the student records there? Sprinks "ivalum21" wrote: Sprinks - I'm having trouble getting the command button to work properly. I know exactly what the problem is, I just don't know how to fix it. When I put the VB code into my database, I used what I believe is the name for my subform (when I have my subform selected, my properties window says "Attendance subform", but when I try to put that into the VB code you have it won't let me use a space. So then I tried to do "Attendance_subform", but that is where my problem comes in. When I click on my control button, it comes up and says its going to run my append query, I click Yes, it tells me how many rows I'm going to append, I click Yes, and then it tells me it can't find the Attendance_subform referred to in my expression. Do I have the wrong subform name? I don't think I do because when my subform is selected, the name property under the Other tab says Attendance subform. Help? ivalum21 "Sprinks" wrote: Forms!FormName!FormControlName is the proper syntax for referring to a control on an open form. The term Forms! refers to the collection of all open forms. The error message means that Access cannot find the control you're specifying, and so is asking you to provide a value. Since you know the form is open, this could only be because either: - Your form is not named SessionDay - Your control is not named SessionID Hope that resolves it. Sprinks "ivalum21" wrote: Sprinks - I created a query called AppendAttendanceRecords and used the INSERT SQL statement you wrote below (and I did notice the attendance spelling thing a couple posts ago...got that fixed...). However, when I have the Attendance form open, I then run the INSERT query and I get an Enter Parameter Value window that pops saying "Forms!SessionDay!SessionID" and wants me to type in a value. I thought the only thing that was wrong is that it should be "Form" instead of "Forms", but that didn't work either. Any ideas?? ivalum21 Here is the code in my command buttons click event: Private Sub Command5_Click() Dim stDocName As String stDocName = "AppendAttendanceRecords" DoCmd.OpenQuery stDocName, acNormal ' Requery subform Me!Attendance_subform.Requery End Sub Everything else is okay. "Sprinks" wrote: Ivalum, Sorry you're having trouble, but I think you're almost there. - Did you create a query by cutting & pasting the INSERT SQL statement into the SQL View window, and save it by the name “AppendAttendanceRecords”? Check the spelling carefully, because I noticed you were spelling “attendance” “attendence” in earlier posts. - The command button code requeries the subform, and assumes that the name of the subform control is “AttendanceQuery”. Change it to the name of your control if not. - If the form and subform are working manually, then I presume the query of the subform is correct. - Is your enrollment table called “Enrollments” and the attendance table “Attendance”? Check the spelling carefully. - With the Attendance form open, with a SessionID and SessionDate entered, try executing the AppendAttendanceRecords query from the query window. After executing it, close the Attendance form, reopen it, and go the same record. Did it insert the student records? If none of these tests resolve your code, post the non-null data properties and name of your subform, and the names of the subform and insert queries. Sprinks "ivalum21" wrote: Sprinks - Alright, I have finished everything you have listed here, and now the only problem I'm having is with the VB code. To prevent any confusion I've made my tables/queries/properties the same as your names, I can change them later if need be. But I have taken your VB code and query and copied it into my command button's click event exactly the way it appears in your message. The query is coming up red thus meaning that it must be wrong somehow...any ideas? I have tested the Attedance form and it works great when I put the appropriate information manually into the tables. But without the VB code, I can't have the data updated automatically. Thank you Sprinks, ivalum21 "Sprinks" wrote: Hi, ivalum. This took a little thought. It always helps me to think about the “things” (tables) and the relationships. In this case, there is really a new “thing”—i.e., an “instance” of one of the sessions. Each SessionID will have 16 or so “instances” in a semester. Each instance will have many students, and, in fact, the same students that are associated with that session. This relationship suggests the following tables: SessionDays: SessionDayID AutoNumber (PK) SessionID Integer (FK to Sessions) SessionDate Date/Time Attendance: AttendanceID AutoNumber (PK) SessionDayID Integer (FK to SessionDays) StudentID Integer (FK to Students) Present Yes/No To enter the instance, create a main form based on SessionDays that includes a combo box for the SessionID (with the same properties as the one on your enrollment form), the same textboxes displaying the day & time via the Column property, and a textbox for the SessionDate. The students are a little trickier. Since StudentName is not a field in Attendance, you could use a combo box to display the student name based on the StudentID in the Attendance table, but a combo box implies user choice, and you really don’t want the user to change the name, but rather just check off if he/she was there. So I suggest a continuous subform based on a query, linked by the SessionDayID: SELECT Attendance.AttendanceID, Attendance.SessionDayID, Attendance.StudentID, Students.StudentName, Attendance.Present FROM Attendance INNER JOIN Students ON Attendance.StudentID = Students.StudentID; Include a textbox for the StudentName and a checkbox for Present. Set the StudentName’s Locked property to Yes (Data tab) and Tab Stop property (Other tab) to No to prevent changes to and tabbing into the textbox. To “preload” Attendance with records for all of the students for this particular SessionDayID, create a command button to insert the records after you’ve entered the main form record. The button will execute an Append query and requery the subform to display the added records, ready for you to check off the attendance. Dim stDocName As String stDocName = "AppendAttendanceRecords" DoCmd.OpenQuery stDocName, acNormal ' Requery subform Me!AttendanceQuery.Requery ' AppendAttendanceRecords Query SQL: INSERT INTO Attendance ( SessionDayID, StudentID ) SELECT SessionDays.SessionDayID, Enrollments.StudentID FROM SessionDays INNER JOIN Enrollments ON SessionDays.SessionID = Enrollments.SessionID WHERE (((SessionDays.SessionID)=[Forms]![SessionDays]![SessionID])); To prevent adding duplicates by pressing the command button more than once, add a multiple field index to the Attendance table. Open Attendance in Design View, and choose View, Indexes. Leave the indexes that Access has already created for you. On a new line at the bottom, give the index a name, such as SingleDayStudent, and choose the SessionDayID from the pick box. On the next line, enter nothing in the Name column, but choose StudentID in the FieldName pick box. Go back up to the previous line, and three fields will show at the bottom of the window. Set Unique to Yes, save & exit. Access will now prevent you from adding duplicates, and will display a message to the user. Please note that the code above is tested but in many cases refers to other objects. If you use different names for your queries, fields, tables, and forms, you will need to adjust the code accordingly. Hope that helps. Sprinks "ivalum21" wrote: Sprinks - You're awesome. Works great. But now that I have the ability to sign students up for a class, I would like to be able to keep track of their attendence. I have created an Attendence table using SessionID, StudentID, and Date. And I'm going to want my form based on the Classes table, then have a subform based on the Attendence table....?? This is where I get lost, because I want it to display the ClassName, and then list all the students that are signed up for that class, then have a Date field to distinguish between classes, then next to the students' name have a checkbox to verify if they were there on that particular class. Can you help me out with this last little feature of my database?? Thank you very much for all your help! ivalum21 "Sprinks" wrote: I believe the ControlSource of the combo box is the problem. Access is interpreting it as an expression, rather than your SessionID field. A control can be bound either to a field or an expression, but not both. Open your form in design view and double-click on the subform to edit the subform itself. Show the properties for the combo box, click the pick box to the right of the ControlSource window, and select SessionID. Hope that helps. Sprinks "ivalum21" wrote: Here is the information you requested... Combo Box --------------------- ControlSource: =Enrollments!SessionID BoundColumn: 1 RowSource: SELECT Sessions.SessionID, Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime FROM Classes INNER JOIN Sessions ON Classes.ClassID=Sessions.ClassID ORDER BY Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime; ColumnCount: 4 Subform (Control) ---------------------- LinkMasterFields: StudentID LinkChildFields: StudentID Subform (Form) ------------------- RecordSource: SELECT Enrollments.* FROM Enrollments; ivalum21 "Sprinks" wrote: Hi, ivalum. Please post the following properties: Combo Box --------------------- ControlSource BoundColumn RowSource ColumnCount |
#18
|
|||
|
|||
Congratulations, and my pleasure.
Sprinks "ivalum21" wrote: Sprinks - When I exited the form and came back in, the old records stay in there, so previous dates appear first, but when I went into add new students into other classes, those wouldn't appear until the VB code ran correctly. I made the change to add bracekts around my subform name. I believe everything is running correctly, I'm going to delete my fake data and start using actual data we have available and see how it runs. The next step is creating some reports displaying student attendance, class lists and stuff like that. Thank you very much for all of your help, I really appreciate it. ivalum21 "Sprinks" wrote: Hi, ivalum. You have the right name. This problem is one reason most developers use names that don't contain spaces--they make the delimiting brackets around object names optional. Me!MySubform.Requery & Me![MySubform].Requery are equivalent but Me!My Subform.Requery won't work. Change the line to: Me![Attendance subform].Requery Also, it sounds like the insert query is running, but because the requery doesn't happen, it wouldn't show the new records. If you exit the form, and come back to the same record, are the student records there? Sprinks "ivalum21" wrote: Sprinks - I'm having trouble getting the command button to work properly. I know exactly what the problem is, I just don't know how to fix it. When I put the VB code into my database, I used what I believe is the name for my subform (when I have my subform selected, my properties window says "Attendance subform", but when I try to put that into the VB code you have it won't let me use a space. So then I tried to do "Attendance_subform", but that is where my problem comes in. When I click on my control button, it comes up and says its going to run my append query, I click Yes, it tells me how many rows I'm going to append, I click Yes, and then it tells me it can't find the Attendance_subform referred to in my expression. Do I have the wrong subform name? I don't think I do because when my subform is selected, the name property under the Other tab says Attendance subform. Help? ivalum21 "Sprinks" wrote: Forms!FormName!FormControlName is the proper syntax for referring to a control on an open form. The term Forms! refers to the collection of all open forms. The error message means that Access cannot find the control you're specifying, and so is asking you to provide a value. Since you know the form is open, this could only be because either: - Your form is not named SessionDay - Your control is not named SessionID Hope that resolves it. Sprinks "ivalum21" wrote: Sprinks - I created a query called AppendAttendanceRecords and used the INSERT SQL statement you wrote below (and I did notice the attendance spelling thing a couple posts ago...got that fixed...). However, when I have the Attendance form open, I then run the INSERT query and I get an Enter Parameter Value window that pops saying "Forms!SessionDay!SessionID" and wants me to type in a value. I thought the only thing that was wrong is that it should be "Form" instead of "Forms", but that didn't work either. Any ideas?? ivalum21 Here is the code in my command buttons click event: Private Sub Command5_Click() Dim stDocName As String stDocName = "AppendAttendanceRecords" DoCmd.OpenQuery stDocName, acNormal ' Requery subform Me!Attendance_subform.Requery End Sub Everything else is okay. "Sprinks" wrote: Ivalum, Sorry you're having trouble, but I think you're almost there. - Did you create a query by cutting & pasting the INSERT SQL statement into the SQL View window, and save it by the name “AppendAttendanceRecords”? Check the spelling carefully, because I noticed you were spelling “attendance” “attendence” in earlier posts. - The command button code requeries the subform, and assumes that the name of the subform control is “AttendanceQuery”. Change it to the name of your control if not. - If the form and subform are working manually, then I presume the query of the subform is correct. - Is your enrollment table called “Enrollments” and the attendance table “Attendance”? Check the spelling carefully. - With the Attendance form open, with a SessionID and SessionDate entered, try executing the AppendAttendanceRecords query from the query window. After executing it, close the Attendance form, reopen it, and go the same record. Did it insert the student records? If none of these tests resolve your code, post the non-null data properties and name of your subform, and the names of the subform and insert queries. Sprinks "ivalum21" wrote: Sprinks - Alright, I have finished everything you have listed here, and now the only problem I'm having is with the VB code. To prevent any confusion I've made my tables/queries/properties the same as your names, I can change them later if need be. But I have taken your VB code and query and copied it into my command button's click event exactly the way it appears in your message. The query is coming up red thus meaning that it must be wrong somehow...any ideas? I have tested the Attedance form and it works great when I put the appropriate information manually into the tables. But without the VB code, I can't have the data updated automatically. Thank you Sprinks, ivalum21 "Sprinks" wrote: Hi, ivalum. This took a little thought. It always helps me to think about the “things” (tables) and the relationships. In this case, there is really a new “thing”—i.e., an “instance” of one of the sessions. Each SessionID will have 16 or so “instances” in a semester. Each instance will have many students, and, in fact, the same students that are associated with that session. This relationship suggests the following tables: SessionDays: SessionDayID AutoNumber (PK) SessionID Integer (FK to Sessions) SessionDate Date/Time Attendance: AttendanceID AutoNumber (PK) SessionDayID Integer (FK to SessionDays) StudentID Integer (FK to Students) Present Yes/No To enter the instance, create a main form based on SessionDays that includes a combo box for the SessionID (with the same properties as the one on your enrollment form), the same textboxes displaying the day & time via the Column property, and a textbox for the SessionDate. The students are a little trickier. Since StudentName is not a field in Attendance, you could use a combo box to display the student name based on the StudentID in the Attendance table, but a combo box implies user choice, and you really don’t want the user to change the name, but rather just check off if he/she was there. So I suggest a continuous subform based on a query, linked by the SessionDayID: SELECT Attendance.AttendanceID, Attendance.SessionDayID, Attendance.StudentID, Students.StudentName, Attendance.Present FROM Attendance INNER JOIN Students ON Attendance.StudentID = Students.StudentID; Include a textbox for the StudentName and a checkbox for Present. Set the StudentName’s Locked property to Yes (Data tab) and Tab Stop property (Other tab) to No to prevent changes to and tabbing into the textbox. To “preload” Attendance with records for all of the students for this particular SessionDayID, create a command button to insert the records after you’ve entered the main form record. The button will execute an Append query and requery the subform to display the added records, ready for you to check off the attendance. Dim stDocName As String stDocName = "AppendAttendanceRecords" DoCmd.OpenQuery stDocName, acNormal ' Requery subform Me!AttendanceQuery.Requery ' AppendAttendanceRecords Query SQL: INSERT INTO Attendance ( SessionDayID, StudentID ) SELECT SessionDays.SessionDayID, Enrollments.StudentID FROM SessionDays INNER JOIN Enrollments ON SessionDays.SessionID = Enrollments.SessionID WHERE (((SessionDays.SessionID)=[Forms]![SessionDays]![SessionID])); To prevent adding duplicates by pressing the command button more than once, add a multiple field index to the Attendance table. Open Attendance in Design View, and choose View, Indexes. Leave the indexes that Access has already created for you. On a new line at the bottom, give the index a name, such as SingleDayStudent, and choose the SessionDayID from the pick box. On the next line, enter nothing in the Name column, but choose StudentID in the FieldName pick box. Go back up to the previous line, and three fields will show at the bottom of the window. Set Unique to Yes, save & exit. Access will now prevent you from adding duplicates, and will display a message to the user. Please note that the code above is tested but in many cases refers to other objects. If you use different names for your queries, fields, tables, and forms, you will need to adjust the code accordingly. Hope that helps. Sprinks "ivalum21" wrote: Sprinks - You're awesome. Works great. But now that I have the ability to sign students up for a class, I would like to be able to keep track of their attendence. I have created an Attendence table using SessionID, StudentID, and Date. And I'm going to want my form based on the Classes table, then have a subform based on the Attendence table....?? This is where I get lost, because I want it to display the ClassName, and then list all the students that are signed up for that class, then have a Date field to distinguish between classes, then next to the students' name have a checkbox to verify if they were there on that particular class. Can you help me out with this last little feature of my database?? Thank you very much for all your help! ivalum21 "Sprinks" wrote: I believe the ControlSource of the combo box is the problem. Access is interpreting it as an expression, rather than your SessionID field. A control can be bound either to a field or an expression, but not both. Open your form in design view and double-click on the subform to edit the subform itself. Show the properties for the combo box, click the pick box to the right of the ControlSource window, and select SessionID. Hope that helps. Sprinks "ivalum21" wrote: Here is the information you requested... Combo Box --------------------- ControlSource: =Enrollments!SessionID BoundColumn: 1 RowSource: SELECT Sessions.SessionID, Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime FROM Classes INNER JOIN Sessions ON Classes.ClassID=Sessions.ClassID ORDER BY Classes.ClassName, Sessions.SessionDay, Sessions.SessionTime; ColumnCount: 4 Subform (Control) ---------------------- LinkMasterFields: StudentID LinkChildFields: StudentID Subform (Form) ------------------- |
#19
|
|||
|
|||
Using a subform to display and add info into table
hi
I was looking for that and fainaly i found it but I tride to do as you side but nothing happend can you tell me once more step by step thank you "ivalum21" wrote: I have a form based on a table called Students, it just displays their first name. I have a subform where I want to display the classes the student is signed up for. I have the following tables: Classes, Enrollments (StudentID, ClassID), and Sessions (ClassID, Day, Time). I'm using all three of these tables for my subform's record source and when I run my form it won't let me input any data for a student. I also want in my subform to have the ClassName be a combo box that displays all classes from the Classes table. Please help! |
#20
|
|||
|
|||
Using a subform to display and add info into table
hi
I was looking for that and fainaly i found it but I tride to do as you side but appear this message to me Microsoft office access can’t append the records in the append query. Microsoft office access set 0 field(s) to Null due to a type conversion failure, and it didn’t add 48 record(s) to the table due to key violation, 0 record(s) due to lock violation, and 0 record(s) due to validation rule violations. Do you want to run the action query anyway? To ignore the error(s) and run the query, click Yes. For an explanation of the causes of the violations, click help. thank you Please help! |
|
Thread Tools | |
Display Modes | |
|
|