Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Hawaii
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding to Combobox List (2000/SP-3)

    Is there a way to add an item to an unbound combobox list?

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

    Re: Adding to Combobox List (2000/SP-3)

    The 'official' definition of unbound is that the Control Source property of the combo box is blank. It is more relevant to know what the Row Source Type is:
    - Table/Query
    - Value List
    - Field List
    - The name of a custom function

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Hawaii
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding to Combobox List (2000/SP-3)

    It is Table/Query but I'm not sure if it needs to be. I initialize the list with an SQL query during the form Load event. Then I'd like to add a line that says something like "(Any)", which, if selected will be handled in the ClickOK event.

    Something like this:

    Private Sub Form_Load()
    cboSubdivisions.RowSource = "SELECT DISTINCT [tblFarming].[Subdivision] FROM tblFarming WHERE ((Not ([tblFarming].[Subdivision]) Is Null));"
    cboSubdivisions.AddItem "(Any)" ' This does not work in Access 2000
    End Sub

    Private Sub cboSubdivisions_Click()
    If cboSubdivisions = "(Any)" Then
    Me.RecordSource = "SELECT * FROM tblFarming"
    Else
    Me.RecordSource = "SELECT * FROM tblFarming WHERE [Subdivision] = '" & cboSubdivisions & "'"
    Me.Refresh
    End If
    End Sub

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

    Re: Adding to Combobox List (2000/SP-3)

    AddItem was only introduced in Access 2002. Moreover, it only works with combo boxes whose Row Source Type is set to Value List.

    For this problem, an easy way to do it would be to add a dummy record to tblFarming with "Any" in the subdivision field. You would have to filter it out for other purposes.

    For a method involving VBA, see ACC2000: How to Add '(all)' to a Combo Box or a List Box Control.

  5. #5
    Star Lounger
    Join Date
    May 2002
    Location
    Hawaii
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding to Combobox List (2000/SP-3)

    Thank you for pointing me in the right direction. I found a solution that meets my needs perfectly from the article you pointed me to. It was not the function they presented, it was the SQL UNION paragraph farther down in that same article. Because I have not seen this solution presented anywhere else in previous threads, I thought I'd share my solution here. I use the changed events that I presented in an earlier message in this thread: (Note that this adds '(All)' to a combobox list without adding a bogus record to your data.)

    Private Sub cboSubdivisions_Click()
    If cboSubdivisions = "(All)" Then
    Me.RecordSource = "SELECT * FROM tblFarming"
    Me.Refresh
    Else
    Me.RecordSource = "SELECT * FROM tblFarming WHERE [Subdivision] = '" & cboSubdivisions & "'"
    Me.Refresh
    End If
    End Sub

    Private Sub Form_Load()
    cboSubdivisions.RowSource = "SELECT '(All)' FROM tblFarming UNION SELECT DISTINCT [tblFarming].[Subdivision] FROM tblFarming WHERE ((Not ([tblFarming].[Subdivision]) Is Null));"
    cboSubdivisions.Value = "(All)"
    End Sub

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

    Re: Adding to Combobox List (2000/SP-3)

    A simple approach would be to create a Union query to populate the combo box. Here's the SQL for it:

    cboSubdivisions.RowSource = "SELECT DISTINCT [Subdivision] FROM tblFarming " _
    & " WHERE ((Not ([tblFarming].[Subdivision]) Is Null)) " _
    & " UNION SELECT '(Any)' FROM tblFarming ORDER BY [Subdivision];"
    Charlotte

  7. #7
    Star Lounger
    Join Date
    May 2002
    Location
    Hawaii
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding to Combobox List (2000/SP-3)

    Yep, just found it (see earlier message). 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
  •