Looping through rows in a subform

[ramaswamynanda]

Hello All,

This is my first question in this forum. I have a superform/subform design. When a value changes in the superform, a function is called which which calculate some values, and change it in every row in the subform. I am not able to figure out how to reach every row in the subform. What i need is

i = subform.rowcount
for count = 0 to i
subform.field.value = calc_value
next

any help will be appreciated

Regards
Nanda

[ADezii]

Hello All,

This is my first question in this forum. I have a superform/subform design. When a value changes in the superform, a function is called which which calculate some values, and change it in every row in the subform. I am not able to figure out how to reach every row in the subform. What i need is

i = subform.rowcount
for count = 0 to i
subform.field.value = calc_value
next

any help will be appreciated

Regards
Nanda

1. What is the Linking Field between the Parent Form and Child Sub-Form?
2. What is the Data Type of this Field?
3. What value change in the Main Form will trigger Recalculations in the Sub-Form? What is this Field's Name?

[ramaswamynanda]

Hi,

Thx for replying. here is the info you requested for

What is the Linking Field between the Parent Form and Child Sub-Form? - It is an autogenerated number called timesheetid. for each master row, there are 1-7 detail rows inthe subform, corresponding to the days of the week. the super form has a field called the week number. when the user enters this the detail form is populated with seven rows, with the first column as weekday name, (mon-sun), second column as the date (for that week, for that day) and then some other columns.
What is the Data Type of this Field? the field in question is the date field
What value change in the Main Form will trigger Recalculations in the Sub-Form? when the week number is changed in the master form, for all rows in the detail form, the date value must change.
What is this Field's Name? date

hope the above was helpful.

thanks
nanda

[ADezii]

Hi,

Thx for replying. here is the info you requested for

What is the Linking Field between the Parent Form and Child Sub-Form? - It is an autogenerated number called timesheetid. for each master row, there are 1-7 detail rows inthe subform, corresponding to the days of the week. the super form has a field called the week number. when the user enters this the detail form is populated with seven rows, with the first column as weekday name, (mon-sun), second column as the date (for that week, for that day) and then some other columns.
What is the Data Type of this Field? the field in question is the date field
What value change in the Main Form will trigger Recalculations in the Sub-Form? when the week number is changed in the master form, for all rows in the detail form, the date value must change.
What is this Field's Name? date

hope the above was helpful.

thanks
nanda

Here is the basic Code Template. Make the necessary substitutions wherever appropriate. Be advised that you can also accomplish the above via an Update Query and a Sub-Form Requery, but I'll leave that for the SQL Gang. In the AfterUpdate() Event of [txtWeekNumber]:

Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
 
MySQL = "SELECT * FROM tblChildTable WHERE [timesheetid] = " & Me![timesheetid] & ";"
 
If Not IsNull(Me![txtWeekNumber]) Then
  Set MyDB = CurrentDb()
  'Recordset for the Sub-Form
  Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset)
    With MyRS
      .MoveFirst
      Do While Not .EOF
        .Edit
          MyRS![date] = 'whatever you are updating to. One reason why I went with
                                'code instead of SQL is that I wasn't sure how each 
                                'Record would be Updated. I feel this is more flexible.
        .Update
        .MoveNext
      Loop
    End With
End If
 
'Requery the Sub-Form to see the fruits of your labor
Me![subfChild].Requery

[ramaswamynanda]

hi,

Thanks for the recordset idea. The following code worked :

TimesheetDetails.Form.Recordset.MoveFirst
With TimesheetDetails.Form.Recordset
Do While Not .EOF
TimesheetDetails!Date = getdateforweeknum(wknum, TimesheetDetails!DayofWeek) ' this is the code that calculates the date
.MoveNext
Loop
End With

Excellent !

Best regards
Nanda