Access 2016 - help creating form input to filtered report by dropdown list.

Background: Access 2016 .accdb file on Windows 10 Pro. Single user at the moment. 8 tables, 6 queries, 7 forms, 3 reports.

Project: We do temporary hiring/staffing for many different industries. I've been tasked with creating a custom database that my users will be able to sort by a number of criteria and generate reports. Mainly, employees. We are going to store their personal information, emergency contact info, and safety training/certifications. If we get a job in, say, New York, that requires 10 hydroblasters with confined spaces training my employees would like to open a form(menu) and drop down the state, type of trade, and safety training required and generate a report showing all ~X number of employees that fit those requirements.

Where I'm at: I've created the EmployeeDB table, loaded it with several fields. Everything from first name to SSN

Notable fields: State - Lookup field from StateAbbrevDB table Job Types - Lookup field from JobtypeDB table Site Specific Training - Lookup field from SiteSpeceificDB table Status - Lookup field from EmployeeStatusDB table @ - attachment field for copies of certifications, IDs, etc.

Forms:

I've created a "main menu" which opens forms for AddANewEmployee - which adds records. By far the easiest form so far.

I've created an EmployeeSearch form which works for specific things by using text boxes and the query arguments: Is Null Or Like "" & [Forms]![EmployeeSearch]![TxtFirstName] & "" - and several OR statements

I'm trying to create an EmployeeFilter form. I cannot wrap my head around how to place the fields on the form - unbound combo box? text field with input mask? Do I use a control source? How can I provide my users a dropdown list containing all 50 states and allow them to filter/browse our employee database based on which state they're in? If it's not too much harder, I'd like the dropdown to be checkboxes and allow multiple state searches, for example on the border of CO, AZ, NM, UT we'd want to see candidates from all 4 states.

I AM new to this but everyone has to start somewhere. Just hoping to get some help pushing past this one form. If I can make it happen for this table, I should be able to reproduce it on my other tables afterwards.

I've uploaded a copy of the .accdb file here: http://s000.tinyupload.com/?file_id=73486783733975093056

Any help would be greatly appreciated -- PS all data in the database is made up gibberish, I'm not posting people's actual SSNs.
Zy0n1cChief Information OfficerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Most of us will not download files from other sites.  Please use this sites tools to upload your file.

For starters, get rid of all the table level lookups.  They are going to come back to bite you once you start writing queries and code.  comboboxes belong on forms.  They do NOT belong on tables.  This is a crutch for people who are not competent to build a query with a join.

To select multiple search terms, you will need to use a list box with the multi-select option and then you will need to write code to construct the In() clause AND the query that will use it.

Your search form should be based on a query that joins the Employee table with the EmployeeSkills table.  This of course presumes that you will ALWAYS select at least one skill.  Your code will make that a requirement.  Of course, if you use a listbox and allow multiple skills to be selected, be prepared to get the same employee record multiple times.  Once for each skill that matches.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Zy0n1cChief Information OfficerAuthor Commented:
Thanks!  Got it working!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.