Checking for duplicate on hitting the enter

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

In a form how could you check the database for duplicates
when the enter key is hit to continue to the next field
and not to proceed to the next field if a duplicate does
exist. Please explain in detail the procedure for the
appropriate changes that need to be made. Thank You.
 
Bob,

The easiest way to do this is to go back to the table desing, and set the
field's Indexed property to Yes (No duplicates). This way Access itself will
warn and prevent saving a record if there is a duplicate.

HTH,
Nikos
 
Thank you. I have done that procedure and it works fine
when you save the file once all fields have been filled in
completly. However, is it possible to get a result if
there is a dupilcate file when in the form, you enter to
the next field after the field you set to Yes (No
duplicates)? Instead of completing the entire form and
finding out at that time once you have filled in all of
possibly 12 fields which can be time consuming. I beleive
there is a way using the "on enter" property field of the
form, but I am unafmiliar in this process.
 
Bob,

OK, got you. You can use the After Update event of the control to run a
macro that looks up the table for the specific value, and displays a warning
message box it a duplicate is found. Open the form in design view, open the
properties window for the control in question and select tab Events. Place
the cursor next to the After Update event, click on the small button with
the three dots that appears on the righ hand side and select Macro builder.
You will be taken to the macro design window, and be asked for a name for
your macro - call it something that makes sense to you. Then, if you don't
have column Condition in your grid as the first column, use menu item View >
Conditions to make it appear. Now in the first row put the followinmg
expression in the Conditions column:

DFirst("[Ctry_ID]","Countries","[Countries].[Ctry_ID]='" &
[Forms]![frmCountries].[CID] & "'")<>""
(this example assumes that the table is called Countries, the field in it is
called Ctry_ID, the form is called frmCountries and the control is called
CID; change to your actual names)
Note: the syntax above assumes that the field is Text type. If it is numeric
then use this instead:
DFirst("[Ctry_ID]","Countries","[Countries].[Ctry_ID]=" &
[Forms]![frmCountries].[CID] )<>""

On the same line, in the Action column type the word "MsgBox" (without the
quotes). You will see a small window with four arguments in the bottom left
hand corner. Type your warning message in the first one, and leave the
others as they are (or play around with them to see what they do). Save and
you're done.

HTH,
Nikos
 
Back
Top