By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,145 Members | 819 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,145 IT Pros & Developers. It's quick & easy.

Looping through rows in a subform

P: 32
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
Jul 31 '07 #1
Share this Question
Share on Google+
4 Replies

ADezii
Expert 5K+
P: 8,736
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?
Jul 31 '07 #2

P: 32
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
Jul 31 '07 #3

ADezii
Expert 5K+
P: 8,736
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]:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
  2.  
  3. MySQL = "SELECT * FROM tblChildTable WHERE [timesheetid] = " & Me![timesheetid] & ";"
  4.  
  5. If Not IsNull(Me![txtWeekNumber]) Then
  6.   Set MyDB = CurrentDb()
  7.   'Recordset for the Sub-Form
  8.   Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset)
  9.     With MyRS
  10.       .MoveFirst
  11.       Do While Not .EOF
  12.         .Edit
  13.           MyRS![date] = 'whatever you are updating to. One reason why I went with
  14.                                 'code instead of SQL is that I wasn't sure how each 
  15.                                 'Record would be Updated. I feel this is more flexible.
  16.         .Update
  17.         .MoveNext
  18.       Loop
  19.     End With
  20. End If
  21.  
  22. 'Requery the Sub-Form to see the fruits of your labor
  23. Me![subfChild].Requery
Jul 31 '07 #4

P: 32
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
Jul 31 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.