469,951 Members | 2,571 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,951 developers. It's quick & easy.

Unending Cascading ComboBox Troubles...

23
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Study_AfterUpdate()
  2.     With Me![Visit]
  3.     If IsNull(Me!Study) Then
  4.       .RowSource = ""
  5.     Else
  6.       .RowSource = "SELECT [Visit] " & _
  7.                    "FROM Visit " & _
  8.                    "WHERE [StudyID]=" & Me!Study
  9.     End If
  10.     Call .Requery
  11.   End With
  12. End Sub
  13.  
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!
Oct 22 '07 #1
19 2079
Rabbit
12,516 Expert Mod 8TB
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.
Oct 22 '07 #2
Amanduh
23
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
Oct 22 '07 #3
Rabbit
12,516 Expert Mod 8TB
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.
Oct 22 '07 #4
Amanduh
23
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT Visit.VisitID, Visit.StudyID, Visit.Visit FROM Study INNER JOIN Visit ON Study.Study=Visit.StudyID WHERE (((Visit.StudyID)=Forms!labdatabase!Study)); 
  2.  
It's still not working, however. I'm getting the "Enter Parameter Value" error still.
Oct 22 '07 #5
Rabbit
12,516 Expert Mod 8TB
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT Visit.VisitID, Visit.StudyID, Visit.Visit FROM Study INNER JOIN Visit ON Study.Study=Visit.StudyID WHERE (((Visit.StudyID)=Forms!labdatabase!Study)); 
  2.  
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?
Oct 22 '07 #6
Amanduh
23
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).
Oct 22 '07 #7
Rabbit
12,516 Expert Mod 8TB
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?
Oct 22 '07 #8
Amanduh
23
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.
Oct 23 '07 #9
Amanduh
23
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?
Oct 25 '07 #10
Rabbit
12,516 Expert Mod 8TB
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cbo1_AfterUpdate()
  2.    Me.cbo2.RowSource = "SELECT * FROM tbl_2 WHERE ID1 = " & Me.cbo1
  3.    Me.cbo3 = Null
  4. End Sub
  5.  
  6. Private Sub cbo2_AfterUpdate()
  7.    Me.cbo3.RowSource = "SELECT * FROM tbl_3 WHERE ID2 = " & Me.cbo2
  8. End Sub
  9.  
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.
Oct 25 '07 #11
Amanduh
23
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cbo1_AfterUpdate()
  2.    Me.cbo2.RowSource = "SELECT * FROM tbl_2 WHERE ID1 = " & Me.cbo1
  3.    Me.cbo3 = Null
  4. End Sub
  5.  
  6. Private Sub cbo2_AfterUpdate()
  7.    Me.cbo3.RowSource = "SELECT * FROM tbl_3 WHERE ID2 = " & Me.cbo2
  8. End Sub
  9.  
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Study_AfterUpdate()
  2.     With Me![Visit]
  3.         If IsNull(Me!Study) Then
  4.             .RowSource = ""
  5.         Else
  6.             .RowSource = "SELECT [Visit] " & _
  7.                          "FROM TblVisit " & _
  8.                          "WHERE [StudyID]=" & Me!Study
  9.         End If
  10.         Call .Requery
  11.     End With
  12. End Sub
  13.  
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?
Oct 25 '07 #12
Rabbit
12,516 Expert Mod 8TB
Here's my current code, based off your (really awesome) article:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Study_AfterUpdate()
  2.     With Me![Visit]
  3.         If IsNull(Me!Study) Then
  4.             .RowSource = ""
  5.         Else
  6.             .RowSource = "SELECT [Visit] " & _
  7.                          "FROM TblVisit " & _
  8.                          "WHERE [StudyID]=" & Me!Study
  9.         End If
  10.         Call .Requery
  11.     End With
  12. End Sub
  13.  
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Visit_AfterUpdate()
  2.     With Me![Test]
  3.         If IsNull(Me!Visit) Then
  4.             .RowSource = ""
  5.         Else
  6.             .RowSource = "SELECT [Tests] " & _
  7.                          "FROM TblTests " & _
  8.                          "WHERE [VisitID]=" & Me!Visit
  9.         End If
  10.         Call .Requery
  11.     End With
  12. End Sub
  13.  
Oct 25 '07 #13
Amanduh
23
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.
Oct 25 '07 #14
Rabbit
12,516 Expert Mod 8TB
The problem is that your VisitID is text.

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

Expand|Select|Wrap|Line Numbers
  1. "VisitID = " & "'" & Me.Visit & "'"
  2.  
Oct 25 '07 #15
Amanduh
23
The problem is that your VisitID is text.

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

Expand|Select|Wrap|Line Numbers
  1. "VisitID = " & "'" & Me.Visit & "'"
  2.  
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?
Oct 26 '07 #16
Rabbit
12,516 Expert Mod 8TB
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.
Oct 26 '07 #17
Amanduh
23
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.
Oct 26 '07 #18
Rabbit
12,516 Expert Mod 8TB
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.
Oct 26 '07 #19
Amanduh
23
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. :)
Nov 9 '07 #20

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by JMosey | last post: by
4 posts views Thread by Strahimir Antoljak | last post: by
4 posts views Thread by pmcguire | last post: by
6 posts views Thread by visionstate | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.