Access 2000 lookups

[jillinsky]

Wondering is this is possible - I have 2 tables. One is categories,
and has catid, catname, hashighercat, and ...I can't think of the 4th
one, but it isn't needed anyway.

There are 40 categories, and about 1400 subcats. the categories are
numbered 1-40, and the rest have their numbers - all of these are in
the same field - catid. You can tell the categories from the
subcategories by the field hashighercat - the 40 categories are null
here, and the subcategories have the categoryid that they belong to.

the product table contains fields for category, catid, subcategory,
and subcatid.

currently, I have a list value lookup for the category field, so the
values can be filled in by typing 1 or 2 or 3 letters. This isn't
connected to the category table.

What I need is - when I type in the category, the catid number should
automatically fill in into the next field (catid). then in the next
field (subcategory) there would now be a dropdown box with the
subcategories for that category only.

Then, when I pick one of the subcats, it's subcatID number would
automatically fill in the last field (subcatid).

Is this possible? By queries? Or by VBA? or how?

Thanks

[Phil Stanton]

I'm confused. Are you? You say you have 2 tables then apparently refer to a
table of categories, a table of sub categories and a table of products.

I am guessing that you have something like

Product Sub category Category
Edam Cheese Dairy Products Food
5 mm Drill Drill bits Tools

You want a form that you select Tools. Then you get a dropdown to select
Hammers, Drill Bits Saws etc
Then having selected Drill bits you want a second dropdown showing 1mm
Drill, 2mm Drill, 5mm Masonry drill etc.

If so it is nearly all queries and a bit of code.

Please clarify your requirements and I'm sure you will get some help

Phil
<ji*******@gmail.comwrote in message
news:a5**********************************@k30g2000 hse.googlegroups.com...

Wondering is this is possible - I have 2 tables. One is categories,
and has catid, catname, hashighercat, and ...I can't think of the 4th
one, but it isn't needed anyway.

There are 40 categories, and about 1400 subcats. the categories are
numbered 1-40, and the rest have their numbers - all of these are in
the same field - catid. You can tell the categories from the
subcategories by the field hashighercat - the 40 categories are null
here, and the subcategories have the categoryid that they belong to.

the product table contains fields for category, catid, subcategory,
and subcatid.

currently, I have a list value lookup for the category field, so the
values can be filled in by typing 1 or 2 or 3 letters. This isn't
connected to the category table.

What I need is - when I type in the category, the catid number should
automatically fill in into the next field (catid). then in the next
field (subcategory) there would now be a dropdown box with the
subcategories for that category only.

Then, when I pick one of the subcats, it's subcatID number would
automatically fill in the last field (subcatid).

Is this possible? By queries? Or by VBA? or how?

Thanks

[Insky]

Yes I am confused too!

In the products table, there are these fields
Category Catid Subcat Subcatid
Food 2 Meat 322
Drink 3 Soda 114
In the category table, we have

CatID CatName HigherCat HasSubCat

2 Food 0 Yes
3 Drink 0 Yes
114 Soda 1 [null]
118 Meat 2 [null]
322 Veggies 2 [null]
344 Beer 1 [null]

In the product table, I want to type "food" into [category], and have
"3" fill into [catid] automatically. Then [subcat] will have a dropbox
showing "meat" and "veggies". I choose one of them, say "veggies", and
"322" will fill into [subcatid] automatically.

I hope that makes better sense!

===========================================

On Jul 23, 5:59*pm, "Phil Stanton" <[e-mail]:

I'm confused. Are you? You say you have 2 tables then apparently refer toa
table of categories, a table of sub categories and a table of products.

I am guessing that you have something like

Product * * * * * * * *Sub category * * * * * *Category
Edam Cheese * * * *Dairy Products * * * *Food
5 mm Drill * * * * * *Drill bits * * * * * * * * * *Tools

You want a form that you select Tools. Then you get a dropdown to select
Hammers, Drill Bits Saws etc
Then having selected Drill bits you want a second dropdown showing 1mm
Drill, 2mm Drill, 5mm Masonry drill etc.

If so it is nearly all queries and a bit of code.

Please clarify your requirements and I'm sure you will get some help

Phil

<[e-mail] in message

news:a5**********************************@k30g2000 hse.googlegroups.com...

Wondering is this is possible - I have 2 tables. One is categories,
and has catid, catname, hashighercat, and ...I can't think of the 4th
one, but it isn't needed anyway.

There are 40 categories, and about 1400 subcats. the categories are
numbered 1-40, and the rest have their numbers - all of these are in
the same field - catid. You can tell the categories from the
subcategories by the field hashighercat - the 40 categories are null
here, and the subcategories have the categoryid that they belong to.

the product table contains fields for category, catid, subcategory,
and subcatid.

currently, I have a list value lookup for the category field, so the
values can be filled in by typing 1 or 2 or 3 letters. This isn't
connected to the category table.

What I need is - when I type in the category, the catid number should
automatically fill in into the next field (catid). then in the next
field (subcategory) there would now be a dropdown box with the
subcategories for that category only.

Then, when I pick one of the subcats, it's subcatID number would
automatically fill in the last field (subcatid).

Is this possible? By queries? Or by VBA? or how?

Thanks- Hide quoted text -

- Show quoted text -

[Insky]

That's wrong - should be

Category Catid Subcat Subcatid
Food 2 Meat 322
Drink 3 Soda 114
In the category table, we have
CatID CatName HigherCat HasSubCat
2 Food 0 Yes
3 Drink 0 Yes
114 Soda 3 [null]
118 Meat 2 [null]
322 Veggies 2 [null]
344 Beer 3 [null]