Using fields already in tables 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
Else
GoTo TheCleaner_Err
End If
End With

TheCleaner_Exit:
Exit Function

TheCleaner_Err:
MsgBox Error$
Resume TheCleaner_Exit

End Function

 

[ntp]

If what you are trying to do is perform an action if CNT is greater than 1 then you should do something like

Private Sub TheCleaner()
On Error Goto TheCleaner_Err

Dim rst as recordset
Set rst = currentdb.openRecordset("SELECT * FROM Table1&quot
rst.movefirst
if rst!CNT > 1 then
...
End if
rst.close
Set rst = nothing
TheCleaner_Exit:
Exit Function

TheCleaner_Err:
MsgBox Error$
Resume TheCleaner_Exit

End sub

What this does is it opens a recordset based on an SQL statement; moves to the first record; tests the value of the field CNT for the first record.

I don't think this is all that you want to do. I don't see much functional use, testing the first record only. You might want to move through the entire list of records.

ntp