Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add (All) Item to combobox (2003)

    I've seen this many times now in tutorials but never thought I'd need something like that.

    I have a form with a combo-box and a listbox. Combobox is limiting the items in the listbox. Works like a charm, but once I selected something in the combobox, I can never get back to the list I had in the beginning (All records).

    My code looks like this now:
    Private Sub Form_Load()

    Dim sListSource As String
    sListSource = " SELECT [tblTeacher].[TID], [tblTeacher].[LastName], [tblTeacher].[FirstName], [tblTeacher].[School], [tblTeacher].[Year]" & _
    " FROM [tblTeacher] " & _
    " WHERE Year = Formulare!frmYear!cboreg " & _
    " ORDER BY [tblTeacher].[LastName], [tblTeacher].[FirstName] "
    Me.lboTeacher.RowSource = sListSource

    End Sub

    Private Sub cboSchool_AfterUpdate()
    Dim sListSource As String

    sListSource = " SELECT [tblTeacher].[TID], [tblTeacher].[LastName], [tblTeacher].[FirstName], [tblTeacher].[School], [tblTeacher].[Year]" & _
    " FROM [tblTeacher] " & _
    " WHERE School = '" & Me.cboSchool.Value & "' AND Year = Formulare!frmYear!cboreg " & _
    " ORDER BY [tblTeacher].[LastName], [tblTeacher].[FirstName] "
    Me.lboTeacher.RowSource = sListSource
    Me.lboTeacher.Requery

    End Sub

    ---------------------------------

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Add (All) Item to combobox (2003)

    Try the following:


    Create a command button with this code:


    Form_Load
    Me.lboTeacher.Requery
    Me.cboSchool = Null


    This reruns the code that ran when the form loaded, requeries the list box, then clears the combo box
    Regards
    John



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

    Re: Add (All) Item to combobox (2003)

    Or perhaps this:

    Private Sub cboSchool_AfterUpdate()
    Dim sListSource As String
    sListSource = " SELECT [tblTeacher].[TID], [tblTeacher].[LastName], [tblTeacher].[FirstName], [tblTeacher].[School], [tblTeacher].[Year]" & _
    " FROM [tblTeacher] WHERE "
    If Not IsNull(Me.cboSchool) Then
    sListSource = sListSource & "School = '" & Me.cboSchool.Value & "' AND "
    End If
    sListSource = sListSource & "Year = Formulare!frmYear!cboreg " & _
    " ORDER BY [tblTeacher].[LastName], [tblTeacher].[FirstName] "
    Me.lboTeacher.RowSource = sListSource
    Me.lboTeacher.Requery
    End Sub

  4. #4
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add (All) Item to combobox (2003)

    I included both of your ideas into my project - still - the perfect solution in my eye is an "(All)" combo-box-item within the others.

    As far as this goes: thanks for your help

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

  6. #6
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add (All) Item to combobox (2003)

    That is what I found too via msdn.
    The only problem is that I don't get it to work with all my SQL code I have in that cbo... I guess I have to stick to the other two solutions.

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

    Re: Add (All) Item to combobox (2003)

    Is there some reason you can't use a union query in the Combobox rowsource? Here's a simple example works based on the Northwind customers table.

    SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactName
    FROM Customers
    UNION
    SELECT "*", "(All)", "" From Customers
    ORDER BY Customers.CompanyName

    If you used that kind of selection in your combobox, then n the AfterUpdate of the combobox, you could check for the All selection and simply remove the filter from your listbox rowsource query.

    Private Sub cboSchool_AfterUpdate()
    Dim sListSource As String
    Dim strWhere As String

    strWhere = " School = '" & Me.cboSchool.Value & "' AND Year = Formulare!frmYear!cboreg "

    If me.cboSchool.Text = "(All") Then
    strWhere = " Year = Formulare!frmYear!cboreg "
    End If

    sListSource = " SELECT [tblTeacher].[TID], [tblTeacher].[LastName], [tblTeacher].[FirstName], [tblTeacher].[School], [tblTeacher].[Year]" & _
    " FROM [tblTeacher] WHERE " & strWhere
    " ORDER BY [tblTeacher].[LastName], [tblTeacher].[FirstName] "
    Me.lboTeacher.RowSource = sListSource
    Me.lboTeacher.Requery

    End Sub
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add (All) Item to combobox (2003)

    Wow, yeah. You guided me into the right direction.

    The Union query and the IF statement did the trick.

    Thanks alot to all of you guys, you're great!

    Dennis

Posting Permissions

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