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
Carlee wrote:
>
> 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!"
> DoCmd.Close acForm, "frmLogon"
> End If
> rstLogon.Close
>
> 'click_err:
> ' Exit Sub
> End Sub
|
Similar Threads
