Solved

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

Posted on 2014-01-16
6
752 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 (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 48

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 50

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 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