?
Solved

Trouble with Cascading Lists in Access

Posted on 2007-07-22
18
Medium Priority
?
407 Views
Last Modified: 2012-06-21
I am trying to create a combo box  in Access that will select entries based on the value that was selected from the previous combo box.  I read the access tips : Cascading lists for Access forms and tried what they suggested.  Here is what I have:

Table: UniformNameTherapy

Form: Working New Uniform Name Form
First Combo Box: cbUniform NAME
Second Combo Box: cbTherapy

Private Sub cbUniform_NAME_AfterUpdate()
On Error Resume Next

    [cbTherapy].RowSource = "Select UniformNameTherapy.Therapy from UniformNameTherapy Where (((UniformNameTherapy.[Uniform NAME]) = '" & [cbUniform Name].Value & "' &));"
End Sub
I have it all strung out so that I don't get confused with the line breaks.  
When I tried this code, it comes back with blanks.  Can anyone tell me what I am doing wrong?
L Tompkins
0
Comment
Question by:LenTompkins
  • 9
  • 9
18 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19544570

try this

Private Sub cbUniform_NAME_AfterUpdate()
On Error Resume Next

    me.[cbTherapy].RowSource = "Select Distinct Therapy from UniformNameTherapy Where [Uniform NAME] = '" & Me.[cbUniform Name] &"'"
End Sub

what is the row source of cbUniform Name ?



0
 

Author Comment

by:LenTompkins
ID: 19544597
The row source of cbUniform Name is another field from UniformNameTherapy.  This table is a source table and we are creating a distinct Uniform Name Table that will have an accepted name, therapy type and other information.  
  When I tried your code, I got the same results.  It comes back blank and I know that there are two records out there that contain therapy information.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19544614
LenTompkins,
can you post the sample data from UniformNameTherapy?

<The row source of cbUniform Name is another field from UniformNameTherapy.>

post the rowsource please.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:LenTompkins
ID: 19544631
NO      NAME      Uniform NAME      Therapy      Type      NDC #      Comments      Type2
5917      Mixing Contanier 3in1 3000ml      Mixing Container      SUPPLY      Supply                  Other
6944      Mixing Viaflex Container 1000ml      Mixing Container      SUPPLY      

The code is wrapping, but the Uniform Name is "Mixing Container" and I want to pick up the type of "Supply".  
Would it work better if I used Like instead of =?            
0
 

Author Comment

by:LenTompkins
ID: 19544640
If it is easier, I can send a copy of the form and some sample data from the table?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19544644
use this  as the rowsource for cbUniform name

select distinct [Uniform NAME] from UniformNameTherapy


0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19544647
LenTompkins,
just zip the db and upload here www.ee-stuff.com

post here the URL of the uploaded data
0
 

Author Comment

by:LenTompkins
ID: 19544946
0
 

Author Comment

by:LenTompkins
ID: 19544951
It is getting late and I will look tomorrow.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19546733
set the rowsource of cbTherapy to

SELECT UniformNameTherapy.Therapy FROM UniformNameTherapy WHERE (((UniformNameTherapy.Therapy) Is Not Null) And ((UniformNameTherapy.[Uniform NAME])=Forms![Working New Uniform Name Form]![cbUniform Name]));


since you are using combo boxes in a continuous form, and the recordsource of the form is based in a query where therapy is null,  the value for cbTherapy will all be the same. Is this what you are after?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19546756
another thing, avoid using spaces in naming forms,controls, tables and fields
0
 

Author Comment

by:LenTompkins
ID: 19548155
In the first two records that are displayed on the form, the uniform name from one record is Mixing Container and the Therapy is Supply which comes from record 5917.  That is what I want to come up, so that the new record 6945  or 6944 uses the same convention.  Did you try that in the code I gave you?  I tried it on the subset tables I sent and on the full blown version and still didn't get Supply brought back.  
Yes, I agree that spaces in the table, name, and form fields are a pain.  I didn't create this database, I'm just maintaining it.  
Since it isn't working right, I thought about displaying all of the fields when a person picks the correct uniform name and holding the number and then using the number to populate the other fields.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19548199
did you set the row source as i mentioned above?

you have to click the therapy drop down arrow to see the values in cbTherapy combo.

do you see Supply? or not
0
 

Author Comment

by:LenTompkins
ID: 19548451
I am not sure what you meant by setting the row source/ BUT I did click the drop down box on therapy and it is blank.  I  also changed the select on the Uniform name to Select names where it is Mixing Container, and the therapy is not null and I show the record number as well.  It picks up the correct record and I saw supply as the therapy, but It will not show in the drop down box.

Can I grab the record number which is unique and use that to extract the therapy?  How do I do that?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19548496
0
 

Author Comment

by:LenTompkins
ID: 19548898
Thank you so much.  I didn't read one of your responses carefully enough and added the code in the After update instead of in Therapy.  Now it works great.  I appreciate the time that you gave me. and increased the points.  
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1600 total points
ID: 19548956
good it is working now. can you close this q now?
0
 

Author Comment

by:LenTompkins
ID: 19549290
Yes, I thought that accept as Solution would close it.  I guess I had better read how to properly close a question
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question