PDA

View Full Version : Error while populating a combobox from Access database



shivboy
06-29-2006, 10:25 AM
Hi,

I coded the following Sub to populate a combobox / listbox but it is generating a Type mismatch error.:banghead:


Sub fillBox(ByVal oForm As Form, ByVal oList As Object, ByVal oField As String, ByVal oTab As String)
dbPath = "C:\abc.mdb"
Dim sql As String
sql = "SELECT " & oField & " FROM " & oTab
Set con = New ADODB.Connection
With con
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open dbPath
End With
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
rs.Open Source:=sql, ActiveConnection:=con, CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, Options:=adCmdText
Do Until rs.EOF
oForm.Controls(oList).AddItem rs(1)
rs.MoveNext
Loop
rs.Close
con.Close
End Sub

When I call the above Sub, I use the following code:


Call fillBox(myForm, myCombo, "sName", "tblState")

Now, where exactly am I going wrong? Please help.

Peace,

Shivboy

lucas
06-29-2006, 10:39 AM
Don't know much about access but many here can help. Did you set a reference?

From a search of the Excel forum: search titles for access
http://www.vbaexpress.com/forum/showthread.php?t=8410&highlight=access

http://www.vbaexpress.com/forum/showthread.php?t=7625&highlight=access

shivboy
06-29-2006, 12:13 PM
I need help with passing the object name as a parameter because that is where the error is occurring. When I pass the name of the form, it accepts it, but when I pass the name of the combo box, it generates the error. Where exactly am I going wrong?




Don't know much about access but many here can help. Did you set a reference?

From a search of the Excel forum: search titles for access
http://www.vbaexpress.com/forum/showthread.php?t=8410&highlight=access

http://www.vbaexpress.com/forum/showthread.php?t=7625&highlight=access

Ken Puls
06-29-2006, 12:19 PM
This is untested, but I would try specifiying your object a little more explicitly:


Sub fillBox(oList As MSForms.Combobox, ByVal oField As String, ByVal oTab As String)
dbPath = "C:\abc.mdb"
Dim sql As String
sql = "SELECT " & oField & " FROM " & oTab
Set con = New ADODB.Connection
With con
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open dbPath
End With
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
rs.Open Source:=sql, ActiveConnection:=con, CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, Options:=adCmdText
Do Until rs.EOF
oList.AddItem rs(1)
rs.MoveNext
Loop
rs.Close
con.Close
End Sub

And from your calling userform:
Dim cBox as msforms.combobox
Set cBox as me.Combobox1
Call fillBox(cbox, "sName", "tblState")

BTW, I also edited your post to use our VBA tags over the board code tags. They make things a little more readable. :)

HTH,