• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

Acces security

hello experts, I have a form which records the set up of a new process. I also a login table. after the login an Index form opens and the user can open the form from there, what I want is to have the form open and show only certain field based on the login ID, if needed the db can be uploaded.  I'm fairly new at this.
  • 4
  • 2
1 Solution
Jeffrey CoachmanMIS LiasonCommented:
There are many valid ways to do this.

One is to store he login ID in a Public variable
Create a Public variable in a module:

    Public lngpubLogInID as long

Then also create a public function to retrieve it:

    Public Function GetLogInID() as long
End Function

Then use this variable to filter the form:
    DoCmd.OpenForm "YourFormName", , , "LoginIDFieldOnTheForm=" & GetLogInID()

But ultimately you will have to either try applying this to your specific database, or post a simple sample of your database for review.
But what I posted should get you going.

VitoeAuthor Commented:
it seems to work but when the form opens it still displays all, what I need is to enable only certain field and the rest are disabled based on the logon ID
Jeffrey CoachmanMIS LiasonCommented:
This is a "Field" based restriction..

Then the "proper" way to do this would be to create another "many-to-many" table to store the allowed/disallowed LogInID's

The "easy" approach is to simply only load the allowed fields in the form.

On the Open event of the form do something like this:
Dim strRecSrc as string
If GetLogInID()=1 then
    'Here you would list the fields you want LoginID1 to see, for example: fld2, fld3
    strRecSrc ="SELECT fld2, fld3"
elseif  GetLogInID()=2 then
     'Here you would list the fields you want LoginID2 to see
    strRecSrc ="SELECT fld1, fld3"
elseif  GetLogInID()=3 then
     'Here you would list the fields you want LoginID3 to see
    strRecSrc ="SELECT fld1, fld2"
End if

strRecSrc =strRecSrc & " FROM YourTable"

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Jeffrey CoachmanMIS LiasonCommented:
...and to be clear, you can use code to open the form normally:
 DoCmd.OpenForm "YourFormName"

And this "field filter" code should still work
VitoeAuthor Commented:
how do I do the "many-to-many" table to store the allowed/disallowed LogInID's?
Jeffrey CoachmanMIS LiasonCommented:
<how do I do the "many-to-many" table to store the allowed/disallowed LogInID's? >
This will be to much to explain if you are new to database design.
What I posted above should work in the mean time...

But you can research the concept here:
You can also see this to make the process a bit simpler:

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could also show/hide the controls on the form, based on the login of the user. I'm not sure how you're storing those values, but in general I use a hidden form to store things like this. The process is something like:

1) User opens database and logs in through my custom form
2) After login, run code to determine the "level" of the user, and place that value in a textbox on the login form named "txUserLevel"
2) That custom form is then HIDDEN (not closed), and I can then refer to it from other objects in my database.

In a form where I want to hide certain controls, I use the Tag method:

1) For each control I wish to hide, I add a value to the Tag property which denotes the lowest "level" of user who can interact with it. In your case, since you will apparently have multiple "levels" of users, you might want to devise a strategy that defines the highest "level" of user (for example, and Admin) down to the lowest "level" of user (for example, a Readonly user). Perhaps an Admin user would have a level of 5, and a Readonly user would have a level of 1
2) I then loop through my controls and examine the Tag property, and take action based on that:

Dim ctl As Control

On Error Resume Next
For each ctl in Me.Controls
  If CInt(ctl.Tag) < Forms("MyHiddenForm").txUserLevel Then
      ctl.Enabled = False
      ctl.Locked = True
    ctl.Enabled = False
    ctl.Locked = True
  End If
Next ctl

This code would be placed in the Open or Load event of your form.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now