Auto pop textbox on 2nd form with same value as 1st form

[Arvin]

Hello,

How can I autofill a text field a 2nd form with the same value that was entered on the 1st form?

Thank you in advance

Arvin

 

[Kryst51]

Try Something Like:

Dim stDocName As String
 
stDocName = "YourSecondFormNameHere"
 
DoCmd.OpenForm stDocName, acNormal, "", "", acAdd, acNormal
Forms![YourSecondFormNameHere]![YourForeignKeyFieldHere] = Me.[YourPrimaryKeyFromFirstFormHere]

Edit: Put this in the click button event, or whatever event triggers the opening of the form.

 

[Arvin]

Wow ...thank you the quick response .... However

I already have a On Click event for the button on the login form which is ...

Private Sub logincmd_Click()
'Check to see if data is entered into the UserName combo box
    If IsNull(Me.Username) Or Me.Username = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.Username.SetFocus
        Exit Sub
    End If
    'Check to see if data is entered into the password box
    If IsNull(Me.Password) Or Me.Password = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.Password.SetFocus
        Exit Sub
    End If
    'Check value of password in tblEmployees to see if this
    'matches value chosen in combo box
    If Me.Password.Value = DLookup("EmpPass", "Employeetbl", _
            "[EmplyID]=" & Me.Username.Value) Then
        EmplyID = Me.Username.Value
        'Close logon form and open splash screen
        DoCmd.Close acForm, "Login Form", acSaveNo
        DoCmd.OpenForm "Weekly Schedule Report"
    Else
    MsgBox "Invalid Password. Please Try Again", vbOKOnly, _
            "Invalid Entry!"
        Me.Password.SetFocus
    End If
    'If User Enters incorrect password 3 times database will shutdown
    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
      MsgBox "You do not have access to this database.Please contact admin.", _
               vbCritical, "Restricted Access!"
        Application.Quit
    End If
End Sub

Where would I put the code you have suggested?

Thank you in advance.

 

[Kryst51]

Wow ...thank you the quick response .... However

I already have a On Click event for the button on the login form which is ...

Private Sub logincmd_Click()
'Check to see if data is entered into the UserName combo box
    If IsNull(Me.Username) Or Me.Username = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.Username.SetFocus
        Exit Sub
    End If
    'Check to see if data is entered into the password box
    If IsNull(Me.Password) Or Me.Password = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.Password.SetFocus
        Exit Sub
    End If
    'Check value of password in tblEmployees to see if this
    'matches value chosen in combo box
    If Me.Password.Value = DLookup("EmpPass", "Employeetbl", _
            "[EmplyID]=" & Me.Username.Value) Then
        EmplyID = Me.Username.Value
        'Close logon form and open splash screen
        DoCmd.Close acForm, "Login Form", acSaveNo
        DoCmd.OpenForm "Weekly Schedule Report"
    Else
    MsgBox "Invalid Password. Please Try Again", vbOKOnly, _
            "Invalid Entry!"
        Me.Password.SetFocus
    End If
    'If User Enters incorrect password 3 times database will shutdown
    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
      MsgBox "You do not have access to this database.Please contact admin.", _
               vbCritical, "Restricted Access!"
        Application.Quit
    End If
End Sub

Where would I put the code you have suggested?

Thank you in advance.

Somewhere in that click event. Test it out. I just used this code in an example I posted and had to try it in a couple of different orders before I got it to work properly.

Edit: Try it after all of your other tests.

 

[Arvin]

Me.[YourPrimaryKeyFromFirstFormHere]

....Does it matter if the fields on the 1st form are "Unbound"

 

[Kryst51]

I don't know, maybe someone else will chime in, but try it out and see, that might be a faster way to find out.

 

[vbaInet]

I don't know, maybe someone else will chime in, but try it out and see, that might be a faster way to find out.

Kryst51: Do you think it will be better on the On Change event so acts as though it's synchronised? What do you think?

By the way Arvin, are both forms open at the same time?

 

[Kryst51]

Kryst51: Do you think it will be better on the On Change event so acts as though it's synchronised? What do you think?

