Two Access Problems

Shepherdess · #1 28-Feb-2008, 08:52 PM

I am preparing a simple database in Access 2003 and have been struggling with two things:

- I have got two fields in a table, one for Country, the other for County. I wanted to change the form in such a way that if someone chooses England as a Country, only English counties appear. I have tried two methods: one with visible yes/no for certain counties (when country=England, only English counties are visible), the other with message box, e.g. "Fife is not in England", but I can't seem to get it right. Do you think I will have to put English, Scottish, Welsh counties in separate fields (it only just occured to me as an option :-) )?
- the other problem is with choosing topic. Users will have to choose main category, and then (if they want) choose subcategory. I have created the lookup table for categories, but I am not sure how to set up subcategories.

I have looked for the answers in two books (one about Access 2003, the other about VBA) and in Office.microsoft.com but could not find anything even remotely relevant. I would not like you to write a code for me, just give me some hints where to look for the answers

Thanks in advance.

ThomasMc · #2 28-Feb-2008, 09:34 PM

on the first bit if I have picked you up correctly, you could use filters or a Query/View using your txtbox/combo value as the parameter and the after update event to requery the data

**Sparky** · #3 28-Feb-2008, 09:37 PM

This might help mate...

http://www.thescripts.com/forum/thread605958.html

ThomasMc · #4 28-Feb-2008, 09:42 PM

yeah just like that

Shepherdess · #5 28-Feb-2008, 10:00 PM

Thanks!

I am going to sleep now but will play with this tomorrow :-)

harpistic · #6 29-Feb-2008, 08:54 AM

Quote:

“

Originally Posted by Shepherdess

I am preparing a simple database in Access 2003 and have been struggling with two things:

- I have got two fields in a table, one for Country, the other for County. I wanted to change the form in such a way that if someone chooses England as a Country, only English counties appear. I have tried two methods: one with visible yes/no for certain counties (when country=England, only English counties are visible), the other with message box, e.g. "Fife is not in England", but I can't seem to get it right. Do you think I will have to put English, Scottish, Welsh counties in separate fields (it only just occured to me as an option :-) )?
- the other problem is with choosing topic. Users will have to choose main category, and then (if they want) choose subcategory. I have created the lookup table for categories, but I am not sure how to set up subcategories.

I have looked for the answers in two books (one about Access 2003, the other about VBA) and in Office.microsoft.com but could not find anything even remotely relevant. I would not like you to write a code for me, just give me some hints where to look for the answers

Thanks in advance.

”

Use two lookup tables, in the first one have country/country id, in the second one have county, countyid. In the OnChange event for the first combo, set it to combo2.column(x) = the value of the id column of the first one.