Multiple combo boxes and text boxes on a search form

[pabloUSA]

Hi guys,
I'm absolutely new to Access and I'm trying to build an database for aircraft operators.

I've got the basic tables structure and relationships but I'm stuck on building an search form to filter records by user input.
I've got following controls on my form (unbound):

1. AircraftType (combo box) from tblAircrafts
2. CompanyName (combo box) from tblListOfAircraftsOperators
3. TeailNumber (text box) from tblAircraftOperators
4. AirportNameSearch (combo box) from tblAirports
5. PassengersNumber (text box) from tblAircraftOperators
6. ManufactureYear (text box) from tblAircraftOperators
7. SourceSearch (combo box) from tblInfoSource
8. CountrySearch (combo box) from tblCountry
9. CategorySearch (combo box) from tblAircraftCategory
10. EamilToOperator (text box) from tblAircraftOperators
11. InteriorPhoto (Bound object frame) from tblAircraftOperators
12. ExteriorPhot (bound object frame) from AircraftOperators

I need to enable users to search for aircrafts based on those criteria. As I mentioned I'm new to Access and I don't have any advanced coding skills. I have a query build to perform the search and this is the code I've managed to write so far:

SELECT AircraftOperators.RegistrationNumber, AircraftOperators.PassengersNumber, AircraftOperators.ManufactureYear, AircraftOperators.EmailToOperator, AircraftOperators.ExteriorPhoto, AircraftOperators.InteriorPhoto, tblListOfAircraftOperators.OpratorName, tblAircrafts.AircraftType
FROM tblAircrafts INNER JOIN (tblAirports INNER JOIN (AircraftOperators INNER JOIN tblListOfAircraftOperators ON AircraftOperators.CompanyName = tblListOfAircraftOperators.ID) ON tblAirports.ID = AircraftOperators.Base) ON tblAircrafts.ID = AircraftOperators.AircraftType
WHERE (((tblListOfAircraftOperators.OpratorName)=[Forms]![SearchForm]![CompanyName]) AND ((tblAircrafts.AircraftType)=[Forms]![SearchForm]![AircraftType])) OR (((tblAircrafts.AircraftType)=[Forms]![SearchForm]![AircraftType]) AND ((IsNull([Forms]![SearchForm]![CompanyName]))<>False)) OR (((tblListOfAircraftOperators.OpratorName)=[Forms]![SearchForm]![CompanyName]) AND ((IsNull([Forms]![SearchForm]![AircraftType]))<>False)) OR (((IsNull([Forms]![SearchForm]![CompanyName]))<>False) AND ((IsNull([Forms]![SearchForm]![AircraftType]))<>False));

Any further coding attempts were unsucesfull.

Please help!!!!

 

[AOB]

Hi pabloUSA,

Couple of questions...

Where do the users see the results of the query you're trying to perform? Do you use some kind of continuous subform, with the record source defined by the SQL you've provided? Or does it just run the query and display the raw datasheet?

Are users mandated to select something from each of the comboboxes? Or could they leave them all blank and view all of the resultant records? Could they just search by some (e.g. AircraftType and CompanyName) but leave others blank?

What is the purpose of the textboxes? They don't seem to form part of the query so why are they there? Can random (unvalidated) text be entered into them and, if so, what happens with it?

Looking at your query, it appears all of the necessary controls have to be populated with something for the query to return something.

For example :

WHERE (((tblListOfAircraftOperators.OpratorName)=[Forms]![SearchForm]![CompanyName])....
 
...AND ((IsNull([Forms]![SearchForm]![CompanyName]))<>False)) OR (((tblListOfAircraftOperators.OpratorName)=[Forms]![SearchForm]![CompanyName])....

If nothing is selected from [CompanyName], this evaluates as :

WHERE (((tblListOfAircraftOperators.OpratorName)=Null....
 
...AND ((True)) OR (((tblListOfAircraftOperators.OpratorName)=Null)....

Which doesn't really make any sense? It will look for records where OpratorName is empty. Of which, presumably, there are none? So you won't get anything returned? No matter what else has been selected?

Check out this thread, it may help you to re-write this properly

Alternatively...

I've developed a couple of search forms similar to what you're trying to achieve but I use VBA to construct the SQL string based on the selections in each of the dependant controls.

I use both comboboxes (i.e. only one selection allowed) and listboxes (i.e. multiple selections allowed) in the search form and then have a continuous subform which lists the resultant data. It's a bit clunky, admittedly, but it's reasonably fast and gives you a lot more control.

For convenience, I use a naming convention for all of my dependent controls, based on the field they are linked to. So the combobox for, say, AircraftType, I would name 'cboAircraftType' (the exact same name, just prefixed by 'cbo' so I know what type of control it is). I use 'lst' as the prefix for listboxes, 'txt' for textboxes etc. - it's entirely up to you what you use but it's good practice.

So let's say you have a subform on your main form where you display your results (I've called it 'sfmResults' for this example)

It has the following RecordSource :

SELECT AircraftOperators.RegistrationNumber,
AircraftOperators.PassengersNumber,
AircraftOperators.ManufactureYear,
AircraftOperators.EmailToOperator,
AircraftOperators.ExteriorPhoto,
AircraftOperators.InteriorPhoto,
tblListOfAircraftOperators.OpratorName,
tblAircrafts.AircraftType
FROM tblAircrafts
    INNER JOIN (tblAirports
        INNER JOIN (AircraftOperators
            INNER JOIN (tblListOfAircraftOperators
            ON AircraftOperators.CompanyName = tblListOfAircraftOperators.ID)
        ON tblAirports.ID = AircraftOperators.Base)
    ON tblAircrafts.ID = AircraftOperators.AircraftType)

Or in other words, all available records

Then you have a command button on your main form which calls the following subroutine which is situated in the main form's code :

Option Explicit
 
Public Sub FilterData()
 
    Dim strFilterCriteria As String
    Dim ctl As Control
    Dim itm As Variant
 
    For Each ctl In Me.Controls
 
        Select Case TypeName(ctl)
 
            Case "ListBox"
 
                If ctl.ItemsSelected.Count > 0 Then
 
                    strFilterCriteria = strFilterCriteria & " AND [" & Replace(ctl.Name, "lst", "") & "] IN ("
 
                    For Each itm In ctl.ItemsSelected
 
                        strFilterCriteria = strFilterCriteria & Chr(34) & ctl.ItemData(itm) & Chr(34) & ","
 
                    Next itm
 
                    ' Remove the last comma and add closing parenthesis for IN clause
 
                    strFilterCriteria = Left(strFilterCriteria, Len(strFilterCriteria) - 1) & ")"
 
                End If
 
            Case "ComboBox"
 
                If Not Nz(ctl.Value,"")="" Then
 
                    strFilterCriteria = strFilterCriteria & " AND [" & Replace(ctl.Name, "cbo", "") & "] = " & Chr(34) & ctl.Value & Chr(34)
 
                End If
 
        End Select
 
    Next ctl
 
    ' Remove leading AND from strFilterCriteria
 
    strFilterCriteria = Trim(Mid(strFilterCriteria, 5))
 
    ' Apply to subform
 
    With Me.sfmResults.Form
        .Filter = strFilterCriteria
        .FilterOn = True
        .Requery
    End With
 
 
End Sub

What's useful here is that you can add more comboboxes and listboxes as you develop the form, without needing to change the code (as long as you strictly follow the naming convention each time)

If you want to have controls on your form which should not form part of the filter, I tend to use the Tag property. So I set the Tag to be something like 'Filter' - again, this can be arbitrary - and then add that as a separate check when constructing the WHERE clause in the VBA :

For Each ctl In Me.Controls
    If Me.Tag = "Filter" Then
        Select Case TypeName(ctl)
        ...etc

It's a bit more complicated and probably a bit of a 'deep-end' jump if you're not already familiar with VBA to some extent but just putting it out there anyway....

Best of luck with it

 

[krowe]

Hi

Just to put my pennies worth in, as i'm am attempting a very similar thing..

I have created a series of queries based on my combo boxes.

query one just filters out which type of application (and returns all values if blank)

SELECT qryAllCasesWithClient.*, qryAllCasesWithClient.Type AS TypeFilter
FROM qryAllCasesWithClient
WHERE (((qryAllCasesWithClient.Type)=Forms!frmReportSelector!SelectType)) Or (((Forms!frmReportSelector!SelectType) Is Null));

query 2 is based on query one, but filters the authority:

SELECT qryReportSelector1_Type.*, qryReportSelector1_Type.Authority AS AuthorityFilter
FROM qryReportSelector1_Type
WHERE (((qryReportSelector1_Type.Authority)=Forms!frmReportSelector!SelectAuthority)) Or (((Forms!frmReportSelector!SelectAuthority) Is Null));

query 3 - (where i'm stuck right now) is based on query 2 and filters by application date

and so on...

I thought this was a neat way to allow me to create just one report with many parameters that could be set by the user in any combination.

Kev