Selecting records in a subform based on several combo boxes

[Ste4en]

Here is an example of what I am trying to do:

I have a main form which has 4 combo boxes all based on tblPeople.
Combo0 is skill
Combo1 is discipline
Combo2 is crart
Comb03 is active

The sub form is a datatable list of the tblpeople.

How do I apply the filter to the datatable. I am assuming I need to build a sql statement somewhere like SELECT from tlpeople WHERE me!combo0 = [tblpeople].[skill] and me!Combo1=[tblpeople].[discipline] etc

Is there a sample database? or can somebody tell me how to go about this.

Thanks
Steve

 

[Ste4en]

Thanks - it did not quite do what I wanted - I found the link below in case anyone else is looking - to a similar example which I can use, I think.

http://www.candace-tripp.com/_pages...s_downloads.asp


Steve

 

[Ste4en]

Can I change my mind. I dont think either do what I need.

I want to set a value in say three combo boxes in a form. Then press a button which will take the values and write a sql statement to filter the sub form.

Any example of this?

Thanks

 

[Ste4en]

Thanks for your help.

I have attached a small database to descibe what i am doing. Open the frmMain. There is a text box which shows the SQL statement I am trying to achieve. Well - it is shown for some specific values - I need it to work for the values selected in the combo box.

The code for this would go in the On Click event for the button, I think. I suppose the SQL should also allow for only one or all combos to be selected.

Thanks again
Steve

 

[Ste4en]

Maybe I am on the wrong road - I changed the statement in the example - I hope it will explain. I need a new statement written each time I press that button.


SELECT tblpeople.Name, tblpeople.Skill, tblpeople.Discipline, tblpeople.Level, tblpeople.ID
FROM tblpeople
WHERE (((tblpeople.Skill)="WHAT IS IN COMBO1") AND ((tblpeople.Discipline)="WHAT IS IN COMBO 2") AND ((tblpeople.Level)="WHAT IS IN COMBO3"));

 

[Ste4en]

Thanks, that writes a SQL, exactly what I wanted. the way you have laid it out i can understand too - that has been part of my trouble.

Trying to explain why I need it - I can use query design view and do one offs, but the people who will be using this database know even less than i do and so will just make thier selections in the combo boxes and the subform below will display thier selection. Doing it this way I am also learning something new.

Now although the code you provided does provide a sql statement how do I apply that filter statement to the sub form.

Thanks again for your help.
Steve

 

[Ste4en]

Bingo - thats it - thanks
Steve