Access 2003 - question on varying the contents of combo boxes

[Patriots87]

I've just started building a database in Access and I'm trying to
learn by looking at other databases in Access and reading books. I'm
getting there, but now I've got a question about something, and I'm
not even sure it can be done. Just keep this in mind when answering
my post - keep your answers simple so that an idiot can understand.
I'm still a beginner.

The example I'm going to use is just that - an example. I understand
there are databases in place for home inventory purposes but this is
the best example I can use to explain what I'm looking for.

I have 2 combo/drop-down boxes on a form. Currently, the contents of
both combo boxes are filled in (well, the first one is filled in, the
second one is still in progress which brings me to my problem - having
too many items in box #2). When I make a selection in combo box #1, I
would like it to change the list of available items in combo box #2.
For example, using the home inventory database as a reference, let's
say combo box #1 is a list of rooms and box #2 is a list of categories
of items. If I select Kitchen in box #1, I want the list in box #2 to
reflect categories of items I should see in a kitchen - like large
appliance, small appliances, etc. I shouldn't see clothes, jewelry,
exercise equipment, etc. If I change my selection in box #1 to Living
Room, I want the contents of box #2 to change to reflect items you
would find in a Living Room - furniture, electronics, possibly small
appliances (like an air purifier or humidifier). In other words, my
current box #2 list is extremely long and getting longer, and I'm
trying to reduce that by having the contents of box #2 change based on
what is selected in box #1. Can this be done and if so, how? Also,
if there's an example database out there I can look at, to learn from,
please reference that as well.

Thank you,
Sandy

 

[John Vinson]

When I make a selection in combo box #1, I
would like it to change the list of available items in combo box #2.

This requires a little bit of simple VBA code (one line!)

Base Combo Box #2 on a Query referencing Combo Box #1 (which I'll
assume is named cboNumber1 on the form named frmMyForm), using

=[Forms]![frmMyForm]![cboNumber1]

as a criterion on the field which will limit the second combo's list.

In cboNumber1's AfterUpdate event, click the ... icon and invoke the
Code Builder and put:

Private Sub cboNumber1_AfterUpdate() ' Access gives you this line free
Me!cboNumber2.Requery
End Sub ' this one too


John W. Vinson[MVP]