For Next Loop Mechanics 2

[access345]

I need a For Next loop to count the amount of times a instance occurs. I will warn helpers this is the first time I have tried to create one. What I am trying to accomplish is to have a counter keep track of defects. When I have a defect a person will click on a button that will call this loop. Update the defectcounter field in the TblPAQ3280Process table and insert the total time in the reworkLabor1 field if this is the first defect. Or the reworkLabor2 field if this is the second time... etc.

'For DefectCounter = 1 To B

'Update ReworkLabor1 Field with total time
'UPDATE TblPAQ3280Process SET TblPAQ3280Process.ReworkLabor&"DefectCounter" = [Enter Total Time of Rework]
'WHERE (((TblPAQ3280Process.ReworkLabor&"DefectCounter") Is Null) AND ((TblPAQ3280Process.SerialNumber)=[Enter Serial Number]));

'Update DefectCounter
'UPDATE TblPAQ3280Process SET TblPAQ3280Process.ReworkCount = "DefectCounter"
'WHERE (((TblPAQ3280Process.SerialNumber)=[Enter Serial Number]));

'
'Next DefectCounter

Any help would be appreciated.

 

[mharroff]

Add this after the DoCmd.Openquery line

SQL = "UPDATE TblPAQ3280Process " & _
      "SET   ReworkCount = " & Counter & " " & _
      "WHERE SerialNumber= " & SerialNumber & " "

CurrentDb.Execute SQL, dbFailOnError

 

[access345]

I added your code:
Public Sub DefectCnt1()

On Error GoTo ERR_DefectCnt_1


Dim SerialNumber As Long
Dim Counter As Double
Dim SQL As String
Dim ReworkCount As Double
Dim strQueryName As String

SerialNumber = 4569

If Counter = 0 Then Counter = 1

Counter = DLookup(([ReworkCount] + (1)), "TblPAQ3280Process", SerialNumber)
strQueryName = "qryupdateRwk" & Counter
DoCmd.OpenQuery strQueryName, acViewNormal, acEdit
SQL = "UPDATE TblPAQ3280Process " & _
"SET ReworkCount = " & Counter & " " & _
"WHERE SerialNumber= " & SerialNumber & " "

CurrentDb.Execute SQL, dbFailOnError
Exit Sub

ERR_DefectCnt_1:
MsgBox "Information not available. Contact Administrator x412"
Exit Sub

End Sub

The Code executes but the counter still does not count.
When I get to the added code this is the message I get:
UPDATE TblPAQ3280Process SET ReworkCount = 1 WHERE SerialNumber= 4569

ReworkCount gets set to 1 but it never goes higher.

 

[mharroff]

Change these 2 lines

If Counter = 0 Then Counter = 1
Counter = DLookup(([ReworkCount] + (1)), "TblPAQ3280Process", SerialNumber)

To

If IsNull(DLookup("ReworkCount", "TblPAQ3280Process", "SerialNumber =" & SerialNumber)) Then
Counter = 1
Else
Counter = DLookup("ReworkCount", "TblPAQ3280Process", "SerialNumber =" & SerialNumber)+1
End If

 

[access345]

Thank you for sticking it out with me. You finally got this working for me. Again thanks for your patience.

 

[mharroff]

Glad to be of service.