Solved

Combo box

Posted on 2013-06-01
15
436 Views
Last Modified: 2013-06-02
Experts,

I have a combo box named cbo1.
Based on the value selected in cbo1 I need cbo2 record source to be tbl1 or tbl2.  Example:
if cbo1 = 1 then use tblS&P for cbo2
if cbo1 = 2 then use tblMoodys for cbo2

cbo1 is a listing of rating agencies (S&P, Moodys, Fitch)
I have made separate tables for each rating agency (S&P, Moodys, Fitch)

maybe my approach is not best.  Any other ideas are welcome.  

here is a pic of tblMoodys and S&P, Fitch are similar)
tblMoodys
0
Comment
Question by:pdvsa
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 18

Accepted Solution

by:
Jerry Miller earned 250 total points
ID: 39213343
In cbo1 afterUpdate event, set the rowSource for cbo2 using separate queries to the desired tables.

RowSource:
http://msdn.microsoft.com/en-us/library/office/ff845390.aspx

AfterUpdate Evenet:
http://msdn.microsoft.com/en-us/library/office/ff197081.aspx
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39213384
No points please, it would look like:

Private sub cbo1_AfterUpdate

    me.cbo1.RowSource = iif(me.cbo1 = 1, "tblS&P", "tblMoodys")

End Sub
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 250 total points
ID: 39213405
An alternative would be to combine your 3 tables into a single table by adding a column for Agency.  Then your RowSource for cbo1 would be:
Select Distinct Agency From NameOfTable

Open in new window

And your RowSource for cbo2 would be:
Select * From NameOfTable Where Agency ='" & Forms!NameOfForm!cbo1 & "'"

Open in new window

Then in your cbo1's AfterUpdate event you would add:
Me.cbo2.Requery
0
 

Author Comment

by:pdvsa
ID: 39213592
should my cbo2 be bound not bound?  

Irog:  that is interesting.  Could I use a query for this combining of tables?  

thank you
0
 

Author Comment

by:pdvsa
ID: 39213607
I think I might have to use a switch function.  

How can I combine switch and IIF or do I need to combine them?  

thanks.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39213617
You could use a query, but don't you think it would be better to have a single table in this case rather than three?  One of the advantages would be if you ever need to add another agency, you would just append their ratings to the same table. There would be no need to add another table and revise your form design to incorporate the new agency.
0
 

Author Comment

by:pdvsa
ID: 39213695
Yes, i do.  I think i will use a not in list and add ratings as i encounter them.  I am dumping the ratings now but in separate tables.  I do t think i will need a switch function now.  

Thank you.  Maybe i will split the pts.  Initially jmiller (fyed doesnt want pts) had the answer and that worked but now i think i should combine into 1 table like irogsinta mentioned but requires a slight design mod.  Thank you for the help. Really appreciate it.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:pdvsa
ID: 39214736
Hi IrogSinta:  I have made a combined table, as suggested as an alternative, and added a field [Agency] as text format, combo box, and have row source, in the table, of Select Distinct Agency From tblRatings_Combined.  Seems like I have a syntax when I clck the dropdown of cbo2.  Do you see it?  

cbo2:
Select * From tblRatings_Combined Where Agency ='" & Forms!frmBanks.[tblRatingAgencies subform].form.cbo1 & "'"

Open in new window


I am using a Parent and subform setup.  parent = frmBanks, sub = tblRatingAgencies subform

thank you
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39214807
What type of error are you seeing?

As a matter of practice, I never use spaces in the object names. I generally use the underscore character or CamelCase to name them. It makes it easier to code when you don't have to remember to put brackets around the names.

Also I shy away from making the added field a string formatted field.  I would have created a table with the agency names, a unique ID, and maybe a description. Then use the unique ID in the combined table instead of the name. But that is my preference and I know that I am a little OCD about such things. :D It is also the first normal form of database creation.

http://databases.about.com/od/specificproducts/a/normalization.htm
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39214819
In a query it would be this way:
Select * From tblRatings_Combined Where Agency = Forms!frmBanks.[tblRatingAgencies subform].form.cbo1

Open in new window

0
 

Author Comment

by:pdvsa
ID: 39215023
Jmiller, Irog:

The error I have is as below in the pic.  I think it is a syntax.  This is with Irog's last suggestion right above this.  

Jmiller:  in regards to the name of controls, I actually do know it is best practice not to use spaces. I didnt want to change it because not sure if i have referenced it elsewhere.  This db is 5 years running and I had some less than spectacular ideas back then.

Error_Msg
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39215048
Are both combo boxes in the subform or is one in the mainform and one in the subform?  Can you post the exact RowSource you have right now?
0
 

Author Comment

by:pdvsa
ID: 39215069
Ok I dont have any errors now.  I changed it to the below:  (I guess I needed a where clause?)

cbo2:
SELECT tblRatings_Combined.Rating, tblRatings_Combined.Comment, tblRatings_Combined.Agency, * FROM tblRatings_Combined WHERE (((tblRatings_Combined.Agency)=[Forms]![frmBanks].[tblRatingAgencies subform].[form].[cbo1]));
0
 

Author Closing Comment

by:pdvsa
ID: 39215139
thank you.  I got it working.
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39215151
Awesome! I understand not changing existing names. You are right since you don't know for sure every place it is referenced. If you are like the rest of us, the luxury of changing the previous person's errors in judgement are way down the priority list as you try to roll out new stuff and patch the old.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help writing a query 6 72
Run Time Error 3071 26 37
Delphi Ado Query To Converts Joined Columns Into Rows Based on Primary Key 4 39
Search Form not Querying 2 11
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now