How to update subform records using loop

[tahirsatti]

I am using below mentioned code to update all records in subform but it update only first record.Please help to resolve this issue so that it will update all records.



Dim tmprs As DAO.Recordset
Dim fld As DAO.Field

Set tmprs = [subform1].Form.RecordsetClone

tmprs.MoveFirst
While Not tmprs.EOF
For Each fld In tmprs.Fields

If [subform1].Form!Class_Id = 2 Then
[subform1].Form!total = [subform1].Form![Sub_Total] * DSum("Qty", "Screen_size", "dt_id=[subform1].Form!dt_id")



End If

If [subform1].Form!Class_Id = 3 Then
[subform1].Form!total = [subform1].Form![Sub_Total] * DSum("Qty", "Screen_size", "dt_id=[subform1].Form!dt_id")
End If


If [subform1].Form!Class_Id = 1 Then
[subform1].Form!total = [subform1].Form![Sub_Total] * DSum("Qty", "Screen_size", "dt_id=[subform1].Form!dt_id")
End If

Next fld
tmprs.MoveNext
Wend

[June7]

Why are you saving calculated data? Especially aggregate calculation?

Why are you hitting each field? Why the conditional? Concatenate variables. Reference to the form control in the DSum is a variable.

Your code references and updates only the first record of subform, not the RecordsetClone records.

With Me.subform1.Form.RecordsetClone
While Not .EOF
If .Class_ID = 1 Or .Class_ID = 2 Or .Class_ID = 3 Then
!Total = Me.subform1.Form.Sub_Total * DSum("Qty", "Screen_size", "dt_id=" & Me.[subform1].Form!dt_id)
End If
.MoveNext
Wend
End With

[tahirsatti]

Thanks for your response,Please tell me how to update all records in subform?

[June7]

I already gave you code to do that. Update the RecordsetClone records.

[tahirsatti]

I use your above mentioned code but it is only update first row not all records.

[tahirsatti]

If i click on second row and press button then above code update second row.Please modify the code so that it will update all rows on single click.

[June7]

Did not know you had attempted code. I did do an edit of the post because I forgot couple of lines. Look at it again.

I don't know why you have the conditional criteria but I incorporated it in the code to the best of my understanding.

Still don't understand why you are saving aggregate data. Review http://allenbrowne.com/AppInventory.html

[tahirsatti]

I am using below mentioned code

With Me.[screendetailsubform].Form.RecordsetClone
While Not .EOF
If .Class_Id = 1 Or .Class_Id = 2 Or .Class_Id = 3 Then
Me.[screendetailsubform].Form!total = Me.[screendetailsubform].Form!Sub_Total * DSum("Qty", "Screen_size", "screen_detail_id=" & [screendetailsubform].Form!Screen_detail_id)
End If
.MoveNext
Wend
End With


But still only updating first record.

[June7]

Because you are updating the textbox on the form, not the field in recordset.

Try:

.total = Me.[screendetailsubform].Form!Sub_Total * DSum("Qty", "Screen_size", "screen_detail_id=" & [screendetailsubform].Form!

[tahirsatti]

When i use your below mentioned code than following error apperears.

" Runtime error 438 Object does not support this property or methoed"

Complete code is given below

Private Sub Command168_Click()
With Me.[screendetailsubform].Form.RecordsetClone
While Not .EOF
If .Class_Id = 1 Or .Class_Id = 2 Or .Class_Id = 3 Then
.total = Me.[screendetailsubform].Form!Sub_Total * DSum("Qty", "Screen_size", "screen_detail_id=" & [screendetailsubform].Form!Screen_detail_id)
End If
.MoveNext
Wend
End With
End Sub

[June7]

The very same sort of procedure works for me.

Which line triggers the error?

If you want to provide your db for analysis, follow instructions at bottom of my post.

[tahirsatti]

Thanks for your response.same .total lines triggers the error when i chenge .total with db field name then edit update message apperas.New code is given below.Please note that total is the name of tcost db field at form.Below mentioned code update all records but with the value of first row.


Private Sub Command168_Click()
With Me.[screendetailsubform].Form.RecordsetClone
While Not .EOF
.MoveFirst
If .Class_Id = 1 Or .Class_Id = 2 Or .Class_Id = 3 Then
.Edit
.tcost = Me.[screendetailsubform].Form!Sub_Total * DSum("Qty", "Screen_size", "screen_detail_id=" & [screendetailsubform].Form!Screen_detail_id)
End If
.Update
.MoveNext
Wend
End With
End Sub

[tahirsatti]

Sir.if possible please help to resolve above mentioned issue.Why i am unable to update all records?.

[June7]

What is the Sub_Total value calculated from?

If you want expeditious help, provide the database for analysis?

[tahirsatti]

Sub total is the value of sum of three other fields i.e
Sub_Total=[Style_Pr]+[Increase_Cost]+[Price_rng_pr]