[ecanestrari]

Hi,

I need your help! I have a good knowledge on Excel and VBA but I'm a newbie on Access.

I'm currently working on a database on Access 2010 where there should be 4 types of users (Admin, Approvers, Inputters, Readers). I've followed the instructions to create a login that worked perfectly (Creating Login Security using Access VBA | ScottGem's space).

The problems is the following:

Inputters should be able to access "frmTransferFCA" and enter data there. After Inputters finish entering information, Approvers should have access to a button "authorizing" the information. I was thinking about using a simple date stamp after pressing the button. Also, the Inputter should be able to modify the information in the form BUT once the Approver pressed the button Inputters shouldn't be able to modify that record.
I've set "Allow Deletions = No" so records can't be deleted by anyone, and I've also added a check box in the same form so if the transfer is cancelled it won't appear in reports but we would still have the record. This check box should be able to be modified only by Approvers.


I really hope you can help me!

I've tried several options and codings but none of them worked as I needed.

If you have any doubt or you need information please let me know.

THANKS!!

[ScottGem]

OK, So you have at least the following fields in your table: InputBy, ApprovedBy, ApprovedWhen. I would suggest you use a function like the one found here: Microsoft Access tips: Locking bound controls on a form to lock the bound controlsby default. Then add an edit button to unlock them

When the button is pressed, you check to see the Access Level as instructed in my blog. You asl check to see whether the ApprovedWhen field is null. If it is, then unlock the controls so the Inputter or Approver can edit. Otherwise, lock the controls if it is an inputter. But unlock for an approver.

If you need more specifics, please let me know.

[ecanestrari]

Thanks a million! You've not only helped me through your blog to set up the login but now you gave me a great idea on how to manage the access levels. THANKS!

[ScottGem]

Glad I was able to assist.

[ecanestrari]

Hi again,

Hope you can help me. I'm trying to restrict access to all users so they can't modify the design nor the information in the tables that involve the login and access levels.

I've unchecked "Use Access Special Keys", the "Display Navigation Pane", the "Allow full Menus", the "Allow Default Shortcut Menus" and also added a VBA code that blocks the bypass key through a password.

Everything seemed perfect but I just realized that users can still access the "Privacy Options" and check again all the options previously unchecked.

Is there any way I can disable that function for this DB or make sure it's always everything blocked?

Thanks!

[ScottGem]

You would have to replace the standard ribbon with your own ribbon to prevent access to the File ribbon. As I start my blog off, Access is not the most secure platform. Can I ask what you are trying to protect against? If this is a corporate app and you have users that will try to get around your security, then you have a bigger problem. You might consider moving the data store to SQL Server. You can use SQL Server Express with Access apps. SQL Server has greater security that can be applied to the tables. You can also try encrypting the back end.

What lengths you go to secure your app depends on what the app is used for and who its used by.

[ecanestrari]

Hi Scott,

Thanks for your help.

This database is to register and approve transactions, it's for internal purpouse mainly but we want to keep away people from touching buttons and breaking it. Most of the users don't have a great Access knowledge so that's a gift and our curse.

I'm a self-taught (and blog helped) Access user and as we needed to build it fast enough we decided to do it in that plataform.
I'm trying now to find different courses on SQL to be able in the future to migrate the Access database to a SQL server.

I still have one last question for you. I have access to one user to "tblUsers" so this person can add new users and reset passwords. Is there a way I can keep a log of when and what this person modified in this table?

Thanks in advance for your help,

Really you've been a life saver!

[ScottGem]

First, if you don't have them, add 2 fields to tblUsers; CreatedWhen (Date/Time), CreatedBy. Set the Default for CreatedWhen in the table to =Now(). Add hidden controls bound to these two on your manage users form. Set the default for the CreatedBy control to:
=Forms!frmLogin!cboUser

This will track when and who added new users.

For changes, you can use the Audit Trail data macro outlined in my blog. Or search on Audit Trail and you should find many VBA solutions for that.

[ecanestrari]

Thank you very much Scott! The macro on your blog, it worked perfectly!

[ScottGem]

You're welcome