By the way Arvin, are both forms open at the same time?

If she wants the other form to pop up everytime she changes the record the main form is talking about this might work.

 

[Arvin]

Hello,

No, both forms are not open at the same time.... Bascially I have 2 forms,

form1 - Login
form2 - Weekly Schedules this also has a subform

Form1 has a 1 cbo and 1 test. The cbo is populated via a query.... and the txt field is for the passsword.

both fields are "Unbound"

Once the user logs in the "login" form will close and Form2 will open.....

 

[Arvin]

Furthermore.....

On Form 2 - there is one field that I would like autofill with the same value that is selected from the cbo on form 1 ......

The subform on Form 2 is in a Datasheet view and is designed based on a query. The query will be based on the value that is autofilled....

I hope I'm making sense : )

 

[Kryst51]

You would use the same logic as in the first code I posted so it would probably look like this: (untested may not work)

Dim stDocName As String
 
stDocName = "YourSecondFormNameHere"
 
DoCmd.OpenForm stDocName, acNormal, "", "", acAdd, acNormal
Forms![YourSecondFormNameHere]![YourForeignKeyFieldHere] = Me.[YourPrimaryKeyFromFirstFormHere]
Forms![YourSecondFormNameHere]![YourComboBoxNameHere] = Me.[YourOtherFieldNameHere]

Although that sounds a little unnormalized if you are storing the information in two tables.... If you just want it for viewing purposes, then drawing that value into a textbox seems OK.

 

[Arvin]

G'day ....

Okay ....so I have tried exactly as you have stated and I keep on getting a Run time error " The expression you entered refers to an object that is closed or doesn't exist"

I have uploaded a smaple of my db ...... if someone could look at please I would appreciate it.

thank you in advance

Arvin

 

[Kryst51]

OK, you need to close the first form AFTER the code to fill in your second form has completed, so switch some code around.

 

[Kryst51]

OK, you had somethings wrong, so see this attached.

Also, some suggestions.

Lose the lookups at the table level, they are bad, see my signature for details.

Also, I would work on a naming convention in order to help you later while coding etc.

Also, in your employee name field, I would make that two fields, one for FirstName, one for LastName. Then in a query you can join them into one.

Let me know if you have any further questions. And if this is what you wanted.

 

[Arvin]

Hi Kryst51....

1st.... Cheers to the power of GraySkull!

2nd ...thank you very much ....

3rd ...

Also, some suggestions.

Lose the lookups at the table level, they are bad, see my signature for details.

Also, I would work on a naming convention in order to help you later while coding etc.

Also, in your employee name field, I would make that two fields, one for FirstName, one for LastName. Then in a query you can join them into one.

I done as you have suggested ... and i didn't lose anything ... which is a good thing ..however i do have a few questions...

1. In my second form the txt is autofilled with the record ID and not the actual name?

2. I can still scroll through the txt box for other names, when I should see only one.

Thats all i can think at this time .......

Thank you for everything!

Arvin

 

[Kryst51]

So when a person logs in you only want to see information for that employee on the screen that pops up?

btw, that is a combo box, just an fyi so as not to confuse (if that's what you are talking about. )

 

[Arvin]

So when a person logs in you only want to see information for that employee on the screen that pops up?

Yes

btw, that is a combo box, just an fyi so as not to confuse (if that's what you are talking about.)

Are you referring to my rant about the form displays the record ID ?

 

[Kryst51]

Yes



Are you referring to my rant about the form displays the record ID ?

Ah, OK, Please post to me your db without the table level lookups, that way I am working with the most current copy.

 

[Arvin]

Here you go .....

and thank you very much

 

[Kryst51]

OK, A few questions, you have some normalization issues going on that we need to correct.

Your employee table shows OrgID and DeptID, but your OrgTable and your DeptTable are related to eachother. Which is the correct relationship?

Options (What I think based off no knowledge of your data)

Org - Dept - Emp

or

Dept - Org - Emp

or

Org - Emp - Dept

You actually have this going on in every table.