Data Type mismatch error?

[Guest]

Hi there

I have logon box which when the user enters a username and password a form will open. The code is below. this worked well in AC97, but in Access XP, i am getting a database type mismatch on the following line

Set rstLogon = dbsMaster.OpenRecordset("SELECT * FROM tblLogon WHERE Username = Username AND Password = Password;"

Can anyone help me on this one

Thank you in advance
Private Sub cmdVerify_Click(
'verifies the user name and password are correct. if they are, access is provided to datatables, if not, the
'access is denie

'On Error GoTo click_err
Dim dbsMaster As Databas
Dim rstLogon As Recordse

Set dbsMaster = CurrentDb(
Set rstLogon = dbsMaster.OpenRecordset("SELECT * FROM tblLogon WHERE Username = Username AND Password = Password;"

With rstLogo
If Not rstLogon.RecordCount = 0 The
Do While Not .EO
If rstLogon!Username = Username And rstLogon!Password = Password The

DoCmd.Close acForm, "frmLogon
DoCmd.Close acForm, "frmAdministration
DoCmd.OpenForm "frmDataTableEditable

ElseIf rstLogon!Username <> Username And rstLogon!Password <> Password The
.MoveNex

Els
MsgBox "The Username or Password entered is incorrect.", vbOKOnly, "Logon Error!
DoCmd.Close acForm, "frmLogon
End I
Loo
End I
End Wit
If Username <> rstLogon.RecordCount = -1 The
MsgBox "Sorry! The information you entered is incorrect."
& Chr(13) & Chr(13) & "Please contact your database administrator", vbOKOnly, "Logon Error!
DoCmd.Close acForm, "frmLogon
End I
rstLogon.Clos

'click_err
' Exit Su
End Su

 

[Phil Hunt]

common problem. You need to get a pair of double quote around the username
and password (the data, not the field name). I won't show you the code,
because it is confusing. But tha's the problem.

Hi there,

I have logon box which when the user enters a username and password a form

will open. The code is below. this worked well in AC97, but in Access XP,
i am getting a database type mismatch on the following line:

Set rstLogon = dbsMaster.OpenRecordset("SELECT * FROM tblLogon WHERE

Username = Username AND Password = Password;")

Can anyone help me on this one?

Thank you in advance,
Private Sub cmdVerify_Click()
'verifies the user name and password are correct. if they are, access is

provided to datatables, if not, then

'access is denied

'On Error GoTo click_err:
Dim dbsMaster As Database
Dim rstLogon As Recordset

Set dbsMaster = CurrentDb()
Set rstLogon = dbsMaster.OpenRecordset("SELECT * FROM tblLogon WHERE

Username = Username AND Password = Password;")

With rstLogon
If Not rstLogon.RecordCount = 0 Then
Do While Not .EOF
If rstLogon!Username = Username And rstLogon!Password = Password Then

DoCmd.Close acForm, "frmLogon"
DoCmd.Close acForm, "frmAdministration"
DoCmd.OpenForm "frmDataTableEditable"

ElseIf rstLogon!Username <> Username And rstLogon!Password <> Password Then
.MoveNext

Else
MsgBox "The Username or Password entered is incorrect.", vbOKOnly, "Logon Error!"
DoCmd.Close acForm, "frmLogon"
End If
Loop
End If
End With
If Username <> rstLogon.RecordCount = -1 Then
MsgBox "Sorry! The information you entered is incorrect." _
& Chr(13) & Chr(13) & "Please contact your database

administrator", vbOKOnly, "Logon Error!"

 

[John Spencer (MVP)]

Access XP defaults to using ADO vice DAO. So you need a reference to the DAO
library. To do that
Open any module
Select Tools: References
Find and Check Microsoft DAO 3.6(???) Object Library
(Not positive on the version number, but I think it is correct)

Once you have that you need to either remove the reference to ADO (you are
probably not using that library) or you need to specify which library you are
using in specific cases.

Dim dbsMaster as DAO.Database
Dim rstLogon as DAO.Recordset

Also, I am suspicious of your SQL statement. I think it should read

"SELECT * FROM tblLogon WHERE Username =" & Chr(34) & me.Username & Chr(34) & "
AND Password =" & Chr(34) & Me.Password & Chr(34)

Of course you could do all this with simpler code, that wouldn't even require
opening a recordset.

Private Sub cmdVerify_Click()
If DCount("*","tblLogon", _
"Username =" & Chr(34) & me.Username & Chr(34) & _
" AND Password =" & Chr(34) & Me.Password & Chr(34)) = 0 THEN

'Your message/actions here for invalid logon
Else
'Successful logon actions here
End If
End Sub

Your current SQL statement is returning every record in tblLogon and that is why
you have to step through every record in the recordset until you get a match.

That would

 

[William Hansen]

I have the same problem. I also have code that checks if
there is no found record:

"If rstAuth.NoMatch = False Then"

which recieves the compile error "Method or
Data Type not found." All this worked in 97. I suspect
the problem is a compatability library is needed, but I
do not know what and where it is. I am using "Microsoft
DAO 3.6 Object Library"

Can anyone help?
Thank you,
Bill Hansen

-----Original Message-----
Hi there,

I have logon box which when the user enters a username

and password a form will open. The code is below. this
worked well in AC97, but in Access XP, i am getting a
database type mismatch on the following line:

Set rstLogon = dbsMaster.OpenRecordset("SELECT * FROM

tblLogon WHERE Username = Username AND Password =
Password;")

Can anyone help me on this one?

Thank you in advance,
Private Sub cmdVerify_Click()
'verifies the user name and password are correct. if

they are, access is provided to datatables, if not, then

'access is denied

'On Error GoTo click_err:
Dim dbsMaster As Database
Dim rstLogon As Recordset

Set dbsMaster = CurrentDb()
Set rstLogon = dbsMaster.OpenRecordset("SELECT *

FROM tblLogon WHERE Username = Username AND Password =
Password;")

With rstLogon
If Not rstLogon.RecordCount = 0 Then
Do While Not .EOF
If rstLogon!Username = Username And rstLogon! Password = Password Then

DoCmd.Close acForm, "frmLogon"
DoCmd.Close acForm, "frmAdministration"
DoCmd.OpenForm "frmDataTableEditable"

ElseIf rstLogon!Username <> Username And

.MoveNext

Else
MsgBox "The Username or Password entered is

incorrect.", vbOKOnly, "Logon Error!"

DoCmd.Close acForm, "frmLogon"
End If
Loop
End If
End With
If Username <> rstLogon.RecordCount = -1 Then
MsgBox "Sorry! The information you entered is incorrect." _
& Chr(13) & Chr(13) & "Please contact your

database administrator", vbOKOnly, "Logon Error!"

 

[William Hansen]

This solves my problem(s) Thanks Bill Hansen

-----Original Message-----
Access XP defaults to using ADO vice DAO. So you need a reference to the DAO
library. To do that
Open any module
Select Tools: References
Find and Check Microsoft DAO 3.6(???) Object Library
(Not positive on the version number, but I think it is correct)

Once you have that you need to either remove the reference to ADO (you are
probably not using that library) or you need to specify which library you are
using in specific cases.

Dim dbsMaster as DAO.Database
Dim rstLogon as DAO.Recordset

Also, I am suspicious of your SQL statement. I think it should read

"SELECT * FROM tblLogon WHERE Username =" & Chr(34) & me.Username & Chr(34) & "
AND Password =" & Chr(34) & Me.Password & Chr(34)

Of course you could do all this with simpler code, that wouldn't even require
opening a recordset.

Private Sub cmdVerify_Click()
If DCount("*","tblLogon", _
"Username =" & Chr(34) & me.Username & Chr(34) & _
" AND Password =" & Chr(34) & Me.Password & Chr (34)) = 0 THEN

'Your message/actions here for invalid logon
Else
'Successful logon actions here
End If
End Sub

Your current SQL statement is returning every record in tblLogon and that is why
you have to step through every record in the recordset until you get a match.

That would

and password a form will open. The code is below. this
worked well in AC97, but in Access XP, i am getting a
database type mismatch on the following line:FROM tblLogon WHERE Username = Username AND Password =
Password;")FROM tblLogon WHERE Username = Username AND Password =
Password;")