SQL and VB

[shadow]

Hi. I have a database with a table called Agents. In this table are the agents Username, Password, Name, Address etc

I want a form so they can login using this data. So for example in my table i would have:

Adamsmith, Pass, Adam Smith, 22 Street.

On my form i would have

Username: (text box called username.text)
Password: (text box called password.text)

And a submit button.

I know i would have to use SQL to retrieve the info from the tables, and then VB to compare it to the text boxs.

Iv tried haveing the button preform a VB script of this:

"SELECT Password FROM Agent
WHERE Username=username.text

if password.text=password then openform.mainmenu; closeform.login
msgbox"Wrong password"

but it doesnt work.. I have no idea where to go from here to any input would be appreciated

 

[KenHigg]

There's a sample in post 19 here:

http://www.access-programmers.co.uk/forums/showthread.php?t=99777

See if it helps.

 

[shadow]

Ok iv had a look and i really dont understand it.

Would you be able to run through the code with me? What each bit does and how?

On Error GoTo Err_cmdLogin_Click

'Check username text box
If IsNull(Me!txtUserName) Or IsNull(Me!txtPassword) Then
MsgBox "Please enter a valid user name and / or password.", vbCritical, "Login Error..."
GoTo Exit_cmdLogin_Click
End If

'Check password textbox
If IsNull(DLookup("[recno]", "tblUsers", "user_name='" & Me!txtUserName & "'")) Then
MsgBox "Please enter a valid user name.", vbCritical, "Login Error..."
GoTo Exit_cmdLogin_Click
End If

'Check for valid passsword
If (DLookup("password", "tblUsers", "user_name='" & Me!txtUserName & "'")) <> Me.txtPassword Then
MsgBox "Invalid password.", vbCritical, "Login Error..."
GoTo Exit_cmdLogin_Click
End If

'If user is admin make admin buttons on main form enabled
If (DLookup("user_type", "tblUsers", "user_name='" & Me!txtUserName & "'")) = "Admin" Then
Forms!frmStartUp!cmdStatTrackAdmin.Enabled = True
Forms!frmStartUp!cmdAppAdmin.Enabled = True
End If

'Set global vars
g_user_name = Me!txtUserName
g_user_type = DLookup("user_type", "tblUsers", "user_name='" & Me!txtUserName & "'")

'Close this form
DoCmd.Close

'Open Main Form
DoCmd.OpenForm "frmMain"

Exit_cmdLogin_Click:
Exit Sub

 

[Rabbie]

Hi. I have a database with a table called Agents. In this table are the agents Username, Password, Name, Address etc

I want a form so they can login using this data. So for example in my table i would have:

Adamsmith, Pass, Adam Smith, 22 Street.

On my form i would have

Username: (text box called username.text)
Password: (text box called password.text)

if this is their real names you will have problems. You shouldn't have names with a . in them especially if you then have a valid property name after the fullstop. Access and VBA will be confused.

And a submit button.

I know i would have to use SQL to retrieve the info from the tables, and then VB to compare it to the text boxs.

Iv tried haveing the button preform a VB script of this:

"SELECT Password FROM Agent
WHERE Username=username.text

if password.text=password then openform.mainmenu; closeform.login
msgbox"Wrong password"

but it doesnt work.. I have no idea where to go from here to any input would be appreciated

If your text boxes actually are called username and password

Then your SQL statement should be

"SELECT Password FROM Agent WHERE Username=Me!username"

You would need to open this SQL as a recordset and then do the compare in your VBA code

Remember to refer to the password box on your form as me!password

 

[KenHigg]

It basically does a series of checks before it does anything. The first thing is to make sure the user has entered a user name and password. If not if gives them a warning and exits the routine. Provided these conditions are met. It proceeds to the second test.

The second thing it does is it uses a dlookup() function to try and match the username they entered to the password. Again, if it fails it gives them a warning and exits the routine.

After these two tests you can do whatever you want like open the start up form, etc.

These two test are really the only things you should need to do in your case as the remaining code deals with user roles and global variables.

 

[shadow]

I have changed the text box names to: usertext and passtext

And then iv tried this:

Dim sqlpassword As String

sqlpassword = "SELECT Password FROM Agent WHERE Username=Me!usertext"

If passtext.Text = sqlpassword Then MsgBox "correct"
If passtext.Text <> sqlpassword Then MsgBox "wrong"


But i get the error message "you cant referance a property or method for a control unless the control has the focus"

and in the debug this is what highlighed:
"If passtext.Text = sqlpassword Then"

 

[shadow]

ok so iv changed my text box names to: usertext and passtext

and then i tried this:

Dim sqlpassword As String

sqlpassword = "SELECT Password FROM Agent WHERE Username=Me!usertext"

If passtext.Text = sqlpassword Then MsgBox "correct"
If passtext.Text <> sqlpassword Then MsgBox "wrong"


But it gives me the message:

"You cant referance a proporty or method for a control unless the control has the focus"

and in the debugger this is whats highlighted:

"If passtext.Text = sqlpassword Then"

 

[Rabbie]

I have changed the text box names to: usertext and passtext

And then iv tried this:

Dim sqlpassword As String

sqlpassword = "SELECT Password FROM Agent WHERE Username=Me!usertext"

