Combo Box has right amount of values, but they aren't showing up

[Matzer]

Hi!



I'm sure this question has been answered before. I've searched for about 2 weeks on how to fix this problem and I just can't figure it out. I've started over so many times.

I'm creating a form where people can choose something in the first combo box [ComboBox1] which results in the second combo box [ComboBox2] displaying only the items that are associated with the first combo box [ComboBox1].

In my second combo box I have the amount of "empty lines" that equals with the items associated with the first combo box. But there is nothing displaying. No words.

[ItsMe]

Can you post the Rowsouce and the Column Width of your combobox?

[Matzer]

Combo 1


Rowsource: SELECT tblPortfolio.ID, tblPortfolio.[PortfolioName] FROM tblPortfolio ORDER BY tblPortfolio.[PortfolioName];
Column Width: 0cm;2.54cm


Combo 2:


RowSource:SELECT tblDepartment.ID, tblDepartment.DepartmentName, tblDepartment.Portfolio FROM tblDepartment WHERE (((tblDepartment.Portfolio)=Forms![Combo Form]!Portfolio)) ORDER BY tblDepartment.DepartmentName;
Column Width: 0cm;7.223cm;0cm

[ItsMe]

Without using VBA I do not see another way other than changing the Bound column of Combo 1. Change the property of Combo 1's "Bound Column to equal 2

I recomend this if it does not impact how your form functions. If you need the ID column in another procedure, you could get it using
Me.Combo_1.Collumn(0)
in VBA

[Matzer]

I've got this going in VBA for Combo 1 After Update (I've tried so many things, I think I got this part on the Microsoft site: http://msdn.microsoft.com/en-us/libr...ffice.12).aspx) )

Private Sub Combo1_AfterUpdate()
' Update the row source of the Combo2 combo box
' when the user makes a selection in the Combo1
' combo box.
Me.Combo2.RowSource = "SELECT DepartmentName FROM" & _
" tblDepartment WHERE Portfolio = " & _
Me.Combo1 & _
" ORDER BY DepartmentName"

Me.Combo2 = Me.Combo2.ItemData(0)

End Sub

[ItsMe]

I now see a problem in the Original Row Source property you posted. The combo name was not in there. So you would have to change bound column for combo1, as sugested, and paste this into the rowsource of Combo2

RowSource:SELECT tblDepartment.ID, tblDepartment.DepartmentName, tblDepartment.Portfolio FROM tblDepartment WHERE (((tblDepartment.Portfolio)=Forms![Combo Form]!Combo1)) ORDER BY tblDepartment.DepartmentName;

Considering the Row Source you first posted, the VBA option would be to build your SQL string like this

Private Sub Combo1_AfterUpdate()
' Update the row source of the Combo2 combo box
' when the user makes a selection in the Combo1
' combo box.

Me.Combo2.RowSource = "SELECT tblDepartment.ID, tblDepartment.DepartmentName, tblDepartment.Portfolio" & _
                      " FROM tblDepartment WHERE tblDepartment.Portfolio)='" & Me.Combo1.Column(2) & "'" & _
                      " ORDER BY tblDepartment.DepartmentName;"

End Sub

[Matzer]

Thanks! I think we may get there soon!
As soon as I try to bring the combo box 2 down, I now get:
"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
So I put this:
RowSource:SELECT tblDepartment.ID, tblDepartment.DepartmentName, tblDepartment.Portfolio FROM tblDepartment WHERE ((tblDepartment.Portfolio)=Forms![Combo Form]!Combo1) ORDER BY tblDepartment.DepartmentName;

[ItsMe]

If you are using VBA to assign the RowSource, you can delete all of the text in the property window for the Row Source field. It is not needed unless you want a default Row Source to be applied in conjunction with the VBA event handler.

[June7]

Are these comboboxes on the same form? Is tblDepartment Portfolio field the foreign key field for the primary key ID field from tblPortfolio? Is this a datasheet or continuous view form?

Consider:

Combo1:

Rowsource: SELECT ID, PortfolioName FROM tblPortfolio ORDER BY PortfolioName;
Column Width: 0cm;2.54cm
BoundColumn: 1
AfterUpdate event procedure code: Me.Combo2.Requery

Combo 2:

RowSource: SELECT ID, DepartmentName FROM tblDepartment WHERE Portfolio=[Combo1] ORDER BY DepartmentName;
Column Width: 0cm;7.223cm