How to fill a form field after upon click, prompt a login, and fill the form field with the username of the logged in

lbradbury

New Member
Joined
May 14, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a form that has a text box in a PruchaseReqHeader form, that upon click it opens another form. This form is frm_login, and prompts for a username and password. The goal is once the user puts in their username and password, the field that was originally clicked populates with the username The code underneath the frm_login, upon clicking the login button is below. This is where I'm stuck, I don't know how to get the text box to populate with the username.

VBA Code:
Private Sub cmd_login_Click()

  Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String
 
  If Trim(Me.txt_username.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Username should not be left blank.", buttons:=vbInformation, title:="Username Required"
    Me.txt_username.SetFocus
    Exit Sub
  End If
 
  If Trim(Me.txt_password.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Password should not be left blank.", buttons:=vbInformation, title:="Password Required"
    Me.txt_password.SetFocus
    Exit Sub
  End If
 
  'query to check if login details are correct
  strSQL = "SELECT FirstName FROM tbl_login WHERE Username = """ & Me.txt_username.Value & """ AND Password = """ & Me.txt_password.Value & """"
 
  Set db = CurrentDb
  Set rst = db.OpenRecordset(strSQL)
  If rst.EOF Then
    MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="Login Error"
    Me.txt_username.SetFocus
  Else
    MsgBox prompt:="Hello, " & rst.Fields(0).Value & ".", buttons:=vbOKOnly, title:="Login Successful"
    DoCmd.Close acForm, "frm_login", acSaveYes
  End If
 
 Set db = Nothing
 Set rst = Nothing

End Sub
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

lbradbury

New Member
Joined
May 14, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Don't know how that's being done, but if you can validate values in your code you should be able to pass them to the form that prompted the input in the first place. Since it's already open AND since the input form is already open, OpenArgs will not work. Something like this on the password form ought to work:
Forms!frm1.txt_approval = Me.txtUserName (or whatever it is that your user name control is). When the password form closes, I believe form1 will reflect the value because it will redraw when it gets the focus. If not, then you need to refresh the control or the form. I would try the control first, otherwise you might run into other issues caused by refreshing or requerying the form, such as messages about missing data or saving incomplete records.

This is listed in my first post, when the textbox is clicked it has an event procedure to open the frm_login that prompts for a username and password. the login button on this form has the following code to verify that the username and password inputted is correct. After this, the frm_login closes if the username and password is validated, at this point I want the username that was inputted to populate in the text field.

VBA Code:
VBA Code:
Private Sub cmd_login_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

If Trim(Me.txt_username.Value & vbNullString) = vbNullString Then
MsgBox prompt:="Username should not be left blank.", buttons:=vbInformation, title:="Username Required"
Me.txt_username.SetFocus
Exit Sub
End If

If Trim(Me.txt_password.Value & vbNullString) = vbNullString Then
MsgBox prompt:="Password should not be left blank.", buttons:=vbInformation, title:="Password Required"
Me.txt_password.SetFocus
Exit Sub
End If

'query to check if login details are correct
strSQL = "SELECT FirstName FROM tbl_login WHERE Username = """ & Me.txt_username.Value & """ AND Password = """ & Me.txt_password.Value & """"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
If rst.EOF Then
MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="Login Error"
Me.txt_username.SetFocus
Else
MsgBox prompt:="Hello, " & rst.Fields(0).Value & ".", buttons:=vbOKOnly, title:="Login Successful"
DoCmd.Close acForm, "frm_login", acSaveYes
End If

Set db = Nothing
Set rst = Nothing

End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
Please note that Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.
Be sure to follow & read the link at the end of the rule too!

If you have posted the question at more places, please provide links to those as well.
If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,085
Office Version
  1. 365
Platform
  1. Windows
I gave you my answer in my last post - incorporate it into your code where the user credentials are recognized as being valid. Then report back if it doesn't work.
The recordset thing is overkill IMO when you could just use DLookup.
 

lbradbury

New Member
Joined
May 14, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I gave you my answer in my last post - incorporate it into your code where the user credentials are recognized as being valid. Then report back if it doesn't work.
The recordset thing is overkill IMO when you could just use DLookup.
This is a bit beyond my skills. do you have any suggestions on resources to complete this? I don't know how to to incorporate this into my code.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,085
Office Version
  1. 365
Platform
  1. Windows
Change the forms! reference (see <<<<<<) to the names of your open form and the control you want to populate. That is really the only line you need. You can stick with your recordset - or not.
Note - when someone is called out for cross posting and says nothing about it in either forum you get on my ignore list. The only reason I'm going to make this reply is because I had the code on an open NotePad and your solution is simple.
VBA Code:
Private Sub cmd_login_Click()
Dim strUser As String

strUser = Me.txt_username
If Trim(strUser & vbNullString) = vbNullString Then
MsgBox prompt:="Username should not be left blank.", buttons:=vbInformation, title:="Username Required"
Me.txt_username.SetFocus
Exit Sub
End If

If Trim(Me.txt_password & vbNullString) = vbNullString Then
MsgBox prompt:="Password should not be left blank.", buttons:=vbInformation, title:="Password Required"
Me.txt_password.SetFocus
Exit Sub
End If

If IsNull(Dlookup("UserName","tbl_login","UserName = '" & strUser & "'")) <> "" Then
MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="Login Error"
Me.txt_username.SetFocus
Else
MsgBox prompt:="Hello, " & strUser & ".", buttons:=vbOKOnly, title:="Login Successful"
Forms!frm1.txt_approval = strUser '<<<<<<<<<<<
DoCmd.Close acForm, "frm_login", acSaveYes
End If

End Sub
 
Solution

lbradbury

New Member
Joined
May 14, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Change the forms! reference (see <<<<<<) to the names of your open form and the control you want to populate. That is really the only line you need. You can stick with your recordset - or not.
Note - when someone is called out for cross posting and says nothing about it in either forum you get on my ignore list. The only reason I'm going to make this reply is because I had the code on an open NotePad and your solution is simple.
VBA Code:
Private Sub cmd_login_Click()
Dim strUser As String

strUser = Me.txt_username
If Trim(strUser & vbNullString) = vbNullString Then
MsgBox prompt:="Username should not be left blank.", buttons:=vbInformation, title:="Username Required"
Me.txt_username.SetFocus
Exit Sub
End If

If Trim(Me.txt_password & vbNullString) = vbNullString Then
MsgBox prompt:="Password should not be left blank.", buttons:=vbInformation, title:="Password Required"
Me.txt_password.SetFocus
Exit Sub
End If

If IsNull(Dlookup("UserName","tbl_login","UserName = '" & strUser & "'")) <> "" Then
MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="Login Error"
Me.txt_username.SetFocus
Else
MsgBox prompt:="Hello, " & strUser & ".", buttons:=vbOKOnly, title:="Login Successful"
Forms!frm1.txt_approval = strUser '<<<<<<<<<<<
DoCmd.Close acForm, "frm_login", acSaveYes
End If

End Sub
1. No need to be snarky.
2. I just saw the response about cross posting, and will be addressing this accordingly. As you can see I am new member so the rules are NOT drilled into my head like they maybe yours, so some empathy would go a long way.
3. Thank you for the code, the reason this is cross posted is this has been struggle for sometime and I'm just trying to learn.
 

lbradbury

New Member
Joined
May 14, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Please note that Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.
Be sure to follow & read the link at the end of the rule too!

If you have posted the question at more places, please provide links to those as well.
If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Thank you, you are correct. I have crossed posted this to the following. I'm hopeful I can learn multiple ways that people approach this scenario to best find learn my way through this.

https://www.accessforums.net/showthread.php?t=82727&p=469573#post469573
 

lbradbury

New Member
Joined
May 14, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
For closure, this is the code I ended up with for anyone this may help.

VBA Code:
Private Sub cmd_login_Click()

  Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String
  Dim strUser As String
  
 strUser = Me.txt_username

  If Trim(Me.txt_username.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Username should not be left blank.", buttons:=vbInformation, title:="Username Required"
    Me.txt_username.SetFocus
    Exit Sub
  End If
 
  If Trim(Me.txt_password.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Password should not be left blank.", buttons:=vbInformation, title:="Password Required"
    Me.txt_password.SetFocus
    Exit Sub
  End If
 
  'query to check if login details are correct
  strSQL = "SELECT FirstName FROM tbl_login WHERE Username = """ & Me.txt_username.Value & """ AND Password = """ & Me.txt_password.Value & """"
 
  Set db = CurrentDb
  Set rst = db.OpenRecordset(strSQL)
  If rst.EOF Then
    MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="Login Error"
    Me.txt_username.SetFocus
  Else
    MsgBox prompt:="Hello, " & rst.Fields(0).Value & ".", buttons:=vbOKOnly, title:="Login Successful"
Forms!PurchaseReqHeader.txt_approval = strUser
DoCmd.Close acForm, "frm_login", acSaveYes
  End If
 
 Set db = Nothing
 Set rst = Nothing

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,332
Messages
5,635,670
Members
416,871
Latest member
jbcpub

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