Results 1 to 12 of 12
  1. #1
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727

    VBA question

    I'm using this but running into an error:

    Private Sub cmdPullUp_Click()
    Dim validCredentials As Integer
    Dim ID As Integer
    On Error GoTo ErrHandler:
    validCredentials = DCount("Ref Num", "[tbl_Counter_Log]", "[Ref Num] ='" & txtReferencePull & "'")

    ID = DLookup("Ref Num", "tbl_Counter_Log", "txtRefNum = '" & Me.txtReferencePull.Value & "'")
    If validCredentials = 1 Then
    DoCmd.Close
    DoCmd.OpenForm "Counter Log", , , "[txtRefNum] = " & ID
    Else
    MsgBox "No Records Are Associated With That Reference Number", vbExclamation, "Notification"


    Me.txtFilter.SetFocus
    End If
    Exit Sub

    ErrHandler:
    MsgBox "Unable To Authenticate At This Time. Contact System Administrator For Help."' Me.txtFilter.SetFocus
    Exit Sub

    End Sub


    The error is:
    Run-time error '3075':
    Syntax error (missing operator) in query expression 'Count(Ref Num)'.

    Anyone know why this isn't working? Trying to get this to open up a form with a specific record that is entered in the txtReferencePull field after clicking the cmdPullUp button. The reference number is a primary key. I figured that would be a good number to pull up a record since it's unique.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    What happens if you enclose Ref Num in [] - remember, spaces cause problems.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    Yeah I tried that. This is the error I get when I use brackets:

    Run-time error 3464
    Data type mismatch in criteria expression

    Debug points to this line:

    validCredentials = DCount("[Ref Num]", "[tbl_Counter_Log]", "[Ref Num] ='" & txtReferencePull & "'")

    The error usually always points to this line. Don't know what I am doing wrong.

  4. #4
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    Ok try take a look at this June7:

    I got this code to work:

    Private Sub cmdPullUp_Click()

    Dim validCredentials As Integer
    Dim ID As Integer
    On Error GoTo ErrHandler:
    validCredentials = DCount("[Customer Name]", "[tbl_Counter_Log]", "[Customer Name] ='" & txtCustName & "' AND [DL Num]='" & txtDLNum & "'")

    ID = DLookup("[Ref Num]", "tbl_Counter_Log", "[Customer Name] = '" & Me.txtCustName.Value & "'")
    If validCredentials = 0 Then
    MsgBox "No Records Are Associated With Those Parameters", vbExclamation, "Notification"
    Me.txtFilter.SetFocus
    ElseIf validCredentials = 1 Then
    DoCmd.Close
    DoCmd.OpenForm "Counter Log", , , "[Ref Num] = " & ID
    Else
    MsgBox "Duplicate Records", vbExclamation, "Notification"
    Me.txtFilter.SetFocus
    End If
    Exit Sub

    ErrHandler:
    MsgBox "Unable To Authenticate At This Time. Contact System Administrator For Help."
    Me.txtFilter.SetFocus
    Exit Sub

    End Sub

    Now I want to alter the code so that this will work:

    Private Sub cmdPullUp_Click()

    Dim validCredentials As Integer
    Dim ID As Integer
    On Error GoTo ErrHandler:
    validCredentials = DCount("[Ref Num]", "[tbl_Counter_Log]", "[Ref Num] ='" & txtRefPull & "'")

    ID = DLookup("[Ref Num]", "tbl_Counter_Log", "[Ref Num] = '" & Me.txtRefPull.Value & "'")
    If validCredentials = 0 Then
    MsgBox "No Records Are Associated With That Reference Number", vbExclamation, "Notification"
    Me.txtFilter.SetFocus
    ElseIf validCredentials = 1 Then
    DoCmd.Close
    DoCmd.OpenForm "Counter Log", , , "[Ref Num] = " & ID
    Else
    MsgBox "Duplicate Records", vbExclamation, "Notification"
    Me.txtFilter.SetFocus
    End If
    Exit Sub

    ErrHandler:
    MsgBox "Unable To Authenticate At This Time. Contact System Administrator For Help."
    Me.txtFilter.SetFocus
    Exit Sub

    End Sub

    The Ref Num is my primary key which is why I would like to use this as the data to be entered to pull up the record. I'm pretty sure this is also why the code does not work. If you take notice I have lessened the field boxes that the user can type in to click the cmdPullUp button to only the one txtRefPull field box because this reference number is unique since its the primary key, this is all I would need to pull up the record that I want in the Counter Log. How can I get this to work?

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Is [Ref Num] a text type field?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    Its an auto number field because it is the primary key. Is my delimiters wrong?

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Apostrophe delimiters are for text type field parameters. Number type does not need parameters. Dates use #.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    So if I get rid of the apostrophes will that work? Do I just leave the quotations in place? Also just to verify, will this code work even though an autonumber PK field is being used here? Thanks for the help as always June7.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    The quote marks are correct. Try the edits and debug. I didn't scrutinize the code beyond seeing the apostrophe delimiters.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    I am getting closer to getting this code to work. However, I am stuck on one part. Right now if I enter a reference number that does not exist it always goes to the ErrHandler. If I turn off the ErrHandler, it will have a run time error 94, invalid use of null. The debug points to the ID = line of code. Does anyone know how to fix it? Basically right now the else statment never executes. It always goes to ErrHandler if the validcredentials is anything but 1.

    Private Sub cmdPullUp_Click()
    If IsNull(Me.txtRefPull) Then
    MsgBox "Please Enter Reference Number"
    Me.txtRefPull.SetFocus
    Exit Sub
    End If


    Dim validCredentials As Integer
    Dim ID As Integer
    'On Error GoTo ErrHandler:
    validCredentials = DCount("[Ref Num]", "[tbl_Counter_Log]", "[Ref Num] = " & txtRefPull & "")
    ID = DLookup("[Ref Num]", "tbl_Counter_Log", "[Ref Num] = " & Me.txtRefPull.Value & "")

    If validCredentials = 1 Then
    DoCmd.Close
    DoCmd.OpenForm "Counter Log", , , "[Ref Num] = " & ID
    Exit Sub
    Else
    MsgBox "No Record Is Associated With The Reference Number Provided", vbExclamation, "Notification"
    Me.txtFilter.SetFocus
    Exit Sub
    End If

    ErrHandler:
    MsgBox "Unable To Authenticate At This Time. Contact System Administrator For Help."
    Me.txtFilter.SetFocus
    Exit Sub

    End Sub

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Is Me.txtRefPull a number type or text type field?



    I modified your code a little
    Code:
    Private Sub cmdPullUp_Click()
        Dim validCredentials As Integer
        Dim ID As Integer
        'On Error GoTo ErrHandler:
    
    
        If IsNull(Me.txtRefPull) Then
            MsgBox "Please Enter Reference Number"
            Me.txtRefPull.SetFocus
            Exit Sub
        End If
    
        validCredentials = DCount("[Ref Num]", "[tbl_Counter_Log]", "[Ref Num] = " & Me.txtRefPull & "")
        If validCredentials >= 1 Then
            ID = DLookup("[Ref Num]", "tbl_Counter_Log", "[Ref Num] = " & Me.txtRefPull & "")
            DoCmd.Close
            DoCmd.OpenForm "Counter Log", , , "[Ref Num] = " & ID
            Exit Sub
        Else
            MsgBox "No Record Is Associated With The Reference Number Provided", vbExclamation, "Notification"
            Me.txtFilter.SetFocus
            Exit Sub
        End If
    
    ExitHere:
        Exit Sub
    
    ErrHandler:
        MsgBox "Unable To Authenticate At This Time. Contact System Administrator For Help."
        Me.txtFilter.SetFocus
        Resume ExitHere
    
    End Sub

  12. #12
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    Thanks so much Steve. This worked perfectly.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-25-2012, 07:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums