Filter Textbox Button

[boberto313]

Hello Everyone!

First, thank you for any and all support you might offer, really, THANK YOU!

I'm not an Access person, or an Excel person at all. I was tasked with sending emails to 140+ team leads notifying them of which team members have not completed specific trainings. In total, there are more than 800 team members. You can imagine how crazy its been trying to manage this much email traffic. I've had to work with Access in the past and I thought it might be easier to create a process where the team leads can self-manage their own updates.

Attached is an anonymized version of my database, other than the names and email addresses being different, its identical to whats living on my desktop.

On the Form, AnnualComplianceTraining, it mostly works as intended. I'm hoping to achieve the following:

When the form loads, I don't want it to display any records. The user must input their passcode and hit a "run" button, which will then show a filtered list.

For example, the team leader whom I've assigned passcode 6739 only has 6 team members out of compliance. Those should be the only 6 records this manager, Nicole C., sees. In this case, the passcode is basically like their signature. So when they go through the records, changing the 'Disposition' field, I should know that only Nicole has done that, and is therefore responsible for the data integrity.

Once this filter function is operating, my next goal is to somehow send this form to every manager (does it need to live in Sharepoint? Is there a way that I can have on databse living somewhere and all of their changes flow to that one file?), they input their codes, and they change their own dispositions. Those changes somehow get saved to my "Master" DB, and from there I can run reports. If you know how to do that or could at least point me in the right direction, that would be helpful!

And finally, on the point of running reports, I currently have a "FullReport" out there, I don't know how difficult it might be to have filters applied ot that report utilizing the "Dispositions" data. So if I only want to see team members who are marked "Exempt," for example, I want to be able to do that. Same goes for Separated, Incomplete, etc.

It feels like I'm asking a lot, and I'd be grateful for any help. Happy to buy you a $25 gift card as a thank you! (I know its not a lot of money but I feel like your help is valuable and thats all I can afford)

ForumDB.zip

[Micron]

When the form loads, I don't want it to display any records.

Unless you have a good reason, this is backwards. I would open the form filtered per user credentials.

That makes this unnecessary

The user must input their passcode and hit a "run" button, which will then show a filtered list.

You can restrict records to the logged in user but what happens to their stuff when they go on vacation - nothing?

I should know that only Nicole has done that,

another case for using user credentials.
You don't "send forms", you send the results of a query or report, usually as a pdf.

Is there a way that I can have on databse living somewhere and all of their changes flow to that one file?)

Your db would be split - each user gets their own front end linked to the same set of tables in a back end db. Perhaps research how to split Access db. It's a big topic.

I don't know how difficult it might be to have filters applied ot that report

Not hard at all. Filtering and emailing a report is a common topic. If the report looks the same save for the data it shows, don't create a report for each scenario. One report that's filtered, or its recordsource is altered when opened.

A training db is a big effort and I suspect you don't really know how much. Your offer is kind but I suspect you can get a lot of help here at no cost if you're willing to do some work. Unfortunately I can't look at your attachment right now. Did you look for any training requirements db templates on the web? You might find one and get some guidance from it even if it's not suitable.

[mike60smart]

Hi
A few things wrong with your construction of tables.

Workingtbl
Your PK should be an Autonumber vice a Number
This table copntains Both the Team Members Names as well as the Team Managers Names. Hence the repeated redundant data.
The field Condition should NOT be a Lookup Field. It should be named DispositionID
The field DispositionID should then be linked to the PK in table Dispositions.

You should have tables as follows:-

tblTeamLeaders
-TeamLeaderID - PK - Autonumber
-TLFirstname
-TLSurname
-TLEMail


tblTeamWorkers
-TeamWorkerID - PK - Autonumber
-TeamLeaderID - FK - Number (Linked to the PK from tblTeamLeaders)
-TWFirsname
-TWSurname
-AssignedContent
-DispositionID - FK - Number (Linked to PK from tblDispositions)

tblDispositions
-DispositionID - PK - Autonumber
-Disposition

[boberto313]

Thanks! I'm making these changes now. Just curious why you didn't think a tblAssignedContent was necessary? I'm learning.

[boberto313]

Unless you have a good reason, this is backwards. I would open the form filtered per user credentials.

That makes this unnecessary

Thanks! I think perhaps we're saying the same thing different ways? Using your idea, when a user opens the split database, would they see a singular textbox requesting their Passcode? If so, that sounds ideal to me. I was previously imagining they'd see the form, but it would be totally blank. Only after entering their passcode into the textbox would the form populate with data. I just didn't know a "splash page" was possible with forms.

You can restrict records to the logged in user but what happens to their stuff when they go on vacation - nothing?
another case for using user credentials.

I'm not sure I understand the scenario. I'll add a little more context to what I'm hoping to achieve. This isn't highly sensitive data and its really just our way of getting the team leads to usher their staff along in completing the annual trainings. There is no training taking place in this database. Only tracking who has completed their training and who has not. As such, I think its a "minimal security" process.

To that end, I used Excel's random number generator to assign a 4-digit passcode to each team lead. Kinda like the 2FA that we've all grown accustomed to. And now, no worries on lockout or resetting. Team leads can simply refer to their email, or if necessary, changing their passcode should be a relatively simple process. I was planning on doing a mail merge to send out a brief email containing their passcodes and access to the database, along with instructions for completion. When the team lead receives this email, they should then turn to their team, lets say you for example, lol and say "Hey, Micron, get in their and complete Happy Class right now." You go do it, let your team lead know its done, then they can go into the database, change your record from "Incomplete" to "Complete" and its done.

The way its working right now is I'm emailing back and forth with each manager to get status updates on every team member... all 800+ of them, lol.

Back to the example, lets say Nicole is your manager. Only I (or the DB administrator) and Nicole knows her 4-digit passcode. So if Micron's record says he completed the Happy Class, and he didn't, well... we know Nicole reported inaccurate information--OR she shared her passcode, both of which fall squarely on her in terms of responsibility.

You don't "send forms", you send the results of a query or report, usually as a pdf.

I think you addressed this with "splitting" the db, thanks! The report that I want to generate is just for me to report to my boss who is still out of compliance. Its not going to the team leads, its for my own reporting/record keeping.

Your db would be split - each user gets their own front end linked to the same set of tables in a back end db. Perhaps research how to split Access db. It's a big topic.

Not hard at all. Filtering and emailing a report is a common topic. If the report looks the same save for the data it shows, don't create a report for each scenario. One report that's filtered, or its recordsource is altered when opened.

Thanks for this tip!

A training db is a big effort and I suspect you don't really know how much. Your offer is kind but I suspect you can get a lot of help here at no cost if you're willing to do some work. Unfortunately I can't look at your attachment right now. Did you look for any training requirements db templates on the web? You might find one and get some guidance from it even if it's not suitable.

Thank you for the recommendation! I am excited to learn Access, I think it can make my life TONS easier so I look forward to learning from you all! Based on the description I provided above, do you still think this is a big undertaking? I was really hoping for it not to be but I don't know what I don't know. As for the gift card, I just want to do something nice for someone nice enough to problem solve for me. May not be necessary but just a token of my appreciation.

[mike60smart]

Hi
That was a mistake on my part as each TeamWorker I would assume would have multiple items assigned.

[Micron]

when a user opens the split database, would they see a singular textbox requesting their Passcode?

Unless users share Windows logins on pc's then I wouldn't bother with password forms/passwords/setting/editing/resetting when they forgot it, etc. I prefer tblUsers with FName, LName, etc. and Windows login username. Then compare table username value with Windows login username. If not found, that means they're not even authorized to open and it closes. If all good, they're in. Then either use a function such as fosUserName when you need it, or set a global variable to that value.

didn't know a "splash page" was possible with forms

Isn't that a separate but unrelated subject?

Only tracking who has completed their training and who has not.

That was my understanding. Your post inferred that if I log in I can see my stuff and nothing else. So I asked how that's supposed to work if I have to do your stuff when you're not there. If that never happens, then disregard.

If you're going to send out passwords via email I see no sense in bothering with them. Do you think M$, your bank or any other site would do that?
If security (and that includes snoopers of any kind) is that lax you may as well not bother, especially if 2 other people know my password.

Based on the description I provided above, do you still think this is a big undertaking?

Relative to your current knowledge level and complexity of the initial db? Please don't think I'm trying to be snarky in that regard because I'm not. I get the impression that you will think it will turn out to be much more difficult than you realized, but that's not a good reason to not move forward with this. Let's start with you studying db normalization then maybe you cobble together some tables once you think you understand that, build relationships in Access then post a pic of that.

I might as well just throw all my links at you so that you might get a better handle on what you're up against if you want a quality result, then maybe you'll decide to ask your boss to hire somebody!

EDIT - BTW, using login username means that you record who entered/edited information, so there's really no worry about sharing responsibilities. If Nicole says I took the training, that info has Nicole's name all over it.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Important for success:
Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - https://www.isladogs.co.uk/multivalu...lds/index.html