Checking for duplicate id numbers in a form

  • Thread starter Thread starter Richardson
  • Start date Start date
R

Richardson

I have a form based on a single table, tblMembers, with the primary key of
MemberID. This is an alphanumeric id number assigned manually. There is a
possibility that the person doing data input will attempt to create
duplicate ID numbers. Does anyone have a suggestion for code that I can
have run when the id field is updated that will check if that id exists, and
prompt the user to create a new id? It would be good if I could also
display some information about the entry that exists so that they can
determine if they are re-entering the same person, or a different person
with the same id number.
I need this so that they do not encounter the duplicate primary key error
when they try to leave the form.

Thanks in advance for your help.

Lori
 
Have you indeed defined MemberID as the primary key of the table, in design
view of the table? If so, Access will not let you store records with
duplicate primary key values. Try it on your form, and see. The standard
error message is not very good, but you can substitute your own message by
providing a Form_Error event & trapping the relevant error number.

HTH,
TC
 
Thank you fo the input.
MemberId is in the primary key and Access does provide the standard error
message. I have also applied form error tracking. However, I am trying to
catch the error and prompt the user before they go all the way through the
form and try to save the data. I want to validate the Memberid immediately
upon update so that I can direct them down the right path for the data in
the record.

Lori
 
Ok, then you need a BeforeUpdate procedure for the member ID textbox
control. Start with something along the following lines. Replace the two
'xxx's with the name of the member ID textbox control.

(untested)

private sub xxx_BeforeUpdate (cancel as integer)
if dlookup (True, "tblMembers", "[memberid]=""" & me![xxx] & """")) then
msgbox "member id already on file!"
cancel = true
endif
end sub

HTH,
TC
 
Back
Top