Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-01-16
6
Medium Priority
?
799 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 48

Expert Comment

by:Dale Fye
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 48

Expert Comment

by:Dale Fye
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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 1000 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 52

Accepted Solution

by:
Gustav Brock earned 1000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

604 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