Solved

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

Posted on 2011-02-17
14
378 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 119

Expert Comment

by:Rey Obrero
Comment Utility
what is >>> the 'pwd' column
0
 

Author Comment

by:daintysally
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<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
Comment Utility
the row source of combo Cbologinname is: SELECT Users.[lD], [Users].[Username] FROM Users ORDER BY [Username];
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
*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
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
AfterUpdate
0
 

Author Comment

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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 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

16 Experts available now in Live!

Get 1:1 Help Now