?
Solved

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

Posted on 2014-01-16
6
Medium Priority
?
774 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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 51

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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

765 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