how to use Multi-Select List box in a Parameter Query

When you get the list of parameters what are you doing with the query, are you opening a report or form?
Cheers, Andrew

When i run the query it should open the form for me to multiselect the countries and once i click Ok on that form the query should give me the results.

Once the query is run it gives me the result in a table format which i then export to excel.
Sample-Database.mdb

Not possible directly as you wish I am afraid. You will need to open the form, allow the user to select the entries in the multi select listbox then your code will need to process the SELECTED items into a list that you can use to modify the SQL of the query or update another query that you can use for the export.

Cheers, Andrew

BTW it may help to know which version of Access you are using also. Cheers, Andrew

siva_iaf,

Try this.

It performs as you specified.

I am sure you can adapt it to work in your database.

;-)

JeffCoachman
Access-EEQ24149288BasicMultiSele.mdb

Access 2003..
So you mean to say that the form with the list of countries would not open just by clicking the query.?

"So you mean to say that the form with the list of countries would not open just by clicking the query.?" Correct
You would normally open the form then the button would do the work after the user had made their selection.
Cheers, Andrew

Understood the second part. But I need the form to open up when i try to run the query and take up the countries that i selected..and once i press Ok on that form ...it needs  to give the results specific to those countries...

If I am getting what you are saying you want to open the query that opens the form that then filters the query.
The normal would be to open the form that then opens the query. The same end result but much simpler.
Cheers, Andrew

The reason i am saying this is that i have a main form with list of all queries in the DB..and this is going to be the user interface..and the user can click any one of the queries to run it and see results..and for one of those queries listed there i want the user to have additional option for selecting some more criterias (countires here as eg...using multiple list box)..
Thanks

The changes you need to make to get this working are as follows.

1. Remove the parameter from qryA
2. Create qryB that selects what you want from queryA
3. In your form rename the listbox to lstCountries
4. Put the code below in the Event Procedure of your command button. The code takes the selection made by the user, modifies qryB and then exports qryB

Cheers, Andrew

Dim strSQL As String
Dim strWHERE As String
Dim varItem As Variant
 
    strSQL = "SELECT *" & vbCrLf & _
             "FROM qryA" & vbCrLf
    
    For Each varItem In Me.lstCountries.ItemsSelected
         strWHERE = strWHERE & ", " & Chr(39) & Me.lstCountries.ItemData(varItem) & Chr(39)
    Next varItem
    
    If strWHERE <> "" Then
       strSQL = strSQL & "WHERE [Country] IN(" & Mid(strWHERE, 3) & ")" & vbCrLf
    End If
    
    CurrentDb.QueryDefs("qryB").SQL = strSQL
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryB", "C:\Test.xls", True

Select allOpen in new window

siva_iaf,

Not trying to interrupt, but did you try the sample I submitted, ( http:#a23658299) you never gave me any feedback?

JeffCoachman

Hi Jeff
I had a look at the Db you had sent but the prob is that in it i  need to access the query thorugh the form....but i want to access the form through the query...

Reg
Siva

Hi Guru

This line is not understood..
# Create qryB that selects what you want from queryA #

Reg
Siva

Create a new query called qryB, use qryB as the source of the data for qryB and include the fields you want.
Cheers, Andrew

siva_iaf

OK,

It seems that TextReport has a better understanding of what you are looking for.

JeffCoachman

Guru
Thanks for the fantastic code, the query B is giving me the results for the countries that i have selected in list box but  the problem is that I have to still open the form to select the list of countries..
I need the form to open automatically when i click the query B.
Summarily here is what i want:

Step1.  I click the query (with parameters)
Step2:  THe form lstcountries should pop up asking me to select the countries specific
Step 3: I select then selcet the countries and click Ok
Step4: I  get the results (similar to Query B)
Regards
Sample-Database.mdb

Ok thanks a Ton  lot i am closing this question after satisfactory response...

I agree with Andrew,

Even to the point where I will withdraw my posts for consideration.

To be honest, I can't see what the advantage to using the query to run the form would be?

My sample follows the same logic as Andrew uses here.
There is nothing new in my sample that Andrew could not duplicate
You can use the info in it, if you like, and give Andrew the points
keep the sample as my gift to you.

;-)

Jeff

Apologies Jeff..but please consider
1. I am new to access ..so my knowledge itself is limited
2. I am new to this site as such ...Really getting to know now as to how things work here...

Hence ..forgive me and i will keep in mind in future.. :-)

Regards
Siva