Solved

MS Access Log On form

Posted on 2004-08-27
8
256 Views
Last Modified: 2012-06-27
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
Comment
Question by:terjr
8 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 50 total points
ID: 11912104
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11912392
good morning Scott,
Actually it is {USER} not {USERS} is the reserved word for access and jet 4.

rey;-)
0
 

Author Comment

by:terjr
ID: 11912494
I changed to the code above and now I get a "type mismatch" error.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11912813
what is the field type for UserName and Password?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 75 total points
ID: 11912832
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
 

Author Comment

by:terjr
ID: 11912838
both are text fields
0
 

Author Comment

by:terjr
ID: 11913221
The Reference is grayed out
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11913273
Click the square button to reset the codes or stop. then tools>references
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now