Solved

VBA for Access 2007 solution to identify if field in table is null

Posted on 2011-02-17
14
381 Views
Last Modified: 2012-05-11
Hi Experts,

I have a user login form on my database and I want to force the end user to enter a new password if the password that they have typed in is equal to the 'apwd' column in the table for that user.  I am using the code below on the AfterUpdate event of the login name box to go to the table and lookup the user that is trying to log in and then compare the password textbox value on the form to the 'apwd' column in the table.  If the password textbox value and the 'apwd' value in the user table is equal or the 'pwd' column value for that user is null, then the new password textbox will become visible on the form with a message box telling the user to enter a new password.  

If Me.txtpwd.Value = DLookup("apwd", "Users", "[ID]=" & Me.Cbologinname.Value) Then
  MsgBox "You must enter a new password.", vbExclamation + vbOKOnly, "Invalid Password"
End If

I get this error when I run the code:
Run-time error 3075: syntax error (missing operator) in query expression 'apwd'

Can someone tell me what I am doing wrong?
0
Comment
Question by:daintysally
  • 7
  • 7
14 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34917509
what is >>> the 'pwd' column
0
 

Author Comment

by:daintysally
ID: 34917532
The 'pwd' column is the column that will contain the user's masked password that they will use to login to the database.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34917544
what is the row source of combo Cbologinname?

if the bound column of combo Cbologinname is text Data type, use this

If Me.txtpwd.Value = DLookup("apwd", "Users", "[ID]='" & Me.Cbologinname & "'") Then
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34917562
<The 'pwd' column is the column that will contain the user's masked password that they will use to login to the database. >

do you mean you have two columns for the Password?  column  apwd and pwd ?

what is the purpose of  column  apwd ?
0
 

Author Comment

by:daintysally
ID: 34917594
the row source of combo Cbologinname is: SELECT Users.[lD], [Users].[Username] FROM Users ORDER BY [Username];
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34917623
reposting


<The 'pwd' column is the column that will contain the user's masked password that they will use to login to the database. >

do you mean you have two columns for the Password?  column  apwd and pwd ?

what is the purpose of  column  apwd ?
0
 

Author Comment

by:daintysally
ID: 34917637
The table has the following fields:ID(which is the primary key), Username, pwd(password), apwd(the password given to the user by the admin to enter the database for the first time), cpwd(field used to confirm the new password that the user has keyed in).  
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34917718
ok...

If Me.txtpwd = DLookup("apwd", "Users", "[ID]=" & Me.Cbologinname & " And [pwd] Is null") Then
  MsgBox "You must enter a new password.", vbExclamation + vbOKOnly, "Invalid Password"
End If
0
 

Author Comment

by:daintysally
ID: 34918687
I am still getting the same error message when I tried your code:

If Me.txtpwd = DLookup("apwd", "Users", "[ID]=" & Me.Cbologinname & " And [pwd] Is null") Then
  MsgBox "You must enter a new password.", vbExclamation + vbOKOnly, "Invalid Password"
End If
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34918726
*do a compact and repair of the db, then
*in the VBA window, do DEBUG > Compile

     correct any errors raised..

*test again
post back.. the result
0
 

Author Comment

by:daintysally
ID: 34919162
okay, the code is not erroring out.  However, it's not doing what I want it to do.  When the user logins into the database and they key in the password that was assigned to them by the admin, a message should appear telling the user that they need to enter a new password.  Once they click on 'Ok', then on the form, the new password and confirm password boxes are made visible so that the user can input a new password that is written to the table in the pwd column.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 34919264
in what event did you place these codes?

If Me.txtpwd = DLookup("apwd", "Users", "[ID]=" & Me.Cbologinname & " And [pwd] Is null") Then
  MsgBox "You must enter a new password.", vbExclamation + vbOKOnly, "Invalid Password"
End If
0
 

Author Comment

by:daintysally
ID: 34919542
AfterUpdate
0
 

Author Comment

by:daintysally
ID: 34919683
I moved that code to the OnClick event on the Login button and that worked fine!!!  Thanks Capricorn1
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Creating and Managing Databases with phpMyAdmin in cPanel.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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 …

856 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