Change the way I login

[johnsonk]

Hi, Can anyone help I have this login form where I enter a username and password to login which works fine but I would like to change the way I login, is there a way where I can just enter my password and it searches the access database and finds and shows the username. Below is what I have at the moment.

Private Sub txtPass_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = 13 Then

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim qry As String

qry = "SELECT * FROM StoresNames WHERE Name = '" & Me.txtUser.Value & "'"

cnn.Open "provider=Microsoft.ace.oledb.12.0;Data Source=" & ThisWorkbook.Path & "\DataBase.mdb"

rst.Open qry, cnn, adOpenKeyset, adLockPessimistic

If rst.RecordCount = 0 Then
 MsgBox "Incorrect User ID"
 
 txtUser = ""
 txtUser.SetFocus
 
 rst.Close
 cnn.Close
 Exit Sub
 
ElseIf rst.Fields("Tag").Value = Me.txtPass.Value Then
 
 rst.Close
 cnn.Close
 Unload Me
 
Else

MsgBox "Incorrect Password"

 txtPass = ""
 txtPass.SetFocus

rst.Close
cnn.Close

End If
End If
End Sub

 

[Joe4]

I would strongly caution against doing this. It kind of "weakens" security, and could be problematic, due to the fact that:
1. Username are guaranteed to be unique, but passwords typically are not. What happens if two people have the same password?
2. If you have enough users, inevitably someone chooses a really weak password, (like "password", or something easily guessable). Someone could conceivably enter a bunch of passwords, looking for a match with any username, in order to log in.

If you are going to go through the hassling of using security, you don't want to do anything to weaken or compromise it.

 

[johnsonk]

Hi Joe4 thanks for the reply, people having the same password will not be an issue as they scan a barcode tag assigned to them I just wanted it so there is no user interaction with a keyboard.

 

[Joe4]

I haven't really done much with "ADODB" recordsets before, but I think the main thing is you would want to change the qry to search on the password, something like:

qry = "SELECT Name FROM StoresNames WHERE Tag = '" & Me.txtPass.Value & "'"

(assuming the password field in your table is "Tag" and "txtPass" is the password field on your form.

That should return the Name you are looking for. Then it is just a matter of plugging it into the "txtUser" field on the form.

 

[Joe4]

By the way, is this User Form and VBA code in Excel or Access?
I see references to Access, but you posted this question in the "Excel Questions" forum.
If it is using an Excel form, then why is Excel being used an at all, instead of doing it all in Access?
Just trying to get a full understanding of what is being used (and why).

 

[johnsonk]

Hi Joe4, Yes it is a an excel form but all the data is in access, I have a booking in/out system which is all done in excel VBA.

I have changed the qry line and I get a run time error and when I debug it fails on ElseIf rst.Fields("Tag").Value = Me.txtPass.Value Then

Private Sub txtPass_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = 13 Then

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim qry As String

'qry = "SELECT * FROM StoresNames WHERE Name = '" & Me.txtUser.Value & "'"
qry = "SELECT Name FROM StoresNames WHERE Tag = '" & Me.txtPass.Value & "'"

cnn.Open "provider=Microsoft.ace.oledb.12.0;Data Source=" & ThisWorkbook.Path & "\DataBase.mdb"

rst.Open qry, cnn, adOpenKeyset, adLockPessimistic

If rst.RecordCount = 0 Then
 MsgBox "Incorrect User ID"
 
 txtUser = ""
 txtUser.SetFocus
 
 rst.Close
 cnn.Close
 Exit Sub
 
[COLOR=rgb(184, 49, 47)]ElseIf rst.Fields("Tag").Value = Me.txtPass.Value Then[/COLOR]
 
 rst.Close
 cnn.Close
 Unload Me
 
Else

MsgBox "Incorrect Password"

 txtPass = ""
 txtPass.SetFocus

rst.Close
cnn.Close

End If
End If
End Sub

Regards

 

[Joe4]

I have changed the qry line and I get a run time error and when I debug it fails on ElseIf rst.Fields("Tag").Value = Me.txtPass.Value Then

I don't think that line is necessary anymore. Since you now already have the Password ("Tag") value, you should no longer have need to check it.
I think you know just want to run the new query, get the record you need, get the UserName field value from that record, and set it on your Form.