How have field on a form auto-populate with a data field from another form

SteveL13 used Ask the Experts™
I have a form that can be opened via a command button from four other forms.  When the 2nd form opens I want a date field to automatically populate from a date field that is in the 1st form.

But since this date field on the 2nd form can be opened from any one of 4 forms, I am not sure how to populate the date field,  Somehow the 2nd form has to know which of the 4 forms is open and use the date field from that form.


Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

There are several solutions.  Here's two.

1. Before opening the pop up form, store the date in a memory variable.  That way the pop up form gets the date from a memory variable and doesn't care what form put it there.
2. Use the open args argument of the OpenForm Method to pass in the date.
Top Expert 2009
From the command button on each form, open the 2nd form, get the Date value from the calling form, using the syntax Me![txtDateField].Value, and write it to the appropriate Date field control on the other form, using the syntax Forms![frmSecondform]![txtDateField].Value.  You can do some error trapping if you wish.  Here is some sample code:
Private Sub cmdOpentargetform_Click()

   Dim dteStart As Date
   Dim frm As Access.Form
   If IsDate(Me![txtStartDate].Value) = True Then
      dteStart = Me![txtStartDate].Value
      DoCmd.OpenForm "frmTarget"
      Set frm = Forms![frmTarget]
      frm![txtStartDate] = dteStart
   End If
End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today