Preventing Duplicates

[FJB]

I use an input form for adding new records. I need to ensure there are
no duplicates by using a "control number" which is unique for each
record. I have made this field the key field and indexed it with no
duplicates.When I enter (on purpose) a duplicate "control number" , I
can continue to complete the form, but when I try to exit the record I
get the error message about duplicates. I would like to get that error
message when I move to the next field, using enter or tab. I am pretty
sure this is possible, but don't know where to go from here. I have
searched the newsgroups and began more confused than before.

Thanks for your help.

Frank

 

[Jeff L]

In the After Update property of your Control Number field put

If Not
IsNull(Dlookup("ControlNumber","YourTableNameHere","ControlNumber = " &
Me.ControlNumber) then
'The control number exists already
msgbox "Your error message here", vbok
end if

If your control number is a text field then you'll need to put
ControlNumber = '" & Me.ControlNumber & "'" instead of what I put
before.

 

[FJB]

Thanks for the help. I copied your code into Word and replaced your
field names with the correct ones as well as the TableName. I can get
the code to save, but when I try to run it, the error message appears
about not being able to locate the database. I am confused since this
is a form within the database. Since the database is on a network
drive, do I need to give the entire path?

Help

 

[Rob Parker]

You're confused? I'm confused as to why you would copy the code into Word
and expect it to work!

As the original poster said, the code needs to be in an event of the
control; the AfterUpdate event was suggested, but the BeforeUpdate event
(which has a Cancel parameter) is better, as I'll explain.

First, the general procedure for entering code is as follows: Open the form
(or report) in design mode, and select the control of interest. In the
properties dialog box, select the Event tab, then double-click in the event
you want to use - the field in the dialog box will now contain "[Event
Procedure]". Double-click the small box with an ellipsis symbol (three
dots) at the right of the field, and you will enter the Visual Basic editor,
in the procedure for the control event you selected; if it does not have any
existing code, the editor will contain the two statements such as:

Private Sub txtControlNumber_BeforeUpdate(Cancel As Integer)

End Sub

with the cursor positioned between them. That's where the code goes! You
can save the changes via the save button or file menu in the Visual Basic
editor, or return to the form design in Access and save changes when you
return to Form View mode.

I strongly suggest that you read the Help file for the topics Programming in
Access and Programming in Visual Basic, or get a third-party book, and learn
the basics - code enhancement of Access forms is essential to build anything
other than the most basic database application.

As for your problem, I suggest you use something like this in the
BeforeUpdate event of your textbox. Note: text appearing after a '
character is treated as a comment in VB; and you will need to watch for
line-wrapping if you cut/paste from this posting:

If Not IsNull(Dlookup("ControlNumber","YourTableNameHere","ControlNumber
= " & Me.ControlNumber)) Then
'The control number exists already
MsgBox "That number is already used. Try again ..." 'you can
change the message text, and also specify the buttons which appear, and the
title of the message box
Cancel = True 'cancels the update of this date (ie. prevents
Access from attempting to save it)
Me.Undo 'returns the contents of the control to the previous
value
End If

And a final cautionary note: the dLookup syntax here will only work if
ControlNumber is a number; if it is a text datatype, you will need
...Dlookup("ControlNumber","YourTableNameHere","ControlNumber = '" &
Me.ControlNumber & "'")
to wrap the text value in single quotes.

HTH,

Rob