Duplicate Form Entry

[Guest]

Hi,

I have several forms which constantly get entered several times causing me a
huge problem with duplicate information. I want to create a warning pop up
message which will warn users that a record already exists and prevent them
re-entering it.

I've tried this before but my VB skills are not exactly good. The form I
want to start with only has four fields. However the two fields I want to
use to spot a duplicate are combo boxes I have created that look up values
from other tables/queries. The two fields are:

Staff Member, (Staff Number, Training Table) This is linked to a query that
concatenates two fields from the staff table (Forename & Surname) . This
field is used to lookup the name of the employee but the staff number is
whats stored on this form.

The second field is also text it basically looks up the name of courses in
the Course Table and stores the course number on this form.

I've tried to include as much information as possible if I've left anything
please give me a shout. I would also like to point out that my VB skills are
very limited.

Please help.

Ian

 

[Guest]

You need to have a key or index setup in the table you are writing to in
order to avoid the duplicates. You can then add an error handler in the
forms vb code. I normally add a "save" button that runs the code to save the
record and close the form, or go to new record. If the record is a duplicate
the error handler should catch it and do what you define. I am not sure what
all runs in your case but the following example may get you headed in the
right direction.

Private Sub Save_Click()
docmd.setwarnings false
on error goto dup_err
docmd. runcommand accmdsaverecord
docmd.close acform,"formname"
docmd.setwarnings true
dup_err:
retvalue=msgbox("The message you want goes here",vbokonly)
docmd.setwarnings true
exit sub
end sub


In this case when the user clicks ok the code will simply stop and they will
be looking at the form again. I set the warnings to false to avoid having
the default access warning for key violations popup. Hope this helps