?
Solved

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

Posted on 2011-02-17
14
Medium Priority
?
386 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

765 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