3 Easy Combo Tasks

The code demonstrates 3 common combobox tasks:
1.) Filling a cbo with a recordset
2.) Setting the cbo Text to a recordset field using a numeric rst field
3.) Setting the cbo Text to a recordset field using a non-numeric rst field

'**************************************
' Name: 3 Easy Combo Tasks
' Description:The code demonstrates 3 common combobox tasks:
1.) Filling a cbo with a recordset
2.) Setting the cbo Text to a recordset field using a numeric rst field
3.) Setting the cbo Text to a recordset field using a non-numeric rst field
' By: Mark Freni (from psc cd)
'
' Inputs:The name of a combobox control, and the recordset field names
'
' Returns:Nothing, but they could be modified to boolean functions very easily
'
' Assumes:The user needs to know how to open a recordset
'**************************************
' Three simplified combobox Tasks:
' 1. Filling a cboBox with a Recordset
' 2. Setting the cboText to a recordset field
' using an numeric recorset field.
' 3. Setting the cboText to a recordset field
' using a non-numeric recordset field.
'
'
Public Sub GetCBOList(cbo As ComboBox)
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Filling a cboBox
' To make this more dynamic, pass the
' Sub the Desc as a string, and the ID
' As a long or integer
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
On Error GoTo FUNCT_ERR
Dim obj As New cClass
Dim rst As New ADODB.Recordset
' I am using a class Method to get
' My Recordset. Getlist is a Class
' Function that returns a disconnected Recordset
Set rst = obj.GetList
' Test the Recordset State to see
' it is open.
If rst.State = 1 Then
' Make sure I don't have an empty rst
Do Until rst.EOF
' Always test for nulls
If Not IsNull(rst!Desc) Then cbo.AddItem rst!Desc
If Not IsNull(rst!UomID) Then cbo.ItemData(cbo.NewIndex) = rst!UomID
' Forget the movenext and you get an endless loop and
' an overflow error.
rst.MoveNext
Loop
rst.Close
End If
FUNCT_EXIT:
Set obj = Nothing
Set rst = Nothing
Exit Sub
FUNCT_ERR:
Err.Raise Err.Number, Err.Source, Err.Description
Resume FUNCT_EXIT
End Sub
Public Sub SetCboText(cbo As ComboBox, val As Variant)
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'PASS THE PROCEDURE A CBO NAME AND A RECORDSET FIELD
'IF THE FIELD IS IN THE DROP-DOWN LIST IT WILL SET THE TEXT
'VALUE FOR THAT CBO TO the listItem.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim i As Long
' LOOP THROUGH CBO Items
For i = 0 To cbo.ListCount - 1
If cbo.ItemData(i) = val Then
cbo.ListIndex = i
GoTo FUNCT_EXIT
End If
Next i
FUNCT_EXIT:
End Sub
Public Sub SetCboText_NonNumeric(cbo As ComboBox, val As Variant)
'SUB USES cboBOXES THAT DO NOT HAVE A NUMERIC ITEMDATA VALUE
'PASS THE PROCEDURE A CBO NAME AND A RECORDSET FIELD
'IF THE FIELD IS IN THE DROP-DOWN LIST IT WILL SET THE TEXT
'VALUE FOR THAT FIELD.
'A good example of Non-Numeric ID is a StateCode: ie.
'TX, MA, NY...
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim i As Long
' Loop through the CBO items, remember the cbo & lstBox
' are zero based lists
For i = 0 To cbo.ListCount - 1
If cbo.List(i) = val Then
cbo.Text = cbo.List(i)
' DoEvents isn't really necessary
DoEvents
GoTo FUNCT_EXIT
End If
Next i
FUNCT_EXIT:
End Sub

Use this form to tell us if this entry should be deleted (i.e contains
no code, is a virus, etc.).This submission should be removed because:

Your Vote

What do you think of this code (in the
Not Given category)?(The code with your highest vote will win this month's coding contest!)
Excellent Good Average Below Average Poor
(See voting log ...)

Other User Comments

There are no comments on this submission.

Add Your Feedback

Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular code, please
click here instead.)

My nonprogramming sites: The Real Estate Crowdfunding Review: Investing tools that cut through the clutter and the hype.
Real information and reviews. Real-time investment feeds. Real profitable. | Dollar candy: Play 60 second puzzles against three other people for real money! Legal in 36 states and overseas.