[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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)
  • 2
1 Solution
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.
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

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