not sure where to go from here.....

[Aaron]

I am attempting my first database however I am stuck trying to figure
something out. I have a table of information that contains categories and
then sub categories under it. Is what i need to do on the form I am creating
is to tell access that when one category is selected in one field i only want
to see the subcategories for the main field in the next field.

 

[Armen Stein]

I am attempting my first database however I am stuck trying to figure
something out. I have a table of information that contains categories and
then sub categories under it. Is what i need to do on the form I am creating
is to tell access that when one category is selected in one field i only want
to see the subcategories for the main field in the next field.

This is often called a "cascading" combobox. There are some basic
instructions on setting it up here:
http://www.fontstuff.com/access/acctut10.htm

This will work fine if your form is in single record mode. However,
it won't work for a continuous form, because when you change the
rowsource of a combobox on a continuous form, you change it for *all*
the records, not just that one.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Wayne-I-M]

Please stop - - -

I have a table of information that contains categories and
then sub categories under it.

You will end up having to redo the whole database if you keep going.

You need "two" tables. One for categories and another for sub categories.
You will than link the tables in the relationship window. So you will need
to add an extra field into the sub categories table that is the same format
as the primary field of the categories tables.

From each table create a query (use the wizard if you need to) and then
create "two" forms based on these queries. Open the categories in dersign
view then select Subform from the toolbox. Select the sub categories form
and select the linking fields (the ID field from the categories table)

Good luck

 

[Aaron]

Can you provide feedback for why the other method failed for you?

 

[Beetle]

If by the "other method" you mean the solution that Armen provided then;

1) Armen only gave you some general information and a link regarding
how to set up cascading combo boxes. He made no comments about
your table structure.

2) On the other hand, Wayne was pointing out that your table structure
is incorrect, which I agree with. Even if you are able to get your combo
boxes to work the way you want, that doesn't mean that your design
is correct. You will end up having other problems eventually. Better
to fix it now, rather than sometime in the future after you have invested
many hours designing forms, etc., that may have to be scrapped when
you finally realize things aren't functioning as they should. Access is a
relational database, not a spreadsheet.

 

[Armen Stein]

If by the "other method" you mean the solution that Armen provided then;

1) Armen only gave you some general information and a link regarding
how to set up cascading combo boxes. He made no comments about
your table structure.

That's true.

2) On the other hand, Wayne was pointing out that your table structure
is incorrect, which I agree with. Even if you are able to get your combo
boxes to work the way you want, that doesn't mean that your design
is correct. You will end up having other problems eventually. Better
to fix it now, rather than sometime in the future after you have invested
many hours designing forms, etc., that may have to be scrapped when
you finally realize things aren't functioning as they should. Access is a
relational database, not a spreadsheet.

But it is *possible* (although unlikely) that the table design is
correct. There are times when a Category may be specified, but the
Subcategory isn't known or is not applicable. In this case both
foreign keys may be stored, but the application must do extra work to
keep them in sync with each other.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Beetle]

I acknowledge your point Armen. When the OP said it was their first db,
I assumed they were making the common mistake of putting all the data
in one table.

It's *possible* (although unlikely) that I was wrong. <g>