Category and Subcategory

  • Thread starter Thread starter MarkK
  • Start date Start date
M

MarkK

I am trying to build a form that allows the user to add a record for a new
product, and specify its category (based upon a table of predefined
categories) and then a subcategory (again, based upon a table that has three
rows: ID, Subcategory, CategoryID). Each category has multiple predefined
subcategories. I am having difficulty limiting the subcategory list for for
the subcategory combobox to only show those subcategories assoicated with the
category the user just entered in the form.

For the Category combo box on the form, I have the following macro for After
Update:
RemoveAllTempVars (there are no other uses of temp vars in my database)
SetTempVar CatValue,Forms![ProductForm]![CategoryID] (i.e., ID value for
category user just inputted)
(If I also place an OpenQuery action next, I see the query table shows only
those subcategories as expected)

For the Subcategory combobox on the form, its list is based upon a query
that uses the criteria of [TempVars]![CatValue] for the CategoryID value. The
intention is to use CatValue to filter the subcategory values.

For the Subcategory combobox, I have a macro assigned to OnMouseDown that is:
Requery (no arguments)

I expected this requery to rerun the query, retuirning only the
subcategories desired. But instead, the form goes to record #1, no other
errors.

Greatly appreciate any help with how to setup the proper macros for this. I
have not been able to find an answer to this in Access 2007 InsideOut.
 
MarkK said:
I am trying to build a form that allows the user to add a record for a new
product, and specify its category (based upon a table of predefined
categories) and then a subcategory (again, based upon a table that has
three
rows: ID, Subcategory, CategoryID). Each category has multiple predefined
subcategories. I am having difficulty limiting the subcategory list for
for
the subcategory combobox to only show those subcategories assoicated with
the
category the user just entered in the form.

For the Category combo box on the form, I have the following macro for
After
Update:
RemoveAllTempVars (there are no other uses of temp vars in my database)
SetTempVar CatValue,Forms![ProductForm]![CategoryID] (i.e., ID value for
category user just inputted)
(If I also place an OpenQuery action next, I see the query table shows
only
those subcategories as expected)

For the Subcategory combobox on the form, its list is based upon a query
that uses the criteria of [TempVars]![CatValue] for the CategoryID value.
The
intention is to use CatValue to filter the subcategory values.

For the Subcategory combobox, I have a macro assigned to OnMouseDown that
is:
Requery (no arguments)

I expected this requery to rerun the query, retuirning only the
subcategories desired. But instead, the form goes to record #1, no other
errors.
Click to expand...

You don't need TempVars, and assuming you can delete all values potentially
breaks any future code that used them. If you have two combo boxes on the
form, cboCategory and cboSubcategory, the cboSubcategory record source would
be "Select subcategoryID, subcategoryName From YourSubcategoryTable Where
categoryID=[cboCategory]". You can first test this manually by pressing the
F9 key with your cursor in cboSubcategory to refresh the dropdown list. Once
it's working, so you know you have the correct syntax for the record source,
add the line of code to the cboCategory AfterUpdate event and the form
OnCurrent event that requeries cboSubcategory: Me.cboSubcategory.Requery.
 
Paul,

Many thanks - This has me 90% of the way there. I understand the SQL code to
write and changed the query such that it operates as expected when the F9 key
is pressed while on the cboSubcategory. I copied the code to a Private Sub
for cboCatID AfterUpdate, but although the query seems to work, I get
compiler errors. Sorry, still learning the programming aspects of SQL and VB.

Private Sub Cat_ID_AfterUpdate()

SELECT [Technology Subcategory]![Subcat ID], [Technology
Subcategory]![Technology Subcategory], [Technology Subcategory]![Technology
Category ID]
FROM [Technology Subcategory]
WHERE [Technology Subcategory]![Technology Category ID] = Forms![TEST
Subcategory Operation]![Cat ID]
ORDER BY [Technology Subcategory]![Technology Subcategory];

