Search Tools Links Login

Fill ComboList All Over


Use one generic sub to fill all comboboxes or listboxes on your form(s), with data pulled out from database.

Original Author: Shauli

Inputs

Tablename, fieldname, optional comboname, optional listboxname, optional orderbyfield

Assumptions

This code is using ADO control, and uses two comboboxes and three listboxes on a form.

Returns

Fills combobox or listbox with data from database.

Code

Option Explicit
'decalre connection and recordset
Dim myConn As ADODB.Connection
Dim myRecSet As ADODB.Recordset
Private Sub Form_Load()
Set myConn = New ADODB.Connection
Set myRecSet = New ADODB.Recordset
myConn.CursorLocation = adUseClient
'modify your connection string
myConn.Open "PROVIDER=Microsoft.jet.OLEDB.4.0;data source=C:game.mdb;"
'modify with tablename, fieldname, optional comboname, optional listboxname, optional orderby
Call Fill_ComboAllOver("tablename", "fieldname", , List1, "fieldname")
'modify with tablename, fieldname, optional comboname, optional listboxname, optional orderby
Call Fill_ComboAllOver("tablename", "fieldname", Combo1) 'no orderby specified
'modify with tablename, fieldname, optional comboname, optional listboxname, optional orderby
Call Fill_ComboAllOver("tablename", "fieldname", Combo2, , "fieldname")
'modify with tablename, fieldname, optional comboname, optional listboxname, optional orderby
Call Fill_ComboAllOver("tablename", "fieldname", , List2, "fieldname")
'modify with tablename, fieldname, optional comboname, optional listboxname, optional orderby
Call Fill_ComboAllOver("tablename", "fieldname", , List3) 'no orderby specified
End Sub
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
myConn.Close
End Sub
Public Sub Fill_ComboAllOver(ByVal sbTableName As String, ByVal sbFieldName As String, _
    Optional ByRef comboName As ComboBox, Optional ByRef listName As ListBox, _
    Optional ByVal sbOrder As String)
On Error Resume Next
'if no orderby sepcified, use the fieldname instead
If sbOrder = "" Then sbOrder = sbFieldName
'clear the combo or the listbox
comboName.Clear
listName.Clear
'open recordset
DoEvents
myRecSet.CursorLocation = adUseClient
myRecSet.Open "SELECT " & sbFieldName & " FROM " & sbTableName & " order by " & sbFieldName & ";", myConn, adOpenKeyset, adLockReadOnly
  With myRecSet
  Do Until .EOF
   'fill combo or listbox
   comboName.AddItem .Fields(sbFieldName)
   listName.AddItem .Fields(sbFieldName)
  .MoveNext
  Loop
  End With
myRecSet.Close
Set comboName = Nothing
Set listName = Nothing
End Sub

About this post

Posted: 2003-06-01
By: ArchiveBot
Viewed: 94 times

Categories

Visual Basic 6

Attachments

No attachments for this post


Loading Comments ...

Comments

No comments have been added for this post.

You must be logged in to make a comment.