RE: Using a subform to display and add info into table

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!
.