combo box lookup help

[Connie]

I have created several combo boxes in my table using the
lookup wizard. The problem is that I have fifty items in
five different categories. The combo box calls up all
fifty items. I want to have the combo box list just the
item in the category that I choose in another field. In
other words when I choose a category in Field A (which is
also from a combo box) then the combo box for Field B
would offer only those choices related to the category I
chose for Field A, not all choices from all categories.
Is there a simple way to do this that does not involve
using VBA as I am not at that level yet. Thanks!

 

[John Vinson]

Is there a simple way to do this that does not involve
using VBA as I am not at that level yet.

No... but the VBA is very easy, consisting of just one line.

Create a Query referencing the first combo box as a criterion. Put

=[Forms]![NameOfForm]![cboCategory]

on the criteria line to select records in the category chosen in the
first combo box (which I'm calling cboCategory, use your own control
name of course). Use this Query as the row source for the second combo
(which I'll call cboItem).

The one line of VBA is in the AfterUpdate event of cboCategory. View
the combo's Properties, and find the AfterUpdate event on the Events
tab. Click the ... icon by it, and choose "Code Builder"; Access will
give you a Sub and End Sub line for free, you just need to add one
more line:

Private Sub cboCategory_AfterUpdate()
Me!cboItem.Requery
End Sub