Unending Cascading ComboBox Troubles...

[Amanduh]

Hi again, brilliant developers. I'm having serious issues with cascading comboboxes. I had it working perfectly before, but then was asked to add an additional variable and everything went haywire (surely due to my incompetence). I've printed out Rabbit's tutorial and read it many times, but like others, I feel I need a more elementary approach. Please let me explain:

I'm creating a database for entering information for research studies. These studies have multiple visits, which have multiple tests. What I'd like to do is create a form that allows you to only select a study from a drop-down box (which is using the table "Study" as a lookup, StudyID is the autonumber PK). Once the Study is selected, I would like for the Visit combobox to be populated by the table "Visit," which has a list of visits for the various studies (which also have their studyid's listed). VisitID is an autonumber PK here. Then, I'd like further refining with Tests, but in the exact same way. A big list of all the tests, bound to a specific visit of a specific study. Here's my current code:

Private Sub Study_AfterUpdate()
    With Me![Visit]
    If IsNull(Me!Study) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [Visit] " & _
                   "FROM Visit " & _
                   "WHERE [StudyID]=" & Me!Study
    End If
    Call .Requery
  End With
End Sub
 

Any advice anyone has would be really appreciated. Currently, when I load the form, I can select a study from the dropdown just fine, but when I go to the Visit dropdown, I get "Enter Parameter Value" for that study.

Thanks in advance!

[Rabbit]

Can you provide the following information?

Relevant table and field information in the following format:
TableName
Field1; Data Type; PK/FK
Field2; Data Type; PK/FK
etc.

[Amanduh]

Sure thing, Rabbit!

Study
Study Name, Text
StudyID, Autonumber, PK

Visit
StudyID, Number
Study, Text
Visit, Text
VisitID, Autonumber, PK

Tests
Study, Text
Visit Name, Text
Tests, Text
TestID, Autonumber, PK

[Rabbit]

Sure thing, Rabbit!

Study
Study Name, Text
StudyID, Autonumber, PK

Visit
StudyID, Number
Study, Text
Visit, Text
VisitID, Autonumber, PK

Tests
Study, Text
Visit Name, Text
Tests, Text
TestID, Autonumber, PK

Let me know if the following assumption is correct:
So it looks like you have a combo box for StudyID on the form. Let's call it cbo_StudyID. You select a StudyID from there.

The next combo box limits the choices by the StudyID. Let's call this one cbo_VisitID.

And the final combo box limits the choices by VisitID. This one will be cbo_Test.

First off, your tables should probably be designed like this:

Study
Study Name, Text
StudyID, Autonumber, PK

Visit
StudyID, Number, FK
Visit, Text
VisitID, Autonumber, PK

Tests
VisitID, Number, FK
Tests, Text
TestID, Autonumber, PK

What you want to do is set up two cascades. Treat each one separately. Study -> Visit and Visit -> Tests. Then you'll want to account for how changing StudyID will affect Tests.

[Amanduh]

Let me know if the following assumption is correct:
So it looks like you have a combo box for StudyID on the form. Let's call it cbo_StudyID. You select a StudyID from there.

The next combo box limits the choices by the StudyID. Let's call this one cbo_VisitID.

And the final combo box limits the choices by VisitID. This one will be cbo_Tests.

Actually, the combo box on the form is for Study (the text name). I was just going to autogenerate the StudyID numbers as I put new studies in the table. Does that make a difference?


Okay, I set up my tables the way you suggested. Also, I created a relationship between Study.StudyID and Visit.StudyID (with the "1" next to Study.StudyID and the infinity sign next to Visit.StudyID). In the Relationship's Properties, "Enforce Referential Integrity" and "Cascade Update Related Fields" are checked. Here's my SQL source code:

SELECT Visit.VisitID, Visit.StudyID, Visit.Visit FROM Study INNER JOIN Visit ON Study.Study=Visit.StudyID WHERE (((Visit.StudyID)=Forms!labdatabase!Study)); 
 

It's still not working, however. I'm getting the "Enter Parameter Value" error still.

[Rabbit]

Actually, the combo box on the form is for Study (the text name). I was just going to autogenerate the StudyID numbers as I put new studies in the table. Does that make a difference?


Okay, I set up my tables the way you suggested. Also, I created a relationship between Study.StudyID and Visit.StudyID (with the "1" next to Study.StudyID and the infinity sign next to Visit.StudyID). In the Relationship's Properties, "Enforce Referential Integrity" and "Cascade Update Related Fields" are checked. Here's my SQL source code:

SELECT Visit.VisitID, Visit.StudyID, Visit.Visit FROM Study INNER JOIN Visit ON Study.Study=Visit.StudyID WHERE (((Visit.StudyID)=Forms!labdatabase!Study)); 
 

It's still not working, however. I'm getting the "Enter Parameter Value" error still.

You should probably join the Study.StudyID. If you designed it like I laid out there's no longer a field called Study.

Where is this SQL? In a query? As the row source for a combo box?

What are the controls on your form? By which I mean what are the names of the relevant combo boxes, text boxes etc.
What is the record source of your form?
For the combo boxes, what is the control source?

[Amanduh]

You should probably join the Study.StudyID. If you designed it like I laid out there's no longer a field called Study.

Where is this SQL? In a query? As the row source for a combo box?

What are the controls on your form? By which I mean what are the names of the relevant combo boxes, text boxes etc.
What is the record source of your form?
For the combo boxes, what is the control source?

Okay, fixed that inconsistency problem.

The SQL is in Visit's Combo Box row source.

The relevant controls are Study (combo box), Visit (combo box), and Tests (combo box). There are other fields but those are free type and have no bearing on these three, and there is an "Enter Record" button, but that would be used well after this event.

My record source is a table - "labdatabase"

The control sources for the combo boxes are tables of the same name (Study for combo box "Study," Visit for combo box "Visit," and Tests for combo box "Table." I'm currently just populating the tables by hand, but making sure that all things are typed consistently throughout (StudyID, etc).

[Rabbit]

Okay, fixed that inconsistency problem.

The SQL is in Visit's Combo Box row source.

The relevant controls are Study (combo box), Visit (combo box), and Tests (combo box). There are other fields but those are free type and have no bearing on these three, and there is an "Enter Record" button, but that would be used well after this event.

My record source is a table - "labdatabase"

The control sources for the combo boxes are tables of the same name (Study for combo box "Study," Visit for combo box "Visit," and Tests for combo box "Table." I'm currently just populating the tables by hand, but making sure that all things are typed consistently throughout (StudyID, etc).

So in table labdatabase there are fields named: Study, Visit, and Tests? They're all numeric foreign keys for the three related tables?

I'm sure this was just a typo but the control sources should not be a table, but one of the fields in table labdatabase. And the last combo box's control source isn't Table but Tests.

Is this all correct?

[Amanduh]

So in table labdatabase there are fields named: Study, Visit, and Tests? They're all numeric foreign keys for the three related tables?

I'm sure this was just a typo but the control sources should not be a table, but one of the fields in table labdatabase. And the last combo box's control source isn't Table but Tests.

Is this all correct?

Sorry, it had been a long day. You're right, it's Tests not Table.

The labdatabase table has the controls Study (Text), Visit (Text), and Tests (Text). Entry Number is the PK, that's an autonumber. It's the only field that wouldn't be redundant.

The corresponding combo boxes (upon hitting the command button) populate this "labdatabase" table, but no other relationships have been made with this table.

Thanks again for all your help.

[Amanduh]

Okay, I think I've actually fixed the original problem. The Study -> Visit cascade works brilliantly. However, I'm running into trouble with the second cascade. I think the problem stems from binding columns. I have to bind the first column (StudyID) to make it sync with the first cascade, but I have to bind it to the third column (VisitID) to make it sync with the second cascade (Visit -> Tests). Any advice?

[Rabbit]

Okay, I think I've actually fixed the original problem. The Study -> Visit cascade works brilliantly. However, I'm running into trouble with the second cascade. I think the problem stems from binding columns. I have to bind the first column (StudyID) to make it sync with the first cascade, but I have to bind it to the third column (VisitID) to make it sync with the second cascade (Visit -> Tests). Any advice?

I'm not sure I follow. Each combobox should be binded to their own fields. Each cascade is set up separately. And then you account for the first column affecting the third column.

Private Sub cbo1_AfterUpdate()
   Me.cbo2.RowSource = "SELECT * FROM tbl_2 WHERE ID1 = " & Me.cbo1
   Me.cbo3 = Null
End Sub
 
Private Sub cbo2_AfterUpdate()
   Me.cbo3.RowSource = "SELECT * FROM tbl_3 WHERE ID2 = " & Me.cbo2
End Sub
 

Plus you need the corresponding On Current code.

But basically, line 2 is cascade 1, line 7 is cascade 2, and line 3 is how I want to handle the interaction between column 1 and column 2.

[Amanduh]

I'm not sure I follow. Each combobox should be binded to their own fields. Each cascade is set up separately. And then you account for the first column affecting the third column.

Private Sub cbo1_AfterUpdate()
   Me.cbo2.RowSource = "SELECT * FROM tbl_2 WHERE ID1 = " & Me.cbo1
   Me.cbo3 = Null
End Sub
 
Private Sub cbo2_AfterUpdate()
   Me.cbo3.RowSource = "SELECT * FROM tbl_3 WHERE ID2 = " & Me.cbo2
End Sub
 

Plus you need the corresponding On Current code.

But basically, line 2 is cascade 1, line 7 is cascade 2, and line 3 is how I want to handle the interaction between column 1 and column 2.

Here's my current code, based off your (really awesome) article:

Private Sub Study_AfterUpdate()
    With Me![Visit]
        If IsNull(Me!Study) Then
            .RowSource = ""
        Else
            .RowSource = "SELECT [Visit] " & _
                         "FROM TblVisit " & _
                         "WHERE [StudyID]=" & Me!Study
        End If
        Call .Requery
    End With
End Sub
 

That is just for the first cascade; the second cascade is similar but with Visit instead of Study and Tests instead of Visit. Should I change the code to what you've done here instead?

[Rabbit]

Here's my current code, based off your (really awesome) article:

Private Sub Study_AfterUpdate()
    With Me![Visit]
        If IsNull(Me!Study) Then
            .RowSource = ""
        Else
            .RowSource = "SELECT [Visit] " & _
                         "FROM TblVisit " & _
                         "WHERE [StudyID]=" & Me!Study
        End If
        Call .Requery
    End With
End Sub
 

That is just for the first cascade; the second cascade is similar but with Visit instead of Study and Tests instead of Visit. Should I change the code to what you've done here instead?

No, mines was just a simplified example. But if you have this, then there's no problem setting up the same thing for the other combo box.

Private Sub Visit_AfterUpdate()
    With Me![Test]
        If IsNull(Me!Visit) Then
            .RowSource = ""
        Else
            .RowSource = "SELECT [Tests] " & _
                         "FROM TblTests " & _
                         "WHERE [VisitID]=" & Me!Visit
        End If
        Call .Requery
    End With
End Sub
 

[Amanduh]

Okay, that coding works fine, the problem is that I can't do both cascades in one go.

Here's how it should go: I pick a study, then a visit that is only available for that study, then a set of tests that are only available for that visit for that study.

Although I can see the qualifying visits in the Visit combobox afterupdate, I can't populate the field with a visit unless the bound column is 1 (the studyID column). With this, I can't go any further because then when I set the Tests combobox bound column to 1 (visitID), I get this error: "Syntax error (missing operator) in query expression '[VisitID]=Day 1'. (Day 1 is one of the visits.)

I'm sure I'm not seeing something properly because I don't have a strong Access foundation. Can you see where I'm going astray? If it would help I could send you the actual database.

[Rabbit]

The problem is that your VisitID is text.

With text, you have to surround it with single quotes.

"VisitID = " & "'" & Me.Visit & "'"
 

[Amanduh]

The problem is that your VisitID is text.

With text, you have to surround it with single quotes.

"VisitID = " & "'" & Me.Visit & "'"
 

The control Visit is text, but the VisitID is the autonumber in TblVisit (and then is a number in TblTests). It won't let me do that, every time I try to just do quotes it says

Compile Error:
Expected: End of Statement

Is that maybe because it is trying to refer to a number instead of text?

[Rabbit]

The control Visit is text, but the VisitID is the autonumber in TblVisit (and then is a number in TblTests). It won't let me do that, every time I try to just do quotes it says

Compile Error:
Expected: End of Statement

Is that maybe because it is trying to refer to a number instead of text?

The data types have to match. The combo box needs to return the ID number you're trying to cascade. It can display the text if you want but it has to be bound to the ID column. Which means the corresponding control source should match the ID column as well.

[Amanduh]

The data types have to match. The combo box needs to return the ID number you're trying to cascade. It can display the text if you want but it has to be bound to the ID column. Which means the corresponding control source should match the ID column as well.

So an autonumber can't sync up to a number? I'm not sure I understand, sorry.

[Rabbit]

An autonumber can sync up to a number, they are the same data type. But you just said that your Visit combo box is returning a string. But that your VisitID is a number. You can't match a number to text. You have to convert one to match the data type of the other.

[Amanduh]

An autonumber can sync up to a number, they are the same data type. But you just said that your Visit combo box is returning a string. But that your VisitID is a number. You can't match a number to text. You have to convert one to match the data type of the other.

How can I convert while still showing the end user all the necessary info (study name, visit name)? Sorry for the delay in response, I was shifted to an emergency other dealie until yesterday. :)