How to create login form using DAO.recordset on a unbound form

[Guest]

Thanks for giving me this opportunity to seek for a help.

I have a table in my access project called users that contains UserID,
username, and TypeID. I create an unbound form with three test field
(txtUsername, txtPassward and txtTypeid) and two control OK and Cancel

I have two different menu callde AdminMenu and EmpMenu. What I want to
accomplish is when a user enter he or her username, password and typid the
system will read a query called users and determines which form to open.

Please, take a look at the code displayed below. The code only open the
adminmenu. please help me.
----------------------------------------------------------------
Dim MyDb As DAO.Database
Dim rs As DAO.Recordset
Dim Title As String
Title = "Makai-IT"
Dim txtUsername As String
Dim txtPassword As String
Dim txtTypeid As integer

If Me.txtPassword = "" And Me.txtUsername = "" Then
MsgBox "That can not comput"
Exit Sub
Call Clear_Click
End If

Set MyDb = CurrentDb
Set rs = MyDb.OpenRecordset("Select * from Users", dbOpenSnapshot)

If Not rs.EOF Then
Me.txtTypeid.SetFocus
Me.txtTypeid = rs("Typeid").Value
Me.txtUsername = rs("Username").Value
Me.txtPassword = rs("Password").Value

rs.FindFirst ("Typeid")

If Me.txtTypeid.Value <= 1 Then
DoCmd.OpenForm "AdminMenu"
Call Clear_Click
Exit Sub
End If

If Me.txtTypeid.Value <= 2 Then
DoCmd.OpenForm "EmpMenu"
Call Clear_Click

Else
MsgBox "No permision"

End If
End If

 

[TC]

Ok, four things.

(1) What event have you attached that code to?

(2) The following lines do not make sense. The recordset query does not
contain an ORDER BY clause, so it will return the records from the
table in unpredictable or random order. So the following lines display,
in the fields on your form, the values from an unpredictable or random
record:

Me.txtTypeid = rs("Typeid").Value
Me.txtUsername = rs("Username").Value
Me.txtPassword = rs("Password").Value

(3) This line also does not make sense: rs.FindFirst ("Typeid")
Check the syntax for the FindFirst method. Presumeably, you want to
find the record with username matching the value that the user entered.
That would need something like this:

rs.findfirst "[username]=""" & me![txtUsername] & """"

(4) You should indent your if/else statements., Otherwise, the code is
way too hard to read - especially as you add more lines!

good:
if blah then
do this
else
do that
endif

BAD:
if blah then
do this
else
do that
endif

HTH,
TC

 

[John Spencer]

Here is a rewrite of your code. You still should have error code

Dim MyDb As DAO.Database
Dim rs As DAO.Recordset
Dim Title As String
Title = "Makai-IT"
'Dim txtUsername As String 'Not used?
'Dim txtPassword As String 'Not used?
'Dim txtTypeid As integer 'Not used?

If IsNull(me.txtPassword) or IsNull(me.TxtUserName) then

MsgBox "That can not comput"
Call Clear_Click 'Moved before Exit so it would run
Exit Sub
End If

Set MyDb = CurrentDb
'Add a where clause to the query
Set rs = MyDb.OpenRecordset _
("Select * from Users" & _
" WHERE UserName=" & Chr(34) & me.TxtUserName & Chr(34) & _
" AND Password= & CHr(34) & me.txtPassword & Chr(34), dbOpenSnapshot)

If Not rs.EOF Then
' Me.txtTypeid.SetFocus 'unneeded since you are setting the value
Me.txtTypeid = rs("Typeid").Value
' Me.txtUsername = rs("Username").Value 'you already have values here
' Me.txtPassword = rs("Password").Value 'you already have values here


If Me.txtTypeid.Value <= 1 Then
DoCmd.OpenForm "AdminMenu"
Call Clear_Click
Exit Sub
End If

If Me.txtTypeid.Value <= 2 Then
DoCmd.OpenForm "EmpMenu"
Call Clear_Click

Else
MsgBox "No permission"

End If
End If

 

[TC]

Change:

if not rs.eof then

to:

if rs.eof then
msgbox "Invalid username or password"
else

Cheers,
TC