Based combobox

[Msjunior90]

Hi,

I'm having a problem that I would like to have help in. I am developing a userform in Word, where I have two Combobox. Both Comboboxes reads data information from an Access Database 2010 table. In my Table I have a Field with different names, and in the other Field I have undersubject to the first Field.

It looks like this.

Filed1 Field2
CompanyA Toys
CarToys
Dolls

So the meaning of the table is that Field2 shpuld be connected to Field 1. Now my code that reads from the databse looks like this
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

Set db = OpenDatabase("C:\Users\company.accdb")
'Define the first recordset
Set rs = db.OpenRecordset("SELECT Field FROM FieldTable")
'Determine the number of records in the recordset
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

cboField.ColumnCount = rs.Fields.Count
'Load the ListBox with the retrieved records
cboField.Column = rs.GetRows(NoOfRecords)
'Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
lbl_Exit:
Exit Sub

It reads everything from Field one and itworks!

Now the problem is when I'm trying to read from the other field, so if CompanyA is selected in combobox1 I want Toys, cars dolls to show up in combobox2. And it doesn't, all that is showing up is only Toys and not the rest. What am I doing wrong, please help me :/

The second code looks like this

Private Sub cboField_Change()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
Dim strReport As String

cboReport.Clear

'Open the .accdb form database to retrieve data
Set db = OpenDatabase("C:\Users\company.accdb")


'Define the first recordset
Set rs = db.OpenRecordset("SELECT Toys FROM FieldTable WHERE Field ='" & cboField & "'")
'Determine the number of records in the recordset
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
'Set the number of ListBox columns = number of fields in the recordset
cboReport.ColumnCount = rs.Fields.Count
'Load the ListBox with the retrieved records
cboReport.Column = rs.GetRows(NoOfRecords)
'Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
lbl_Exit:

Exit Sub




End Sub

[jonh]

You've only set CompanyA for Toys and not the other two.

Filed1 Field2
CompanyA Toys
CompanyA CarToys
CompanyA Dolls

That would list three CompanyA's so you'd need to use SELECT DISTINCT in your first query.

[Msjunior90]

You've only set CompanyA for Toys and not the other two.

Filed1 Field2
CompanyA Toys
CompanyA CarToys
CompanyA Dolls

That would list three CompanyA's so you'd need to use SELECT DISTINCT in your first query.


Thank you for the help. but it is still only showing the first one or no one.

[jonh]

I don't know then. Debug your values.

I tested this in excel and it worked ok.


Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\test.accdb;"
conn.Open
Set rs = CreateObject("ADODB.Recordset")
rs.ActiveConnection = conn

rs.Open "select a from table1"
cboField.Column = rs.GetRows(rs.RecordCount)

rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

[Msjunior90]

Ok its workin now! Byt the problem I have now is that it writes out <div> between every object, this is because I putt all in one Cell can I fix that?

[jonh]

You lost me.

What objects? What cells?

I only know <div> as an HTML element.

[Msjunior90]

This is how the database looks like.

So when the combbox gets Object it show up like this <div>Toys &nsbp Cars...</div>. And when entering it into the values all are written out.12285

[jonh]

If toys, cars and dolls are separate things that need to be individually selectable, you need a record for each one as I said in post #2.
What you have there is not proper table design.