Type Mismatch Error

[magcasen]

Hi can someone help me out with my code, i tried to compare the text that i get in my form and compare with the value that i have on my table but it seems that i'm getting type mismatch error even though the data type of the
Username Field on my table is Text , same goes for my Password Field on my table as text on ms access



Here is my code on VBA:

Option Compare Database

Private Sub Command1_Click()


If (DLookup("Username", "User", "Username = '" & Me.txtUserName.Value & "'")) And (DLookup("Password", "User", "Password= '" & Me.txtPassword.Value & "'")) Then
DoCmd.OpenForm "Form1"
Else
Me.txtUserName.SetFocus
Me.txtPassword.SetFocus
MsgBox "Incorrect Input, Please enter the Username and Password Required"
End If

End Sub

 

[magcasen]

Whats the difference with the data type of the text in textbox in VBA and the data type Text in MS Access ??? why don't they get along =___=

 

[Galaxiom]

Probably be something to do with Nulls.

 

[magcasen]

can you help me fix the null problem?

 

[JHB]

Try the below:

If DLookup("Username", "User", "Username ='" &  Me.txtUserName.Value & "' AND Password='" & Me.txtPassword.Value & "'") Then

 

[JANR]

can you help me fix the null problem?

Wrap DLookup using the NZ() function to handle nulls.

ex:

If  Nz(DLookup(.......),"") = "" Then ...

I would just open a recordset and test if there are any records, if so you open Form1

ex:

Private Sub Command1_Click()
With Currentdb.OpenRecordset("Select * From User Where Username = '" & Me.txtUsername & "' And " & _
                             "Password = '" & Me.txtPassword & "'")
    If .Recordcount > 0 Then
       Docmd.OpenForm "Form1"
    Else
       Me.txtUserName.SetFocus
       Me.txtPassword.SetFocus
       MsgBox "Incorrect Input, Please enter the Username and Password Required"
    End If
End with
End Sub

JanR

 

[robslob]

I think the Type mismatch is an error in the logic.

If the user is Tom and the password is 1234 then you would have:
IF 'Tom' AND '1234' THEN.....
which makes no sense.

The recordset solution looks best for me.

 

[Solo712]

Try the below:

If DLookup("Username", "User", "Username ='" &  Me.txtUserName.Value & "' AND Password='" & Me.txtPassword.Value & "'") Then

With the Nz() added, this is the correct solution. The error is in the messed up criteria syntax. One further improvement that I would suggest is that both the txtUserName and txtPassword are tested for Null values before they are fed into the function. The Nz() function for the DLookup alone does not help as it handles the Null in the result and not in the arguments.

Best,
Jiri