Change the way I login

johnsonk

Board Regular
Joined
Feb 4, 2019
Messages
169
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.

1609858701450.png


VBA Code:
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Feb 4, 2019
Messages
169
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
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:
Rich (BB code):
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Feb 4, 2019
Messages
169
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

VBA Code:
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
1609948954783.png


Regards
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top