Full Version: Code to open form using employee ID from current form
UtterAccess Forums > Microsoft® Access > Access Forms
kblehman
When a user enters employee information on frmEmployees I would like to code a cmd button that will close frmEmployees and open frmAssets with that employee's name/ID already in the appropriate object on frmAssets.
md button = cmdAddEmplAsset
frmEmployees.txtEmployeeID control source = EmployeeID
frmAssets.cboEmployeeID control source = EmployeeID
Otried using the linking wizard for the new cmd button but it opens up frmAssets with all the information for the employee's first asset record. I want to open frmAssets with only the employee's name (EmployeeID is the bound column) in cboEmployeeID object.
Therefore, if I use frmEmployees to add employee info for Cindy Crawford, I want to click on cmdAddEmplAsset and have frmEmployees close, then have frmAssets open with Cindy Crawford's name (via Employee ID) already populating frmAssets.cboEmployeeID.
I tried my hand at the code but so far no luck:
CODE
Private Sub cmdAddEmplAsset_Click()
On Error GoTo Err_cmdAddEmplAsset_Click
    Dim EmpID As String
    EmpID = Me.txtEmployeeID
    
   DoCmd.OpenForm "frmAssets", , , , , acDialog, "GotoNew"
   Me![cboEmployeeID] = EmpID
   Me!.cboEmployeeID.Requery

Exit_cmdAddEmplAsset_Click:
    Exit Sub
Err_cmdAddEmplAsset_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddEmplAsset_Click
End Sub
Roger_Carlson
One way to do this is use the OpenArgs parameter of the OpenForm command, which will send a string value to the newly opened form. On opening the form, you read the OpenArgs value and set your Control Source of the form programmatically using that value.

It's easier to show than describe. On my website is a small sample database called: OpenArgs.mdb, which illustrates how to use OpenArgs to solve a variety of problems.
mishej
Pass the EmpID to the new form in the OpenArgs argument (String).
!--c1-->
CODE
Private Sub cmdAddEmplAsset_Click()
  On Error GoTo Err_cmdAddEmplAsset_Click
    Dim EmpID As String
    ' force a save if this is a bound form
    If Me.Dirty Then Me.Dirty = False
    EmpID = Me.txtEmployeeID
   ' this next line opens the frmAssets form and waits    
   ' DoCmd.OpenForm "frmAssets", , , , , acDialog, EmpID

   ' this one opens frmAssets form but continues without waiting
   DoCmd.OpenForm "frmAssets", , , , , , EmpID
Exit_cmdAddEmplAsset_Click:
    Exit Sub
Err_cmdAddEmplAsset_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddEmplAsset_Click
End Sub

Then your Form_Open event in frmAssets should recognize the EmpID and take appropriate action.
kblehman
Mishej,
Oinserted your code in the OnClick Property of the cmd button but it's simply opening frmAssets to the first record, not opening it to a new record and inserting EmpID into frmAssets.cboEmployee.
houldn't frmAssets be opened as a new record and EmpID be placed in cboEmployeeID? (Also, does the form then need to be refreshed?)
Seems to me EmpID needs to be used to create the new record.
kblehman
Guys FYI, I got it to work:
CODE
Private Sub cmdAddEmplAsset_Click()
  On Error GoTo Err_cmdAddEmplAsset_Click
nbsp;   Dim EmpID As String
    ' force a save if this is a bound form
    If Me.Dirty Then Me.Dirty = False
    EmpID = Me.txtEmployeeID
   ' this next line opens the frmAssets form and waits
   ' DoCmd.OpenForm "frmAssets", , , , , acDialog, EmpID

   ' this one opens frmAssets form but continues without waiting
   DoCmd.OpenForm "frmAssets", , , , acFormAdd, , cboEmployeeID = EmpID
   Forms!frmAssets!cboEmployeeID = EmpID
  
Exit_cmdAddEmplAsset_Click:
    Exit Sub
Err_cmdAddEmplAsset_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddEmplAsset_Click
End Sub

I had to add the "Forms!frmAssets!cboEmployeeID = EmpID" line of code to do it. I tried using it as the open argument but it didnt work. Nor could I remove the argument text and leave the Forms! line, but at least I got it to copy the employee name from the info form to the assets form in add mode.
Kerry
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.