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));
It's enabling two first combo boxes to perform the search correctly but I any attempts to write further code were unsuccessful.
Could anyone help me with this please?
Thanks
:banghead: P.S. I've been stuck on this for almost a week now.
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));
It's enabling two first combo boxes to perform the search correctly but I any attempts to write further code were unsuccessful.
Could anyone help me with this please?
Thanks
:banghead: P.S. I've been stuck on this for almost a week now.
Last edited: