Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2012
    Location
    Middle Georgia
    Posts
    11

    Unanswered: Change combo recordsource based on select...case

    I am setting up a form that gives the user a choice on which fields they can filter on. The first combobox contains table column names for its value list. Based which name they select will change the recordsource for the second combobox. However, I don't know if I am setting it up correctly. Should it change after_update or on_change? cboSrchField1 is the 1st combo and cbofilterfld1 is the 2nd.

    Code:
    Private Sub cboSrchField1_AfterUpdate()
    Select Case cboSrchField1.Value
        Case 1
                Me.cbofilterfld1.RowSource = "cboCouncil"
        Case 2
                Me.cbofilterfld1.RowSource = "cboActivity"
        Case 3
                Me.cbofilterfld1.RowSource = "cboFSC"
        Case 4
                Me.cbofilterfld1.RowSource = "cboMMAC"
        Case 5
                Me.cbofilterfld1.RowSource = "cboNoun"
        Case 6
                Me.cbofilterfld1.RowSource = "cboSOR"
        Case 7
                Me.cbofilterfld1.RowSource = "cboWS"
    End Select
    End Sub
    Thanks.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The RowSource property needs to be either the name of a table or saved query, or SQL (SELECT...FROM...). Names like "cboCouncil" makes it sound like you're trying to use the name of a combo.
    Paul

  3. #3
    Join Date
    Oct 2012
    Location
    Middle Georgia
    Posts
    11
    cboCouncil, cboFSC, etc are names of tables that I created to populate the 2nd combobox. Do I use after_Update or on_change? What about a requery?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Having those tables is almost certainly a normalization mistake, but the code should work. The after update event is correct.
    Paul

  5. #5
    Join Date
    Oct 2012
    Location
    Middle Georgia
    Posts
    11
    I have edited the script to see if I am on the right path and still doesn't work.

    Code:
    Private Sub cboSrchField1_AfterUpdate()
    
    Select Case cboSrchField1.Value
        Case 1
                Me.cbofilterfld1.RowSource = "filterCouncil"
                Me.cbofilterfld1.ControlSource = "Council"
                Me.cbofilterfld1.Requery
        Case 2
                Me.cbofilterfld1.RowSource = "filterActivity"
                Me.cbofilterfld1.ControlSource = "NIIN Activity"
                Me.cbofilterfld1.Requery
        Case 3
                Me.cbofilterfld1.RowSource = "filterFSC"
                Me.cbofilterfld1.ControlSource = "FSC"
                Me.cbofilterfld1.Requery
        Case 4
                Me.cbofilterfld1.RowSource = "filterMMAC"
                Me.cbofilterfld1.ControlSource = "MMAC"
                Me.cbofilterfld1.Requery
        Case 5
                Me.cbofilterfld1.RowSource = "filterNoun"
                Me.cbofilterfld1.ControlSource = "Noun"
                Me.cbofilterfld1.Requery
        Case 6
                Me.cbofilterfld1.RowSource = "filterSOR"
                Me.cbofilterfld1.ControlSource = "SOR"
                Me.cbofilterfld1.Requery
        Case 7
                Me.cbofilterfld1.RowSource = "filterWS"
                Me.cbofilterfld1.ControlSource = "Application"
                Me.cbofilterfld1.Requery
    End Select
    End Sub

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    "Doesn't work" doesn't give much to go on.
    Paul

  7. #7
    Join Date
    Oct 2012
    Location
    Middle Georgia
    Posts
    11
    Sorry. I changed from Select case to If statements. The Search Combo works, but when I select an item, the selected item immediately disappears from the box and #NAME? appears in the filter Combobox. However, it is using the correct recordsource. Items in the filter Combobox can't be selected though.

    Code:
    Private Sub cboSrchField1_AfterUpdate()
    
    If Me.cboSrchField1.Value = "Council" Then
                Me.cbofilterfld1.RowSource = "filterCouncil"
                Me.cbofilterfld1.ControlSource = "Council"
    ElseIf Me.cboSrchField1.Value = "Activity" Then
                Me.cbofilterfld1.RowSource = "filterActivity"
                Me.cbofilterfld1.ControlSource = "NIIN Activity"
    ElseIf Me.cboSrchField1.Value = "FSC" Then
                Me.cbofilterfld1.RowSource = "filterFSC"
                Me.cbofilterfld1.ControlSource = "FSC"
    ElseIf Me.cboSrchField1.Value = "MMAC" Then
                Me.cbofilterfld1.RowSource = "filterMMAC"
                Me.cbofilterfld1.ControlSource = "MMAC"
    ElseIf Me.cboSrchField1.Value = "Noun" Then
                Me.cbofilterfld1.RowSource = "filterNoun"
                Me.cbofilterfld1.ControlSource = "Noun"
    ElseIf Me.cboSrchField1.Value = "Noun" Then
                Me.cbofilterfld1.RowSource = "filterNoun"
                Me.cbofilterfld1.ControlSource = "Noun"
    ElseIf Me.cboSrchField1.Value = "SOR" Then
                Me.cbofilterfld1.RowSource = "filterSOR"
                Me.cbofilterfld1.ControlSource = "SOR"
    ElseIf Me.cboSrchField1.Value = "WS" Then
                Me.cbofilterfld1.RowSource = "filterWS"
                Me.cbofilterfld1.ControlSource = "Application"
    End If
    End Sub
    Attached Thumbnails Attached Thumbnails cboSrchField1.bmp   cbofilterfld1.bmp  

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can you post the db here?
    Paul

  9. #9
    Join Date
    Oct 2012
    Location
    Middle Georgia
    Posts
    11
    I can post a skinnyed down version of it. I cut down most of the form except the portion that was giving me fits.
    Attached Files Attached Files

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The #Name error is because the form isn't bound, so you're setting the control source to a field that doesn't exist in the form's record source.
    Paul

  11. #11
    Join Date
    Oct 2012
    Location
    Middle Georgia
    Posts
    11
    I thought I was binding it to a field after update. The combobox populates with the correct data.

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Yes, but you're saying "bind this control to the field named 'Council'", but that field doesn't exist in the form's source (because there isn't any). Don't know what your end goal is, but dropping the control source lines will get rid of the error.
    Paul

  13. #13
    Join Date
    Oct 2012
    Location
    Middle Georgia
    Posts
    11
    Works great. Thanks!! Guess I tried to make it more difficult than it was.

  14. #14
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problemo!
    Paul

Posting Permissions

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