Combo Box Selection of Query Values in Form

[kevinnaomi]

Hi,
I have a table that contains over 500 lines of purchasing data. I have organised with 4 columns separating the information. The table is called "costs". The columns are Category, Action, Type and Sub-Type.
Each of these columns has multiple entries of different values. e.g. Category: Hardware, Software and Misc. Action: Purchase, Costs, Maintenance...

I am trying to create a form that will give me combo boxes for each of these columns. Once I have selected the 4 types of entries I want to search for, I want to find all records in the COSTS table that have the matching criteria.
E.G. and row that has Hardware+Purchase+Server+Machine.

If anyone could give me some pointers it would be greatly appreciated.

 

[Darrylles]

Hi,

Difficult to give a definitive without the relationships, but the general idea is this...

When user has selected a value from each combo and wants to display resultant data: you have some code that builds the SQL query dependant on selected values....

dim SqlStr as string

SqlStr = "select * from Costs where Category = " & me!cmbCategory & " and Action = " & me!cmbAction & " and Type = " & me!cmbType & " SubType = " & me!cmbSubType

You then run a form and use SqlStr as it's Control Source.

This is very general and depends on whether you have all fields in the Costs table or whether you have related tables for Category, Action etc.

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[kevinnaomi]

Darrylle,
Thank you for this information.
In my database there are no relationships to or from the COSTS table. It is totally independant.
I am sorry, but I am not sure how to use the SQL code you provided...Should it be in a macro or something?
The form will have 4 combo boxes that get contents from the columns of the COSTS table. Then a Find button that, i expect, runs the SQL code you gave to query the COSTS table and show all matching records???

I appreciate your help very much.

 

[Darrylles]

Hi,

Create another form (call it frmResults), draw all fields you want to display from the Costs table.

On your search form - paint a command button.

In it's <On Click> code event type this....

docmd.openform "frmResults"
frmResults.controlsource = SqlStr
frmResults.requery

You'll probably get errors due to the SQL string - just come back with any errors.

Regards,

Darrylle

&quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot; darrylles@totalise.co.uk

 

[kevinnaomi]

Darrylle,
Thanks. I will try that now and let you know...

 

[kevinnaomi]

Darrylle,
I created form called &quot;frmResults&quot; and added combo boxes for fields I want to query.
Then I created new form with command button and added the docmd.openform code that you gave me....
when I tried it, it gave me error message saying that it &quot;can`t find the macro docmd.&quot; should I create a macro with the first code in it??? or is a module required?