Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2011
    Location
    Grand Haven / Royal Oak Michigan USA
    Posts
    23

    Unanswered: New form based off previous forms combo box values

    I have a prompt form where I am using 2 combo boxes. The first combo box is there to select a supervisor… collecting their ID and their Name. The second combo box is there to select an employee based off of the supervisor selection; collecting the employee ID, their Name and their Current Position. I want the selections made in the prompt form to populate the next form I intend to open. I made both pieces of the code I created to handle the prompt logic public in anticipation of having to share the stored string values for the supervisor and employee, perhaps in a module, to pass to the next piece of code to open the next form. So far I am grabbing their ID but I would like to collect and store the name values and the current position to pass to the next form. I would like to open the next data entry form with the supervisor and employee information populated but I do not want to write the record to the table until the form is entirely populated and a save feature is activated based off of form level validation. I want to either write a new record to the table where the data is to be stored for the employees or based off of a feature, yet to be developed, I would like to allow a historic record to be edited based off a date selection, most likely I will use a combo box... but I will need to develop some historic data records to assist with development of that feature. I have created a table for the data to be stored, unfortunately there is no historic data because the excel files used prior to this system require a good deal of work to extract that historic information. How can I open the next form and retain the values for supervisor ID, supervisor Name, employee ID, employee Name and employee Current Position without writing to the table in preparation of creating a new record once the new form is filled completely?

    The code I have for the prompt form thus far:

    Public Sub cboEmployee_AfterUpdate()


    Dim strEmpID As String

    strEmpID = Me.cboEmployee.Value

    Me.Requery
    DoCmd.OpenForm "frmEPDP02"
    DoCmd.Close acForm, "frmEPDP01"

    End Sub

    Public Sub cboSupervisor_AfterUpdate()
    On Error GoTo Err_employeesearch_Click

    '************************************************* ************************************'
    '*Logic used on frmEPDP01 prompt form with controls cboSupervisor & cboEmployee: *'
    '* *'
    '*cboSupervisor *'
    '*Row Source: SELECT DISTINCT qryEPDP001.SuperID, qryEPDP001.Supervisor, _ *'
    '*qryEPDP001.[Super Lst Nm] FROM qryEPDP001 ORDER BY qryEPDP001.[Super Lst Nm]; *'
    '*Column Count: 6 *'
    '*Column Widths: 0";1";0";0";0";0" *'
    '* *'
    '*cboEmployee *'
    '*SELECT qryEPDP002.EmpID, qryEPDP002.EmpName, qryEPDP002.SuperID FROM qryEPDP002 _ *'
    '*ORDER BY qryEPDP002.[ Last Name]; *'
    '*Column Count: 6 *'
    '*Column Widths: 0";1";0";0";0";0" *'
    '************************************************* ************************************'

    'Stop

    Dim strSuperID As String

    strSuperID = Me.cboSupervisor.Value
    Me.cboEmployee = ""
    Me.cboEmployee.Requery
    Me.Requery

    Me.lblEmployee01.Visible = True
    Me.lblEmployee02.Visible = True
    Me.cboEmployee.Visible = True

    Exit_employeesearch_Click:

    Exit Sub

    Err_employeesearch_Click:

    MsgBox Err.Description
    Resume Exit_employeesearch_Click

    End Sub
    Last edited by robgrdhvn; 01-18-11 at 13:11. Reason: Adding code

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You can use the OpenArgs parameter in the DoCmd.OpenForm method. OpenArgs will accept anything you want to pass to it. I will use it to pass a delimited list of values like:

    1`Whatever`Something Else`4

    Then use the Split function to separate the values in the form that was opened. In the form that is opened, you can look for Me.OpenArgs to see if any data was passed to the form when it was opened.

  3. #3
    Join Date
    Jan 2011
    Location
    Grand Haven / Royal Oak Michigan USA
    Posts
    23
    How do you suggest I get the stored string values to the next form?

    Public Sub cboEmployee_AfterUpdate()

    Stop

    Dim strEmpID As String
    Dim strEmpName As String
    Dim strEmpPosition As String

    strEmpID = Me.cboEmployee.Column(0)
    strEmpName = Me.cboEmployee.Column(1)
    'strEmpPosition = Me.cboEmployee.Column(4)

    'Me.Requery
    DoCmd.OpenForm "frmEPDP02"
    DoCmd.Close acForm, "frmEPDP01"

    End Sub

    Public Sub cboSupervisor_AfterUpdate()
    On Error GoTo Err_employeesearch_Click
    Stop

    Dim strSuperID As String
    Dim strSuperName As String

    strSuperID = Me.cboSupervisor.Column(0)
    strSuperName = Me.cboSupervisor.Column(1)
    'Me.cboEmployee = ""
    'Me.cboEmployee.Requery
    'Me.Requery

    Me.lblEmployee01.Visible = True
    Me.lblEmployee02.Visible = True
    Me.cboEmployee.Visible = True

    Exit_employeesearch_Click:

    Exit Sub

    Err_employeesearch_Click:

    MsgBox Err.Description
    Resume Exit_employeesearch_Click

    End Sub

  4. #4
    Join Date
    Jan 2011
    Location
    Grand Haven / Royal Oak Michigan USA
    Posts
    23
    This statement does not make my code implode but how do I utilize this value on frmEPDP02's form load?

    DoCmd.OpenForm "frmEPDP02", OpenArgs:="Me.cboSupervisor.Column(0)"

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    First you want to make sure you are passing the right info. You want to remove the " around the combobox reference, like this:

    DoCmd.OpenForm "frmEPDP02", OpenArgs:=Me.cboSupervisor.Column(0)

    Then in the form frmEPDP02 use Me.OpenArgs to get to the string that is passed to the form. In the Load event try:

    Msgbox Me.OpenArgs

  6. #6
    Join Date
    Jan 2011
    Location
    Grand Haven / Royal Oak Michigan USA
    Posts
    23
    I would be all set if I were working with just the Supervisor ID... But I want to pass; Me.cboSupervisor.Column(0), Me.cboSupervisor.Column(1), Me.cboEmployee.Column(0), Me.cboEmployee.Column(1) and Me.cboEmployee.Column(4) any suggestions on how to write this code?

    'frmEPDP01
    Public Sub cboEmployee_AfterUpdate()

    Dim strEmpID As String
    Dim strEmpName As String
    Dim strEmpPosition As String

    strEmpID = Me.cboEmployee.Column(0)
    strEmpName = Me.cboEmployee.Column(1)
    strEmpPosition = Me.cboEmployee.Column(4)

    DoCmd.OpenForm "frmEPDP02", OpenArgs:=Me.cboSupervisor.Column(0)

    ' DoCmd.Close acForm, "frmEPDP01"

    End Sub

    ' frmEPDP02
    Private Sub Form_Load()

    Me.txtSuperID = Me.OpenArgs

    End Sub

  7. #7
    Join Date
    Jan 2011
    Location
    Grand Haven / Royal Oak Michigan USA
    Posts
    23
    Leave it to me to try something UNORTHODOX... I had to catch up a bit because the design database I was working with became corrupt, most likely due to the code I am attempting to create, I am able to carry the ID onto the next data entry form from the prompt form. When I tried to pull more information; supervisor Name, employee ID, employee Name and employee Current Position the feature began bellyaching that there was too much data. I am continuing development and will approach the overall solution from a different direction. Thank you for your help getting at least the ID value to the next form! My best, Rob

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •