Multiple Selections in list box

[pabloUSA]

Hi,
Could anyone please help me to modify my query to accept parameters from a multiple choice list box. This is the SQL code behind my search query:

SELECT AircraftOperators.RegistrationNumber, AircraftOperators.PassengersNumber, AircraftOperators.ManufactureYear, AircraftOperators.EmailToOperator, AircraftOperators.ExteriorPhoto, AircraftOperators.InteriorPhoto, tblListOfAircraftOperators.OpratorName, tblAircrafts.AircraftType, tblAirports.AirportName, InfoSource.SourceType, tblCountry.CountryName, tblAircraftCategory.AircraftCategory
FROM tblCountry INNER JOIN (tblAircrafts INNER JOIN (tblAircraftCategory INNER JOIN (InfoSource INNER JOIN (tblAirports INNER JOIN (AircraftOperators INNER JOIN tblListOfAircraftOperators ON AircraftOperators.CompanyName = tblListOfAircraftOperators.ID) ON tblAirports.ID = AircraftOperators.Base) ON InfoSource.ID = AircraftOperators.Source) ON tblAircraftCategory.ID = AircraftOperators.AircraftCategory) ON tblAircrafts.ID = AircraftOperators.AircraftType) ON (tblCountry.ID = tblAirports.Country) AND (tblCountry.ID = AircraftOperators.Country)
WHERE (((AircraftOperators.RegistrationNumber) Like "*" & [Forms]![SearchForm2]![TailNumber2] & "*") AND ((AircraftOperators.PassengersNumber) Like "*" & [Forms]![SearchForm2]![PassengersNumber2] & "*") AND ((AircraftOperators.ManufactureYear) Like "*" & [Forms]![SearchForm2]![ManufactureYear2] & "*") AND ((tblListOfAircraftOperators.OpratorName) Like "*" & [Forms]![SearchForm2]![CompanyName2] & "*") AND ((tblAircrafts.AircraftType) Like "*" & [Forms]![SearchForm2]![AircraftType2] & "*") AND ((tblAirports.AirportName) Like "*" & [Forms]![SearchForm2]![AirportNameSearch2] & "*") AND ((InfoSource.SourceType) Like "*" & [Forms]![SearchForm2]![SourceSearch2] & "*") AND ((tblCountry.CountryName) Like "*" & [Forms]![SearchForm2]![CountrySearch2] & "*") AND ((tblAircraftCategory.AircraftCategory) Like "*" & [Forms]![SearchForm2]!
[List44] & "*"));

I have got an unbound multiple list box called List44 (Row Source: query based on table tblAircraftCategory, Multi Select - Extended) that needs to be passing parameters to my main query called AircraftSearch2. The multiple choice list box have the following fields:

1. Piston
2. Turbo Prop
3. Entry Level Jet
4. Light Jet
5. Super Light Jet
6. Midsize Jet
7. Super Midsize Jet
8. Heavy Jet
9. Ultra Long Range
10. Helicopter
11. Air Ambulance
12. Cargo
13. Vip Airliner
14. Airliner

The user will use the form for selecting search criteria (the form is called SearchForm2 and has 5 combo boxes, 3 text boxes and one multiple choice list box)
I'm very new to access and do not have any advance coding skills. Could anyone guide me through modifying (or coding a separate module) my query to include my multiple choice list box in my query?

Thanks

 

[pr2-eugin]

You need to create a Dynamic Query. Inbuilt/Pre-compiled query is not capable of doing this. You will need to loop through the Listbox to get all the constraints.

 

[Mile-O]

You need to create a Dynamic Query. Inbuilt/Pre-compiled query is not capable of doing this. You will need to loop through the Listbox to get all the constraints.

I'm very new to access and do not have any advance coding skills. Could anyone guide me through modifying (or coding a separate module) my query to include my multiple choice list box in my query?

There are many examples on the forum of how this can be done. But, before searching, know that it will require some VBA coding to get the desired effect.

 

[pabloUSA]

OK. Thanks for info. Although my coding skills are quite poor I'm always up for a challenge. Could you please let me know if I have to start the entire query all over again or I can build up on stuff that I already have (e.g. in other words, I have to delete the entire query and start building a new one or I could add a Dynamic Query to the existing structure).

Thanks

 

[pabloUSA]

I've been searching through forum's posts and didn't find (or missed it) anything that could be directly applicable to my situation. I found this code for report generated from dynamic query:

Dim strDocname as String
Dim Criteria as String
Dim i as Variant

strDocname="myReportName"

If IssueList.ItemsSelected.Count > 0 Then
' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![IssueList].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[ID]=" _
& Me![IssueList].ItemData(i)
Next i
Else
MsgBox "Please select a model."
Exit Sub
End If
DoCmd.OpenReport strdocname, acPreview, , Criteria

Can anyone please help me to adapt it to my circumstances?

Thanks