Ask for employee ID,check against a table of registered IDs,give error if not in tabl

[seriestoo2]

Hey guys, I'm new to visual basic and I'm trying to create a piece of code that would, after clicking a particular button, prompt the user for an employee ID (preferably using InputBox), then check the ID against an existing table of registered IDs. It would return an error and prompt the user to try again if null or an unregistered string is given.

I appreciate any help.

[pbaldy]

What do you have so far? Tools include InputBox, DCount() and an If/Then/Else block. More on the DCount() syntax:

DLookup Usage Samples

[seriestoo2]

This is it:

Pin = Trim(InputBox("Enter Employee PIN", "PIN Required"))

[pbaldy]

If DCount(...) = 0 Then

[seriestoo2]

Ok here's my updated code. It keeps returning "Runtime error: 2471"

Line1:        
        entry = Trim(InputBox("Enter Employee PIN", "PIN Required"))
        If entry = "" Then Exit Sub
        empl = DLookup("[Employee]", "EmplPIN", "[PIN]=entry")
        If empl = Null Then
                MsgBox "PIN not found"
                GoTo Line1
            Else
                MsgBox ("Employee registered as" & empl)
            End If

[pbaldy]

It would help to know the data types of the fields, but try changing these 2 lines:

empl = DLookup("[Employee]", "EmplPIN", "[PIN]=" & entry)
If IsNull(empl) Then

[seriestoo2]

OK I really appreciate all your help so far. Now I'm getting runtime error 6, overflow.
The PIN field is a number, Employee is text.

        Dim entry As IntegerLine1:
        entry = InputBox("Enter Employee PIN", "PIN Required")
        empl = DLookup("[Employee]", "EmplPIN", "[PIN]=" & entry)
        If IsNull(empl) Then
                MsgBox "PIN not found"
                GoTo Line1
            Else
                MsgBox ("Employee registered as" & empl)
            End If

[pbaldy]

If the first line isn't a typo you've jammed it all together instead of having separate lines. Given the use I'd add

Dim empl As Variant

If it still errors, what value is being entered? Integer can only handle up to roughly 32k. You want Long if the value is bigger than that.

[seriestoo2]

Thank you so much! The issue was that some PINs were 5 digits, putting them above 32k.

Final question: is there a way I can have a msgbox if a user enters too many digits, or text, instead of a runtime error appearing?

[pbaldy]

You can test for text by testing entry with IsNumeric(). You can test for digits either by testing for > 99999 or using the Len() function.