Cascading Combo Boxes

[raychow22]

Hello Forum,

I am having trouble with the cascading combo boxes.

I want to display "Segment" combo box that list Segments 1-4. Each Segment has its own "Work Area". WA 1,2,3,& 4. After the first selected "Segment" box is selected; The next "Work Area" box will be specific to the "Segment" selected.

I have tried some tutorials, but was unsuccessful.

[ranman256]

the query for cboWorkArea, looks at cboSegment....

select WAID from table where segment = forms!myForm!cboSegment

BUT you need to refresh the cbo after the pick so the list refreshes:

sub cboSegment_afterupdate()
   cboWorkArea.requery
end sub

[raychow22]

ranman256,

Thanks for the reply!! So I tried selecting WAID table where segment = forms!myform!cboSegment. It shows up blank. Here are the pictures to illustrate.

[aytee111]

First, it looks like your table contains lookups - remove them, they mess up what you are trying to accomplish.

Second, fix your naming conventions, it is all too confusing. Change all primary key fields to what it contains, e.g. SegmentID, and use that name on all other tables. Change fields to have more descriptive names, e.g. SegmentName. When designing a table, you are a techy, not a user, so use names that are absolute where there is no doubt as to what the field contains. Much of your confusion comes from this.

tblWAID contains a field called "Segments" - with a name like this who knows what it contains. Your screenshot shows that it is storing the SegmentID (correctly). After you have changed the name of this field, go back to your work area row source query. The criteria says "this segment ID must match the segment ID which is in the first column of the combobox Segment" - which hopefully is the SegmentID.

[raychow22]

First, it looks like your table contains lookups - remove them, they mess up what you are trying to accomplish.

Second, fix your naming conventions, it is all too confusing. Change all primary key fields to what it contains, e.g. SegmentID, and use that name on all other tables. Change fields to have more descriptive names, e.g. SegmentName. When designing a table, you are a techy, not a user, so use names that are absolute where there is no doubt as to what the field contains. Much of your confusion comes from this.

tblWAID contains a field called "Segments" - with a name like this who knows what it contains. Your screenshot shows that it is storing the SegmentID (correctly). After you have changed the name of this field, go back to your work area row source query. The criteria says "this segment ID must match the segment ID which is in the first column of the combobox Segment" - which hopefully is the SegmentID.

Thanks aytee111 for the quick response.

I'm still getting lost. I removed the lookups on the fields tblSegID and tblWAID; Changed the fields "Segments" to SegID on both tblSegID and tblWAID. Here are the screen shots

[aytee111]

Good job!

Set the column widths on the comboboxes so that the user doesn't see the id field.

Did you follow all the instructions, including post # 2?

[raychow22]

I think I did; I name SegID box "cboSeg" and WA box to "cboWA" with vba code as followed. I also set the criteria as shown on the work area row source for segid. Here are pictures.

[raychow22]

One more

[aytee111]

I can't see the criteria for the work area row source. Make sure that it matches the form. Not sure why it isn't working. Try changing Me.cboWA to Me!cboWA. Copy and paste the row source for cboWA into a new query window, then select a segment on the form and see if the query works. If not, change the criteria to a manual number and see if that works. Try and pinpoint exactly where the error is occurring.

(On tblWAID, change the primary key to be WorkAreaID (never have spaces in names) and the name to WorkAreaName (or something like that). Don't use "ID" as a field name anywhere and use it only for primary keys/linked fields. This is a naming convention, not a rule. The result will help you for the life of the database.)

[raychow22]

Oh BTW. I forgot to ask if the combo box can have multiple selections. Say Seg1 and Seg2 will display Work Areas for Seg1 and Seg2?

[raychow22]

WA row source

[raychow22]

Here it is.

[aytee111]

Multi-select comboboxes are not straightforward. I don't use them so can't help you. This might http://www.utteraccess.com/forum/Mul...-t1989417.html

[raychow22]

Ok!!! I found the problem ; I forgot I changed cboSegment to "cboSeg"!!! Thanks aytee111 for helping me!! Now is there a way I can add multiple values of the combo box??

[aytee111]

Still nothing is apparent as to where the problem lies. Did you test the query?

In the AfterUpdate sub type,
Debug.Pring Me!cboSegment
and see what is displayed

(All tables should carry id's as foreign keys, the field with the control source of WA should be WorkAreaID and your row source should have that as the first column.)