How to combine a subform table to selections made in a combo box. And more questions.

[Ploepsie]

Hey, first of all I want to state that I am very new to using Access and therefor the question might be relevantly easy to solve but I have spent hours thus far to find the answer online and in Access 2000.

Question uno (1)
I have a table with around 60,000+ lines which gets updated every week. There has to be certain selections so I and others can get the relevant information ( the info we need) in just a few clicks.

I have made around 6 combo boxes that refer to each others selection i.e. if box 1 selects A box 2 only shows the possibly entries with the A value of box 1.

Now I am trying to create a subform based on the same table where combo box 1 till 6 get their info from. I would like the AfterUpdate event of combo box 1 , 2 ,3 ,4 5, 6 requery the subform table. Well that isn't that hard to do, what I find utterly annoying is that I have no clue how to make the subform look at the values entered / selected in the combo boxes? As I tried to enter in recordsource a select query WHERE [table]![col] like [Forms]![form]![combo] which resulted into #name errors.

Question 2
if I have a table like :

ID DATA MORE
Name Peter
LOC UK
PHON 111..
Name Walter
LOC US
PHONE 222..
etc. etc.

And I want to filter this on for example NAME, LOC, PHONE. Creating combo1 wouldn't be that hard, letting it search for name, group by and voila.

For the LOC (combo2) it would be harder (thus hard I dont know how too, I have a easier, but less beautifull solution atm)as it should check the choice made in combo1 and select all LOC that have the same NAME as selected in combo1.

For combo3 it would be similar to it 2.

I hope any of you would be so kind to help a new one out since I am eager to learn acces inside out. If necessary I can upload a sample database so you have a better idea of what my problem is.

Thanks for the help, hopefully in the near future I can join you lot with handing out information to newer users.

Kind regards!

[NeoPa]

Hi Ploepsie. Welcome to Bytes!

Let's start with a couple of basics. First we normally post separate questions in their own threads. Things can get complicated otherwise. As these two seem very general at the moment and overlap somewhat, we'll leave this as one for now, but with a heads up that you may need to fork the second one out at a later stage when we have a clearer understanding of where we are.

Secondly, this is posted in the SQL Server forum, but it's an Access question. I'll move it across for you.

[NeoPa]

As far as the technicalities of the question goes, I'd direct you first to Example Filtering on a Form. There is a database in there to download and play with. It also discusses some of the issues you are struggling with related to filtering forms. You should also consider seriously whether a subform is actually required. It seems to me at first glance that a single form with unbound filter controls in the Header section and bound data controls in the Detail section (as illustrated in the linked article) would suit you perfectly.

Let us know when you're comfortable with these concepts and then we can discuss cascading the filtering, or limiting the filter options in the unbound ComboBox controls based on selections made from other of the ComboBoxes.

[Ploepsie]

ok

sorry for posting it in such way. I will remember it for the next time I make a topic. Also I was looking through the directory for my question and this one seemed the most fitting :P. haha, excellent example of my knowledge of access. But hey, we got to start somewhere.

I'll check the example DB out right now, thanks for the help. And I'll comment back afterwards!

Good day,

Ploeps

[NeoPa]

Sounds good.

In truth this is such a commonly asked question (as is the belief that subforms are required when that's almost never true) that you will gain much important understanding when you see and comprehend how this can be done.

Have fun, and let us know when you need further explanations etc.