• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

MS Access Log On form

I am using a table named "Users" to store authorized users of the database.  It has two fields named "UserName" and "UserPassword".  I have constructed a simple LogOn form with two text fields named "MyUserName" and "MyPassword".  The LogOn form also has an "OK" button and a "Cancel" button.  I am using the following  code in conjunction with the OnClick function for the "OK" button and I am getting a syntax error in the FROM clause.  Please assist.

Dim rs As Recordset
Set rs = CurrentDb().OpenRecordset("SELECT Users.* FROM Users" & "WHERE(((Users.UserName)=""&Me.MyUserName&"") AND ((Users.UserPassword)=""&Me.MyPassword&"")));")
If rs.Recordset = 0 Then
Beep
MsgBox "Invalid User Name or Password"
RS.Close
Exit Sub
Else
 Open Main switchboard
End If
rs.Close
0
terjr
Asked:
terjr
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try this instead:

Set rs = CurrentDb.OpenRecordset ("SELECT Users.* FROM Users WHERE (Users.UserName='" & Me.MyUserName &"' AND Users.UserPassword='" & Me.MyPassword & "')")

Note also that "Users" is a reserved word in Access ... consider adopting a nameing convention, as the use of reserved words can lead to frustration and unexpected behaviour. Google on "nameing convention" for further reading.

0
 
Rey Obrero (Capricorn1)Commented:
good morning Scott,
Actually it is {USER} not {USERS} is the reserved word for access and jet 4.

rey;-)
0
 
terjrAuthor Commented:
I changed to the code above and now I get a "type mismatch" error.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Rey Obrero (Capricorn1)Commented:
what is the field type for UserName and Password?
0
 
shanesuebsahakarnCommented:
USERS and USER are both reserved words. User is an object of the Users collection.

I suspect the type mismatch comes this:
Dim rs As Recordset

Add a reference to DAO 3.5 (Tools->References, check Microsoft DAO 3.5) and change the line to:
Dim rs As DAO.Recordset
0
 
terjrAuthor Commented:
both are text fields
0
 
terjrAuthor Commented:
The Reference is grayed out
0
 
Rey Obrero (Capricorn1)Commented:
Click the square button to reset the codes or stop. then tools>references
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now