Referencing existing fields in a tables from 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 > 1 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

 

[jimmythegeek]

What are you using all the sendkeys for?? It looks like you are copying and pasting all of one record into another record. Let us know what your are trying to accomplish, and I know we can give you a better way to do whatever it is you need to do. Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[scking]

If !CNT > 0

I agree with Jim that you seem to be taking the hardest road with your design. Even if you continue to use the sendkeys you could use a simple loop to reiterate the 5 commands that are repeated.

Steve King

 

[scking]

If !CNT > 0

I agree with Jim that you seem to be taking the hardest road with your design. Even if you continue to use the sendkeys you could use a simple loop to reiterate the 5 commands that are repeated.

It appears like you don't have 'Options Explicit' set at the top of your module. It requires any variable used to be declared. The reason it always shows 0 is because it is not using the value from the table but since it allows you to create a variant variable without declaring it is using the 0 value of an undeclared variable.

Steve King