Populating a DataCombo Box 1

[rickkcir]

I am having trouble populating a datacombo box. I have two datacombo boxes, datacombo1 & datacombo2. Datacombo2's data is based on what is chosen in datacombo1. I have made the recordset, but now I need to get the datacombo box filled with the recordset. How do I fill datacombo2 with the data that I just got out of the table using the sql statement? Thanks for any help!

Here is the code I am using:

Private Sub DataCombo1_Change()

Dim sql As String
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
Dim rs As ADODB.Recordset

Dim selection As String
selection = Me.DataCombo1

Set rs = New ADODB.Recordset

cnn.CursorLocation = adUseClient

cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\Rick\VB Project\Reports.mdb;Persist Security Info=False"

sql = "Select Subcategory from tbl_SubCategory where Category = ' " & selection & " ';"

rs.Open sql, cnn, , adLockOptimistic


End Sub

 

[UncleT]

Try the following after you open the recordset.

datacombo2.clear

while not rs.eof
datacombo2.additem (rs.fields("Subcategory&quot.value
rs.movenext
wend



Hope this helps.

 

[rickkcir]

Thanks for the response...I appreciate it.

But....it doesn't seem to be recognizing the clear command or the additem command. Any ideas?

Thanks again.

 

[UncleT]

What type of combo box are you using?

 

[rickkcir]

It is a DataCombo Box

 

[UncleT]

Can you use the basic combobox that is standard with visual basic? If so the code provided should work.

 

[UncleT]

rickkcir

Sorry I misunderstood the question.

try the following after you create the recordset


Set datacombo2.RowSource = rs
datacombo2.ListField = rs.Fields(0).Name

This should work.

 

[rickkcir]

Thank you very much for helping me out.

It's still not working though, should I maybe have this in the datacombo2_click perhaps?

Here is my whole code:

Private Sub DataCombo1_Change()

Dim sql As String
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
Dim rs As ADODB.Recordset

Dim selection As String
selection = Me.DataCombo1

Set rs = New ADODB.Recordset

cnn.CursorLocation = adUseClient

cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\Rick\VB Project\Reports.mdb;Persist Security Info=False"

sql = "Select Subcategory from tbl_SubCategory where Category = ' " & selection & " ';"

rs.Open sql, cnn, , adLockOptimistic

Set DataCombo2.RowSource = rs
DataCombo2.ListField = rs.Fields(0).Name

End Sub

 

[UncleT]

Is the DataCombo1_Change event getting fired. Use a breakpoint an see if it is getting fired. If it is I am not sure what is happening. If it is not you may need to put all the code in the DataCombo1_Click event.

 

[rickkcir]

Ok, the DataCombo1_Change event is getting fired. I put in a variable to count the records, i.e.:

dim var as integer
var = rs.recordcount

and I can see that var = 0, which is quite bewildering as my sql statement is very simple and straightforward.

Anyhow, thanks again for the help...I'm sure it will work once I actually start pulling some data.

 

[rickkcir]

It does work perfectly now that I got the bug out of my sql statement (I had a space between my single quotes and double quotes when I was using the stored variable).

UncleT, thank you very much for you help...it is appreciated.