using multiple Listboxes 1

[tourguy]

Hi, I have a query called 'Query1' and a form called 'frmSearch'. In the frmSearch, I want to be able to use two listboxes to limit the search for Query1. One listbox is called 'Year' and the other called 'Months'
So if I choose January under the listbox Months and 2004 under the listbox Year, and when I press a command button, it will open up the Query1 with the search. How would I be able to do this?? Thank you..

 

[Mike555]

You need to create criteria for the Year and Month fields in your query. Probably look something like...

=[Forms]![frmSearch]![Year]
=[Forms]![frmSearch]![Months]

HTH

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[tourguy]

Ok, I added
=[Forms]![frmSearch]![Months]
under the Months criteria
and
=[Forms]![frmSearch]![Year]
under the Year criteria

Is this all to it, because now I am getting nothing out of the query? It does pop up an Input message box for me to type in something, but now it is giving me nothing when I type in something.

 

[Mike555]

Are you running the query from the form? The query will only work properly if it is executed from the form, via a command button or something else after the Months & Year fields are populated.

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[tourguy]

yeah the query is running from the form. As for the command button, I made it to execute the Query, but nothing works.

 

[Mike555]

tourguy, Can you confirm that your listboxes are, in fact, named Year and Months?

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[PHV]

Can you please post the SQL code of Query1 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya tourguy . . . . .

First:

[blue]=[Forms]![frmSearch]![Year]
=[Forms]![frmSearch]![Months][/blue]

The above returns the [blue]bound columns[/blue] of the listboxes, which in your case is more than likely the [blue]primaryKeys[/blue]. So your already extracting the wrong data.

The following won't work as well and for the same reason:

[blue]=[Forms]![frmSearch]![Year].Value
=[Forms]![frmSearch]![Months].value

=[Forms]![frmSearch]![Year].ItemData(Idx)
=[Forms]![frmSearch]![Months].ItemData(Idx)[/blue]

You need to return data from the proper column, and could use the [blue]Column Property[/blue]:

[blue]=[Forms]![frmSearch]![Year].Column(1)
=[Forms]![frmSearch]![Months].Column(1)[/blue]

However, when used in criteria fields, the above will cause an error. I believe its because the [blue]Column Property[/blue] [purple]is only available to 'VBA' at run time[/purple]. So for this reason try the following (should work!):

Add the following functions to the code module of frmSearch:

[blue]Public Function qryMonths()
   qryMonths = Forms!frmSearch!lbMonths.Column(1)
End Function

Public Function qryYear()
   qryYear = Forms!frmSearch!lbYear.Column(1)
End Function[/blue]

Then add the following criteria to the query proper:

[blue][forms]![frmSearch].[qryMonths]
[forms]![frmSearch].[qryYear][/blue]

Give it a whirl and let me know . . . . .

See Ya! . . . . . .

 

[tourguy]

Thanks alot AceMan, this really made it easy for me. thank you Mikee for helping too..