Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2006
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Refresh form via a Combo Box (2003)

    I have a form frmFindOrg which displays an organization's information based on the user's choice in a combo box cboOrgName, which was created using the Wizard. The table it is based on has a field "OrgID" which is the primary key, "ParentOrgID," "OrgName," and some other fields. ParentOrgID is used when one organization in the table is a department of another organization.
    Sample:
    OrgID-ParentOrgID-OrgName
    2201-(null)-Company A
    2202-2201-Company A Finance Department
    2203-2201-Company A HR Department
    2204-(null)-Company B

    I have a command button cmdOrgChildren on frmFindOrg that will open a continuous form frmOrgChildren to display all of a given organization's departments. For instance, if I am viewing Company A's information and click the cmdOrgChildren, frmOrgChildren will open, displaying a list containing Company A Finance Department and Company A HR Department.

    I would like to have a command button cmdSwitch in the header of frmOrgChildren that, when clicked, will close frmOrgChildren and will update frmFindOrg to show the information of the selected "child" from frmOrgChildren.

    I've got:
    -----
    Private Sub cmdSwitch_Click()

    Dim stOrgID As String

    stOrgID = [OrgID]

    Forms!frmFindOrg.SetFocus
    Forms!frmFindOrg!cboOrgName = stOrgID
    Forms!frmOrgChildren.SetFocus
    DoCmd.Close

    End Sub
    ------
    This changes the value of cboOrgName to the correct organization, but the rest of the fields don't change. That is, if I select Company A Finance Department in frmOrgChildren and click cmdSwitch, cboOrgName on frmFindOrg shows "Company A Finance Department," but the rest of the fields still show the information for Company A, not Company A Finance Department.

    What does the code for cmdSwitch_Click() have to be?

    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refresh form via a Combo Box (2003)

    Your code doesn't select the child record in frmFindOrg, it edits the parent record. To make it select the child record, the code has to search for it:

    Private Sub cmdSwitch_Click()
    Dim rs As Object
    With Forms!frmFindOrg
    Set rs = .Recordset.Clone
    rs.FindFirst "OrgID = " & Me.OrgID
    If Not rs.EOF Then
    .Bookmark = rs.Bookmark
    .SetFocus
    DoCmd.Close acForm, Me.Name, acSaveNo
    Else
    MsgBox "Child not found.", vbExclamation
    End If
    End With
    End Sub

    This assumes that OrgID is a number field. If it is defined as a text field, use

    ...
    rs.FindFirst "OrgID = " & Chr(34) & Me.OrgID & Chr(34)
    ...

  3. #3
    New Lounger
    Join Date
    Jun 2006
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh form via a Combo Box (2003)

    Works great- thanks, Hans!

    By the way, is there a way to have the combo box change to reflect the record that now shows on frmFindOrg? For instance, if I pull up Company A on frmFindOrg, then click cmdOrgChildren, choose Company A Finance Department on frmOrgChildren, and click cmdSwitch (with the new code you provided), it brings me back to frmFindOrg- the text boxes on frmFindOrg show the information for Company A Finance Department, but cboOrgName still says Company A.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refresh form via a Combo Box (2003)

    You can put code in the On Current event of frmFindOrg:

    Private Sub Form_Current()
    Me.cboOrgName = Me.OrgID
    End Sub

  5. #5
    New Lounger
    Join Date
    Jun 2006
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh form via a Combo Box (2003)

    Great- thanks!

Posting Permissions

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