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 - "labdatabas e"

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 - "labdatabas e"

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 "labdatabas e" 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?