Referencing fields from an existing using VBA

[nipchop]

The module below looks at a table (called Table1) within that table is a field called CNT. This is a long integer number which stores a count of shipments. If this is greater than 1, it runs a macro (converted into VBA).

I have a problem in that it never enters the IF statement because it doesn't think that CNT = 1 or greater although it is. If it is changed to IF CNT = 0 then the statement works and the macro is ran...

I dont think that the field is being looked at and think that the reference to this field is wrong.

Can anyone help me?!


Function TheCleaner()
On Error GoTo TheCleaner_Err

DoCmd.OpenTable "Table1", acNormal, acEdit
DoCmd.GoToRecord acTable, "Table1", acFirst
With Table1
If CNT = 0 Then
SendKeys "^c", False
SendKeys "{DOWN}", False
SendKeys "^v", False
SendKeys "{UP}", False
SendKeys "{TAB}", False
SendKeys "^c", False
SendKeys "{DOWN}", False
SendKeys "^v", False
SendKeys "{UP}", False
SendKeys "{TAB}", False
SendKeys "^c", False
SendKeys "{DOWN}", False
SendKeys "^v", False
SendKeys "{UP}", False
SendKeys "{TAB}", False
SendKeys "^c", False
SendKeys "{DOWN}", False
SendKeys "^v", False
SendKeys "{UP}", False
SendKeys "{TAB}", False
SendKeys "^c", False
SendKeys "{DOWN}", False
SendKeys "^v", False
SendKeys "{UP}", False
SendKeys "{TAB}", False
SendKeys "^c", False
SendKeys "{DOWN}", False
SendKeys "^v", False
SendKeys "{UP}", False
SendKeys "{TAB}", False
SendKeys "{TAB}", False
SendKeys "{TAB}", False
SendKeys "{TAB}", False
SendKeys "^c", False
SendKeys "{DOWN}", False
SendKeys "^v", False
SendKeys "{UP}", False
SendKeys "{TAB}", False
SendKeys "^c", False
SendKeys "{DOWN}", False
SendKeys "^v", False
SendKeys "{UP}", False
SendKeys "{TAB}", False
SendKeys "^c", False
SendKeys "{DOWN}", False
SendKeys "^v", False
SendKeys "{UP}", False
SendKeys "{TAB}", False
SendKeys "^c", False
SendKeys "{DOWN}", False
SendKeys "^v", False
SendKeys "{UP}", False
SendKeys "{TAB}", False
SendKeys "{TAB}", False
SendKeys "^c", False
SendKeys "{DOWN}", False
SendKeys "^v", False
Else
GoTo TheCleaner_Err
End If
End With

TheCleaner_Exit:
Exit Function

TheCleaner_Err:
MsgBox Error$
Resume TheCleaner_Exit

End Function

 

[nipchop]

The module below looks at a table (called Table1) within that table is a field called CNT. This is a long integer number which stores a count of shipments. If this is greater than 1, it runs a macro (converted into VBA).

I have a problem in that it never enters the IF statement because it doesn't think that CNT = 1 or greater although it is. If it is changed to IF CNT = 0 then the statement works and the macro is ran...

I dont think that the field is being looked at and think that the reference to this field is wrong.

Can anyone help me?!


Function TheCleaner()
On Error GoTo TheCleaner_Err

DoCmd.OpenTable "Table1", acNormal, acEdit
DoCmd.GoToRecord acTable, "Table1", acFirst
With Table1
If CNT = 0 Then
SendKeys "^c", False
SendKeys "{DOWN}", False
SendKeys "^v", False
SendKeys "{UP}", False
SendKeys "{TAB}", False
Else
GoTo TheCleaner_Err
End If
End With

TheCleaner_Exit:
Exit Function

TheCleaner_Err:
MsgBox Error$
Resume TheCleaner_Exit

End Function

 

[CybOrg]

If the column in the table is named "CNT" then the proper syntax is:

With Table1
IF !CNT = 0 Then
Do the stuff to do when CNT=0
Else
...
Endif
.Close
End With

The problem is the missing "!". What happens is that CNT becomes an implicit variable (No 'Option Explicit'!) which remains constant at zero (actually undefined).