Nulls, empty string, unbound textbox, required and Allow Zero Length 1

[Conner]

I have a txtbox on a form that is designed to take the user's input and send it to a parameter query.

The user inputs a "secret" PIN number in the unbound field (txtPinNumber) on frmSignIn.

The PIN number is then sent to qrySignIn as a parameter [Forms!][frmPinNumber][txtPIN].

The user then double clicks on a command button to close the open form and open a second form that gathers sign-in time, etc.

My problem is I want this command button to do more than close a form and open a new one.

What I need is for the command button to handle the situation where the user does not enter anything in the PIN number text box (txtPIN) or the user enters a pin number that is not stored in a table (tblPinNumbers). The Pin numbers are strings.

I've tried writting code for the first part of my problem: the user does not enter anything but clicks on the command button anyway. I don't have a clue as to how to handle the second part: the user enter a pin number that doesn't exist.

My code does not work. I get an Access error message "Invalid Use of Null" I've tried everything I can find to do with tables, queries, etc that list "required" and "Allow Zero Length". I suspect since this is an unbound txtbox that this is part of my problem.

Here is my code to this point. I've added to what Access did for me when I made the cmdButton open a linked form.

Private Sub CmdClose_dblClick(Cancel As Integer)
On Error GoTo Err_CmdClose_Click

Dim stDocName As String
Dim stLinkCriteria As string

'I added this variable
Dim strValue As String

strValue=Me!txtPin

'The use of IsNull seems to be causing the problem.
If IsNull(strValue) Then
MsgBox "No PIN Number Entered",vbQuestion+vbOkOnly
DoCmd.Close
DoCmd.OpenForm "frmChooseOne"
Else
'Here I pick up with what code Access VBA wrote for me.
stDocName="[PinNumber]= "&"'" &Me![txtPin]&"'"
DoCmd.OpenForm stDocName...stLinkCriteria
End If

Exit_CmdClose_Click:
End Sub

Err_CmdClose_Click
MsgBox Err.Description
Resume Exit_CmdClose_Click
End Sub

How do I handle null values in an unbound text box? And once I correct that part of the code, how do I handle nonexistent PIN numbers that are not in the table tblPinNumbers?

Please do not make an assumptions about my understanding of VBA and Access. I am a Stranger in a StrangeLand.

 

[CajunCenturion]

You will get an error on the first assignment statement if Me!txtPin is null, therefore, I would try the following:

strValue=Nz(Me!txtPin, vbNullString)

And then, instead of checking for Null (already done that), check the len of the string

If (Len(strValue) > 0) Then

Next, I would consider using the DLookUp function to verify the existence of the PIN. The DLookup function is described in the Help files.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Conner]

CajunCenturion
I've just tried the first part of the solution -- using the Nz Function and then testing the length of the variable strValue. At first, it didn't work, but when I changed the "If (Len(strValue)>0" to "If (Len(strValue)=0" I got what I had programmed to follow the Then part of my If statement. So, seems to be working...

Now on to the DLookUp function.

Thanks.