Code Before Opening Form To Check User's Right

[Ashfaque]

Hello Gentlemen,

I am planning to give different rights (AllowEdit, ViewOnly…) to the user on accessing forms and reports in a db on a single pc instead of dealing with server as my client have only one pc.

As a present requirement, db would be in one pc only and would be used by 3-4 users. I need each of users to limit their access to some of the forms and reports in the same pc. To get it, what I did is as follows:

Example:

One of my tables called tblEmployees (Back End Table) linked in FE containing following fields:

1. Unumber - Autonumber
2. UNames - Text
3. Pwd - Password
4. F1 - Yes/No
5. F2 - Yes/No
6. F3 - Yes/No

F1, F2….stands for form1, form2…

There is a login menu form. Something like 2 text boxes on the form and opening at the startup. User selects his name from a combo and types his password. If the user is available in the tblEmployees, Access opens main form and store UNumber and UName with time stamp in a local table called “T_CurrUser. If user not available in tblEmployees, it should close or give some msg.

Now, when the Main form opens (after login with correct user and pwd) and user wants to click different command buttons to open the form, I need to write code to check whether the current user have right to open the selected form (check marked to allow opening this form in the table tblEmployees) BEFORE opening it.

If check mark is set to false on F1 in the table tblEmployees, it means he can’t open form1, and a msg should pop up “Not allowed to view”..Something likes that.

1. I don’t know the code I created with simple if then, where to write ? I mean on which event of form ? On open / On Activate or where. Bcz the code should run before form opens and should check the table whether the user have check mark value true or false.

I tried to write the following code but some time it works some time doesn’t. What is wrong with code lines?

On Click of Command Button that opens Form1

Dim USR = DlookUp(“lngEmpId”,”T_CurrUser”) ‘ Local Table emp.

‘To check if Form1 check mark is set to true
If DlookUp(“F1”,”tblEmployees”,”lngEmpId=USR)=True Then
Docmd.OpenForm “Form1”
Else
End If

When another user logs on the same pc (off course, after login out of first user) the local table T_CurrUser record will be replaced with new user. Note that local table T_CurrUser contains always one record. Hopefully I described correctly.

Any idea will be respected.

With kind regards

Ashfaque

[Kempes]

What I think you need is a Workgroup File.

Open help and type Workgroup, then select Creating.

This should guide you through creating one.

Basically, rather than the user choosing their name off a drop down, it will prompt them to key their User Name and password into the login options controlled by the workgroup.

You can then Create User Groups in Tools/Security/User and Group accounts and select which forms you want them to see and which you dont under Group Permissions.

You will also need to create a shortcut to the db and link through the workgroup file in order the start the database.

[Ashfaque]

Thanks Kempes,

I read the workgroup information file creation procedure thur help.

One thing is not cleared to me. If I create a new Work Group Inf file keeping save all its contents (name and password.....). Would it apply only to the db which I create for or it would apply to whole MS ACCESS whenever I open ACCESS ?

Second, as per the help file, it works with MDB only. What about MDE? Will it work with ? (Sorry, I did not mentioned earlier that I had plan to implement this trick with MDE.)

If it doesnt, my idea to create a table with yes/no type filed to open froms would work better.

Any other idea ?

With kind regards,

Ashfaque

[Ashfaque]

Thanks Kempes,

I read the workgroup information file creation procedure thru help.

One thing is not cleared to me. If I create a new Work Group Inf file keeping save all its contents (name and password.....). Would it apply only to the db which I create for or it would apply to whole MS ACCESS whenever I open ACCESS ?

Second, as per the help file, it works with MDB only. What about MDE? Will it work with ? (Sorry, I did not mentioned earlier that I had plan to implement this trick with MDE.)

If it doesnt, my idea to create a table with yes/no type filed to open froms would work better. I would like to know better about security (I know its a very lengthy subject but even) before doing anything with db.

Any other idea ?

With kind regards,

Ashfaque