I've cascaded combo boxes in other databases, but this for some reason won't work for me. I'm a newbie when it comes to Access so I'm not quite sure on how to go about looking where I'm messing up.
Right now I've just tried to sync the cboArea with the cboBone, but when I choose the cboArea, nothing comes up for the cboBone. I'm really confused because I've used the exact same code in another db.
Here is the short answer to your problem:
Your SQL statement is trying to compare string values (Bones) to an ID value. As such, your SQL returns nothing and thus your second combo box displays nothing. Modify your SQL statement to account for the text display value in the first combo box.
Here is the long answer:
Let's walk through this process so you can learn some debugging tips to help yourself with these types of issues in the future.
Here is your existing code:
Code:
Private Sub cboArea_AfterUpdate()
' Update the row source of the cboBone combo box
' when the user makes a selection in the cboArea
' combo box.
Me.cboBone.RowSource = "SELECT Bone FROM" & _
" tblBone WHERE Area = " & _
Me.cboArea & _
" ORDER BY ID"
Me.cboBone = Me.cboBone.ItemData(0)
End Sub
Now when you select a value in your first combo box (cboArea) it's not clear what Access is doing or not doing as the case may be, correct? Let's add some additional code to find out.
I'd like you to copy and paste this revised code in place of your existing code:
Code:
Private Sub cboArea_AfterUpdate()
' Update the row source of the cboBone combo box
' when the user makes a selection in the cboArea combo box.
' SQL rowsource to build for cboBone
Dim strSQL As String
' Define the SQL to use based on what the user selected in the cboArea combo box
strSQL = "SELECT Bone FROM" & _
" tblBone WHERE Area = " & _
Me.cboArea & _
" ORDER BY ID"
' Open a message box and display the completed SQL we created
MsgBox strSQL
' Set the rowsource now of cboBone based on completed SQL
Me.cboBone.RowSource = strSQL
' Select the first value in the drop down list
Me.cboBone = Me.cboBone.ItemData(0)
End Sub
What I've done is several things:
1. Add in some additional comments (optional, but I like lots of comments)
2. Instead of directly setting the row source, I've added an additonal step of first assigning the SQL to a string variable which we'll use later to set the row source.
3. I added a MessageBox action to display the generated SQL value so you can understand what Access is trying to do. (We'll remove this later.)
Note, you can also use Debug.Print to print this value out to the Immediate Window if you want. Either way is fine for this simple debugging purpose.
Please copy that code, compile the code, and then save the form changes.
Next, open your form in regular form view and select a value from the first cboArea combo box.
You should see a message box appear with text like this:
SELECT Bone FROM tblBone WHERE Area = 1 ORDER BY ID
Humm....do you see any issues with that?
While it might look good in terms of syntax, let's think about this for a moment. This SQL is telling Access to go find records in tblBone (fine so far) where Area (still fine so far) = 1 and then order the records by ID.
Wait, Area = 1?
That doesn't seem right does it?
Open your tblBone table and take a look at your fields. Your table has ID, Bone, and Area fields. Your Area field has text values like Upper Extremity, Lower Extremity, etc.
So does it make sense to say, for example, "Where Area = 1"? No, it should say something like "Where Area = Upper Extremity"
Ahh haaa, now we're getting somewhere. Access is doing exactly what you asked. It's trying to find records in tblBone where the Area field matches some number. Access returns no matches and thus nothing is displayed in the second combo box.
So the next step is, "Where is Access getting this ID value? Shouldn't it be looking for the text I selected in the first combo box?"
Well, now let's look carefully at the first combo box. Your control source is based directly on tblArea which has two fields - ID and Area. You have chosen to hide the ID value by setting the column widths to 0";2" and the first column is the bound one.
That setup is fine, however, let's take a look at your code again here:
Code:
' Define the SQL to use based on what the user selected in the cboArea combo box
strSQL = "SELECT Bone FROM" & _
" tblBone WHERE Area = " & _
Me.cboArea & _
" ORDER BY ID"
You're using Me.cboArea in there which means Access is going to use the ID column of tblArea and not the Area field. Even though you can't see the ID value in the drop down for the first combo box, Access is going to use that value since it is the bound column.
Ok, so how do you grab the display value in that combo box?
You can reference the Column property of the combo box to grab the second column. Note, the Column property is zero based so if you want to grab the second column, use 1. Modify your strSQL to this:
Code:
' Define the SQL to use based on what the user selected in the cboArea combo box
strSQL = "SELECT Bone FROM" & _
" tblBone WHERE Area = " & _
Me.cboArea.Column(1) & _
" ORDER BY ID"
Notice we are now using the Column property.
Compile the code again and save the form changes.
Open the form again and select the first value in the first combo box.
You'll see a message box displaying:
SELECT Bone FROM tblBone WHERE Area = Upper Extremity ORDER BY ID
Now we're getting somewhere right? This looks more like what we would expect. Area = Upper Extremity. If you close the message box and drop down the second combo box, however, you still won't see any values. Why?
Well, we've solved the first problem, but now we've encountered a second problem. By adding in some debugging message boxes (or Debug.Print lines) we can slowly chip away and experiment to uncover the issues and try things out.
Our message box shows that it's getting the right value, but Upper Extremity is a text value and this means we need to wrap that value in quotation marks in order for the SQL to work. We need it to look like this: Area = "Upper Extremity"
In order to do this, you have to wrap extra quote marks around the value there in the SQL with VBA. Change your code to the following:
Code:
strSQL = "SELECT Bone FROM tblBone WHERE Area =" & "'" & Me.cboArea.Column(1) & "'" & " ORDER BY ID"
You'll notice I put that all one line for clarity. If you look carefully, I've wrapped the value of the combo box in extra quotes. Instead of the single ' in between two double quotes, I could have also used four quotes instead.
Compile this code, save the form changes, and select a value in the first combo box one more time. Your message box should now display:
SELECT Bone FROM tblBone WHERE Area ='Upper Extremity' ORDER BY ID
See the quotes now around Upper Extremity?
Dismiss the message box and observe what happens to the second combo box. It works! You should now finally see values in the second combo box.
Now that we have it working, you are safe to remove the message box line since you are now finished debugging. Your final code should look something like:
Code:
Private Sub cboArea_AfterUpdate()
' Update the row source of the cboBone combo box
' when the user makes a selection in the cboArea combo box.
' SQL rowsource to build for cboBone
Dim strSQL As String
' Define the SQL to use based on what the user selected in the cboArea combo box
strSQL = "SELECT Bone FROM tblBone WHERE Area =" & "'" & Me.cboArea.Column(1) & "'" & " ORDER BY ID"
' Set the rowsource now of cboBone based on completed SQL
Me.cboBone.RowSource = strSQL
' Select the first value in the drop down list
Me.cboBone = Me.cboBone.ItemData(0)
End Sub
Compile the code changes one last time, save the form changes, and then take it for a test drive. You should see the second bones combo box correctly display the dependent values based on the first area combo box.
You're all set. Give yourself a high five for a job well done.
Hope that helps,
--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation
----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.mspx
----------
