[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Combo Box Additem

Posted on 2006-03-20
12
Medium Priority
?
419 Views
Last Modified: 2010-04-07
I have 5 combo boxes on a form.  these boxes are filled with the function shown below.  What I can't seem to figure out is how to "additem" for something not already on the list generated by the function.  Anyone got an idea?

Public Function RDcomboPaint() As ADODB.Recordset
Dim ssql As String
Dim rsc As ADODB.Recordset
Dim msgr As VbMsgBoxResult
cboPt1.Clear
cboPt2.Clear
cboPt3.Clear
cboPt4.Clear
cboPt5.Clear
    ssql = "SELECT itemnmbr "
    ssql = ssql & "FROM itemlist "
       
    Set rsc = New ADODB.Recordset
        With rsc
            .Open ssql, CN, adOpenDynamic
       
            Do Until rsc.EOF
                cboPt1.AddItem rsc![itemnmbr]
                cboPt2.AddItem rsc![itemnmbr]
                cboPt3.AddItem rsc![itemnmbr]
                cboPt4.AddItem rsc![itemnmbr]
                cboPt5.AddItem rsc![itemnmbr]
                rsc.MoveNext
            Loop
        End With
End Function
0
Comment
Question by:Pioneermfg
12 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16235281
I'm not entirely sure I understand what you mean, but you can just pass a string to the AddItem method:

    cboPt1.AddItem "wibble"
    cboPt2.AddItem "wibble"
    cboPt3.AddItem "wibble"

 etc, etc
0
 
LVL 3

Author Comment

by:Pioneermfg
ID: 16235316
My quetion is how can my users add an item not already in the list?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16235335
If its an editable combo then you can use the lostfocus event, check the Text property and compare it to the entries in the list and add it if its not already there.

Alternatively, give them a text field where they can type some text, and then add it to the list via the AddItem method.
0
Independent Software Vendors: 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!

 
LVL 3

Expert Comment

by:ptan218
ID: 16235345
Why don't you use DISTINCT in your SQL statement ?

ssql = "SELECT DISTINCT itemnmbr FROM itemlist "
0
 
LVL 12

Expert Comment

by:Preece
ID: 16235518
You might also order your recordset:

ssql = "SELECT DISTINCT itemnmbr FROM itemlist ORDER BY itemnmbr"
0
 
LVL 3

Author Comment

by:Pioneermfg
ID: 16237204
first off the itemlist is already sorted.  How do I tell if the combobox is editable?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16237282
Its Style property will be set to "Dropdown Combo"
0
 
LVL 3

Author Comment

by:Pioneermfg
ID: 16237377
It is set to dropdown combo.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16237416
Then the user should be allowed to enter their own value, or choose from the list. So, if you want what the user enters to be added to the list you need to handle the LostFocus event, check if the item is in the list, and add it id it's not (maybe prompting the user to confirm first)
0
 
LVL 3

Author Comment

by:Pioneermfg
ID: 16237464
ok, how?
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 2000 total points
ID: 16237537
Private Sub Combo1_LostFocus()

    Dim sNewVal As String
   
    If ListIndex = 0 Then
   
        sNewVal = Combo1.Text
        For i = 1 To Combo1.ListCount
            If Combo1.List(i) = sNewVal Then Exit Sub
        Next i
       
        '// If we get here the new value isn't in the list
        Combo1.AddItem sNewVal
        Combo1.Text = ""
       
    End If

End Sub
0
 
LVL 3

Author Comment

by:Pioneermfg
ID: 16237696
Thank you!!!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month18 days, 8 hours left to enroll

825 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