Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.

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



Application Security Testing: An Integral Part of DevOps

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
		If rsCbo.Fields.Count = 1 Then 
			' If only 1 column then no index
			Do Until rsCbo.EOF
				cb.AddItem rsCbo(0)
			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)
		End If
		Set rsCbo = rsCbo.NextRecordset
	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"
	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.



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