problems appending records with unbound textboxes in main/subforms

[dayna]

I apologize for yet another thread on tracking student attendance, but I’ve been struggling with this data entry form for longer than I care to admit. What I’ve been able to piece together so far seems to work (under limited circumstances and with a lot of assuaging), but there are several flaws in the form that I can’t seem to solve. In addition to posting the relevant parts of my DB, I’ll try to explain what I’ve got:

First, I have an unbound form named Attendance with three fields:
[Select Class] (Combo box)
[Date] (textbox)
[Contact Hours] (DLookup from Classes table; will be invisible to users)

My subform is based on a query that displays the last and first names of active students who are enrolled in the selected class. I also have an additional unbound textbox in my subform for each student’s contact hours (or hours spent in the classroom on any given date). The default value of this textbox is set to the [Contact Hours] field in my main form.

My first problem is that when the Attendance form is first opened, #Error is displayed in the [Contact Hours] field of my subform. The strange thing is, when I switch to design view, and then back to form view, the subform works perfectly well. Yet, when I close the form and open it again, I get the familiar #Error until I change views.

Secondly, although the [Contact Hours] field on my main form updates beautifully when the [Select Class] combo is changed, the [Contact Hours] field in the subform does not refresh/requery. Again, it only works properly if I select a class, switch to design view, and then switch back to form view. All of the other fields requery just fine without switching views.

As if that weren’t enough, the [Contact Hours] field on my subform will not allow me to input a different value for each student. Even after I cajole my form into displaying the [Contact Hours] default value properly, once I change the contact hours for one student, that change cascades for every student in the class! Aggghhhh!

If we assume the impossible, i.e., that each student comes to class on time every day and accrues the same number of hours, my form appends these records to the attendance table quite nicely. The problem is that this never happens, and I’m tasked with tracking each student’s actual hours spent in the classroom, rounded to the nearest fifteen minutes.

I’ll spare you the litany of things I’ve tried and tutorials I’ve followed. I realize that there are probably some major problems with my approach, but this works better than anything else I’ve come up with over the course of the last month. Any ideas? Thanks so much!

 

[Pat Hartman]

Unbound controls can contain data only while the form is open. The data is not saved any where. In the case of a continuous form, what you see are copies of the same form showing data from different rows of the recordsource. However, the controls exist only once as far as Access is concerned so they can have only one set of properties - hence an unbound control will show the same value for each row.

What did you intend to do with this data since you are not storing it?

 

[dayna]

Well, the plan was to use the form data in an append query that will store new attendance records in the Attendance table. I just can't seem to hammer out the whole [Contact Hours] thing.

I would like for my attendance form to display a list of all the students in a selected class. Next to each student's name, I would like to have some kind of control that will accept each student's contact hours for a specified date. I would like the value of that control to default to the number of hours that the selected the class meets. (This data is stored in the Classes table as [Daily Contact Hours].) This would cut down on data entry time because teachers would only have to modify rows in cases of absences or tardies.

The problem is that if I make [Contact Hours] a calculated field on the underlying query, I can't modify the value in the form. If I set up the form so it contains nested subforms (Classes --> Students --> Attendance), I can't seem to get the form to look right, where the contact hours control aligns with the student name control.

As I write this, I'm getting an idea, so I'll go try it out. Let me know if you've got any suggestions. Thanks!

 

[Pat Hartman]

Your form is based on the wrong table. You need to base the form on the attendance table. You can select each person from a combo,o one at a time or you can have a button that appends all the attendance records for the period and you just have to go through them and update the hours. I pick door # 2.

 

[dayna]

Thanks, Pat, for your reply. Door #2 is definitely what I’m after, I just can’t seem to figure out how to get there. When I base my form on the attendance table, I have a lot of trouble populating student names by class, among other issues. I’ve tried basing my Student Name controls on a query and the Contact Hours control on the attendance table, but when I do this, Access changes the form to “single” when what I want is “continuous.”

As for the idea I spoke of, it didn’t work out. I’m still stuck in the same place. Any suggestions?

 

[Pat Hartman]

Create a query that selects all the members of a class. All you need is the classID and the StudentID. Change this query into an append query by using the Query menu item. When it asks for a table, enter the name of the relation table.

If your FK and PK names are identical (as they should be), Access will automatically populate the AppendTo row. If the names are different, you will need to select the "to" column names yourself. Select the third needed column in the attendance table which is the date field. You have no column from the roster table to use to populate this. You will need to use a parameter. Use something like:
Forms!yourformname!yourdatefield

You will also need a parameter to collect the class for which you want to record attendance:
Forms!yourformname!Yourclassfield

This will refer to the form you are using to enter the attendance and the date field on the form where the user tells you what date they are entering attendance for.

You will need to add a button to the form to run this append query. The button wizard will walk you through the process. When the wizard is done, open the code window because you need to add additional code.

After the RunSQL statement that was generated by the query, you need requery the subform:

Me.yoursubformname.Form.Requery