Combo Box Additem
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
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
ASKER
My quetion is how can my users add an item not already in the list?
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.
Alternatively, give them a text field where they can type some text, and then add it to the list via the AddItem method.
Why don't you use DISTINCT in your SQL statement ?
ssql = "SELECT DISTINCT itemnmbr FROM itemlist "
ssql = "SELECT DISTINCT itemnmbr FROM itemlist "
You might also order your recordset:
ssql = "SELECT DISTINCT itemnmbr FROM itemlist ORDER BY itemnmbr"
ssql = "SELECT DISTINCT itemnmbr FROM itemlist ORDER BY itemnmbr"
ASKER
first off the itemlist is already sorted. How do I tell if the combobox is editable?
Its Style property will be set to "Dropdown Combo"
ASKER
It is set to dropdown combo.
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)
ASKER
ok, how?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!!!
cboPt1.AddItem "wibble"
cboPt2.AddItem "wibble"
cboPt3.AddItem "wibble"
etc, etc