Solved

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

Posted on 2011-02-17
14
380 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

813 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now