DataCombo

[Newtechuser]

Hi

I am trying to populate a DataCombo box from a recordset
I was hoping someone would have the correct syntax for this.

Thanks

 

[kbWilliamson]

Try this out...

Declare these variables as Public in the Main Form or Main Module (Mod1)
{I declared these in Mod1 in this example}
(you can name them any thing you want but you'll need these variable types)

Option Explicit
Public DataWs As Workspace				'assumes DAO
Public DataDB As Database	
Public rsSource As Recordset
Public pstrDataFileName as String 'Full path to the DataBase
Public pstrDataTableName as String 'Table with in the DataBase
Public pstrFieldName as String 'Field to select for ComboBox
Public pblnDBOpen as Boolean

This is the code for the comboBox you want to fill from a database.
If you give each comboBox a different name you can call FillDropList
for all of them.

Private Sub cmboBox_DropDown()
	Mod1.pstrDataFileName = { your DataBase PATH AND FILE NAME } 
	Mod1.pstrDataTableName = {your Table Name}
	Mod1.pstrFieldName = {your Field Name}
    FillDropList cmboBox
End Sub

This is the code for the Public Sub to fill the list.

Public Sub FillDropList(cmbCombo As ComboBox)

	If Mod1.pblnDBOpen = False
		Set Mod1.DataWs = DBEngine.Workspaces(0)
		Set Mod1.DataDB = Mod1.DataWs.OpenDatabase("C:\" & _
                   Mod1.pstrDataFileName & ")
		Mod1.pblnDBOpen = True
	End If
	
	Set rsSource = Mod1.DataDB.OpenRecordset("SELECT * FROM " & _
          Mod1.pstrDataTableName & " ORDER BY " & _
          Mod1.pstrFieldName &",dbOpenDynaset)
	
	With cmbCombo
		rsSource.MoveFirst
		.Clear
		Do While Not rsSource.EOF
			.AddItem(rsSource(Mod1.pstrFieldName) 
			rsSource.MoveNext
		Loop
		.ListIndex = (.ListCount = 0)
	End With

	Mod1.DataDB.Close
	Mod1.pblnDBOpen = False
	
End Sub

Good Luck