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

On Sep 3, 1:58*am, Kassie <kassie_kassel...@xxx.com> wrote:
> Hi
>
> I need to set the value of a combo box depending on the value selected ina
> 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 suggestionson
> 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.

Tks Piet,
Your comment at the end solved the riddle for me! Hardly ever works in
Access, but always in Excel, hence the stupidity, but thanks ever so much!

--

Kassie Kasselman
Change xxx to hotmail


"(E-Mail Removed)" wrote:

> On Sep 3, 1:58 am, Kassie <kassie_kassel...@xxx.com> wrote:
> > 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.
>

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


"(E-Mail Removed)" wrote:

> On Sep 3, 1:58 am, Kassie <kassie_kassel...@xxx.com> wrote:
> > 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.
>

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

"Kassie" <(E-Mail Removed)> wrote in message
news:6586ACE2-AE52-48AB-99E8-(E-Mail Removed)...
> 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
>
>
> "(E-Mail Removed)" wrote:
>
>> On Sep 3, 1:58 am, Kassie <kassie_kassel...@xxx.com> wrote:
>> > 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.
>>

Thanks ever so much for responding Graham. I have set up two tables as per
your suggestion. I still have two problems though.

The statement "If IsNull(cbxSubCat.Column(1)) Then cbxSubCat = Null" is not
accepted. Message received is that I cannot set this value to Null.

While your suggestion gets the first combobox to work perfectly, the second
combobox, cbxSubCat, remains blank. Keeping the properties window open while
opening the form, shows that the SQL statement to define the RowSource for
cbxSubCat actually includes the CatID number, but the dropdown remains blank.

Maybe I am missing something here?

--
Hth

Kassie Kasselman
Change xxx to hotmail


"Graham Mandeno" wrote:

> 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
>
> "Kassie" <(E-Mail Removed)> wrote in message
> news:6586ACE2-AE52-48AB-99E8-(E-Mail Removed)...
> > 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
> >
> >
> > "(E-Mail Removed)" wrote:
> >
> >> On Sep 3, 1:58 am, Kassie <kassie_kassel...@xxx.com> wrote:
> >> > 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.
> >>
>
>
>

Hi Kassie

Is cbxSubCat bound to a "required" field? This could explain the error
message attempting to set it to Null. You could either remove the
"required" property for that field or create a special SubCat "Undefined"
with sbcID=0 and set it to 0 instead.

If the RowSource of your combo is correct but it is blank, the most likely
reason is that one of the other properties (RowSourceType, ColumnCount,
BoundColumn, ColumnWidths) is not set correctly. If that all seems OK, copy
the RowSource string and paste it into the SQL view of a hew query to see if
it returns the two columns as expected.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Kassie" <(E-Mail Removed)> wrote in message
news:501AA972-53E0-477F-BE70-(E-Mail Removed)...
> Thanks ever so much for responding Graham. I have set up two tables as
> per
> your suggestion. I still have two problems though.
>
> The statement "If IsNull(cbxSubCat.Column(1)) Then cbxSubCat = Null" is
> not
> accepted. Message received is that I cannot set this value to Null.
>
> While your suggestion gets the first combobox to work perfectly, the
> second
> combobox, cbxSubCat, remains blank. Keeping the properties window open
> while
> opening the form, shows that the SQL statement to define the RowSource for
> cbxSubCat actually includes the CatID number, but the dropdown remains
> blank.
>
> Maybe I am missing something here?
>
> --
> Hth
>
> Kassie Kasselman
> Change xxx to hotmail
>
>
> "Graham Mandeno" wrote:
>
>> 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
>>
>> "Kassie" <(E-Mail Removed)> wrote in message
>> news:6586ACE2-AE52-48AB-99E8-(E-Mail Removed)...
>> > 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
>> >
>> >
>> > "(E-Mail Removed)" wrote:
>> >
>> >> On Sep 3, 1:58 am, Kassie <kassie_kassel...@xxx.com> wrote:
>> >> > 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.
>> >>
>>
>>
>>

Thanks Graham!!

I reset column count to 3, and it now works! Thanks ever so much!
--


Kassie Kasselman
Change xxx to hotmail


"Graham Mandeno" wrote:

> Hi Kassie
>
> Is cbxSubCat bound to a "required" field? This could explain the error
> message attempting to set it to Null. You could either remove the
> "required" property for that field or create a special SubCat "Undefined"
> with sbcID=0 and set it to 0 instead.
>
> If the RowSource of your combo is correct but it is blank, the most likely
> reason is that one of the other properties (RowSourceType, ColumnCount,
> BoundColumn, ColumnWidths) is not set correctly. If that all seems OK, copy
> the RowSource string and paste it into the SQL view of a hew query to see if
> it returns the two columns as expected.
> --
> Good Luck :-)
>
> Graham Mandeno [Access MVP]
> Auckland, New Zealand
>
> "Kassie" <(E-Mail Removed)> wrote in message
> news:501AA972-53E0-477F-BE70-(E-Mail Removed)...
> > Thanks ever so much for responding Graham. I have set up two tables as
> > per
> > your suggestion. I still have two problems though.
> >
> > The statement "If IsNull(cbxSubCat.Column(1)) Then cbxSubCat = Null" is
> > not
> > accepted. Message received is that I cannot set this value to Null.
> >
> > While your suggestion gets the first combobox to work perfectly, the
> > second
> > combobox, cbxSubCat, remains blank. Keeping the properties window open
> > while
> > opening the form, shows that the SQL statement to define the RowSource for
> > cbxSubCat actually includes the CatID number, but the dropdown remains
> > blank.
> >
> > Maybe I am missing something here?
> >
> > --
> > Hth
> >
> > Kassie Kasselman
> > Change xxx to hotmail
> >
> >
> > "Graham Mandeno" wrote:
> >
> >> 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
> >>
> >> "Kassie" <(E-Mail Removed)> wrote in message
> >> news:6586ACE2-AE52-48AB-99E8-(E-Mail Removed)...
> >> > 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
> >> >
> >> >
> >> > "(E-Mail Removed)" wrote:
> >> >
> >> >> On Sep 3, 1:58 am, Kassie <kassie_kassel...@xxx.com> wrote:
> >> >> > 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.
> >> >>
> >>
> >>
> >>
>
>
>