VBA question

[data808]

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.

[June7]

What happens if you enclose Ref Num in [] - remember, spaces cause problems.

[data808]

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.

[data808]

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?

[June7]

Is [Ref Num] a text type field?

[data808]

Its an auto number field because it is the primary key. Is my delimiters wrong?

[June7]

Apostrophe delimiters are for text type field parameters. Number type does not need parameters. Dates use #.

[data808]

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.

[June7]

The quote marks are correct. Try the edits and debug. I didn't scrutinize the code beyond seeing the apostrophe delimiters.

[data808]

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

[ssanfu]

Is Me.txtRefPull a number type or text type field?



I modified your code a little

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

[data808]

Thanks so much Steve. This worked perfectly.