Solved Multiple Form Filter Question (1 Viewer)

[Methodikal]

Hi,

I have a Form with a subform.

The top or main form is Employees. The subform is called Teleworking.

I have Two Employee Tables in Relationship with PositionID as a primary key pointing to ReportsTo as a Foreign Key to create join since some Employees are supervisors.

On the Employees table I have a field named IsSupervisor (Yes/No - Checkbox) and a field named IsTeleworking (Yes/No - Checkbox).

I am creating a form to Enter in work schedules for Employees that telework.

On the form I wish to create a combobox that has the Supervisors names and filters the form to only provide the Employees of the supervisor selected that are teleworking.

The subform collects the Schedule.

On the Teleworking Table the EmployeeID is saved with the record.

My question is... How would I create the combobox to show only the Supervisor's FirstName and LastName while filtering the form?

I assume the combobox row source would use a sql statement; however, I'm a Noob.

Maybe something like:

SELECT DISTICT [EmpFirstName], [EmpLastName] From tblEmployee WHERE IsSupervisor=Yes;

Then the After-Update would be some VBA. But honestly I have no idea what the heck to do.

Can anyone please help?

 

[arnelgp]

maybe you can see what is going on in this form.

 

[Methodikal]

I will view that as soon as I get home. I can't view on my work laptop. It won't let me download. Thank you.

 

[theDBguy]

Hi. Welcome to AWF!

If the sample file Arnel posted doesn't help, you might consider posting a sample copy of your db to help us understand what you need.

 

[Methodikal]

Hi. Welcome to AWF!

If the sample file Arnel posted doesn't help, you might consider posting a sample copy of your db to help us understand what you need.

I can always post an example with some test data.

 

[Methodikal]

OMG! @arnelgp are you serious? How did you know this so well? I have been taking courses and trying for a long time and you do it like nothing. Is there a book or something on Access and VBA that I need? I am trying to learn this stuff seriously.

 

[arnelgp]

if you need a book i can "share" it with you.
mostly are old but still applies to the newer versions of access.

 

[Methodikal]

Here is the DB I am trying to create. I wanted to put the combo box on top of the Employee Name. Then filter by Supervisor Name and Filter by IsTeleworker. I am going to try to learn as much as I can from Arnelgp's example. But this is the actual way I am trying to make it look and feel. I need it as user-friendly as possible since the people that will be using it are not educated in Access or Excel really.

if you need a book i can "share" it with you.
mostly are old but still applies to the newer versions of access.

I would appreciate that so much. I need anything that can help. I am the only semi-functional computer guy in my whole region and they are relying on me since our region gets tossed to the side like trash.

 

[arnelgp]

your table tblTelework is not normalized (for the Yes/No Days),
i made some tables (ends with T) and queries (ending in Q) and forms/subform.

 

[Methodikal]

your table tblTelework is not normalized (for the Yes/No Days),
i made some tables (ends with T) and queries (ending in Q) and forms/subform.

Thank you so much. I'm going to look at these asap. I studied the first file you made and have so many questions. I tried to recreate it but I'm missing some thing. So appreciate everything you are doing for me. But honestly I need some sleep atm. I didn't sleep all night. Will check back in the morning.

 

[Methodikal]

your table tblTelework is not normalized (for the Yes/No Days),
i made some tables (ends with T) and queries (ending in Q) and forms/subform.

BTW, what do you mean by normalized?

 

[arnelgp]

Google can better explain it to you.
search "database/table normalization".

 

[Methodikal]

I've been trying to understand what you did on these. You're awesome. As for the checkboxes being not normalized I have no idea how else to do it. I'm thinking of having to come up with a code for each possible combination of days teleworking.

 

[Methodikal]

Again thank you for what you have done. I am trying to learn. I have been reading the books you sent me. It is a lot to consume. I have also been working with the files you created to try to understand what you did. I may have to see if I can pay you out of my pocket if you have some time to walk me through everything you did because it was a lot. I know your time is valuable and I feel awful asking. I'm honestly just trying to help this nonprofit and trying to learn ms access as much as possible. I figure learning how to build a database would help me a lot in the future.

Now I'm running into a problem.

I need to have it schedule from a start date to an end date. Not by week. I also need to be able to record the days of the week they are teleworking. Like I was doing with the checkboxes but where they are able to be normalized. No idea how I can do that.

Here's an example:

John Smith works for Susan Potter (Supervisor). Susan has 25 teleworking employees and 25 non-teleworking employees.

John Smith will be teleworking starting 12/1/2022 (start date can be any date in the calendar) and he will be reviewed on 1/31/2023 (end date can be any date in the calendar). He will telework on Mondays, Wednesdays and Fridays.

Sarah Johnson works for Mark Turner (Supervisor). Mark has 30 subordinates and 12 telework while the rest work from the office.

Sarah Johnson will be teleworking starting on 12/15/2022 and be reviewed on 3/1/2023. She will telework on Tuesday and Thursday.

I'm trying to get the form load and have a combobox with Supervisor First Name and Last Name. A second combobox of the employees (of the selected Supervisor) that are only able to telework. A subform has the start date of when they are able to begin teleworking and an end date. The subform also has the days of the week they can telework (any combination of days of teleworking or in office) and their scheduled times for arrival, lunch start, lunch end, and departure time for those days they are teleworking.

This way we can do a report or look at another form with all of the teleworking employees and then filter by supervisor, week, month, location, or whatever we need.

I want to learn how to get this working right so I can expand the database in the future for other uses.

I'm creating tables for locations, job titles, departments, employees, teleworking, and alternative work schedule.

The position numbers are unique and so are the employee IDs; however, they have leading zeros so I can't use Number as the type. Which worries me if I use them as Keys.

Any advice would be appreciated.