Q: Nigel Heremaia wants to prevent users from entering the same data in the FirstName and LastName fields in different records. He is using this code, but it isn't working:
Private Sub Form_BeforeUpdate(Cancel As Integer)
FirstName = DLookup("[FirstName]", "tblClients", "[FirstName] ='" _
& Me!FirstName & "'")
LastName = DLookup("[LastName]", "tblClients", "[LastName] ='" _
& Me!LastName & "'")
FullName = FirstName & LastName
If FullName <> 0 Then
MsgBox "Client has already been entered in the database."
Cancel = True
A: The problem here is that you are comparing FullName to 0, and the expression FullName <> 0 will always evaluate to True, because whatever value is in the FullName variable, it is a String value, not a numeric value. To clarify this, declare your variables as String, and wrap the FirstName and LastName variables with Nz() – this converts Nulls in text fields to zero-length strings. Then you can check whether Full Name <> "" (instead of 0). That should work the way you intend.
Article posted: Tuesday, 27 May 2008
there's more ...
If you liked this article you'll LOVE our new ebooks.
Windows 8 for Microsoft Office users A practical guide the new, changed and unfamiliar in Windows 8
A focused and unvarnished look at Windows 8, especially written for
the many people who use Microsoft Office Get it today
- click here.
ORGANIZING OUTLOOK EMAIL - tame your Outlook 2010 Inbox
100+ pages of practical tips and help to streamline,
automate and search your Inbox. Get more
than you ever thought possible from Outlook. Read it today
- click here.