If passtext.Text = sqlpassword Then MsgBox "correct"
If passtext.Text <> sqlpassword Then MsgBox "wrong"


But i get the error message "you cant referance a property or method for a control unless the control has the focus"

and in the debug this is what highlighed:
"If passtext.Text = sqlpassword Then"

As I said before dont use the text property. It isn't what you want.

Rewrite as follows

If passtext = sqlpassword Then MsgBox "correct"
If passtext <> sqlpassword Then MsgBox "wrong"

You actually want the value property but as that is the default you dont need to specify it.

Also it may help to expand passtext to Me!passtext if you still have issues.

 

[shadow]

thanks rabbie i really appreciate the help.

It not longer gives me the error message, but it always gives me "wrong" even if the username and password is correct.

If i tell you my table layout maybe you could see where i have gone wrong..

The table is called Agent. It has 2 fields one called Username and the other Password?

Am i missing a line of code maybe?

 

[Rabbie]

Here is some code that will do the the trick. It goes in the On_click event of your Submit button

Private Sub Command2_Click()
Dim sqlpassword As String, db As DAO.Database, rs As DAO.Recordset
 Set db = CurrentDb
 sqlpassword = "SELECT Password FROM Agent WHERE agent.Username ='" & Me!usertext & "'"
 Set rs = db.OpenRecordset(sqlpassword)
If Me!passtext = rs!Password Then
   MsgBox "correct"
Else
   MsgBox "wrong"
End If
    
End Sub

 

[shadow]

excellent thats amazing

My degree is in international business management and when i saw a module called "database systems" i though hmm how hard can it be.. haha how wrong was I!!

One problem. It returns the "correct" if its correct, but when its wrong is doesnt return the "wrong" it says

"run-time error 3021:
No current Record"

any ideas?

 

[Rabbie]

That error is because it couldn't find the username in the table. If you put in a correct user but wrong password it displays "Wrong"

This version should work Ok

Private Sub Command2_Click()
Dim sqlpassword As String, db As DAO.Database, rs As DAO.Recordset
 Set db = CurrentDb
 sqlpassword = "SELECT Password FROM Agent WHERE agent.Username ='" & Me!usertext & "'"
 Set rs = db.OpenRecordset(sqlpassword)
If rs.EOF Then
  MsgBox ("User not found")
  Exit Sub
End If
If Me!passtext = rs!Password Then
   MsgBox "correct"
Else
   MsgBox "wrong"
End If
    
End Sub

 

[KenHigg]

That all seems like too much sugar for a nickel to me - ? What's wrong with the dlookup() method(s)....?

 

[shadow]

lol whats the dlookup method then?

 

[KenHigg]

The first two 'checks' in post #3

edit: My bad, the first 3 checks.

 

[Rabbie]

Dlookup is fine but it can take a lot longer to run. If Agents table is small then it will not matter.

Solution with Dlookup since Ken didnt want to show off

Private Sub Command2_Click()
If Me!passtext = DLookup("Password", "Agent", "agent.Username ='" & Me!usertext & "'") Then
   MsgBox "correct"
Else
   MsgBox "wrong"
End If
    
End Sub

 

[KenHigg]

That will not tell the user if it was a bad user name or bad password word.

And, just my method , I like to have the code go through a systematic series of tests and when the code makes it through all the tests then not be in the middle of an If routine when the meat of the code executes.

Bad:

If (test something)
If the test is passes do what the button indicated
else
if the test fails warn the user
end if

Good:

If (test something)
if the test fails warn the user and exit the routine
end if

do what the button indicated

 

[Rabbie]

That will not tell the user if it was a bad user name or bad password word.

And, just my method , I like to have the code go through a systematic series of tests and when the code makes it through all the tests then not be in the middle of an If routine when the meat of the code executes.

Bad:


Good:

Quite correct Ken. Very observant of you. It's actually a security function to make it more difficult for unauthorised users to guess passwords/usernames if they don't know which is wrong.

Your checks are begining to look a bit over sugarred too.

We can of course put a combo box on the form so the user can only select a valid user name. The options are many - it just depends on what shadow actually wants.

Since he has said he is doing it as part of a course the main thing will be to have a valid solution.

 

[KenHigg]

Your checks are begining to look a bit over sugarred too.

It goes a beyond this context of this use. When you have a routine where conditions need to be met before you execute the primary pc of code, things are much easier if you can isolate the test part of your code from the rest of it. Better to break it up into logical chunks instead of having spaghetti code.

 

[Rabbie]

It goes a beyond this context of this use. When you have a routine where conditions need to be met before you execute the primary pc of code, things are much easier if you can isolate the test part of your code from the rest of it. Better to break it up into logical chunks instead of having spaghetti code.

I wasn't aware I was writing spaghetti code. Not a single Go to in sight. I was providing a simple subroutine that did a single job. Not my definition of spaghetti code. Perhaps you have a different definition. i agree I could have written it as a function type Boolean to say whether the log-in was valid but that would be easy to produce from what is there. In my example the code did similar but different things so there was no problem. easy for some-one to adapt if required.

I actually disagree with your objection to embedding the code in the if condition. Good practice in Structured Programming and the complete opposite to Spaghetti coding which you quite rightly oppose.

By embedding it the condition when the code is obeyed is made quite clear so whats your objection to that.