dropdown list to depend on other field

[ianking]

I'm trying to set up a database of books in a small library - each book has a prefix (Maths, English, Art etc) which I want users to be able to pick from a drop down list - I can do this, but the next field is a sub-category - is there a way of populating the dropdown list for the sub-category field depending on the chosen entry in the prefix field. eg if maths was chosen I would like the user to have the choice of number area, volume etc. but a different choice if the prefix was English.

any help very gratfully received - it seems to be a simple problem but it has me stumped.

 

[pbaldy]

Search here on "cascading" combo boxes.

 

[boblarson]

And here's a sample, if that will help...

http://downloads.btabdevelopment.com/Samples/combos/CascadingCombosSample.zip

 

[ianking]

cascading combo boxes

Many thanks - it's knowing what to look for that count's isn't it!

regards,

Ian

 

[ianking]

still stuck

I'm afraid I'm still stuck - I've made some progress I think, but still can't get the combo boxes to cascade. I've taken the liberty of attaching my database to this post in case it is a simple mistake I've made

I also have another problem as I want my form to show existing records as well as allow data entry (is this a bad idea?) - how do I force the form to automatically start on a new record?

 

[pbaldy]

You're requerying the second combo in code, but the second combo doesn't have a row source to requery. That technique would require it to have a row source that referred to the first combo.

One way to have the form start on a new record while still having the others available is to have this in the load event:

DoCmd.GoToRecord , , acNewRec

Whether it's a good idea probably depends on how many records the db has. A lot of records will cause this to be a poorly performing form. Generally speaking I never have a form that shows all records in a table, except "lookup" type tables.

 

[ianking]

thanks again

many thanks

Ian

 

[USEN]

Cascading Combo Box with multiselect option?

And here's a sample, if that will help...

http://downloads.btabdevelopment.com/Samples/combos/CascadingCombosSample.zip

Hello Bob...I'm fairly new to the developing end of Access. After reading this thread, I'm almost certain that the cascading combo box is what I need; however, I'd also like for the end choice to be multi-select. Is this possible?

What I want to do is very similiar to ianking whereas I want the 1st list (in my case it's a list of Skill categories...i.e. Administrative Services, Computer Programming, etc.) which when a selection is made, another list opens...(i.e. if Adm. Services is selected, the 2nd list would include MS Excel, MS Word, MS Access, MS Powerpoint, MS FrontPage, etc.). The difference here from what ianking wanted is that I would also need for the user to be able to select multiple items from this 2nd list...i.e. MS Excel AND, MS Word, AND MS PowerPoint). I know the multiselection property is supposed to be available on the form...(haven't reached that point yet), but will it work in addition to the Cascading Combo Box? Also, since I'm discovering that this is all done on the form rather than the table like I was attempting to do, what tables do I need? I currently have tables set up for each group. Here's the one for Programming. It didn't copy here, but the ID is an AutoNumber. There are 17 groups of tables in all.

tbl_Skills_Programming_Software_Database_Development
ProgrammingID Programming / Software / Database Development
1 Programming
2 Web Development/Programming
3 Database Design and Admin
4 PDAs and Handheld Devices
5 Wireless Programming
6 Game Programming
7 Telephony Software
8 Embedded System Design/Programming
9 IT Training
10 Quality Assurance
11 Other Technology Projects

I was then attempting to use lookup fields to get to the next group where the user could choose multiple selections which would all then post back to the main Freelancer table. Reading these threads has lead me to realize that it isn't possible that way. I got the list of individual skills to show up in the Grouped category list, but it wouldn't go up to the next level simply because the lookup field was blank until that selection was made. Also, I'm trying to get all the multiple results to post to 1 field in the parent table separated by commas.

I've previously set up a multiselect drop-down box in Excel with a little VBA coding to make it work (I know very little about VBA, but was lucky to find help with the exact coding I needed on the internet). Since Access & Excel are interchangable (import/export) with each other, I know that the multiselect does work in Access, but I don't know if it will also work with Cascading Combo boxes.

Thanks for any suggestions or assistance you might offer,
USEN

 

[pbaldy]

You would want a listbox as the final control rather than a combo, as the listbox will allow multiselect. Most cascading combo techniques should work fine to populate a listbox instead of a combo.

Most of us would caution against storing multiple values in the same field. It can make subsequent tasks much more difficult than they should be. The normalized approach would be a one-to-many related table that had a record for each skill possessed by a person (fields for the person ID and the skill ID).

Though I'm not sure I fully understand what you've got, I suspect you may not want 17 tables for groups, but 1 table with an extra field for the group. More info would clarify that.

 

[USEN]

Hi Paul,

Thank you so much for your quick response. OK, what I have is a company who uses Freelance individuals to work on their client's projects. The projects unlimited and the Freelancers work online thus their skills are also unlimited. It you are familiar with Guru.com...this is a very similiar situation. Difference is that my client does the actual hiring of the professionals who preforms the actual work for their clients. If you go to Guru.com in the "employers" section, you'll see each of the 17 major categories that have anywhere from say 5 to 20 (rough estimate) skills listed in each one. Basically what I wanted was to set up something that would act very similiar to the tree heirachy on Windows start menu where you can click on 1 item (say "My Documents") which open up another list of items (folders in the Start menu senerio such as My Music, My Vidoes, My Pictures, etc., but a list of categories in mine) and you then have a list of files which can be opened (a list of actual skills in mine). If you are buring a DVD, for instance, you might choose several of these files to go on that DVD. Same situation here. One of these freelancers may have several skills. What I'm doing is setting up a Freelancers table that will be filled out using a form. I need something that will allow them to select as many skills as applicable for each freelancer. This way, when they have a client who comes up saying they need say someone who can write a software program for them, all they have to do is query that field. They definitely want to do their data entry from a form rather than from dataview or table.

So...is the Cascading Combo/List Box the best way to go? Like I said, I was attempting to do it from table using lookup fields to obtain the information that I would eventually put onto the form in a drop-down list box. All the data for the freelancers will go onto this one form...including their personal information, emergency contacts, their skills, etc. They want to be able to enter it all from this one form. Once I've complete that, then there will be another form/table combination for the Clients and a 3rd form/table combination for the Projects; however, they will not be as extensive. All the rest of the tables that I've created so far is strickly to enable them to list all the skills. Then, because they are overseas and have freelancers who speak various languages (they also do translations for clients), I'll have a similiar situation to be able to list all the languages they speak. I have 2 fields that will link to that list...the first being their Native (Primary) language, and the 2nd being any other languages they use. I planned to use the same "language" table for both fields. And finally, a 3 set of tables (or lists) for the various Industries in which they have experience.

If you have a better suggestion as to how I can make multiple selections from a form that will be entered onto the same table for the same freelancer...I'm all ears.

Thanks,
USEN

 

[USEN]

Hi everyone,

I'm stuck again. I set up a Combo Box on my form for the first list (Skill Categories), and then a List Box which depends on the choice from the combo box (contains the individual skills). I set the List Box to "simple multiselect" so that each person can select more than one skill. I have 3 sets of these duo-boxes (in case one person has skills in more than one category) & they work great! The Combo boxes aren't bound whereas the List Boxes are because I don't need to record the categories in my table...only the actual skills. I wrote the VBA code to keep the combo boxes updated (AfterUpdate event). That, too, works fine. The problem I now have is...regardless of whether I make one selection or several, they don't insert the choice(s) into the main table (tbl_Freelancer_Information). Note: Other objects on the form post to the table correctly.

The Control Source = Skills_ID (which is a number type field in tbl_Freelancer_Information table and is also the Primary Key field (AutoNumber) for the tbl_Freelancer_Skills table. Relationship has been established for these 2 tables on this common field.

The Row Source Type = Table/Query

The Row Source = SELECT [tbl_Freelancer_Skills].[Skills_ID], [tbl_Freelancer_Skills].[Skills Names] FROM tbl_Freelancer_Skills WHERE ((([tbl_Freelancer_Skills].[Skills_Categories_ID])=[Forms]![frm_Freelancer_Information]![cbo_Skills_Categories_#1])) ORDER BY [tbl_Freelancer_Skills].[Skills Names];

Column Count = 3 (all 3 fields from tbl_Freelancer_Skills)
Bound Column = 1 (first column is Skills_ID)

I would greatly appreciate any assistance,
USEN

 

[USEN]

Hi again...I forgot to mention that I also have VBA coding for Current event of the form. Here is my VBA coding:

Private Sub cbo_Skills_Categories__1_AfterUpdate()
Me.lsb_Skills__1 = Null
Me.lsb_Skills__1.Requery
Me.lsb_Skills__1 = Me.lsb_Skills__1.ItemData(0)
End Sub
Private Sub cbo_Skills_Categories__2_AfterUpdate()
Me.lsb_Skills__2 = Null
Me.lsb_Skills__2.Requery
Me.lsb_Skills__2 = Me.lsb_Skills__2.ItemData(0)
End Sub
Private Sub cbo_Skills_Categories__3_AfterUpdate()
Me.lsb_Skills__3 = Null
Me.lsb_Skills__3.Requery
Me.lsb_Skills__3 = Me.lsb_Skills__3.ItemData(0)
End Sub
Private Sub Form_Current()
Me.lsb_Skills__1.Requery
Me.lsb_Skills__2.Requery
Me.lsb_Skills__3.Requery
End Sub

Again, I deeply appreciate any assistance you all might give me.

Thanks,
USEN

 

[pbaldy]

Well, if you're still determined to store the values in one field:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210203

but note that even this MS article recommends against it.

 

[USEN]

Thanks! Actually, I'm NOT determined...I just can't figure out any other way they can choose multiple categories with an unlimited number of skills within each one. Now I understand why most people have a set amount...it's MUCH easier, but they need to be able to show this data somehow & I can't figure out any other way. I'll talk to them again & explain the problems them might be facing.

Thanks for your help.

 

[pbaldy]

With properly designed tables, there would be no limit to the selected number. If you're worried about that, I'm visualizing a table with fields like Skill1, Skill2, etc. That would not be a properly normalized design. With a table like:

PersonID SkillID

There's no limit to how many skills a person can have.

 

[USEN]

Thanks Paul. That sounds like a better plan, but how does that give me unlimited choices? I'm still too new using this end of Access. I'm better at using it than I am at writing it.... I've set up simple databases before with only a few tables and I didn't bother with forms because I find it easier just to enter the data directly into the table; however, I'm not the boss in this particular case & they want to enter everything using forms.

I have another simplier situation where they want to list the person's Native Language & also any other languages they are fluent in. I say it's simplier because the choice doesn't depend on a previous choice like the skills depend on the skills categories choice. I originally had the languages listed in one table and establlished 2 different relationships (2 copies of the same table) with the Language_ID in the main table. I set up 2 different boxes...1 a combo for the Native Language, & the other a listbox (because again I thought I could use MultiSelect). Even when both boxes were set to accept only 1 selection, a selection in one automatically selected the same choice in the other. I tried writing code for the AfterUpdate event, but it didn't work. I must be entering something incorrectly because it doesn't update. I finally ended up making a duplicate copy of the Languages table with a different name & a different name for the ID field. That fixed the one problem, but now I have the same problem I have with the multiple skills in the other table as now they could possibly have multiple additional languages. These 2 tables only have 2 fields each. the first one named tbl_Language_Native has fields named Native_Language_ID using an AutoNumber and a text field named Languages. The other table is named tbl_Languages_Other and it has an autoNumber field named Other_Languages_Id (uses the same exact numbers as the first table...which means if I add a new language, I'll have to add it to both tables) and the text field also named Languages (same exact list as the other table). Without using MultiSelect, this would only allow them one additional language choice.

Can you use this simplier situation and tell me what fields I would use to combine them into one table that would allow unlimited choices? I'm certain that if I can get one set going, then I will be able to carry on with the other tables I have that require multiple selections...in addition to the skills tables and the languages, I'll have 3 or 4 other similiar tables which will need various seclections. I just need something to go on. My main problem is that I know VERY LITTLE about Visual Basics. I know enough to understand what I'm seeing and can duplicate it as long as I have an example using fields from one of my own tables and/or forms, but I'm not at all familiar with the actual event procedures to know what function each one does. That's why I'm certain that I've written something wrong in the code for updating my combo & list boxes. I've used examples that I found online...problem is, they didn't use the "naming conventions" that I learned to use so I have trouble distinguishing what object the event is actually being written for. Of course, if I can figure out the tables structure you're describing that will allow unlimited choices, that will eliminate the need for most of the VBA coding...except to reset the boxes before beginning each new form.

Again, I appreciate all the time you're spending. This would be much easier on Skype or another IM rather than on a Forum...
USEN

 

[MSAccessRookie]

... This would be much easier on Skype or another IM rather than on a Forum...
USEN

You are right about that, but then the rest of us would never have had the chance to learn what you did. The forum takes a little longer, but sometimes (like this time) it can help out more than just the person who started asking the questions.

 

[boblarson]

Sample

Here's a sample for you. This shows how you can add as many user preferences and values for them as required.

 

[USEN]

I fully understand about it helping others. I just didn't want everyone getting "bored" with all my long questions & problems. They surely will be by the time we get through all of this...that is...IF we ever get through it all...lol. Thanks for your time & patience. Perhaps one day I'll know enough to be able to help someone else out. Ya think? Ha! Ha! Perhaps Access, yes...VBA...not so sure. I've taught myself quite a bit of the beginners steps, until it got a little over my head for self-teaching without anyone around to explain it. I was using both the the Teach Yourself VB5 in 21 Days and the Teach Yourself in 24 Hours. Guess I got close to half way through them before it became too confusing.

Anyway...thanks again for your time. I just received an email notifying me of your reply...so I'll stop here & read it...

USEN

BTW...I'd be more than happy to give you a positibe reputation comment...Just tell me how.

 

[USEN]

Thanks Bob...I'll definitely go check it out. Thanks also for the tip on how to leave positive feedback for you guys.

USEN