OPen form Add mode & pass data across

Posted on 2013-01-16
Last Modified: 2013-01-17
Dear Experts,

I want to be able to open up a pop up form to add data, and also populate 1 field from the original form:

The forms & fields are as follows:

The form from which the pop up is launched is "Employee" & has a field [EmployeeID] which I want to to take across to the pop up form.

The pop up form is "AddHRLog" & the field to be populated is [EmployeeID]

So basically when the pop up opens it fills in EmployeeID with the details from Employee.EmployeeID

I have got this far ...

DoCmd.OpenForm "AddHRLog", , , , acFormAdd

Open in new window

Can anybody help?
Question by:correlate
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 61

Expert Comment

ID: 38783147
DoCmd.OpenForm "AddHRLog", , , , acFormAdd, OpenArgs := Me.EmployeeID

Open in new window

And in the open event of your popup form:

Me.EmployeeID = OpenArgs

Open in new window

LVL 61

Expert Comment

ID: 38783151
If you are using Access 2007 or higher you can also use TempVars for this:
LVL 61

Expert Comment

ID: 38783178
Here's another article about TempVars:

I don't have Access 2007 handy but I believe the syntax in your case would be:

TemVars.Add "lngEmployeeID", Me.EmployeeID
DoCmd.OpenForm "AddHRLog", , , , acFormAdd

Open in new window

And in the popup form's Open Event:
Me.EmployeeID = TempVars("lngEmployeeID")

Open in new window

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 38783308

Thanks for this - am struggling to get this to work:

In the 1st form I have ...

TempVars.Add "lngEmployeeID", Me.EmployeeID.Value
DoCmd.OpenForm "AddHRLog", , , , acFormAdd]

Open in new window

I have added in the .Value as I was getting an error saying "TempVars can only store data. They cannot store objects"

I'm not sure if it makes a difference but both fields are numbers

In the pop up form I have...
Private Sub Form_Open(Cancel As Integer)
Me.EmployeeID = TempVars!lngEmployeeID
End Sub

Open in new window

Any ideas?
LVL 31

Accepted Solution

Helen Feddema earned 500 total points
ID: 38783798
Or you can just set lngEmployeeID from the current record on the initial form, and set a control on the popup form to it.  Something like this (BTW, I recommend using the named argument syntax, for clarity):

Private Sub cmdOpenPopup_Click()

   Dim frm As Access.Form
   Dim lngEmployeeID As Long
   lngEmployeeID = Me![EmployeeID]
   DoCmd.OpenForm FormName:="frmPopup"
   Set frm = Forms("frmPopup")
   frm![txtEmployeeID].Value = lngEmployeeID
End Sub

Open in new window

LVL 61

Expert Comment

ID: 38783821
In your popup form the tempvars syntax should be


Author Comment

ID: 38783851
Hi both
thanks for these - I'll give them both a go in the morning when I'm back at the PC - I'll double check, but I think I tried that syntax from mbizup, but will give it a go again

Author Closing Comment

ID: 38787064
Thanks for this works a treat

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question