Login/Password screen: "Invalid use of Null" (w/code)

[Tigerdude]

I have almost completed this simple, semi-secure login screen. I have tblPassword with id, UserID and Pword fields. I have basic login screen with two text boxes and one command button. All works well except the UserID input has to match the UserID field perfectly or I get an Access error message "Invalid Use of Null". (If UserID is not typed correctly, I want to get my error message).
A wrong password typed in produces my desired results and if everything is correct it opens frmMAIN. Any code breakers see the problem?
Private Sub cmdlogon_Click()
On Error GoTo Err_cmdlogon_Click
Dim strPassword As String
strPassword = DLookup("[Pword]", "tblPassword", "[UserId] = '" & Text1 & "'&quot
If LCase(strPassword) = LCase(Text2) Then
Visible = False
DoCmd.OpenForm "frmMain", acNormal, , , , acWindowNormal
Else
MsgBox "Can't log on. Username and password do not match. " _
& Chr(13) & "Please try again", vbCritical
End If
Exit_cmdlogon_Click:

Me![Text1] = ""
Me![Text2] = ""

Exit Sub

Err_cmdlogon_Click:
MsgBox Err.Description
Resume Exit_cmdlogon_Click
End Sub
Here's an easy one: How do I get the cursor in the first text box everytime the form opens.

Thanks in advance,
Tigerdude

 

[Maquis]

You need this code right after your Dlookup statement.

If IsNull(strpassword) then
msgbox "Invalid user name entered"
else
<rest of code>
End If Maq B-)
<insert witty signature here>

 

[nickjar2]

How about:
strPassword = nz(DLookup(&quot;[Pword]&quot;, &quot;tblPassword&quot;, &quot;[UserId] = '&quot; &
Text1 & &quot;'&quot,&quot;&quot

Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)

 

[Robertd]

Hi bear with me i havent touched access for a while

snip----
On Error GoTo Err_cmdlogon_Click
'try
Dim strPassword as variant
Dim strTemp as string
'Dim strPassword As String

strPassword = DLookup(&quot;[Pword]&quot;, &quot;tblPassword&quot;, &quot;[UserId] = '&quot; & Text1 & &quot;'&quot
if isnull(strPassword) then
' user name not found strpassword contains a NULL
strTemp = &quot;Can't log on. Username and password &quot;
strTemp = strtemp & &quot;do not match. &quot;
strtemp = strTemp & VbCRLF
strtemp = strTemp & &quot;Please try again&quot;
MsgBox strTemp, vbCritical
elseIf LCase(strPassword) = LCase(Text2) Then
' we got a match for user checked password ok
Visible = False
DoCmd.OpenForm &quot;frmMain&quot;, acNormal, , , ,
acWindowNormal
End If
end snip -----

a sting cant accept a NULL it can be empty eg &quot;&quot;, where as
a variant can accept a NULL

see how you go, and chect the various data types and the special NULL value.

regards
RobertD
PS since i droped off line writing this NZ is also valid, but still check your data types and the return type from the functions that you use, it will make life easier
s-)

Robert Dwyer
rdwyer@orion-online.com.au

 

[GingerR]

hey all--
isnull wont work cause it's a string not number
using nz i got undefined error.

yes your problem tho is that when there is no match you get that error you mentioned.
i wrote this and tested. you need to test for:
UserID is blank
Password is blank
UserID is indeed valid
Password is valid

the only one you were testing for was valid pwd, but that much doesnt matter if other fields are blank or userid is invalid.

On Error GoTo Err_cmdlogon_Click
    Dim strPassword As String
    Dim intUserIDCount, intPasswordCount
    
    If text1 = "" Then
        MsgBox "Please enter a User ID", vbCritical, "Invalid Logon"
        text1.SetFocus
        Exit Sub
    End If
    
    If text2 = "" Then
        MsgBox "Please enter a Password", vbCritical, "Invalid Logon"
        text2.SetFocus
        Exit Sub
    End If
            
    'Check to see if UserID is valid
    intUserIDCount = DCount("UserID", "tblPassword", "[UserID] = '" & text1 & "'")
    If intUserIDCount = 0 Then
        MsgBox "Invalid UserID. Please try again", vbCritical, "Invalid Logon"
        Me.text1 = ""
        Me.text2 = ""
        Me.text1.SetFocus
        Exit Sub
    End If
    
    'Check to see if Password is valid for this UserID
    intPasswordCount = DCount("[Pword]", "tblPassword", "[Pword] = '" & text2 & "' and [UserId] = '" & text1 & "'")
    If intPasswordCount > 0 Then
        Me.Visible = False
        DoCmd.OpenForm "frmMain"
    Else
        MsgBox "Can't log on. Username and password do not match. " _
                    & Chr(13) & "Please try again", vbCritical
        Me.text2 = ""
        Me.text2.SetFocus
    End If
            

Exit_cmdlogon_Click:
    Exit Sub

Err_cmdlogon_Click:
    MsgBox Err.Description
    Resume Exit_cmdlogon_Click

put in diff combos of data to test.
have fun--g

 

[nickjar2]

I tried the nz function like this:
strPassword = nz(DLookup(&quot;[Pword]&quot;, &quot;tblPassword&quot;, &quot;[UserId] = '&quot; &
Text1 & &quot;'&quot,&quot;&quot

and it worked ok. Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)

 

[Tigerdude]

Thanks all very much. Excellent suggestions and a great forum. I decided to use GingerR's code (Cut&Paste) and it worked great. I only added 'text box clear' statements at very end so that the text boxes would be blank after a successful login.
Thanks GingerR and all

Tigerdude