Question user access rights/

[Mado]

Iam developing a small application to handle annual leaves and HR records for employees. i already finished the login form but i want certain users "Department Admin" for example to have full access on the main form while other users " assistant" to be only able to veiw data without adding or editing fields...

any sggestions?

 

[Kafrin]

Are you using the built-in security features of Access? If so you should just be able to change the permissions for the users/groups.

If not, then you need to save somewhere what username your user has logged in as. Then in the OnOpen event of the form you can set the AllowEdits, AllowAdditions and AllowDeletions properties of the form depending on who they've logged in as.

If you are using the Access security features but just want to make one or two forms different, then follow my second suggestion, but instead of having to store the username somewhere you can find out who they've logged in as using CurrentUser.

 

[Mado]

Thanks alott..

actually i prefer your second suggestion because in some cases they might use the same pc to login..

to be honest i thought about it and i already added a new column to the employeelogin table called "usrstatus" and upon login it will check if the current employee is "user" or "admin" but since i am new to access i didnt get excactly what you meant when u said i have to store the status somewhere and then use it with onopen event ...

can you be more specific please
thaks agian

 

[Kafrin]

After a person has logged in, how do you know what username they used to log in with? This is going to be important. My best suggestion without more info is that you store the user status that goes with their login in a public variable, as follows...

Create a module.
In the module add the line:
Public CurrUserStatus as string
Save the module (I usually call this type of module 'Utilities', but any name will do).

Now, when your login form checks the table to see what status the user has, add the line:
CurrUserStatus = [EmployeeLogonRecordset]!UserStatus

Now open up one of the forms you want to limit in design view and show the properties. On the Events tab, find OnOpen, change it to [Event Procedure] and click the ... button to bring up the code window. You are now in the OnOpen event of the form. You will want to end up with code that looks somehting like this:

Private FormName_Open()

Me.AllowAdditions = (CurrUserStatus = "admin")
Me.AllowDeletions = (CurrUserStatus = "admin")
Me.AllowEdits = (CurrUserStatus = "admin")

End Sub

This code looks when the form is opened to see what the CurrUserStatus variable is. For each of the three items it sets them to True if the CurrUserStatus is "admin" and False otherwise. So only someone with "admin" status will be allowed to add, delete or edit records. Everyone else will only be able to view records.

A word of warning: other errors that occur can wipe out public variables, so you may want to find a different way of storing the logon data, but I can't right now (and without more info) think what the best way to do this would be. Ultimately, if a user who logged in with "admin" status finds that they can't edit records on a form, then they can always close the database and log back in to sort it out.

 

[Mado]

Thankkkks alot Kafrin

That worked perfectly with me. but will little changes because i was having problems getting the user status out of the table record so i used the dlookup fundction

CurrentUserStatus = DLookup("usrAccess", "Employees", "[EmployeeID]=" & Me.cboEmployee.Value)

then I was not able to use the variable value directly in the form so i had to assign its value to a textbox ( later i made that textbox hidden)

me.textbox= CurrentUserStatus
then used your suggestion on the On Open event and it worked like magic

Me.AllowAdditions = (me.textbox = "Admin")
Me.AllowDeletions = (me.textbox ="Admin")
Me.AllowEdits = (me.textbox = "Admin")

thanks agian and i will be back for more

 

[Kafrin]

Excellent, glad I could help and that you found a way to make it work.

 

[Mado]

Iam back as promised

actually im now finalizing the application but i noticed somethng annoying..

i had made a pop up form to display alert for passport expiry date ( the query check expiry date within one month from current date) its working fine but is case where no expiry item found, the query is returning no records and so the pop up form is displaying empty records...

i was able to sort this with reports because there is OnNoData event but its not the case with forms.....i just want the form to display a msgbox that theres not expiry items with one month instead of annoying empty form or simply i dont want the pop up form to appear at all!

suggestions?

 

[Kafrin]

You could open the recordset in code, see if there are any records, and then only open the form if there are.

Firstly, if the form just looks directly at a table then that's fine. If it doesn't (which I suspect is true as there must be some filtering happening here), then you need to make sure the recordsource is saved as a query. Any filtering that takes place directly on the form should be put in the query instead. Now set the query as the recordsource for the form.

Then (using MyQuery as the name of the query) your code that brings the form up will need something like this added:

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("MyQuery")

If rs.RecordCount <> 0 Then

[Enter your code for opening the form here]

End If

rs.Close

 

[Mado]

IT WORKEDD!!!

you are my access hero

thanks.

 

[Mado]

Re: user access rights

When im trying to fill the form ..while clicking on the TAB key the focus is not moving from one tab to another in proper order!!

I need the focus to be from top to down and left to right

any idea?

 

[Kafrin]

If you right click on the form, one of the options should be Tab Order. Click on this and you get a pop-up window showing a list of your controls in their current order. Drag the control names up and down the list until they're in the order you want.