Access VBA - populate combo box with ABC etc up to variable count

Access form : I need a form combo-box drop down with values  A, B, C, D etc up to count of records in a recordset.

Recordset is Groups in company, so if there are 8 groups, I need drop-down to have A through H as options, but if only 4 groups, then A through D in drop-down.

Can't quite get my head around this.  Can anyone help? Thank you.
Who is Participating?
Gustav BrockConnect With a Mentor CIOCommented:
This screams for a callback function, one of Access' hidden gems.
Here is a demo:
Public Function ListGroups( _
    ctl As Control, _
    lngId As Long, _
    lngRow As Long, _
    lngCol As Long, _
    intCode As Integer) As Variant
    Static dbs          As DAO.Database
    Static rst          As DAO.Recordset
    Dim varValue        As Variant
    Select Case intCode
        Case acLBInitialize
            Set dbs = CurrentDb
            Set rst = dbs.OpenRecordset("Select 65, Name From MSysObjects Where Type = 1 Group By Name")
            varValue = True             ' True to initialize.
        Case acLBOpen
            varValue = Timer            ' Autogenerated unique ID.
        Case acLBGetRowCount            ' Get rows.
            varValue = rst.RecordCount  ' Set number of rows.
        Case acLBGetColumnCount         ' Get columns.
            varValue = rst.Fields.Count ' Set number of columns.
        Case acLBGetColumnWidth         ' Get column width.
            varValue = -1               ' Use default width.
        Case acLBGetValue               ' Get the data.
        If Not rst.EOF Then
            rst.Move lngRow
            varValue = rst.Fields(lngCol).Value
            If lngCol = 0 Then
                varValue = Chr(varValue + lngRow)
            End If
        End If
        Case acLBGetFormat              ' Format the data.
            varValue = ctl.Format       ' Use format of control.
        Case acLBEnd
            Set rst = Nothing
            Set dbs = Nothing
    End Select
    ' Return Value.
    ListGroups = varValue

End Function

Open in new window

For this, set the combobox' ColumnCount to 2 and the RowSourceType to ListGroups (Note. Not as  =ListGroups but as ListGroups).

Of course, replace the SQL with that of yours.
Works like magic.

Dale FyeCommented:
How do you determine how many groups there are?  What is the maximum number of groups?  Could it go over 26?

I have a table (tbl_Numbers) I use for a variety of things.  The table contains a single field (intNumber) and ten records (the values 0 to 9).  With this table, you could create a query that provides you the numbers 1 - 26

Select tens.intNumber * 10 + Ones.intNumber as intNumber
FROM tbl_Numbers Tens, tbl_Numbers Ones
WHERE tens.intNumber * 10 + Ones.intNumber BETWEEN 1 and 26

From there, you could make that a subquery and use whatever your # of Groups is to get your list.

SELECT chr(64 + intNumber) as GroupLetter
Select tens.intNumber * 10 + Ones.intNumber as intNumber
FROM tbl_Numbers Tens, tbl_Numbers Ones
WHERE tens.intNumber * 10 + Ones.intNumber BETWEEN 1 and 26
) as subNumbers
WHERE subNumbers.intNumber <= GroupNumber
ORDER BY subNumbers.intNumber
Dale FyeCommented:
BTW, it would be easier with a table of numbers from 1-26, but I just like my multi-functional tbl_Numbers and the numerous ways I find to use it.  ;-)
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

You could also use DCOUNT to get the number of rows, and a procedure with a case statement to set the value list of the rowsourcetype in the combo box - not nearly as elegant as fyeds,
MonkeyPieAuthor Commented:
Thanks so far.

Based on your responses, I would like to take it one step further:

Select chr(64 + IntNumber) as letter From GROUP

where intNumber is an auto-increment 1, 2, 3 etc.  I know I have done something like this before, but I can't remember or find it.

There is some special trick to add an increment column to SQL - does anyone know how?
COACHMAN99Connect With a Mentor Commented:
no idea, but you would have finished the case statement and gone to bed by now :-)

select case NZ(dcount(ID, tblName, where ..)

case 5 str="A;B;C;D;E"

cbo.rowsource = str
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.