pdvsa
asked on
Combo box
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)
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
should my cbo2 be bound not bound?
Irog: that is interesting. Could I use a query for this combining of tables?
thank you
Irog: that is interesting. Could I use a query for this combining of tables?
thank you
ASKER
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.
How can I combine switch and IIF or do I need to combine them?
thanks.
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.
ASKER
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.
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.
ASKER
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:
I am using a Parent and subform setup. parent = frmBanks, sub = tblRatingAgencies subform
thank you
cbo2:
Select * From tblRatings_Combined Where Agency ='" & Forms!frmBanks.[tblRatingAgencies subform].form.cbo1 & "'"
I am using a Parent and subform setup. parent = frmBanks, sub = tblRatingAgencies subform
thank you
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
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
In a query it would be this way:
Select * From tblRatings_Combined Where Agency = Forms!frmBanks.[tblRatingAgencies subform].form.cbo1
ASKER
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.
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.
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?
ASKER
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.Commen t, tblRatings_Combined.Agency , * FROM tblRatings_Combined WHERE (((tblRatings_Combined.Age ncy)=[Form s]![frmBan ks].[tblRa tingAgenci es subform].[form].[cbo1]));
cbo2:
SELECT tblRatings_Combined.Rating
ASKER
thank you. I got it working.
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.
Private sub cbo1_AfterUpdate
me.cbo1.RowSource = iif(me.cbo1 = 1, "tblS&P", "tblMoodys")
End Sub