Solved

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

Posted on 2014-01-16
6
707 Views
Last Modified: 2014-01-22
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.
0
Comment
Question by:MonkeyPie
6 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39787442
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
FROM (
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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39787444
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.  ;-)
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39787451
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,
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:MonkeyPie
ID: 39787559
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?
0
 
LVL 7

Assisted Solution

by:COACHMAN99
COACHMAN99 earned 250 total points
ID: 39787667
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
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 39788976
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.
            rst.MoveLast
            rst.MoveFirst
            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.MoveFirst
            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
            rst.Close
            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.

/gustav
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now