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

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

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
daintysally
Asked:
daintysally
  • 7
  • 7
1 Solution
 
Rey Obrero (Capricorn1)Commented:
what is >>> the 'pwd' column
0
 
daintysallyAuthor Commented:
The 'pwd' column is the column that will contain the user's masked password that they will use to login to the database.
0
 
Rey Obrero (Capricorn1)Commented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Rey Obrero (Capricorn1)Commented:
<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
 
daintysallyAuthor Commented:
the row source of combo Cbologinname is: SELECT Users.[lD], [Users].[Username] FROM Users ORDER BY [Username];
0
 
Rey Obrero (Capricorn1)Commented:
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
 
daintysallyAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
daintysallyAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
*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
 
daintysallyAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
daintysallyAuthor Commented:
AfterUpdate
0
 
daintysallyAuthor Commented:
I moved that code to the OnClick event on the Login button and that worked fine!!!  Thanks Capricorn1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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