Combo Box v. List Box (1 Viewer)

[mbentle]

Well my search for the perfect cascading combo is still in place. I've gotten a cascading combo box based on two tables to work, however I'm having a heck of a time getting cascading list boxes to work when it's based on multilpe tables.

Someone suggested to me that perhaps the event procedure for a list box needed to be taken off 'after update' (as it is for combo boxes) and placed somewhere else. Is there any merit to this?

I'm trying to create a training table that features User information and then the training information for that user like described below:

tblUser
UserID
UserName
UserPassword (from the logOn example which btw is brilliant)
UserLevelID (again from the logOn example
TrainingID
UserJobTitle (this is a look-up to identify a specified number of job possibilities...it helps with keeping the input easy for some of the guys who aren't very computer savvy but will need to enter info)

tblTraining
TrainingID
CourseName
CompletionDate
Location
Instructor
CourseOutline
MonthsValid
CompletionHours
UserID

I have the Primary keys set as UserId and TrainingID and I Have the relationship set on the UserID (not sure if I should change that to TrainingID which is the only reason I even have that field in the tblUser).

Any suggestions or help is greatly appreciate.

 

[Fizzio]

Your structure is ok but you have not described what you want your cascading lisboxes to do

 

[mbentle]

doh! You're right!

It's a simple cascading list box. I want to select a name in the list box on the left and have it display the training information in the list box on the right.

Eventually, I want to take that a step further and be able to select a specific training item on the right to display the details of that training item on the right.

Effectively making three list boxes.

Unless there is an easier way to achieve the same effect. I'm open to any ideas I just need to K.I.S.S. because of the learning curve for the users.

 

[Fizzio]

This can be tricky depending on what you are trying to achieve. Have a look at the demo I have posted for the 'easy' and 'advanced' forms which shows a slight difference in the third listbox

Easy - lists all components of a course
Avd - lists only the components a user has taken from the course selected (I think this is what you are looking for)

It is rough and ready mind you - knocked up between patients!

 

[mbentle]

I noticed you used queries. I wasn't aware you could do that like that.
May I ask how you used them (I don't understand queries very much) or maybe you can point me to a good explanation of how they work?

You certainly did demonstrate precisely what I was asking for. Is it possible to create multiple columns for the third list box?

 

[Fizzio]

Queries really give you the power to get out of your database what you put in in almost any permutation you want! Hang around the Queries forum for some posts and maybe look on the FAQ for some useful links. I cannot think of a good link off the top of my head!

You can display multiple colums in any of the listboxes. Just set the column number (format in the properties box) to as many as you want to display and set the widths accordingly. In your query powering the listbox, show the colums you want to display.

hth

 

[mbentle]

Hey Fizzio
I was really delving into how you did this and I have some more questions (sorry I'm a n00b)

I noticed you have a tblComponents and tblUserComponents. Whats the difference? I'm trying to set up the advanced version but I'm having a hard time understanding how you set up the tables and relationships for it because to me, these two tables look the same as does tblCourses and tblUsersCourses. I'm guessing one is for the advanced and one for the beginner but I'm just guessing.

BTW, I truly appreciate your helping me with this. I'm having a heck of a time. At this point I'd be thrilled if I can just get one set of cascading links to work let alone the third box.

 

[Fizzio]

I cannot access my Demo at the minute as I designed in 2k but only have access to 97 oh:

The main difference is that tblComponents are linked to the tblCourses table ie 1 course can have many components.
This is ok if the student enrols for a course and has to take all the componenets.

However....

If the course is modular, the student will be able to select components from a course and therefore needs a record of which components they have taken, hence the tblUserComponents which is a junction table, allowing a many-to-many relationship between users and components.

This relationship comes in useful if you not only store the courses a user takes (tblUserCourses) but also which componenets this user has taken in the course (tblUserComponents)

I would use nested subforms to record the courses and components and then use the nested listboxes as a quick reference only. If you need any more help let me know but I'll try to add a modified demo soon.

hth

 

[mbentle]

Thanks Fizzio,
I'm going to work on it and see what I come up with. You have been a very big help!

 

[Pat Hartman]

You need to change your table structure. You have defined a 1-many relationship -- 1 course can be taken by many people. I think you have a many-to-many relationship since I presume that 1 person may also take many course, not just 1. So, you need a third table to implement the relationship.

tblUser
UserID
FirstName
LastName
UserPassword (from the logOn example which btw is brilliant)
UserLevelID (again from the logOn example
UserJobTitle (this is a look-up to identify a specified number of job possibilities...it helps with keeping the input easy for some of the guys who aren't very computer savvy but will need to enter info)

tblTraining
TrainingID
CourseName
CompletionDate
Location
Instructor
CourseOutline
MonthsValid
CompletionHours
UserID

tblUserTraining:
TrainingID
UserID

 

[Fizzio]

Pat, from my impression, I think that mbentle wants the possibility of allowing people to take no only a course but selectively differing modules within that course structure.
I agree that a junction table is necessary to establish a many-to-many relationship but how mbentle chooses to split structure this, I provided different options for this in the demo many-many on courses / many-many on components etc. It seems a little more complex than the usual many-many relationship we so often see. Any comments on the demo appreciated (albeit very rough and ready!)

 

[Pat Hartman]

I didn't look at the demo. I only posted because you didn't remark on the structure being incorrect.

 

[Fizzio]

Yes, sorry Pat.
Reviewing the thread I realise I had actually said that the structure was ok but then redeemed myself in the data structure in the demo!
Thanks for the correction

 

[mresann]

Maybe it's my strict code practice but...

I base every control on a query. Even if it's a lookup table specifically designed for the control, using a query generally uses less resources.

Using the After_Update events in "parent" combo boxes can trigger the "Change" event in "child".

s for using a list box compared to a combo box:

In general, using either a list box or a combo box has to do more with form design and user actions than actual manipulation of the data. For instance, I use a combo box when I want to give the user the option to type in his own values (for AutoComplete), making it easier for instance to find a datum, say by name. Also, the combo box is compact enough for space.

I use a list box when I want to show several items at a glance, and I have the room to put them. Sometimes using a combo box can be too much activity, ie clicking, then scrolling, then clicking again rather than just scroll/click. But both controls in Access are easy to set up with either queries or, on occasion, Value lists.