I hope this is the right place to put this... Please let me know since I have never really done the forum thing before.
This is to go through multiple subforms and calculate a total estimated and actual for each material listed along with carryover for the pricing. It worked great until we went from Access 2003 to Access 2010. For the longest it has been here and there that it errors but as we get busier it seems to happen more and more.
I can't figure out for the life of me where I am going wrong on this one. I know things should be better laid out on this, but I am more of a break/ fix kind of guy I guess. I get multiple types of errors on this, 3420 - where it says I am not specifying an object on the .movefirst line. It sometimes says the field does not exist on the !priceEst line. It has given me another that I can not think of off the top of my head.
Well here goes.
This is to go through multiple subforms and calculate a total estimated and actual for each material listed along with carryover for the pricing. It worked great until we went from Access 2003 to Access 2010. For the longest it has been here and there that it errors but as we get busier it seems to happen more and more.
I can't figure out for the life of me where I am going wrong on this one. I know things should be better laid out on this, but I am more of a break/ fix kind of guy I guess. I get multiple types of errors on this, 3420 - where it says I am not specifying an object on the .movefirst line. It sometimes says the field does not exist on the !priceEst line. It has given me another that I can not think of off the top of my head.
Well here goes.
Code:
Private Sub Command113_Click()
On Error GoTo Err_Command113_Click
Const MaxNumIte = 200
Dim TotA(MaxNumIte) As Double
Dim TotE(MaxNumIte) As Double
Dim TotR(MaxNumIte) As Double
Dim TotP(MaxNumIte) As Double
Dim IDMatR(MaxNumIte)
For I = 0 To MaxNumIte
TotA(I) = 0
TotE(I) = 0
TotR(I) = 0
TotP(I) = 0
IDMatR(I) = 0
Next I
NumIte = -1
IDArea = 100 * Int(Form_MATFORM.Areas_subform16.Form.Recordset!IDAre / 100)
Form_MATFORM.Areas_subform16.Form.Filter = "dbo_Areas.IDare<" & (IDArea + 99) & " And dbo_Areas.IDare>" & (IDArea - 1)
Form_MATFORM.Areas_subform16.Form.FilterOn = True
Me.Areas_subform16.Form.Recordset.MoveFirst
While Not Me.Areas_subform16.Form.Recordset.EOF
If (Me.Areas_subform16.Form.Recordset!IDAre > IDArea - 1) And (Me.Areas_subform16.Form.Recordset!IDAre < IDArea + 99) Then
Form_MATFORM.[Areas subform16].Controls("RAreMat subform").Form.Recordset.MoveFirst
With Form_MATFORM.[Areas subform16].Controls("RAreMat subform").Form.Recordset
While Not .EOF
If IsNull(!PriceEst) Then
.Edit
!PriceEst = 0
.Update
End If
IDMatE = !IDMat
InsNewIte = True
For I = 0 To NumIte
If IDMatR(I) = IDMatE Then
InsNewIte = False
Exit For
End If
Next I
If InsNewIte Then
NumIte = NumIte + 1
IDMatR(NumIte) = IDMatE
TotA(NumIte) = Nz(!Actual, 0)
TotE(NumIte) = Nz(!Estimate, 0)
TotP(NumIte) = !PriceEst
Else
TotA(I) = TotA(I) + !Actual
TotE(I) = TotE(I) + !Estimate
If Nz(!PriceEst, 0) > 0 Then
TotP(I) = !PriceEst
End If
End If
.MoveNext
Wend
End With
End If
Me.Areas_subform16.Form.Recordset.MoveNext
Wend
Me.Areas_subform16.Form.Recordset.MoveFirst
While Not Me.Areas_subform16.Form.Recordset.EOF
If (Me.Areas_subform16.Form.Recordset!IDAre > IDArea - 1) And (Me.Areas_subform16.Form.Recordset!IDAre < IDArea + 99) Then
With Me.Areas_subform16.Controls("RAreMat subform").Form.Recordset
.MoveFirst
While Not .EOF
IDMatE = !IDMat
For I = 0 To NumIte
If IDMatE = IDMatR(I) Then Exit For
Next I
If TotA(I) > 0 Then
EstimateE = Int(TotE(I) * !Actual / TotA(I))
.Edit
TotR(I) = TotR(I) + EstimateE
!Estimate = EstimateE
!PriceEst = TotP(I)
.Update
End If
.MoveNext
Wend
End With
End If
Me.Areas_subform16.Form.Recordset.MoveNext
Wend
Me.Areas_subform16.Form.Recordset.MoveFirst
While Not Me.Areas_subform16.Form.Recordset.EOF
If (Me.Areas_subform16.Form.Recordset!IDAre > IDArea - 1) And (Me.Areas_subform16.Form.Recordset!IDAre < IDArea + 99) Then
With Me.Areas_subform16.Controls("RAreMat subform").Form.Recordset
.MoveFirst
While Not .EOF
IDMatE = !IDMat
For I = 0 To NumIte
If IDMatE = IDMatR(I) Then Exit For
Next I
If TotA(I) > 0 Then
.Edit
!Estimate = !Estimate + TotE(I) - TotR(I)
!PriceEst = TotP(I)
TotR(I) = TotE(I)
.Update
End If
.MoveNext
Wend
End With
End If
Me.Areas_subform16.Form.Recordset.MoveNext
Wend
Form_MATFORM.Areas_subform16.Form.Filter = ""
Form_MATFORM.Areas_subform16.Form.FilterOn = False
Exit_Command113_Click:
Exit Sub
Err_Command113_Click:
MsgBox Err.Description
Resume Exit_Command113_Click
End Sub