Results 1 to 3 of 3

Thread: DLookup

  1. #1
    Join Date
    Jul 2011

    Unanswered: DLookup


    I am trying to create a Login form within Access 2003. The login process works fine however there are 'events' I require my system to do.

    In the tblUser entity, I have an attribute called AccountSuspended. This is a Boolean data type.

    In the login form, I want a piece of code so that after the user enters their username, the application uses a DLookup to see whether that user's account is suspended. In other words, check if the given user account's AccountSuspended attribute is checked.

    I tried the following but it never worked:

    Private Sub UserID_AfterUpdate()

    If Me.UserID.Value = DLookup("Username" = Me.UserID.Value, "tblUser", "AccountSuspended" = True) Then
    MsgBox ("Account Suspended")
    End If

    End Sub

    Can anyone help me with this problem?


  2. #2
    Join Date
    Oct 2003
    Here is a solution. Clearly not the best but it will work.

    Create a list box on your login form and set the options Visible = False.

    The Row source will be a query searching for all users that match the username text box, and where Active = True i.e.

    SELECT Table1.Username
    FROM Table1
    WHERE (((Table1.Username)=[Forms]![Form1]![Text01]) AND ((Table1.Active)=True));

    When you update the text box, requery the list box.

    If the user is active it will show in the list. If not, the list will remain empty. Therefore;

    if Me.List01.ListCount < 0 Then
    ' Do Nothing
    Msgbox "This user is either inactive, or does not exist!"
    End If
    I've attached an example. Active users are Christyxo and paulmoscrop95
    Attached Files Attached Files

  3. #3
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 2
    As an aside, your DLookup function is well out of whack.

    The three arguments (two required, one optional) as named in the help file are expr (string expression that is the name of a field, or an expression that performs a calculation on a field), domain (the name of the table holding the field whose value you want) and criteria (a string expression that identifies the record whose value from expr that you want to return - without this argument, DLookup returns the first value from expr).

    Therefore, I believe that what you need is:
    If Not Nz(DLookup("AccountSuspended", "tblUser", "Username = '" & Me.UserID.Value & "'"), False)...
    (Note the Nz function - I tend to use DLookup within Nz as a means of handing null returns, but it's up to you.)
    The above snippet will fire the code after "Then" under the following conditions:
    If the user is found and suspended;
    The user is not found.
    If you want to send different messages to each situation, remove the Nz function and use the DLookup function to populate a variant, then check the variant for nulls.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts