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.

 

[Golom]

Here's a starting point

Sub xxx(TotalTime As Long, SerialNumber As Long)
Dim SQL                         As String
For DefectCounter = 1 To B

    [COLOR=black cyan]'Update ReworkLabor1 Field with total time[/color]
    SQL = "UPDATE TblPAQ3280Process " & _
          "SET   ReworkLabor" & DefectCounter & " = " & TotalTime & " " & _
          "WHERE ReworkLabor" & DefectCounter & " Is Null " & _
          "  AND SerialNumber= " & SerialNumber & " "
    CurrentDb.Execute SQL

    [COLOR=black cyan]'Update DefectCounter[/color]
    SQL = "UPDATE TblPAQ3280Process " & _
          "SET   ReworkCount = " & DefectCounter & " " & _
          "WHERE SerialNumber= " & SerialNumber & " "
    CurrentDb.Execute SQL

Next DefectCounter

You cannot have User Supplied parameters in SQL generated in this way. You must supply the actual values for those parameters because the JET Engine (as opposed to Access) does not generate an input box for them.

 

[access345]

I adapted your Code

Public Sub DefectCnt()

On Error GoTo ERR_DefectCnt_1

Dim TotalTime As Long
Dim SerialNumber As Long
Dim SQL As String
Dim Defectcounter As Double


For Defectcounter = 1 To B

'Update ReworkLabor1 Field with total time
SQL = "UPDATE TblPAQ3280Process " & _
"SET ReworkLabor" & Defectcounter & " = " & TotalTime & " " & _
"WHERE ReworkLabor" & Defectcounter & " Is Null " & _
" AND SerialNumber= " & SerialNumber & " "
CurrentDb.Execute SQL

'Update DefectCounter
SQL = "UPDATE TblPAQ3280Process " & _
"SET ReworkCount = " & Defectcounter & " " & _
"WHERE SerialNumber= " & SerialNumber & " "
CurrentDb.Execute SQL

Next Defectcounter

Exit Sub

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

End Sub


With this code what I am seeing is the Counter is not counting and the values are not being added to the table.

 

[Golom]

You are probably getting errors but they are not being returned to your code. You need to use

 CurrentDb.Execute SQL[red], dbFailOnError[/red]

to get JET to return error conditions on action queries.

 

[access345]

I added the dbFailOnError command and the counter still is not working. When I step through the program It goes to For Defectcounter = 1 To B than to exit sub. Maybe I need to take the query language out and do a DoCMd openQuery()?

 

[Remou]

I do not see where you set B (1 to B) as equal to any number.

 

[Golom]

What value does "B" have? If it is less than 1 then nothing inside the For Loop will be executed.

 

[access345]

Dim TotalTime As Long
Dim SerialNumber As Long
Dim SQL As String
Dim DefectCounter As Double
Dim Count As Double

Set Count = 1

For DefectCounter = 1 To Count



'Update ReworkLabor1 Field with total time
SQL = "UPDATE TblPAQ3280Process " & _
"SET ReworkLabor" & DefectCounter & " = " & TotalTime & " " & _
"WHERE ReworkLabor" & DefectCounter & " Is Null " & _
" AND SerialNumber= " & SerialNumber & " "
CurrentDb.Execute SQL, dbFailOnError

'Update DefectCounter
SQL = "UPDATE TblPAQ3280Process " & _
"SET ReworkCount = " & DefectCounter & " " & _
"WHERE SerialNumber= " & SerialNumber & " "
CurrentDb.Execute SQL, dbFailOnError

Next DefectCounter


Exit Sub

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

End Sub


Now I am getting a Compile error Object required

 

[access345]

Different approach

Public Sub DefectCnt1()

On Error GoTo ERR_DefectCnt_1


Dim TotalTime As Long
Dim SerialNumber As Long
Dim SQL As String
Dim DefectCounter As Double
Dim Counter As Double

Set Counter = 1

For DefectCounter = 1 To Counter



'Update ReworkLabor1 Field with total time
If DefectCounter = 1 Then
DoCmd.OpenQuery (qryupdateRwk1), acViewNormal, acEdit

End If

'Update DefectCounter
'SQL = "UPDATE TblPAQ3280Process " & _
'"SET ReworkCount = " & Defectcounter & " " & _
'"WHERE SerialNumber= " & SerialNumber & " "
'CurrentDb.Execute SQL, dbFailOnError

Next DefectCounter


Exit Sub

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

End Sub


I am still getting the object error for counter

 

[mharroff]

Don't use "Set" with Set Counter = 1
It should just be Counter=1

 

[access345]

I Removed the Set command
When I step through the code
If DefectCounter = 1 Then
DoCmd.OpenQuery (qryupdateRwk1), acViewNormal, acEdit
I get to the DefectCounter =1
But when I look at qryupdateRwk1 it says its empty

 

[PHV]

DoCmd.OpenQuery "qryupdateRwk1", acViewNormal, acEdit

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[access345]

The query did execute thanks PHV. But my next question is if I have count =1 than how is the counter going to work? Isn't the counter always going to be at 1?

 

[access345]

This is the whole module:

Public Sub DefectCnt1()

On Error GoTo ERR_DefectCnt_1


Dim TotalTime As Long
Dim SerialNumber As Long
Dim SQL As String
Dim DefectCounter As Double
Dim Counter As Double


