Login script with restricted access


Grateful for some advice:

My problem or thought process is the following:-   (using MS A03)

I have a simple database . One table has a number of fields.
Groups of records have the same value for a particular field...In other words
record1, field x has value A
record2, field x has value A
record3, field x has value A
record4, field x has value B
record5, field x has value C

Several users have access to this database but each user can/should  only see records of a particular value for field x...in other words
User Bob should only see the records of field x with value A
User Tom should only see the records of field x with value B

My idea is to create a login form  based on a username and password which would be looked up in a separate table. Once the username and password is verified, the process/routine would then only allow access to the records he/she is entitled to view and/or amend.

Is this feasible and /or is there a better way of addressing this problem?

Thanks  (I am willing to go into more detail)
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That's generally the way things work in Access - you create your login methods, and then reference those to determine which user sees which data (or Forms, or Reports).

If your only goal is to determine who has logged in, then you could just create a simple table, and one form:


Your form (fLogin) would have two textboxes - txUser and txPass -  and a button (cmLogin). When the user presses cmLogin, you'd do this:

If Nz(DLookup("LoginID", "tLogins", "UserName='" & Me.txUser & "' AND Password='" & Me.txPass & "'"),0) <>0 Then
  '/ login success, so hide this form and open the next
  DoCmd.OpenForm "YourMainForm"
  Me.Visible = False
  Msgbox "Invalid credentials
End If

You then refer back to fLogin to determine who has logged in, and take appropriate action. For example, if I were to open fData:

Sub Form_Load()
  Select Case fLogin("txUser")
    Case "Bob"
      Me.Recordsource = "SELECT * FROM SomeTable WHERE FieldX='A'
    Case "Tom"
      Me.Recordsource = "SELECT * FROM SomeTable WHERE FieldX='B'
  End Select
End Sub

That's the simplest way. You could get very creative with this, if you need to control exactly which forms users can open, and exactly what data can be managed.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Note too there is at least one commercial product for this:


You'd still have to implement the data restrictions, but that product will create the basic infrastructure you need to manage this.
PipMicAuthor Commented:
I Need to tweak the code
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.