Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo box format display (Access 97)

    Hi

    I have grouped records in a combo box relating to various subject types. So when the user clicks on the combo box they get something like this:

    Registration - Activity 1
    Registration - Activity 2
    Registration - Activity 3
    Change - Activity 1
    Change - Activity 2

    etc

    I would like for display purposes to show a space between each grouping eg

    Registration - Activity 1
    Registration - Activity 2
    Registration - Activity 3

    Change - Activity 1
    Change - Activity 2

    I realise I could put a null record in the combo box look table, but there are issues with doing this. So can this be done by other means?

    Regards
    WTH

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

    Re: Combo box format display (Access 97)

    It's fairly unusual to set up a combo box that way, but it can be done.

    In the following, I have assumed that you have a table or query with separate fields for category and activity. I used tblSubjects as a name.

    Create a function FillList. You can put it in the form module or in a standard module. In the latter case, you can make it Private.

    Function FillList() As String
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strCurCat As String
    Dim strPrevCat As String
    Dim strRet As String
    strSQL = "SELECT * FROM tblSubjects ORDER BY Category, Activity"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    Do While Not rst.EOF
    strCurCat = rst!Category
    If strCurCat <> strPrevCat Then
    strRet = strRet & ";"
    End If
    strRet = strRet & strCurCat & " - " & rst!Activity & ";"
    strPrevCat = strCurCat
    rst.MoveNext
    Loop
    FillList = Mid(strRet, 2, Len(strRet) - 2)
    rst.Close
    Set rst = Nothing
    End Function

    Set the RowSourceType of your combo box to List of Values. Leave the RowSource property empty.

    In the OnLoad or OnOpen event of your form, include a line

    cboMyCombo.RowSource = FillList

    where cboMyCombo is the name of the combo box.

    The user will be able to select an empty separator row. You'll have to handle this in the event handler that processes the combo box selection.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box format display (Access 97)

    Hi

    Thanks for the advice. I'll give it try.

    Regards
    WTH

Posting Permissions

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