Create new record on secondary form

[agibsonsw]

Hello. Access 2007.
I have a staff form with a button that opens a second form to show their holiday details. But if I start a new holiday record it doesn't remember
their staff id number.
What's the best way to carry across the staff id to the holidays form please? Andy.

[JohnH]

Set the default value of the StaffID control on the second form (it can be hidden) to:
=Forms("frmstaffDetails")!StaffID

This assumes that you left the staff form open when you open the second form.

[HansV]

You can use the On Load event of the holidays form to set the default value of StaffID:

Code: Select all

Private Sub Form_Load()
  On Error Resume Next
  Me.StaffID.DefaultValue = Chr(34) & Forms!frmStaff!StaffID & Chr(34)
End Sub

where frmStaff is the name of the staff form and StaffID the id field.

[agibsonsw]

Hello. Thanks both. Is there an advantage to using the On Load event? Andy.

[HansV]

If you only open the holidays form from the staff form, John's method is easier because it doesn't use VBA. But if the user can open the holidays form while the staff form is not open, you'd get #Error in staff id in a new record.
You won't get the error with the On Load event because the line On Error Resume Next prevents it.

[JohnH]

If you always view Holidays in the context of having the Staff form open, the easiest solution of the lot would be to add Holidays as a subform to the Staff form, then Master and child linking will fill in StaffID automatically. You could add a Tab control, and put the Holidays on another page.