Search table to confirm if record exists

[Guest]

Hi all
I expect this is an easy one (when you know how!)
I have a main form with a continuous subform displaying related records. On
the main form I have an "add new record" button which opens up the form in
Add mode. The primary key used for the table on which the main form is based
is a unique string of up to 7 characters.

I need to check to see whether the primary key entered already exists in the
table, and if it does, I need to alert the user with a message box to that
effect. (As things stand at the moment, as soon as the key field is entered,
if a record with that key already exists, the subform displays the related
records. I need to stop this before it happens so the user enters a unique
primary key.)
Access automatically does this check when the user tries to save the
record... but I have several fields below this key field which the user must
complete before trying to save. How can I do an "immediate" check on the
string entered ? (Presumably on the OnUpdate event of the textbox... but
what is the code that is needed?)

TIA
rich

 

[Guest]

Hi, Rich.

I need to check to see whether the primary key entered already exists in the
table, and if it does, I need to alert the user with a message box to that
effect.

In the OnBeforeUpdate( ) event of the text box:

If DCount("PKey", "MyTable", "PKey= '" & Me!txtPKey & "'") > 0 Then

MsgBox "This primary key has already been used."
End If

.... where PKey is the name of the primary key field, this field is a Text
data type, and MyTable is the name of the table.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

If Not IsNull(DLookUp("PKey", "MyTable", "PKey= '" & Me!txtPKey & "'")) Then
MsgBox "This Key alreadsy exists"
End If

Although the DCount will do the job, I prefer the DLookup because it does
not have to search the entire database every time. It will only be do a
complete search if the value does not exist.

 

[Guest]

Thanks guys.
I used Klatuu's method. I then stopped Access from still trying to save the
record by including Me.Undo in the code.

 

['69 Camaro]

Hi, Klatuu.

I prefer the DLookup because it does
not have to search the entire database every time.

I assume you are referring to a table scan to locate the appropriate record.
(Please correct me if I'm wrong.) Domain functions utilize indexes when
indexes have been assigned to fields, and since it's the primary key, it has
a unique index, so no table scan will be made to locate the record. Only
the index will be checked for the value to determine whether there's a
match.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

['69 Camaro]

Hi, Rich.

Klatuu's method takes longer to execute at run time with the extra IsNull( )
function, but since you copied and pasted his code into your module it
didn't require any extra typing time from you. The user's won't notice the
difference . . . but if they ever do, here's one place you know how to make
it run a tiny fraction of a second faster. ;-)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.