set search parameters by clicking in list box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am not a computer expert, so bare with me....

I want to open a form, double click in a list box, and have the selection I
clicked on set the parameter value for a query. Little beyond my abilities,
but probably simple if one of you can help. I am using Access 2002.
 
You can put the listbox in as the criteria for the query. If your listbox is
named "lstCriteria" and it related to the "Product" field in your query, then
in the product field criteria you could type:

=Forms!MyForm.lstCriteria

Of course, that only works if the form is open. Otherwise, you could build a
function to return the value of lstCriteria if the form is open, with a
provision for if it isn't, and then enter that function name in as criteria
for the Product field:

=MyFunction()

All of this only works if the multi-select property of the listbox is set to
false. If the property is set to TRUE, then you need to build a function
which takes the multiple selections of your listbox and concatenates them
into an SQL "WHERE" clause without the word "WHERE" (ie, Product = "Product1"
OR Product = "Product2" OR Product = "Product14"), and then apply it as a
filter to the query. If that is the direction you have to go, enter the
function into the double-click event of the listbox.
 
You shouldn't directly open the query. Rather you should have a form or
report based on the query. Create a pop-up form named PFrmCriteria and put a
listbox named QueryCriteria on the form. Put the following code in the
AfterUpdate event of the listbox:
Me.Visible = False
Open the query in design view and put the following expression in the
criteria of the appropriate field:
Forms!PFrmCriteria!QueryCriteria
At the button to open your form or report, put the following code in the
Click event:
DoCmd.OpenForm "PFrmCriteria",,,,,acDialog
DoCmd.OpenForm "MyForm" 'If the query is the basis of a form named MyForm
DoCmd.OpenReport MyReport", acPreview 'If the query is the basis of a
report named MyReport
DoCmd.Close, acForm "PFrmCriteria"
 
Back
Top