Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using fields already in tables using VBA

Using fields already in tables using VBA

Using fields already in tables using VBA

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
            GoTo TheCleaner_Err
        End If
    End With

    Exit Function

    MsgBox Error$
    Resume TheCleaner_Exit

End Function

RE: Using fields already in tables using VBA

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")
    if rst!CNT > 1 then
    End if
    Set rst = nothing
    Exit Function

    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.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close