Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Visual Basic
Expertise: Intermediate
Apr 6, 2000

Load a Combo Box Array With a Compound Recordset in One Call

This code takes an array of combo boxes and fills them using a compound recordset. This allows all combo boxes on a form to be loaded with one sub call. Match the recordsets in the same order as the combo boxes in the array. Specify the display item as the first field, and ItemData as the second field in the select statements:
 
Sub FillComboBoxArray(cbArray As Variant, rsCbo _
	As ADODB.Recordset)
	Dim cb As ComboBox
	For Each cb In cbArray
		cb.Clear
		If rsCbo.Fields.Count = 1 Then 
			' If only 1 column then no index
			Do Until rsCbo.EOF
				cb.AddItem rsCbo(0)
				rsCbo.MoveNext
			Loop
		Else
			Do Until rsCbo.EOF  
				' If 2 fields then 2nd is index
				cb.AddItem rsCbo(0)
				' Numbers only
				If IsNumeric(rsCbo(1)) Then _
					cb.ItemData(cb.NewIndex) = rsCbo(1)
				rsCbo.MoveNext
			Loop
		End If
		Set rsCbo = rsCbo.NextRecordset
	Next
	Set rsCbo = Nothing
End Sub
Here's an example of how to create a compound resultset and call the FillComboArray subroutine:
 
Private Sub Form_Load()
	Dim sql As String
	Dim rs As New ADODB.Recordset
	Dim cn As ADODB.Connection
	Set cn = New ADODB.Connection
	With cn
		.ConnectionString = "driver={SQL Server};" & _
			"server=YOURSERVER;uid=sa;" & "pwd=;database=pubs"
		.Open
	End With
	sql = "SELECT au_lname FROM Authors; " & _
		"SELECT lname, job_id FROM Employee; " & _
		"SELECT pub_name FROM Publishers"
	Set rs = New ADODB.Recordset
	rs.Open sql, cn
	FillComboBoxArray cboData, rs
End Sub
Kirk Ward
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date