Counter = 1

For DefectCounter = 1 To Counter



'Update ReworkLabor1 Field with total time
If Counter = 1 Then
DoCmd.OpenQuery "qryupdateRwk1", acViewNormal, acEdit

End If

If Counter = 2 Then
DoCmd.OpenQuery "qryupdateRwk2", acViewNormal, acEdit

End If

If Counter = 3 Then
DoCmd.OpenQuery "qryupdateRwk3", acViewNormal, acEdit

End If

If Counter = 4 Then
DoCmd.OpenQuery "qryupdateRwk4", acViewNormal, acEdit

End If

If Counter = 5 Then
DoCmd.OpenQuery "qryupdateRwk5", acViewNormal, acEdit

End If

If Counter = 6 Then
DoCmd.OpenQuery "qryupdateRwk6", acViewNormal, acEdit

End If

If Counter = 7 Then
DoCmd.OpenQuery "qryupdateRwk7", acViewNormal, acEdit

End If

If Counter = 8 Then
DoCmd.OpenQuery "qryupdateRwk8", acViewNormal, acEdit

End If

If Counter = 9 Then
DoCmd.OpenQuery "qryupdateRwk9", acViewNormal, acEdit

End If

If Counter = 10 Then
DoCmd.OpenQuery "qryupdateRwk10", acViewNormal, acEdit

End If
Next DefectCounter


Exit Sub

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

End Sub

 

[mharroff]

Just a guess but maybe:

Counter = DLookup("ReworkCount", "tblPAQ3280Process", "SerialNumber =" & SerialNumber) + 1

This will look in the table and see how may reworks are currently listed and then add one to it.

Also,

If the rework counter is at 5 do you really want to run qryupdateRwk1 thru qryupdateRwk5 or just qryupdateRwk5?

If you just want qryupdateRwk5 then something like this might work

On Error GoTo ERR_DefectCnt_1

Dim TotalTime As Long
Dim SerialNumber As Long
Dim SQL As String
Dim DefectCounter As Double
Dim Counter As Double
Dim strQueryName As String


Counter = DLookup("ReworkCount", "tblPAQ3280Process", "SerialNumber =" & SerialNumber) + 1
strQueryName = "qryupdateRwk" & Counter
DoCmd.OpenQuery strQueryName, acViewNormal, acEdit

'Update DefectCounter
'SQL = "UPDATE TblPAQ3280Process " & _
      '"SET   ReworkCount = " & Defectcounter & " " & _
      '"WHERE SerialNumber= " & SerialNumber & " "
'CurrentDb.Execute SQL, dbFailOnError

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

 

[access345]

Public Sub DefectCnt1()

On Error GoTo ERR_DefectCnt_1


Dim SerialNumber As Long
Dim Counter As Double
Dim ReworkCount As Double

SerialNumber = 4869

Counter = DLookup("ReworkCount", "TblPAQ3280Process", "SerialNumber =" & SerialNumber) + 1
strQueryName = "qryupdateRwk" & Counter
DoCmd.OpenQuery strQueryName, acViewNormal, acEdit

Exit Sub

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

End Sub

This is the code I really want. Unfortunatly it does not work. I get the serialnumber into the counter but nothing else.

 

[mharroff]

Not sure if this is your problem but the Dlookup will fail if ReworkCount is null.
Try this

Public Sub DefectCnt1()

On Error GoTo ERR_DefectCnt_1


Dim SerialNumber As Long
Dim Counter As Double
Dim ReworkCount As Double

SerialNumber = 4869

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

strQueryName = "qryupdateRwk" & Counter
DoCmd.OpenQuery strQueryName, acViewNormal, acEdit

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

End Sub

 

[access345]

Public Sub DefectCnt1()

On Error GoTo ERR_DefectCnt_1


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

SerialNumber = 4869

Counter = DLookup("ReworkCount", "TblPAQ3280Process", "SerialNumber =" & SerialNumber) + 1
strQueryName = "qryupdateRwk" & Counter
DoCmd.OpenQuery strQueryName, acViewNormal, acEdit
Exit Sub

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

End Sub

My ReworkCount Field was null so I changed it to 0
I added a Dim statement for the str QueryName
When I look at the locals window
I get:
SerialNumber = 4869
ReworkCount =0
Counter =0
StQueryName =""

 

[mharroff]

Is it completing the code or does it error out?

 

[access345]

I changed some things and now he code will execute. But the counter is not working.

Public Sub DefectCnt1()

On Error GoTo ERR_DefectCnt_1


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

SerialNumber = 4569

If Counter = 0 Then Counter = 1

Counter = DLookup("ReworkCount", "TblPAQ3280Process", SerialNumber) + 1
strQueryName = "qryupdateRwk" & Counter
DoCmd.OpenQuery strQueryName, acViewNormal, acEdit

Exit Sub

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

End Sub

My ReworkCount variable is not updating. So I thought I would put it in my query. But I could not figure out how to put the variable in the query.

Here is the query:

UPDATE TblPAQ3280Process SET TblPAQ3280Process.ReworkLabor1 = [Enter Total Time of Repair], TblPAQ3280Process.ReworkCount = "rework"+1
WHERE (((TblPAQ3280Process.SerialNumber)=[Enter Serial Number]));