Results 1 to 4 of 4
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    VBA to fill ComboBoxes (XP)

    I have a Form/SubForm setup. Tables are Branch and Employee. I have a ComboBox to select a Branch.
    When a selection is made, I want to populate the Employees ComboBox with only those attached to selected branch.
    The next step after that is to choose an employee to show their pay information.

    So far, I made a sample SQL query to get the syntax exactly right and verify that it did what I expected.
    I added it to the VBA. module

    Got the
    Alan

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

    Re: Query to fill ComboBoxes (XP)

    Hello Alan,

    If you have a main form based on Branch and a subform based on Employee, you may not need the cascading combo box setup. Just set the master and child link fields of the subform control to the field that identifies a branch. That should be enough to synchronize the subform with the main form.

    If that doesn't help, please provide some more information.
    What do you mean by "I made a sample SQL query ... I added it to the VBA. module"?
    And what do you mean by "it seems to die" - do you get an error message (if so, what does it say?), or does your subform stay empty, or ...?
    Perhaps you can post the code you now have.

  3. #3
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: VBA to fill ComboBoxes (XP)

    My Code and an attached picture of the latest error message.

    '---------------------------------------------------------------------------------------------
    Private Sub cboChooseBranch_AfterUpdate()
    Form_frmMainWeekly.RecordSource = cboChooseBranch
    Me.Requery
    Form_Open False
    End Sub

    Private Sub Form_Open(Cancel As Integer)
    Dim strSQL As String
    strSQL = "SELECT ([Last Name] & " & Chr(34) & ", " & Chr(34) & " & [First Name]) AS Expr1"
    strSQL = strSQL & vbCrLf & "FROM tblBranch RIGHT JOIN tblConsultants ON tblBranch.[Branch ID] = tblConsultants.[PR Branch] "
    strSQL = strSQL & vbCrLf & "WHERE (((tblBranch.Branch) = " & Chr(34) & cboChooseBranch & Chr(34) & ")) "
    strSQL = strSQL & vbCrLf & "ORDER BY ([LastName] & " & Chr(34) & ", " & Chr(34) & " & [FirstName]);"

    Debug.Print "strSQL " & strSQL
    sfrmConsultInfo.cmboConsultInfo.RowSource = strSQL

    End Sub

    Private Sub cmdcloseForm_Click()
    On Error GoTo Err_cmdcloseForm_Click


    DoCmd.Close

    Exit_cmdcloseForm_Click:
    Exit Sub

    Err_cmdcloseForm_Click:
    MsgBox Err.Description
    Resume Exit_cmdcloseForm_Click

    End Sub

    Private Sub Combo6_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Branch ID] = '" & Me![Combo6] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub

    '----------------------------------------------------------------------------------
    Attached Images Attached Images
    Alan

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA to fill ComboBoxes (XP)

    Are you referring to a combobox on a subform? If so, try

    sfrmConsultInfo.Form.cmboConsultInfo.RowSource = strSQL
    Charlotte

Posting Permissions

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