Hi Kassie
You should set the RowSourceType for your combo boxes to "Table/Query",
not
"Field List".
Do you have all your categories and subcategories in a single table, or
do
you have a separate table for each? I would recommend the latter:
tblCat
=====
catID (PK)
catName (text)
tblSubCat
=======
sbcID (PK)
sbcCat (FK to catID)
sbcName (text)
Create a two-field unique index on sbcCat and sbcName, and a one-to many
relationship between catID and sbcCat)
Now, for cbxCat:
RowSource: "Select catID, catName from tblCat order by catName;"
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0 (this hides the left column)
And for cbxSubCat:
RowSource: <blank>
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0
And your event procedure:
Private Sub cbxCat_AfterUpdate()
cbxSubCat.RowSource = "Select sbcID, sbcName from tblSubCat where
sbcCat=" _
& Nz(cbxCat, 0) & " order by sbcName;"
' if current SubCat does not correspond to cbxCat selection then delete
it
If IsNull(cbxSubCat.Column(1)) Then cbxSubCat = Null
End Sub
You should also Call cbxCat_AfterUpdate from Form_Current to rebuild
cbxSubCat when the record changes.
--
Good Luck
Graham Mandeno [Access MVP]
Auckland, New Zealand
Sorry, but this does not work!
If I create a table tblCategory, with fields Cat and SubCat, Cat
indexed
as
Dup OK, then on the input form set cbxCat's RowSourceType to Field
list,
and
RowSource to tblCategory, then when I click on the combobox, I see
"Cat"
and
"SubCat", instead of a listing of the categories. If I select Cat, I
get a
listing of Categories under cbxSubCat - all instances of each category.
If I
select SubCat, I get a listing of all the sub categories under
cbxSubCat.
If I set the RowSourceType for cbxCategory to value list, and click on
the
down arrow, I get "tblCategory". cbxSubCat is still blank.
If I set the RowSourceType to Table/Query, and click on the down arrow,
I
get a listing of all the records under Category in tblCategory, and not
an
indexed list. If a category has 6 sub categories, I will see 6
instances
of
that category. If I then select a category, and click the down arrow
of
cbxSubCat, I get an inputbox "Enter Parameter Value" and with the name
of
the
category I selected in cbxCat,above the textbox, and with OK and Cancel
buttons below the textbox.
By the way, I am using Acces 2002
--
Hth
Kassie Kasselman
Change xxx to hotmail
:
Hi
I need to set the value of a combo box depending on the value
selected
in a
previous combobox.
In the first combobox, the user selects a Category ID. Each
category
however has specific sub categories. As such, I want to ensure that
the user
will select only the subcategory relevant to the category already
selected.
I was thinking it would be best to make use of various value lists,
since
subcategories are about 3 to 8 per category, but I am open to
suggestions on
how to best achieve this.
The table into which these details will be posted, is called
tblDecisions,
and contains the following fields:
DecNr - Number;
DecDate - Date;
DecCatID - Number;
DecSubCat - Text;
DecRevAsk - Date;
DecRevTaken - Date; and
DecAct - Yes/No
DecID is a one to many relationship with tblCat, consisting of
CatID - Autonumber
Category - Text.
The inputform contains all these fields, with DecCatID and DecSubCat
being
comboboxes, called cbxCatID and cbxSubCat. Obviously, what I need
to
get, is
how to code the AfterUpdate event of the cbxCatID box, to correctly
populate
the cbxSubCat box's value list.
--
Thanks a mil
Kassie Kasselman
Change xxx to hotmail
put the values in a table... there's an example of how to do this
here:
http://www.mvps.org/access/forms/frm0028.htm
<here's the content of that page...>
(Q) How can I limit the contents of one combo/list box based on
what's selected in another combo/list box?
(A) An easy way to do this would be to assign a dynamic SQL
statment to the RowSource of the secondary combo/list box at runtime.
Let's say you have two comboboxes, cbxCombo1 and cbxCombo2. The
RowSourceType of cbxCombo1 is set to "Field List" and RowSource to a
table Category. cbxCombo2 doesn't have anything under RowSource.
In this case, you can put code in the AfterUpdate event of
cbxCombo1 that assigns the proper RowSource to cbxCombo2.
'**************** Code Start *************
Private Sub cbxCombo1_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!cbxCombo1
strSQL = strSQL & " from Categories"
Me!cbxCombo2.RowSourceType = "Table/Query"
Me!cbxCombo2.RowSource = strSQL
End Sub
'**************** Code End *************
To filter records in a combo/listbox based on the value selected
in another combo/listbox, you can use a stored query which uses the
first control's value as a parameter. For example,
Select PeopleID, PeopleName from tblPeople Where PeopleID = Forms!
FormName!NameOfFirstControl;
Then all you need to do is issue a Requery on the second combo/listbox
in this first control's AfterUpdate event.
Private Sub NameOfFirstControl_AfterUpdate()
Me!NameOfSecondControl.Requery
End Sub
so all your Subcategories would go in a single table with their parent
category. Index the category field (Duplicates OK). Then you should
be off to the races.
