07-11-11, 17:53 #1Registered User
- Join Date
- Jul 2011
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")
Can anyone help me with this problem?
07-12-11, 09:31 #2Registered User
- 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.
WHERE (((Table1.Username)=[Forms]![Form1]![Text01]) AND ((Table1.Active)=True));
When you update the text box, requery the list box.
if Me.List01.ListCount < 0 Then ' Do Nothing Else Msgbox "This user is either inactive, or does not exist!" End If
07-12-11, 11:09 #3Grumpy old man (training)
Provided Answers: 11
- Join Date
- Sep 2006
- Surrey, UK
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)...
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.
Beers earned: 2