End Sub
Paul Shapiro said:
MarkK said:
I am trying to build a form that allows the user to add a record for a new
product, and specify its category (based upon a table of predefined
categories) and then a subcategory (again, based upon a table that has
three
rows: ID, Subcategory, CategoryID). Each category has multiple predefined
subcategories. I am having difficulty limiting the subcategory list for
for
the subcategory combobox to only show those subcategories assoicated with
the
category the user just entered in the form.

For the Category combo box on the form, I have the following macro for
After
Update:
RemoveAllTempVars (there are no other uses of temp vars in my database)
SetTempVar CatValue,Forms![ProductForm]![CategoryID] (i.e., ID value for
category user just inputted)
(If I also place an OpenQuery action next, I see the query table shows
only
those subcategories as expected)

For the Subcategory combobox on the form, its list is based upon a query
that uses the criteria of [TempVars]![CatValue] for the CategoryID value.
The
intention is to use CatValue to filter the subcategory values.

For the Subcategory combobox, I have a macro assigned to OnMouseDown that
is:
Requery (no arguments)

I expected this requery to rerun the query, retuirning only the
subcategories desired. But instead, the form goes to record #1, no other
errors.
Click to expand...

You don't need TempVars, and assuming you can delete all values potentially
breaks any future code that used them. If you have two combo boxes on the
form, cboCategory and cboSubcategory, the cboSubcategory record source would
be "Select subcategoryID, subcategoryName From YourSubcategoryTable Where
categoryID=[cboCategory]". You can first test this manually by pressing the
F9 key with your cursor in cboSubcategory to refresh the dropdown list. Once
it's working, so you know you have the correct syntax for the record source,
add the line of code to the cboCategory AfterUpdate event and the form
OnCurrent event that requeries cboSubcategory: Me.cboSubcategory.Requery.

.
Click to expand...
 
The record source is a property of the combo box and doesn't need to be
updated in the event handler. The category combo box AfterUpdate event
handler just needs the one line:
Me.cboSubcategory.Requery
replacing cboSubcategory with the name of your subcategory combo box. The
same line of code goes in the form's OnCurrent event handler.

MarkK said:
Paul,

Many thanks - This has me 90% of the way there. I understand the SQL code
to
write and changed the query such that it operates as expected when the F9
key
is pressed while on the cboSubcategory. I copied the code to a Private Sub
for cboCatID AfterUpdate, but although the query seems to work, I get
compiler errors. Sorry, still learning the programming aspects of SQL and
VB.

Private Sub Cat_ID_AfterUpdate()

SELECT [Technology Subcategory]![Subcat ID], [Technology
Subcategory]![Technology Subcategory], [Technology
Subcategory]![Technology
Category ID]
FROM [Technology Subcategory]
WHERE [Technology Subcategory]![Technology Category ID] = Forms![TEST
Subcategory Operation]![Cat ID]
ORDER BY [Technology Subcategory]![Technology Subcategory];

End Sub
Paul Shapiro said:
MarkK said:
I am trying to build a form that allows the user to add a record for a
new
product, and specify its category (based upon a table of predefined
categories) and then a subcategory (again, based upon a table that has
three
rows: ID, Subcategory, CategoryID). Each category has multiple
predefined
subcategories. I am having difficulty limiting the subcategory list for
for
the subcategory combobox to only show those subcategories assoicated
with
the
category the user just entered in the form.

For the Category combo box on the form, I have the following macro for
After
Update:
RemoveAllTempVars (there are no other uses of temp vars in my database)
SetTempVar CatValue,Forms![ProductForm]![CategoryID] (i.e., ID value
for
category user just inputted)
(If I also place an OpenQuery action next, I see the query table shows
only
those subcategories as expected)

For the Subcategory combobox on the form, its list is based upon a
query
that uses the criteria of [TempVars]![CatValue] for the CategoryID
value.
The
intention is to use CatValue to filter the subcategory values.

For the Subcategory combobox, I have a macro assigned to OnMouseDown
that
is:
Requery (no arguments)

I expected this requery to rerun the query, retuirning only the
subcategories desired. But instead, the form goes to record #1, no
other
errors.
Click to expand...

You don't need TempVars, and assuming you can delete all values
potentially
breaks any future code that used them. If you have two combo boxes on the
form, cboCategory and cboSubcategory, the cboSubcategory record source
would
be "Select subcategoryID, subcategoryName From YourSubcategoryTable Where
categoryID=[cboCategory]". You can first test this manually by pressing
the
F9 key with your cursor in cboSubcategory to refresh the dropdown list.
Once
it's working, so you know you have the correct syntax for the record
source,
add the line of code to the cboCategory AfterUpdate event and the form
OnCurrent event that requeries cboSubcategory: Me.cboSubcategory.Requery.
Click to expand...
Click to expand...
 
Thank you. This works exactly as expected. Your advice is very much
appreciated.

Paul Shapiro said:
The record source is a property of the combo box and doesn't need to be
updated in the event handler. The category combo box AfterUpdate event
handler just needs the one line:
Me.cboSubcategory.Requery
replacing cboSubcategory with the name of your subcategory combo box. The
same line of code goes in the form's OnCurrent event handler.

MarkK said:
Paul,

Many thanks - This has me 90% of the way there. I understand the SQL code
to
write and changed the query such that it operates as expected when the F9
key
is pressed while on the cboSubcategory. I copied the code to a Private Sub
for cboCatID AfterUpdate, but although the query seems to work, I get
compiler errors. Sorry, still learning the programming aspects of SQL and
VB.

Private Sub Cat_ID_AfterUpdate()

SELECT [Technology Subcategory]![Subcat ID], [Technology
Subcategory]![Technology Subcategory], [Technology
Subcategory]![Technology
Category ID]
FROM [Technology Subcategory]
WHERE [Technology Subcategory]![Technology Category ID] = Forms![TEST
Subcategory Operation]![Cat ID]
ORDER BY [Technology Subcategory]![Technology Subcategory];

End Sub
Paul Shapiro said:
I am trying to build a form that allows the user to add a record for a
new
product, and specify its category (based upon a table of predefined
categories) and then a subcategory (again, based upon a table that has
three
rows: ID, Subcategory, CategoryID). Each category has multiple
predefined
subcategories. I am having difficulty limiting the subcategory list for
for
the subcategory combobox to only show those subcategories assoicated
with
the
category the user just entered in the form.

For the Category combo box on the form, I have the following macro for
After
Update:
RemoveAllTempVars (there are no other uses of temp vars in my database)
SetTempVar CatValue,Forms![ProductForm]![CategoryID] (i.e., ID value
for
category user just inputted)
(If I also place an OpenQuery action next, I see the query table shows
only
those subcategories as expected)

For the Subcategory combobox on the form, its list is based upon a
query
that uses the criteria of [TempVars]![CatValue] for the CategoryID
value.
The
intention is to use CatValue to filter the subcategory values.

For the Subcategory combobox, I have a macro assigned to OnMouseDown
that
is:
Requery (no arguments)

I expected this requery to rerun the query, retuirning only the
subcategories desired. But instead, the form goes to record #1, no
other
errors.

You don't need TempVars, and assuming you can delete all values
potentially
breaks any future code that used them. If you have two combo boxes on the
form, cboCategory and cboSubcategory, the cboSubcategory record source
would
be "Select subcategoryID, subcategoryName From YourSubcategoryTable Where
categoryID=[cboCategory]". You can first test this manually by pressing
the
F9 key with your cursor in cboSubcategory to refresh the dropdown list.
Once
it's working, so you know you have the correct syntax for the record
source,
add the line of code to the cboCategory AfterUpdate event and the form
OnCurrent event that requeries cboSubcategory: Me.cboSubcategory.Requery.
Click to expand...
Click to expand...

.
Click to expand...
 
Back
Top