Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

using multiple Listboxes 1

Status
Not open for further replies.

tourguy

Technical User
Nov 6, 2003
27
US
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..
 
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?
 
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.
 
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?
 
yeah the query is running from the form. As for the command button, I made it to execute the Query, but nothing works.
 
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?
 
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
 
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:
Code:
[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:
Code:
[blue][forms]![frmSearch].[qryMonths]
[forms]![frmSearch].[qryYear][/blue]
Give it a whirl and let me know . . . . .

Calvin.gif
See Ya! . . . . . .
 
Thanks alot AceMan, this really made it easy for me. thank you Mikee for helping too..
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top