01-01-10, 11:09 #1Registered User
- Join Date
- Jan 2010
Unanswered: Subform updates all rows when single row changed
I have a problem with a subform echoing a change to controls in any of its rows.
The main form is single form, popup, non-modal, and contains only a combobox (which is not referenced by the subform query - it's hard-coded for now) and an Exit button.
The subform is continuous forms, non-popup, non-modal and displays an ID, Name, Start Time, End Time (both combo, based on a query), Hours (calculated from Start/End Time) and Notes for each record selected by its query.
The subform is populated as I expect, but whenever I update either of the time controls (string format) on any row, the change is propagated to the same field in all rows of the subform, and the calculated Hours control does similarly.
How can I refer to the individual row's controls and prevent this propagation?
Thanks in advance.
01-01-10, 12:23 #2Moderator
- Join Date
- Dec 2004
- Madison, WI
If you're working with subforms and equations or expressions, you may have to factor in the autonumber field for the equation or something to distinctly identify which record in the subform to update. Usually when someone says their value is propogated to all the records in a continous subform, there's something wrong with either how the subform's recordsource is setup or with how the equation is setup to update the records in the subform (ie. it's trying to update all records in a subform using an equation with values from the main form.) Or a field is needed in the subform's table to do the equation row specific. A continuous subform needs to have the equation or formula based on that specific row in the subform (again, possibly using something like the autonumber or a field to identify the SPECIFIC row in the subform or have the field in the subform itself.) These equations are often put into the subform to be row specific versus driven off an event on the main form. If the equation is driven off the main form, chances are you need some kind of a BOUND field in the subform (and subform's table) to calculate the specific equation for that specific row in the subform.
Now if it's an UNBOUND field on the subform (or expression in an UNBOUND field), it WILL propogate to all the fields on the subform (since the UNBOUND field is again, not tied to a specific row in the subform.) To solve this, you'll need to again, add the field (whatever field you need in order to make the calculation specific to the row in the subform) to the subform's table and then base your calculations on that BOUND field for the specific record in the subform.) Sometimes this is where you need to 'break the rule' of storing summed values in a table if you're doing total hours or other summing totals for your subform records.
Without knowing more, I'm guessing you need to add a field to your subform's table to make the calculation work specific to the subform row.
Last edited by pkstormy; 01-01-10 at 12:44.Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)
01-02-10, 07:37 #3Registered User
- Join Date
- Jan 2010
Many thanks for the swift response. I appreciate I need to bind the row controls to actual columns in the table, but I'm not sure how to go about it exactly. If I can (hopefully) clarify:
The form (frmAttendance) is to be used to track daily hours of attendance for each student attending one of several dayschools.
All individuals (clients, partners, students, employees, etc) are reflected in tblPerson, with personID as the primary key.
Students are linked to their parent via xref table tblClientStudent, and tblStudentAttend contains one record per student+date with a PK of studentID + attendDate. The tblStudentAttend.studentID field maps to tblPerson.personID.
The main form (frmAttendance) contains a school name, a processing date, the subform in question and an Exit button.
The subform (frmStudentAttend) includes one row per child attending the school, populated by qryAttendance, which selects personID and nameLast/First from tblPerson, where personID matches tblClientStudent.studentID and the form's school ID (hard-coded for now) matches tblPerson.schoolID.
I have tried adding the start/end dates, hours and notes columns to tblPerson, so that all row controls are in the table providing the row identifiers.
The start/end dates are combo controls, populated by a query, qryAttendTimes, which loads times (e.g. 07:00, 07:30, etc) to each combo box. This can be replaced by hard-coded times, if it makes things easier.
I need to send the data to another table, tblStudentAttend, for statistical reporting and accounting purposes.
Now (finally), here's what I think is my problem: I can add start/end, hours and notes to tblPeople easily enough, even if they won't be referenced past this form's processing. It seems I also have to add these columns to tblStudentAttend, to allow me to save row data to that table.
I hope I've made thinks clear enough in this tome!