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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

JonXL

Active Member
Joined
Feb 5, 2018
Messages
455
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
How are you figuring out which field "was originally clicked"?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
Is this really the same question or a more detailed description of this?

If so, let's try to keep this to one thread, as per rule 12 here: Message Board Rules
 

lbradbury

New Member
Joined
May 14, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Is this really the same question or a more detailed description of this?

If so, let's try to keep this to one thread, as per rule 12 here: Message Board Rules
Two different inquires.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
Two different inquires.
OK, that's fine. I am guessing that they are somewhat related, so we just want to make sure that one doesn't spill over into the other, where we end up discussing the same issue/question in two diffnerent threads.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,826
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

In general it would be the case that since the user entered their username in the username field, it would already be populated with a username. So seems like the problem is already solved. Other than that, you typically know what field you want to work with by name, for instance, me.txtUsername or me.txtOtherTextField -- these are two text fields on your form (one is called txtUsername and the other is called txtOtherTextField)
 

lbradbury

New Member
Joined
May 14, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
In general it would be the case that since the user entered their username in the username field, it would already be populated with a username. So seems like the problem is already solved. Other than that, you typically know what field you want to work with by name, for instance, me.txtUsername or me.txtOtherTextField -- these are two text fields on your form (one is called txtUsername and the other is called txtOtherTextField)
Perhaps, I'm not being clear enough. The text box txt_approval, when this field is clicked on it launches the frm_login, prompting a username and password. This looks back to the tbl_login for verification. This is where the actions stop. The txt_approval box doesn't change. I want this txt_approval box to populate with the username just logged on.
 

Attachments

  • MSAccessDatabase.JPG
    MSAccessDatabase.JPG
    154.7 KB · Views: 3

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,826
Office Version
  1. 2019
Platform
  1. Windows
With another form involved, one solution is when the other form "closes" to not actually close it but only hide it or make it invisible. Then you can get data from the other form with syntax like [forms]![otherForm]!txtSomeField.value (if I recall correctly anyway - I don't use MsAccess nearly as much as I once did. Once you have 'scraped' what you need from that other form, then issue a command to really close it.

You might also find that some dialog box can do what you want (they typically return values such as "true" or "false" to indicate success or failure as required).

I think there is a way to pass parameters to forms (and get them back). I forgot what they are called - OpenArgs I think.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,085
Office Version
  1. 365
Platform
  1. Windows
This looks back to the tbl_login for verification.
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,272
Messages
5,635,217
Members
416,847
Latest member
inaramos

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