Access 2010 - Cascading Combo Boxes

[Abdullah]

Hello,

I am working to put together a database, it's still early in the process, but I am hitting a snag.

As part of it, I have a table called tblobservations, a sample of it's data is below:

ID Component Condition
1 Window Sill
2 Window Head
3 Window Stained
4 Balcony Flashing
5 Vent Seam
6 Vent Damage

I am trying to achieve a form with 2 combo boxes, where you'll choose a component from combo box 1, and that selection will then influence the selections available in combo box 2.

Currently, I have combo box 1 named Component and in it's property sheet under Data - ROW Source, I have;

SELECT DISTINCT [tblconditions].[component], [tblconditions].[component] FROM tblconditions;

Pardon the use of VBA tags.

Combo box 1 is working, the drop down gives the 3 options, Window, Balcony, Vent (no duplicate components appear in drop down).

I believe that the second part of it, requires Combo box 1 to have an [Event Procedure] in the Event tab - After Update line. That is where I'm having trouble.

I was given an example, but am not sure it actually works. It has been changed only in regard to the table/combo designations, so that it would fit my project.


Option Compare Database
Option Explicit

Private Sub ComboComponent_AfterUpdate()

ComboCondition.RowSource = "SELECT tblobservations.condition " & _
"FROM tblobservations" & _
"WHERE tblobservations.condition = '" & ComboComponent.Value & "' " &_
"ORDER BY tblobservations.condition;"
End Sub


The problem is I keep getting errors with the VB code, or nothing happens at all. The condition (Combo box 2) remains blank in my form regardless of my selection in Combo box 1.

Any help, either getting the code to work by telling combo box 2 which selection was made, or another method to achieve my goal would be greatly appreciated.

[hansup]

In ComboComponent_AfterUpdate, your SELECT statement joins together tblobservations and WHERE without a space between them.

Whenever you build a SQL statement, use a variable to hold it. Then you can use Debug.Print to view the completed string in the Immediate Window (rather than trying to imagine what it should look like).

Try this version of your procedure.

Private Sub ComboComponent_AfterUpdate()
Dim strSql As String
strSql = "SELECT o.condition" & vbCrLf & _
"FROM tblobservations AS o" & vbCrLf & _
"WHERE o.condition = '" & ComboComponent.Value & "'" & vbCrLf & _
"ORDER BY o.condition;"
Debug.Print strSql
ComboCondition.RowSource = strSql
End Sub

[Abdullah]

Hansup,

That worked....thank you so much, I was having a bear of a time wrestling with that before your help.

[Abdullah]

Actually seems like I spoke slightly too soon.

My form actually has 3 sets of those combo boxes...so that 3 entries can be made at one time.

I used the code, and it works on the first set, but not the second or third. I changed variable names so they are all unique, but the second combo box for both sets 2 and 3 remain blank.

I'm not sure what is going on.

[Abdullah]

It seems like the 2nd combo box, ends up with the correct number of entry lines, depending on what is chosen in the 1st combo box.

However, no actual text appears in the lines. I thought it was because the box was too narrow, but after making it wider, the same condition persists. Where the number of lines in combo box 2 is correct, but no text appears in those lines.

[Abdullah]

Sure do feel silly, but text was not being displayed because the column width was set to 0.

Got it working...thank you again