Validation Rule for field values

[Brian G]

I have a database that includes a phone number field. I want to prevent a
user from creating a new record that contains a phone number that is
currently in a record that exists in the database.

I am using a form for data entry into the table. Somehow I'd like for there
to be a check to see if the phone number typed into the phone number field to
be checked against existing phone numbers in the database.

I've researched various sources but haven't been able to find the solution.
I'm not familiar with VB so any replies that suggest using that will probably
not work for me. Sorry, just being honest.

Thanks in advance for any help.
Brian...

 

[Wayne-I-M]

Hi Brian

Use this AfterUpdate of your telephone field on the form. Change the names
to what you use on your form and in your table.


If ((DLookup("[PrimaryFieldID]", "[TableName]", "[TelephoneNumber] ='" &
Form!TelephoneNumber &'"))) Then
MsgBox "This telephone number is already listed in DB", vbOKOnly,
"New Booking checker"
End If

 

[Allen Browne]

Open your table in design view.
Select the Phone Number field.
In the lower pane of table design, set:
Indexed: Yes (No Duplicates)

 

[storrboy]

To further ensure this (in case anything is in error with the code, or
some un-thought of condition occurs) set the Indexed property on the
phone number field to Yes (No Duplicates). An Access error will occur
if a duplicate phone number is attempted to be added, but the previous
posts method should catch that before hand and display the custom
message.

 

[Wayne-I-M]

Hi

Allen's answer is the simplest LoL

But just in case you want to - ?? (there was a typo in my last - just
checked this and it's OK)

Private Sub NumberConmtrol_AfterUpdate()
If ((DLookup("[PrimaryFieldID]", "[TableName]", "[NumberField] ='" &
Form!NumberControl & "' "))) Then
Beep
MsgBox "This number is already in use", vbOKOnly, "Telephone number
checker"
End If
End Sub


Notes - you will need to alter these.
Number field = the field containing the number in the "table"
PrimaryFieldID = The ID Primary field of the record containg the number in
the "table"
NumberControl = the field containing the number in the "form"

You must leave all the '" in place - just change the text.
Take the Beep off in you don't want it.


Hope this helps

 

[Brian G]

When I created the field up I did set the Index to Yes (No duplicates).
Unfortunately it didn't alert about the duplicate number until the form was
completely filled out and I know that the end users are going to be
aggravated. Maybe my first question should have been - is there a way to get
the system check for duplicates when the user tabs out of the phone number
field.

Wayne - please forgive my ignorance. I tried to paste the code that you
wrote into the VB code editor but I kept getting errors. I'm sure that it's
something that I'm doing or not doing.

If I could borrow your expertise one more time. Below are the specifics
concerning my database.
Business Phone = field name on form
frm Contacts = form name
Business Phone = field in table containing data
tbl Contacts
Record ID = not sure if this is needed but this is unique to each record in
the database

Below is what I entered into the VB Editor window:

Private Sub Business_Phone_AfterUpdate()
If ((DLookup("Business_Phone", "tbl_Contacts", "Business_Phone ='" &
frm Contacts!Business_Phone & "' "))) Then
Beep
MsgBox "This number is already in use", vbOKOnly, "Telephone number "
checker ""
End If
End Sub

Thanks in advance for your help.
Brian...

Hi

Allen's answer is the simplest LoL

But just in case you want to - ?? (there was a typo in my last - just
checked this and it's OK)

Private Sub NumberConmtrol_AfterUpdate()
If ((DLookup("[PrimaryFieldID]", "[TableName]", "[NumberField] ='" &
Form!NumberControl & "' "))) Then
Beep
MsgBox "This number is already in use", vbOKOnly, "Telephone number
checker"
End If
End Sub


Notes - you will need to alter these.
Number field = the field containing the number in the "table"
PrimaryFieldID = The ID Primary field of the record containg the number in
the "table"
NumberControl = the field containing the number in the "form"

You must leave all the '" in place - just change the text.
Take the Beep off in you don't want it.


Hope this helps


--
Wayne
Manchester, England.

Open your table in design view.
Select the Phone Number field.
In the lower pane of table design, set:
Indexed: Yes (No Duplicates)

 

[Wayne-I-M]

Hi Brian

Open the form in design view
Select the control [Business Phone] - oh and when you have time go back and
change the name to remove the space between the words - but that can wait)
Right click [Business Phone]
Open the properties box
Select the Data column
Select the AfterUpdate row
Click the build option (...)
Select code
You will see this

Private Sub [Business Phone]_AfterUpdate()

End Sub

You need to insert the code between these 2 lines so that is looks like this
(cut and paste if you want)


Private Sub Business Phone_AfterUpdate()
If ((DLookup("[Record ID]", "[tbl Contacts]", "[Business Phone] ='" &
Form!Business Phone & "' "))) Then
Beep
MsgBox "This number is already in use", vbOKOnly, "Telephone number
checker"
End If
End Sub


Give it a try and let me know if there is a problem - if it does not work
you should post the "exact" message that appears and also any lines that are
highlighted in yellow.

--
Wayne
Manchester, England.

When I created the field up I did set the Index to Yes (No duplicates).
Unfortunately it didn't alert about the duplicate number until the form was
completely filled out and I know that the end users are going to be
aggravated. Maybe my first question should have been - is there a way to get
the system check for duplicates when the user tabs out of the phone number
field.

Wayne - please forgive my ignorance. I tried to paste the code that you
wrote into the VB code editor but I kept getting errors. I'm sure that it's
something that I'm doing or not doing.

If I could borrow your expertise one more time. Below are the specifics
concerning my database.
Business Phone = field name on form
frm Contacts = form name
Business Phone = field in table containing data
tbl Contacts
Record ID = not sure if this is needed but this is unique to each record in
the database

Below is what I entered into the VB Editor window:

Private Sub Business_Phone_AfterUpdate()
If ((DLookup("Business_Phone", "tbl_Contacts", "Business_Phone ='" &
frm Contacts!Business_Phone & "' "))) Then
Beep
MsgBox "This number is already in use", vbOKOnly, "Telephone number "
checker ""
End If
End Sub

Thanks in advance for your help.
Brian...

Hi

Allen's answer is the simplest LoL

But just in case you want to - ?? (there was a typo in my last - just
checked this and it's OK)

Private Sub NumberConmtrol_AfterUpdate()
If ((DLookup("[PrimaryFieldID]", "[TableName]", "[NumberField] ='" &
Form!NumberControl & "' "))) Then
Beep
MsgBox "This number is already in use", vbOKOnly, "Telephone number
checker"
End If
End Sub


Notes - you will need to alter these.
Number field = the field containing the number in the "table"
PrimaryFieldID = The ID Primary field of the record containg the number in
the "table"
NumberControl = the field containing the number in the "form"

You must leave all the '" in place - just change the text.
Take the Beep off in you don't want it.


Hope this helps


--
Wayne
Manchester, England.

Open your table in design view.
Select the Phone Number field.
In the lower pane of table design, set:
Indexed: Yes (No Duplicates)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a database that includes a phone number field. I want to prevent a
user from creating a new record that contains a phone number that is
currently in a record that exists in the database.

I am using a form for data entry into the table. Somehow I'd like for
there
to be a check to see if the phone number typed into the phone number field
to
be checked against existing phone numbers in the database.

I've researched various sources but haven't been able to find the
solution.
I'm not familiar with VB so any replies that suggest using that will
probably
not work for me. Sorry, just being honest.

Thanks in advance for any help.
Brian...

 

[Brian G]

Wayne,

Thanks so much for your help. I got a Complile Error: Syntax Error.

This line was highlighted in yellow:
Private Sub Business_Phone_AfterUpdate()

The line below was highlighted in red:
If ((DLookup("[Record ID]", "[tbl Contacts]", "[Business Phone] ='" &

Thanks,
Brian...

Hi Brian

Open the form in design view
Select the control [Business Phone] - oh and when you have time go back and
change the name to remove the space between the words - but that can wait)
Right click [Business Phone]
Open the properties box
Select the Data column
Select the AfterUpdate row
Click the build option (...)
Select code
You will see this

Private Sub [Business Phone]_AfterUpdate()

End Sub

You need to insert the code between these 2 lines so that is looks like this
(cut and paste if you want)


Private Sub Business Phone_AfterUpdate()
If ((DLookup("[Record ID]", "[tbl Contacts]", "[Business Phone] ='" &
Form!Business Phone & "' "))) Then
Beep
MsgBox "This number is already in use", vbOKOnly, "Telephone number
checker"
End If
End Sub


Give it a try and let me know if there is a problem - if it does not work
you should post the "exact" message that appears and also any lines that are
highlighted in yellow.

--
Wayne
Manchester, England.

When I created the field up I did set the Index to Yes (No duplicates).
Unfortunately it didn't alert about the duplicate number until the form was
completely filled out and I know that the end users are going to be
aggravated. Maybe my first question should have been - is there a way to get
the system check for duplicates when the user tabs out of the phone number
field.

Wayne - please forgive my ignorance. I tried to paste the code that you
wrote into the VB code editor but I kept getting errors. I'm sure that it's
something that I'm doing or not doing.

If I could borrow your expertise one more time. Below are the specifics
concerning my database.
Business Phone = field name on form
frm Contacts = form name
Business Phone = field in table containing data
tbl Contacts
Record ID = not sure if this is needed but this is unique to each record in
the database

Below is what I entered into the VB Editor window:

Private Sub Business_Phone_AfterUpdate()
If ((DLookup("Business_Phone", "tbl_Contacts", "Business_Phone ='" &
frm Contacts!Business_Phone & "' "))) Then
Beep
MsgBox "This number is already in use", vbOKOnly, "Telephone number "
checker ""
End If
End Sub

Thanks in advance for your help.
Brian...

Hi

Allen's answer is the simplest LoL

But just in case you want to - ?? (there was a typo in my last - just
checked this and it's OK)

Private Sub NumberConmtrol_AfterUpdate()
If ((DLookup("[PrimaryFieldID]", "[TableName]", "[NumberField] ='" &
Form!NumberControl & "' "))) Then
Beep
MsgBox "This number is already in use", vbOKOnly, "Telephone number
checker"
End If
End Sub


Notes - you will need to alter these.
Number field = the field containing the number in the "table"
PrimaryFieldID = The ID Primary field of the record containg the number in
the "table"
NumberControl = the field containing the number in the "form"

You must leave all the '" in place - just change the text.
Take the Beep off in you don't want it.


Hope this helps


--
Wayne
Manchester, England.



:

Open your table in design view.
Select the Phone Number field.
In the lower pane of table design, set:
Indexed: Yes (No Duplicates)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a database that includes a phone number field. I want to prevent a
user from creating a new record that contains a phone number that is
currently in a record that exists in the database.

I am using a form for data entry into the table. Somehow I'd like for
there
to be a check to see if the phone number typed into the phone number field
to
be checked against existing phone numbers in the database.

I've researched various sources but haven't been able to find the
solution.
I'm not familiar with VB so any replies that suggest using that will
probably
not work for me. Sorry, just being honest.

Thanks in advance for any help.
Brian...

 

[Wayne-I-M]

Sorry my fault - just test that code and the space is messing it up.

Go into the properties box and change the name (on the "Other" column) to
BusinessPhone

Take out the space

The code will now look like this (this works)


Private Sub Business Phone_AfterUpdate()
If ((DLookup("[Record ID]", "[tbl Contacts]", "[Business Phone] ='" &
Form!BusinessPhone & "' "))) Then
Beep
MsgBox "This number is already in use", vbOKOnly, "Telephone number
checker"
End If
End Sub



Note the space has been removed from ='" & Form!BusinessPhone & "' "))) Then



--
Wayne
Manchester, England.

When I created the field up I did set the Index to Yes (No duplicates).
Unfortunately it didn't alert about the duplicate number until the form was
completely filled out and I know that the end users are going to be
aggravated. Maybe my first question should have been - is there a way to get
the system check for duplicates when the user tabs out of the phone number
field.

Wayne - please forgive my ignorance. I tried to paste the code that you
wrote into the VB code editor but I kept getting errors. I'm sure that it's
something that I'm doing or not doing.

If I could borrow your expertise one more time. Below are the specifics
concerning my database.
Business Phone = field name on form
frm Contacts = form name
Business Phone = field in table containing data
tbl Contacts
Record ID = not sure if this is needed but this is unique to each record in
the database

Below is what I entered into the VB Editor window:

Private Sub Business_Phone_AfterUpdate()
If ((DLookup("Business_Phone", "tbl_Contacts", "Business_Phone ='" &
frm Contacts!Business_Phone & "' "))) Then
Beep
MsgBox "This number is already in use", vbOKOnly, "Telephone number "
checker ""
End If
End Sub

Thanks in advance for your help.
Brian...

Hi

Allen's answer is the simplest LoL

But just in case you want to - ?? (there was a typo in my last - just
checked this and it's OK)

Private Sub NumberConmtrol_AfterUpdate()
If ((DLookup("[PrimaryFieldID]", "[TableName]", "[NumberField] ='" &
Form!NumberControl & "' "))) Then
Beep
MsgBox "This number is already in use", vbOKOnly, "Telephone number
checker"
End If
End Sub


Notes - you will need to alter these.
Number field = the field containing the number in the "table"
PrimaryFieldID = The ID Primary field of the record containg the number in
the "table"
NumberControl = the field containing the number in the "form"

You must leave all the '" in place - just change the text.
Take the Beep off in you don't want it.


Hope this helps


--
Wayne
Manchester, England.

Open your table in design view.
Select the Phone Number field.
In the lower pane of table design, set:
Indexed: Yes (No Duplicates)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a database that includes a phone number field. I want to prevent a
user from creating a new record that contains a phone number that is
currently in a record that exists in the database.

I am using a form for data entry into the table. Somehow I'd like for
there
to be a check to see if the phone number typed into the phone number field
to
be checked against existing phone numbers in the database.

I've researched various sources but haven't been able to find the
solution.
I'm not familiar with VB so any replies that suggest using that will
probably
not work for me. Sorry, just being honest.

Thanks in advance for any help.
Brian...

 

[Wayne-I-M]

Take the space out of the control name on the form

Private Sub Business_Phone_AfterUpdate()

Should be

Private Sub BusinessPhone_AfterUpdate()



--
Wayne
Manchester, England.

Wayne,

Thanks so much for your help. I got a Complile Error: Syntax Error.

This line was highlighted in yellow:
Private Sub Business_Phone_AfterUpdate()

The line below was highlighted in red:
If ((DLookup("[Record ID]", "[tbl Contacts]", "[Business Phone] ='" &

Thanks,
Brian...

Hi Brian

Open the form in design view
Select the control [Business Phone] - oh and when you have time go back and
change the name to remove the space between the words - but that can wait)
Right click [Business Phone]
Open the properties box
Select the Data column
Select the AfterUpdate row
Click the build option (...)
Select code
You will see this

Private Sub [Business Phone]_AfterUpdate()

End Sub

You need to insert the code between these 2 lines so that is looks like this
(cut and paste if you want)


Private Sub Business Phone_AfterUpdate()
If ((DLookup("[Record ID]", "[tbl Contacts]", "[Business Phone] ='" &
Form!Business Phone & "' "))) Then
Beep
MsgBox "This number is already in use", vbOKOnly, "Telephone number
checker"
End If
End Sub


Give it a try and let me know if there is a problem - if it does not work
you should post the "exact" message that appears and also any lines that are
highlighted in yellow.

--
Wayne
Manchester, England.

When I created the field up I did set the Index to Yes (No duplicates).
Unfortunately it didn't alert about the duplicate number until the form was
completely filled out and I know that the end users are going to be
aggravated. Maybe my first question should have been - is there a way to get
the system check for duplicates when the user tabs out of the phone number
field.

Wayne - please forgive my ignorance. I tried to paste the code that you
wrote into the VB code editor but I kept getting errors. I'm sure that it's
something that I'm doing or not doing.

If I could borrow your expertise one more time. Below are the specifics
concerning my database.
Business Phone = field name on form
frm Contacts = form name
Business Phone = field in table containing data
tbl Contacts
Record ID = not sure if this is needed but this is unique to each record in
the database

Below is what I entered into the VB Editor window:

Private Sub Business_Phone_AfterUpdate()
If ((DLookup("Business_Phone", "tbl_Contacts", "Business_Phone ='" &
frm Contacts!Business_Phone & "' "))) Then
Beep
MsgBox "This number is already in use", vbOKOnly, "Telephone number "
checker ""
End If
End Sub

Thanks in advance for your help.
Brian...

:

Hi

Allen's answer is the simplest LoL

But just in case you want to - ?? (there was a typo in my last - just
checked this and it's OK)

Private Sub NumberConmtrol_AfterUpdate()
If ((DLookup("[PrimaryFieldID]", "[TableName]", "[NumberField] ='" &
Form!NumberControl & "' "))) Then
Beep
MsgBox "This number is already in use", vbOKOnly, "Telephone number
checker"
End If
End Sub


Notes - you will need to alter these.
Number field = the field containing the number in the "table"
PrimaryFieldID = The ID Primary field of the record containg the number in
the "table"
NumberControl = the field containing the number in the "form"

You must leave all the '" in place - just change the text.
Take the Beep off in you don't want it.


Hope this helps


--
Wayne
Manchester, England.



:

Open your table in design view.
Select the Phone Number field.
In the lower pane of table design, set:
Indexed: Yes (No Duplicates)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a database that includes a phone number field. I want to prevent a
user from creating a new record that contains a phone number that is
currently in a record that exists in the database.

I am using a form for data entry into the table. Somehow I'd like for
there
to be a check to see if the phone number typed into the phone number field
to
be checked against existing phone numbers in the database.

I've researched various sources but haven't been able to find the
solution.
I'm not familiar with VB so any replies that suggest using that will
probably
not work for me. Sorry, just being honest.

Thanks in advance for any help.
Brian...

 

[Brian G]

Success!

Wayne - Thanks for your help! The word wrap on this message board was what
was keeping the code from working. I came in with a clear mind this morning
and just started trying stuff. Funny how simply removing the hard returns was
the problem. Now that it works I am going to apply the code to another field.

I'd offer to buy you a couple of cold ones at the nearest Pub, but I'm not
sure that my boss would allow me to expense the trip. haha

Thanks again for your help.
Brian G
Dallas, TX

Sorry my fault - just test that code and the space is messing it up.

Go into the properties box and change the name (on the "Other" column) to
BusinessPhone

Take out the space

The code will now look like this (this works)


Private Sub Business Phone_AfterUpdate()
If ((DLookup("[Record ID]", "[tbl Contacts]", "[Business Phone] ='" &
Form!BusinessPhone & "' "))) Then
Beep
MsgBox "This number is already in use", vbOKOnly, "Telephone number
checker"
End If
End Sub



Note the space has been removed from ='" & Form!BusinessPhone & "' "))) Then



--
Wayne
Manchester, England.

When I created the field up I did set the Index to Yes (No duplicates).
Unfortunately it didn't alert about the duplicate number until the form was
completely filled out and I know that the end users are going to be
aggravated. Maybe my first question should have been - is there a way to get
the system check for duplicates when the user tabs out of the phone number
field.

Wayne - please forgive my ignorance. I tried to paste the code that you
wrote into the VB code editor but I kept getting errors. I'm sure that it's
something that I'm doing or not doing.

If I could borrow your expertise one more time. Below are the specifics
concerning my database.
Business Phone = field name on form
frm Contacts = form name
Business Phone = field in table containing data
tbl Contacts
Record ID = not sure if this is needed but this is unique to each record in
the database

Below is what I entered into the VB Editor window:

Private Sub Business_Phone_AfterUpdate()
If ((DLookup("Business_Phone", "tbl_Contacts", "Business_Phone ='" &
frm Contacts!Business_Phone & "' "))) Then
Beep
MsgBox "This number is already in use", vbOKOnly, "Telephone number "
checker ""
End If
End Sub

Thanks in advance for your help.
Brian...

Hi

Allen's answer is the simplest LoL

But just in case you want to - ?? (there was a typo in my last - just
checked this and it's OK)

Private Sub NumberConmtrol_AfterUpdate()
If ((DLookup("[PrimaryFieldID]", "[TableName]", "[NumberField] ='" &
Form!NumberControl & "' "))) Then
Beep
MsgBox "This number is already in use", vbOKOnly, "Telephone number
checker"
End If
End Sub


Notes - you will need to alter these.
Number field = the field containing the number in the "table"
PrimaryFieldID = The ID Primary field of the record containg the number in
the "table"
NumberControl = the field containing the number in the "form"

You must leave all the '" in place - just change the text.
Take the Beep off in you don't want it.


Hope this helps


--
Wayne
Manchester, England.



:

Open your table in design view.
Select the Phone Number field.
In the lower pane of table design, set:
Indexed: Yes (No Duplicates)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a database that includes a phone number field. I want to prevent a
user from creating a new record that contains a phone number that is
currently in a record that exists in the database.

I am using a form for data entry into the table. Somehow I'd like for
there
to be a check to see if the phone number typed into the phone number field
to
be checked against existing phone numbers in the database.

I've researched various sources but haven't been able to find the
solution.
I'm not familiar with VB so any replies that suggest using that will
probably
not work for me. Sorry, just being honest.

Thanks in advance for any help.
Brian...

 

[Wayne-I-M]

Glad it worked in the end.

 

[Cody Kiroff]

Hi,
I have tried to adapt this code to my project. I want it to do exactly the
same thing. I believe that I am very close, but I think that I have a
problem in my naming. I have the following:

SP Number = field name on form
SP Number = field in table containing data
SP Info = table that contains data

And my record ID is unique to the SP Info. I want each record to be
different by SP Number. Is there a way to adapt this to mine without
changing names. I have many forms and reports that are dependent on the
names I currently have. Changing them would be a severe headache.

 

[Cody Kiroff]

Hi, I am having problems. I tried to use this code, but it didn't work for
me. I was wondering if you could help me adapt this code. I found it
helpful, but for some reason it wouldn't work. Could you take a look at it?

SP Number = field name on form
frmSPInfo = form name
SP Number = field in table containing data
SP Info is the table

I don't have a record ID, but SP Number is the value that I don't want to
allow duplicates of. I tried to implement the previous code, but I didn't
have a Record ID so I didn't know how to make it work. Thank you.