There are other things that look wrong.....
Should NOT use special characters in object names. It appears you have field names "[#ofRows2]" and "[#ofRows]".
Better would be "NumOfRows" and "NumOfRows2"
Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.
------------------
In your code, you have
Code:
DoCmd.SetWarnings (WarningsOff)
and
DoCmd.SetWarnings (warningsOn)
The correct syntax, per Access, is
Code:
DoCmd.SetWarnings False
and
DoCmd.SetWarnings True
------------------
I never use DoCmd.SetWarnings. Instead, I use the Execute method.
Example
Code:
currentdb.execute (strSQL), dbfailonerror
I modified your posted code.
Code:
Private Sub Command522_Click()
Dim d As DAO.Database
Set d = CurrentDb
If DLookup("[Total]", "qry_BP40_Gummy_We04_Pt2") <= 30 Then
d.Execute ("qry_BP40_Gummy_We04_Pt8"), dbFailOnError
d.Execute ("qry_BP40_Gummy_UPK_Add_Pt4"), dbFailOnError
DoCmd.RefreshRecord
Else
If DLookup("[#ofRows2]", "qry_BP40_Gummy_We04_Pt3") <= 2 Then
d.Execute ("qry_BP40_Gummy_We04_Pt5"), dbFailOnError
d.Execute ("qry_BP40_Gummy_We04_Pt9"), dbFailOnError
DoCmd.RefreshRecord
Else
Select Case Me.SKID
Case "SKID5"
Call We04_Pt1
Call We04_Pt2
Case "SKID5-F"
d.Execute ("qry_BP40_Gummy_We04_Pt8"), dbFailOnError
DoCmd.RefreshRecord
Case "SKID6"
Dim I As Integer
For I = 1 To DLookup("[#ofRows]", "qry_BP40_Gummy_We04_Pt3")
d.Execute ("qry_BP40_Gummy_We04_Pt7"), dbFailOnError
DoCmd.RefreshRecord
Next I
End Select
End If
End If
Set d = Nothing
End Sub
------------------
In code like this,
Code:
Case "SKID6"
Dim I As Integer
For I = 1 To DLookup("[#ofRows]", "qry_BP40_Gummy_We04_Pt3")
d.Execute ("qry_BP40_Gummy_We04_Pt7"), dbFailOnError
DoCmd.RefreshRecord
Next I
End Select
you are assuming that the DLookup will ALWAYS return a number of rows greater than 0.
Consider
Code:
Case "SKID6"
Dim I As Integer
Dim NumRows As Integer
NumRows = DLookup("[#ofRows]", "qry_BP40_Gummy_We04_Pt3")
If NumRows > 0 Then
For I = 1 To NumRows
d.Execute ("qry_BP40_Gummy_We04_Pt7"), dbFailOnError
DoCmd.RefreshRecord
Next I
End If
End Select