Login script with restricted access

Posted on 2014-07-16
Last Modified: 2014-07-21

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 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)
Question by:PipMic
    LVL 84

    Accepted Solution

    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.
    LVL 84
    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.

    Author Closing Comment

    I Need to tweak the code

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now