Cascaded Drop Down Lists in Access 2007...

[Mansoor]

Hi all,

I am trying to build cascaded drop down lists (combo boxes) in access form.
I have two combo boxes. While running the form, one combo box is filled with
the names of countries and other with the names of cities. Now, I want to
populate the second combo box with only those cities based on the country in
the first combox box. How can I do this?

Thanks in advance,
Best Regards.

 

[Allen Browne]

See:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

 

[Mansoor]

Thanx for the reply...
Now, I am getting the following error:

The expression After Update you entered as the event property setting
produced the following error: Invalid outside procedure.
* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or macro.

This error occurs when an event has failed to run because Microsoft Office
Access cannot evaluate the location of the logic for the event. For example,
if the OnOpen property of a form is set to =[Field], this error occurs
because Access expects a macro or event name to run when the event is fired.

 

[Allen Browne]

You need to set the After Update property of the combo to:
[Event Procedure]

Then click the Build button (...) beside this.
Access will open the code window.
You put the code in there.

Then (still in the code window), choose Compile on the Debug menu, to ensure
Access understood the code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanx for the reply...
Now, I am getting the following error:

The expression After Update you entered as the event property setting
produced the following error: Invalid outside procedure.
* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or macro.

This error occurs when an event has failed to run because Microsoft Office
Access cannot evaluate the location of the logic for the event. For
example,
if the OnOpen property of a form is set to =[Field], this error occurs
because Access expects a macro or event name to run when the event is
fired.

See:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

 

[Pendragon]

Allen,

I am using this method for two combo boxes without issue except that my
combo boxes are on a continuous form (subform, actually). So long as the
selection for combo1 is the same for each record on the continuous form, the
data for combo2 is displayed correctly. When combo1 has a different value
selected, the data for combo2 for all other records go blank.

Please see my thread from 12/4 in conversation with Albert Kallal entitled
"combo box filters on continuous form" - I would appreciate your thoughts.

I have searched and read much on forms coding but have not been able to find
anything addressing (or resolving) the problem. I noted to Albert that I
thought at some point I had seen a comment about this being an unfixable
problem on a continuous form, but I couldn't find the thread and truthfully,
find it hard to believe.

Thanks!

 

[Allen Browne]

Each row of a continuous form does not have its own RowSource values, i.e.
there is only one for all the rows. Consequently the combo will display
blank if:
a) You restrict its RowSource such that it doesn't have the values it needs
to display for all rows, and
b) The bound column is not the display column (typically where the bound
column is zero-width.)

One simple way to solve this is to display the bound column. For example, if
the combo is for choosing a category, you really don't need a hidden
autonumber. The category name is a perfectly suitable natural key. And since
the bound value is the display value, Access has what it needs to display,
regardless of whether it is in another column of the RowSource.

If that's not suitable, you can work around it by changing the RecordSource
of the form to a query that contains both the form's main table and also the
lookup table for the combo. Be sure to use an outer join if the combo's
field is not required. Add the display column from the lookup table to the
query's fields.

Now add a text box bound to the lookup field to your form. Place it on top
of the combo (Format | Send To Front.) Size it so sits over the combo except
for the drop-down arrow at the right. To the user, it looks like the textbox
part of the combo. Set the combo's TabStop to No, and in move the Tab Order
(View menu) for the text box so it is in the right place. In the Enter event
of the text box, SetFocus to the combo.

The trick is that when the combo takes focus, only the one in the current
record jumps in front of the text box. In the other rows, the text box is
still in front of the combo, and so all rows show the value (since the text
box has it), even though it is not in the combo's RowSource.

I didn't read Albert's thread, but I imagine he probably said something
similar.