• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1191
  • Last Modified:

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

I have a Query Named qryA and a list box named listB with list of different countries. I want selected items (on or more than one) in List box to be passed on as  parameters to query A.

Can someone please help with the VBA Code for

1. Query for the list box to close after selecting multiple fields and clicking OK button.
2.Query to pass the selection to parameters  for the Query A

Any sample database could be of much more use...
Regards
Siva
0
siva_iaf
Asked:
siva_iaf
  • 9
  • 8
  • 4
1 Solution
 
TextReportCommented:
When you get the list of parameters what are you doing with the query, are you opening a report or form?
Cheers, Andrew
0
 
siva_iafAuthor Commented:
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
0
 
TextReportCommented:
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
TextReportCommented:
BTW it may help to know which version of Access you are using also. Cheers, Andrew
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
siva_iafAuthor Commented:
Access 2003..
So you mean to say that the form with the list of countries would not open just by clicking the query.?
0
 
TextReportCommented:
"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
0
 
siva_iafAuthor Commented:
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...
0
 
TextReportCommented:
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
0
 
siva_iafAuthor Commented:
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
0
 
TextReportCommented:
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

Open in new window

0
 
Jeffrey CoachmanMIS LiasonCommented:
siva_iaf,

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

JeffCoachman
0
 
siva_iafAuthor Commented:
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
0
 
siva_iafAuthor Commented:
Hi Guru

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

Reg
Siva
0
 
TextReportCommented:
Create a new query called qryB, use qryB as the source of the data for qryB and include the fields you want.
Cheers, Andrew
0
 
Jeffrey CoachmanMIS LiasonCommented:
siva_iaf

OK,

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

JeffCoachman
0
 
siva_iafAuthor Commented:
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
0
 
TextReportCommented:
There is no way that I know that would work for you the way you are wanting it to. Please take my advise, from someone who has been working in Access for over 17 years, the form should drive the query and not the query driving the form.

When the query opens you do not have any events to run your code, queries are not really designed as your User Interface, more an object that does the work for you.

Cheers, Andrew
0
 
siva_iafAuthor Commented:
Ok thanks a Ton  lot i am closing this question after satisfactory response...
0
 
Jeffrey CoachmanMIS LiasonCommented:
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

0
 
siva_iafAuthor Commented:
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
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 9
  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now