Duplicates in an Access Table that don't show up until ADD is clic

[Guest]

and, if possible show the record with the duplicate number. I have a basic
table that has a field called ANumber that restricts duplicates. However in
the form, it doesn't tell us it is a duplicate until we hit the ADD button at
the bottom of the form after ALL the data has been entered. How can I tell
it to let the person entering know it is a duplicate as soon as the number
has been entered?

I am not very proficient at writing code, so please help!

 

[RD]

Hi Catlady,

the problem is that Access can't tell it's a duplicate until it tries to save
the record. That's way way it was designed. The only way you are going to be
able to tell if ANumber is a dupe is to add code to your form that will check
the table after that field on the form is filled out.

I'm afraid I don't have time to come up with some aircode for you right now but
I'll check in tomorrow to see if anyone else has volunteered.

Regards,
RD

 

[Guest]

I believed that was the only way to do it, but am not very good with writing
code. I have tried a number of things but they just haven't worked for me.
If you find time to help later I would appreciate it.

Thank you.

 

[Roger Carlson]

There are a number of ways to do this, but here's one:

First, let's assume that your table called Table1 and your field is called
ANumber. Let's further assume ANumber is a number (the code would be
different if it's text and I'll give both). Lastly, let's say the textbox
that is bound to ANumber is called txtANumber.

In the BeforeUpdate event of the txtANumber textbox, add an Event Procedure
with the following code:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " & txtANumber) Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

If ANumber is a text field, the code would vary slightly to this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = '" & txtANumber &
"'") Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

NOTE: watch for word-wrap the "If" and "Then" need to be on one line.

Just replace your table, field, and textbox names in the above and you
should be good.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Roger, Thank you so much! I was able to modify the code slightly and it
worked! I knew there had to be simple code just couldn't get it in my head
the proper syntax. I had a programmer that does work for us tell me it would
take several days to write that kind of code.

Again, I really appreciate your help!

There are a number of ways to do this, but here's one:

First, let's assume that your table called Table1 and your field is called
ANumber. Let's further assume ANumber is a number (the code would be
different if it's text and I'll give both). Lastly, let's say the textbox
that is bound to ANumber is called txtANumber.

In the BeforeUpdate event of the txtANumber textbox, add an Event Procedure
with the following code:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " & txtANumber) Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

If ANumber is a text field, the code would vary slightly to this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = '" & txtANumber &
"'") Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

NOTE: watch for word-wrap the "If" and "Then" need to be on one line.

Just replace your table, field, and textbox names in the above and you
should be good.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

and, if possible show the record with the duplicate number. I have a basic
table that has a field called ANumber that restricts duplicates. However in
the form, it doesn't tell us it is a duplicate until we hit the ADD button at
the bottom of the form after ALL the data has been entered. How can I tell
it to let the person entering know it is a duplicate as soon as the number
has been entered?

I am not very proficient at writing code, so please help!

 

[Roger Carlson]

Humpf! I made it up from scratch AND created a little database to test it
in about 20 minutes.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger, Thank you so much! I was able to modify the code slightly and it
worked! I knew there had to be simple code just couldn't get it in my head
the proper syntax. I had a programmer that does work for us tell me it would
take several days to write that kind of code.

Again, I really appreciate your help!

There are a number of ways to do this, but here's one:

First, let's assume that your table called Table1 and your field is called
ANumber. Let's further assume ANumber is a number (the code would be
different if it's text and I'll give both). Lastly, let's say the textbox
that is bound to ANumber is called txtANumber.

In the BeforeUpdate event of the txtANumber textbox, add an Event Procedure
with the following code:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " & txtANumber) Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

If ANumber is a text field, the code would vary slightly to this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = '" & txtANumber &
"'") Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

NOTE: watch for word-wrap the "If" and "Then" need to be on one line.

Just replace your table, field, and textbox names in the above and you
should be good.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

and, if possible show the record with the duplicate number. I have a basic
table that has a field called ANumber that restricts duplicates.

However
in

the form, it doesn't tell us it is a duplicate until we hit the ADD

button
at

the bottom of the form after ALL the data has been entered. How can I tell
it to let the person entering know it is a duplicate as soon as the number
has been entered?

I am not very proficient at writing code, so please help!

 

[Guest]

Roger, that's exactly what I thought! I wondered if I could ask another
quick question. Someone else who wrote gave me code saying if it was a
duplicate did they want to open the record, and to push yes if so. That's a
really good idea, but I can't make the code work. Is this difficult to do?

Humpf! I made it up from scratch AND created a little database to test it
in about 20 minutes.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger, Thank you so much! I was able to modify the code slightly and it
worked! I knew there had to be simple code just couldn't get it in my head
the proper syntax. I had a programmer that does work for us tell me it would
take several days to write that kind of code.

Again, I really appreciate your help!

There are a number of ways to do this, but here's one:

First, let's assume that your table called Table1 and your field is called
ANumber. Let's further assume ANumber is a number (the code would be
different if it's text and I'll give both). Lastly, let's say the textbox
that is bound to ANumber is called txtANumber.

In the BeforeUpdate event of the txtANumber textbox, add an Event Procedure
with the following code:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " & txtANumber) Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

If ANumber is a text field, the code would vary slightly to this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = '" & txtANumber &
"'") Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

NOTE: watch for word-wrap the "If" and "Then" need to be on one line.

Just replace your table, field, and textbox names in the above and you
should be good.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



and, if possible show the record with the duplicate number. I have a basic
table that has a field called ANumber that restricts duplicates. However
in
the form, it doesn't tell us it is a duplicate until we hit the ADD button
at
the bottom of the form after ALL the data has been entered. How can I
tell
it to let the person entering know it is a duplicate as soon as the number
has been entered?

I am not very proficient at writing code, so please help!

 

[Roger Carlson]

In that case, try something like this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= CLng(Me.ANumber.text)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " & ANumber) Then
Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = " & ANumber
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub

Note: I added the IF ME.NEWRECORD bit because this only makes sense if it
happens in a new record. Without this, it will happen if you try to change
the value of an existing record, which may have unforeseen results.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger, that's exactly what I thought! I wondered if I could ask another
quick question. Someone else who wrote gave me code saying if it was a
duplicate did they want to open the record, and to push yes if so. That's a
really good idea, but I can't make the code work. Is this difficult to do?

Humpf! I made it up from scratch AND created a little database to test it
in about 20 minutes.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger, Thank you so much! I was able to modify the code slightly and it
worked! I knew there had to be simple code just couldn't get it in my head
the proper syntax. I had a programmer that does work for us tell me

it
would

take several days to write that kind of code.

Again, I really appreciate your help!

:

There are a number of ways to do this, but here's one:

First, let's assume that your table called Table1 and your field is called
ANumber. Let's further assume ANumber is a number (the code would be
different if it's text and I'll give both). Lastly, let's say the textbox
that is bound to ANumber is called txtANumber.

In the BeforeUpdate event of the txtANumber textbox, add an Event Procedure
with the following code:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " &

txtANumber)
Then

MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

If ANumber is a text field, the code would vary slightly to this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = '" & txtANumber &
"'") Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

NOTE: watch for word-wrap the "If" and "Then" need to be on one line.

Just replace your table, field, and textbox names in the above and you
should be good.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



and, if possible show the record with the duplicate number. I have

a
basic

table that has a field called ANumber that restricts duplicates. However
in
the form, it doesn't tell us it is a duplicate until we hit the

ADD
button

at
the bottom of the form after ALL the data has been entered. How can I
tell
it to let the person entering know it is a duplicate as soon as

the
number

has been entered?

I am not very proficient at writing code, so please help!

 

[Guest]

Roger, Sorry to be a pest, but I'm been fussing with this code for several
hours and wonder what I'm doing wrong. This is what I have (the original
working code is dimmed out). The new code isn't working. Was I supposed to
add it to the original code? If not, if you can tell with a quick look why
it isn't working let me know. Otherwise, we'll just stick with the original
code. It isn't that big of deal, but would be nice if I could get it to work.

Thanks,

In that case, try something like this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= CLng(Me.ANumber.text)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " & ANumber) Then
Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = " & ANumber
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub

Note: I added the IF ME.NEWRECORD bit because this only makes sense if it
happens in a new record. Without this, it will happen if you try to change
the value of an existing record, which may have unforeseen results.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger, that's exactly what I thought! I wondered if I could ask another
quick question. Someone else who wrote gave me code saying if it was a
duplicate did they want to open the record, and to push yes if so. That's a
really good idea, but I can't make the code work. Is this difficult to do?

Humpf! I made it up from scratch AND created a little database to test it
in about 20 minutes.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Roger, Thank you so much! I was able to modify the code slightly and it
worked! I knew there had to be simple code just couldn't get it in my
head
the proper syntax. I had a programmer that does work for us tell me it
would
take several days to write that kind of code.

Again, I really appreciate your help!

:

There are a number of ways to do this, but here's one:

First, let's assume that your table called Table1 and your field is
called
ANumber. Let's further assume ANumber is a number (the code would be
different if it's text and I'll give both). Lastly, let's say the
textbox
that is bound to ANumber is called txtANumber.

In the BeforeUpdate event of the txtANumber textbox, add an Event
Procedure
with the following code:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " & txtANumber)
Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

If ANumber is a text field, the code would vary slightly to this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = '" & txtANumber &
"'") Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

NOTE: watch for word-wrap the "If" and "Then" need to be on one line.

Just replace your table, field, and textbox names in the above and you
should be good.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



and, if possible show the record with the duplicate number. I have a
basic
table that has a field called ANumber that restricts duplicates.
However
in
the form, it doesn't tell us it is a duplicate until we hit the ADD
button
at
the bottom of the form after ALL the data has been entered. How can I
tell
it to let the person entering know it is a duplicate as soon as the
number
has been entered?

I am not very proficient at writing code, so please help!

 

[Guest]

Roger sorry, but when it debugs, this is the line it doesn't like. When I
move over it, it shows the ANumber I put in, but it continues to highlight it
and say it needs to be deguged. I'm not sure what I'm doing wrong, as one
time it says it is a Type MisMatch but when I saved it and opened a new form
it still says it needs to be degugged but returns the number I entered? It
isn't worth a lot of time but if you have a moment to look at it I'd
appreciate it.

In that case, try something like this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= CLng(Me.ANumber.text)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " & ANumber) Then
Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = " & ANumber
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub

Note: I added the IF ME.NEWRECORD bit because this only makes sense if it
happens in a new record. Without this, it will happen if you try to change
the value of an existing record, which may have unforeseen results.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger, that's exactly what I thought! I wondered if I could ask another
quick question. Someone else who wrote gave me code saying if it was a
duplicate did they want to open the record, and to push yes if so. That's a
really good idea, but I can't make the code work. Is this difficult to do?

Humpf! I made it up from scratch AND created a little database to test it
in about 20 minutes.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Roger, Thank you so much! I was able to modify the code slightly and it
worked! I knew there had to be simple code just couldn't get it in my
head
the proper syntax. I had a programmer that does work for us tell me it
would
take several days to write that kind of code.

Again, I really appreciate your help!

:

There are a number of ways to do this, but here's one:

First, let's assume that your table called Table1 and your field is
called
ANumber. Let's further assume ANumber is a number (the code would be
different if it's text and I'll give both). Lastly, let's say the
textbox
that is bound to ANumber is called txtANumber.

In the BeforeUpdate event of the txtANumber textbox, add an Event
Procedure
with the following code:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " & txtANumber)
Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

If ANumber is a text field, the code would vary slightly to this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = '" & txtANumber &
"'") Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

NOTE: watch for word-wrap the "If" and "Then" need to be on one line.

Just replace your table, field, and textbox names in the above and you
should be good.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



and, if possible show the record with the duplicate number. I have a
basic
table that has a field called ANumber that restricts duplicates.
However
in
the form, it doesn't tell us it is a duplicate until we hit the ADD
button
at
the bottom of the form after ALL the data has been entered. How can I
tell
it to let the person entering know it is a duplicate as soon as the
number
has been entered?

I am not very proficient at writing code, so please help!

 

[Roger Carlson]

Exactly what does your code look like and what is the exact error message?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger sorry, but when it debugs, this is the line it doesn't like. When I
move over it, it shows the ANumber I put in, but it continues to highlight it
and say it needs to be deguged. I'm not sure what I'm doing wrong, as one
time it says it is a Type MisMatch but when I saved it and opened a new form
it still says it needs to be degugged but returns the number I entered? It
isn't worth a lot of time but if you have a moment to look at it I'd
appreciate it.

In that case, try something like this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= CLng(Me.ANumber.text)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " & ANumber) Then
Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = " & ANumber
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub

Note: I added the IF ME.NEWRECORD bit because this only makes sense if it
happens in a new record. Without this, it will happen if you try to change
the value of an existing record, which may have unforeseen results.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger, that's exactly what I thought! I wondered if I could ask another
quick question. Someone else who wrote gave me code saying if it was a
duplicate did they want to open the record, and to push yes if so.

That's
a

really good idea, but I can't make the code work. Is this difficult

to
do?

:

Humpf! I made it up from scratch AND created a little database to

test
it

in about 20 minutes.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Roger, Thank you so much! I was able to modify the code slightly

and
it

worked! I knew there had to be simple code just couldn't get it in my
head
the proper syntax. I had a programmer that does work for us tell

me
it

would
take several days to write that kind of code.

Again, I really appreciate your help!

:

There are a number of ways to do this, but here's one:

First, let's assume that your table called Table1 and your field is
called
ANumber. Let's further assume ANumber is a number (the code

would
be

different if it's text and I'll give both). Lastly, let's say the
textbox
that is bound to ANumber is called txtANumber.

In the BeforeUpdate event of the txtANumber textbox, add an Event
Procedure
with the following code:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " & txtANumber)
Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

If ANumber is a text field, the code would vary slightly to this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = '" & txtANumber &
"'") Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

NOTE: watch for word-wrap the "If" and "Then" need to be on one line.

Just replace your table, field, and textbox names in the above

and
you

should be good.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



and, if possible show the record with the duplicate number. I

have
a

basic
table that has a field called ANumber that restricts duplicates.
However
in
the form, it doesn't tell us it is a duplicate until we hit

the
ADD

button
at
the bottom of the form after ALL the data has been entered.

How
can I

tell
it to let the person entering know it is a duplicate as soon

as
the

number
has been entered?

I am not very proficient at writing code, so please help!

 

[Guest]

All code is like this (I dimmed out what wasn't working) When I purposely
type in a duplicate number and tab out of the field I get a Run-time error
'13": Type mismatch. When I run my cursor over it the highlighted line, it
is the (A_Number = CLng(Me.A_Number.Text)) Clng that comes up with a type
mismatch.


Private Sub A_Number_BeforeUpdate(Cancel As Integer)

If ANumber = DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
MsgBox "You have entered a duplicate number."
Cancel = True
Me.A_Number.SelStart = 0
Me.A_Number.SelLength = Len(Me.ANumber)
End If
'In that case, try something like this:

'Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
'Dim Response As Integer
'Dim ANumber As Long
'Dim rs As Object
'
' If Me.NewRecord Then
' A_Number = CLng(Me.A_Number.Text)
' If ANumber = DLookup("ANumber", "tblNewApplicants ", "[ANumber] = " &
ANumber) Then
' MsgBox "Value Exists - Open Existing?", vbYesNo
' If Response = vbYes Then
' Me.A_Number.Undo
' DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
'
' Set rs = Me.Recordset.Clone
' rs.FindFirst "[ANumber] = " & ANumber
' Me.Bookmark = rs.Bookmark
' Else
' Cancel = True
' Me.A_Number.SelStart = 0
' Me.A_Number.SelLength = Len(Me.ANumber)
'End If
'End If
'End If
'
End Sub

Exactly what does your code look like and what is the exact error message?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger sorry, but when it debugs, this is the line it doesn't like. When I
move over it, it shows the ANumber I put in, but it continues to highlight it
and say it needs to be deguged. I'm not sure what I'm doing wrong, as one
time it says it is a Type MisMatch but when I saved it and opened a new form
it still says it needs to be degugged but returns the number I entered? It
isn't worth a lot of time but if you have a moment to look at it I'd
appreciate it.

In that case, try something like this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= CLng(Me.ANumber.text)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " & ANumber) Then
Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = " & ANumber
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub

Note: I added the IF ME.NEWRECORD bit because this only makes sense if it
happens in a new record. Without this, it will happen if you try to change
the value of an existing record, which may have unforeseen results.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger, that's exactly what I thought! I wondered if I could ask another
quick question. Someone else who wrote gave me code saying if it was a
duplicate did they want to open the record, and to push yes if so. That's
a
really good idea, but I can't make the code work. Is this difficult to
do?


:

Humpf! I made it up from scratch AND created a little database to test
it
in about 20 minutes.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Roger, Thank you so much! I was able to modify the code slightly and
it
worked! I knew there had to be simple code just couldn't get it in my
head
the proper syntax. I had a programmer that does work for us tell me
it
would
take several days to write that kind of code.

Again, I really appreciate your help!

:

There are a number of ways to do this, but here's one:

First, let's assume that your table called Table1 and your field is
called
ANumber. Let's further assume ANumber is a number (the code would
be
different if it's text and I'll give both). Lastly, let's say the
textbox
that is bound to ANumber is called txtANumber.

In the BeforeUpdate event of the txtANumber textbox, add an Event
Procedure
with the following code:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " &
txtANumber)
Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

If ANumber is a text field, the code would vary slightly to this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = '" &
txtANumber &
"'") Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

NOTE: watch for word-wrap the "If" and "Then" need to be on one
line.

Just replace your table, field, and textbox names in the above and
you
should be good.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



and, if possible show the record with the duplicate number. I have
a
basic
table that has a field called ANumber that restricts duplicates.
However
in
the form, it doesn't tell us it is a duplicate until we hit the
ADD
button
at
the bottom of the form after ALL the data has been entered. How
can I
tell
it to let the person entering know it is a duplicate as soon as
the
number
has been entered?

I am not very proficient at writing code, so please help!

 

[Roger Carlson]

Sorry. I didn't know that ANumber was text. My code assumed it was
numeric. This should work:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= Me.ANumber.text
If ANumber= DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = '" & ANumber & "'"
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

All code is like this (I dimmed out what wasn't working) When I purposely
type in a duplicate number and tab out of the field I get a Run-time error
'13": Type mismatch. When I run my cursor over it the highlighted line, it
is the (A_Number = CLng(Me.A_Number.Text)) Clng that comes up with a type
mismatch.


Private Sub A_Number_BeforeUpdate(Cancel As Integer)

If ANumber = DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
MsgBox "You have entered a duplicate number."
Cancel = True
Me.A_Number.SelStart = 0
Me.A_Number.SelLength = Len(Me.ANumber)
End If
'In that case, try something like this:

'Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
'Dim Response As Integer
'Dim ANumber As Long
'Dim rs As Object
'
' If Me.NewRecord Then
' A_Number = CLng(Me.A_Number.Text)
' If ANumber = DLookup("ANumber", "tblNewApplicants ", "[ANumber] = " &
ANumber) Then
' MsgBox "Value Exists - Open Existing?", vbYesNo
' If Response = vbYes Then
' Me.A_Number.Undo
' DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
'
' Set rs = Me.Recordset.Clone
' rs.FindFirst "[ANumber] = " & ANumber
' Me.Bookmark = rs.Bookmark
' Else
' Cancel = True
' Me.A_Number.SelStart = 0
' Me.A_Number.SelLength = Len(Me.ANumber)
'End If
'End If
'End If
'
End Sub

Exactly what does your code look like and what is the exact error message?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger sorry, but when it debugs, this is the line it doesn't like. When I
move over it, it shows the ANumber I put in, but it continues to

highlight
it

and say it needs to be deguged. I'm not sure what I'm doing wrong, as one
time it says it is a Type MisMatch but when I saved it and opened a

new
form

it still says it needs to be degugged but returns the number I

entered?
It

isn't worth a lot of time but if you have a moment to look at it I'd
appreciate it.


:

In that case, try something like this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= CLng(Me.ANumber.text)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " &

ANumber)
Then

Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = " & ANumber
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub

Note: I added the IF ME.NEWRECORD bit because this only makes sense

if
it

happens in a new record. Without this, it will happen if you try to change
the value of an existing record, which may have unforeseen results.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger, that's exactly what I thought! I wondered if I could ask another
quick question. Someone else who wrote gave me code saying if it

was
a

duplicate did they want to open the record, and to push yes if so. That's
a
really good idea, but I can't make the code work. Is this

difficult
to

do?


:

Humpf! I made it up from scratch AND created a little database

to
test

it
in about 20 minutes.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Roger, Thank you so much! I was able to modify the code

slightly
and

it
worked! I knew there had to be simple code just couldn't get

it
in my

head
the proper syntax. I had a programmer that does work for us

tell
me

it
would
take several days to write that kind of code.

Again, I really appreciate your help!

:

There are a number of ways to do this, but here's one:

First, let's assume that your table called Table1 and your

field
is

called
ANumber. Let's further assume ANumber is a number (the code would
be
different if it's text and I'll give both). Lastly, let's

say
the

textbox
that is bound to ANumber is called txtANumber.

In the BeforeUpdate event of the txtANumber textbox, add an Event
Procedure
with the following code:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " &
txtANumber)
Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

If ANumber is a text field, the code would vary slightly to this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = '" &
txtANumber &
"'") Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

NOTE: watch for word-wrap the "If" and "Then" need to be on one
line.

Just replace your table, field, and textbox names in the

above
and

you
should be good.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



and, if possible show the record with the duplicate

number. I
have

a
basic
table that has a field called ANumber that restricts duplicates.
However
in
the form, it doesn't tell us it is a duplicate until we

hit
the

ADD
button
at
the bottom of the form after ALL the data has been

entered.
How

can I
tell
it to let the person entering know it is a duplicate as

soon
as

the
number
has been entered?

I am not very proficient at writing code, so please help!

 

[Guest]

Roger, It still didn't like this line: ANumber = Me.A_Number.Text same
error message. If I change it to A_Number then I get a Run-time error
-'21473567 (80020009)': The macro or function set to the BeforeUpdate or
ValidationRule property for thi Field is prevent Microsoft Access from saving
the data in the field.

So, I thought maybe since I had ANumber set to "No Duplicates" that was
creating a problem so I changed it to allow duplicates. That didn't help
either. This is my last effort and then they'll just have to look up the
record themselves. Thanks for all your help.

Sorry. I didn't know that ANumber was text. My code assumed it was
numeric. This should work:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= Me.ANumber.text
If ANumber= DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = '" & ANumber & "'"
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

All code is like this (I dimmed out what wasn't working) When I purposely
type in a duplicate number and tab out of the field I get a Run-time error
'13": Type mismatch. When I run my cursor over it the highlighted line, it
is the (A_Number = CLng(Me.A_Number.Text)) Clng that comes up with a type
mismatch.


Private Sub A_Number_BeforeUpdate(Cancel As Integer)

If ANumber = DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
MsgBox "You have entered a duplicate number."
Cancel = True
Me.A_Number.SelStart = 0
Me.A_Number.SelLength = Len(Me.ANumber)
End If
'In that case, try something like this:

'Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
'Dim Response As Integer
'Dim ANumber As Long
'Dim rs As Object
'
' If Me.NewRecord Then
' A_Number = CLng(Me.A_Number.Text)
' If ANumber = DLookup("ANumber", "tblNewApplicants ", "[ANumber] = " &
ANumber) Then
' MsgBox "Value Exists - Open Existing?", vbYesNo
' If Response = vbYes Then
' Me.A_Number.Undo
' DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
'
' Set rs = Me.Recordset.Clone
' rs.FindFirst "[ANumber] = " & ANumber
' Me.Bookmark = rs.Bookmark
' Else
' Cancel = True
' Me.A_Number.SelStart = 0
' Me.A_Number.SelLength = Len(Me.ANumber)
'End If
'End If
'End If
'
End Sub

Exactly what does your code look like and what is the exact error message?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Roger sorry, but when it debugs, this is the line it doesn't like. When I
move over it, it shows the ANumber I put in, but it continues to highlight
it
and say it needs to be deguged. I'm not sure what I'm doing wrong, as one
time it says it is a Type MisMatch but when I saved it and opened a new
form
it still says it needs to be degugged but returns the number I entered?
It
isn't worth a lot of time but if you have a moment to look at it I'd
appreciate it.


:

In that case, try something like this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= CLng(Me.ANumber.text)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " & ANumber)
Then
Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = " & ANumber
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub

Note: I added the IF ME.NEWRECORD bit because this only makes sense if
it
happens in a new record. Without this, it will happen if you try to
change
the value of an existing record, which may have unforeseen results.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger, that's exactly what I thought! I wondered if I could ask
another
quick question. Someone else who wrote gave me code saying if it was
a
duplicate did they want to open the record, and to push yes if so.
That's
a
really good idea, but I can't make the code work. Is this difficult
to
do?


:

Humpf! I made it up from scratch AND created a little database to
test
it
in about 20 minutes.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Roger, Thank you so much! I was able to modify the code slightly
and
it
worked! I knew there had to be simple code just couldn't get it
in my
head
the proper syntax. I had a programmer that does work for us tell
me
it
would
take several days to write that kind of code.

Again, I really appreciate your help!

:

There are a number of ways to do this, but here's one:

First, let's assume that your table called Table1 and your field
is
called
ANumber. Let's further assume ANumber is a number (the code
would
be
different if it's text and I'll give both). Lastly, let's say
the
textbox
that is bound to ANumber is called txtANumber.

In the BeforeUpdate event of the txtANumber textbox, add an
Event
Procedure
with the following code:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " &
txtANumber)
Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

If ANumber is a text field, the code would vary slightly to
this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = '" &
txtANumber &
"'") Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

NOTE: watch for word-wrap the "If" and "Then" need to be on one
line.

Just replace your table, field, and textbox names in the above
and
you
should be good.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



and, if possible show the record with the duplicate number. I
have
a
basic
table that has a field called ANumber that restricts

 

[David C. Holley]

..Text? what type of control is A_Number and have you tried the .Value
property instead?

Roger, It still didn't like this line: ANumber = Me.A_Number.Text same
error message. If I change it to A_Number then I get a Run-time error
-'21473567 (80020009)': The macro or function set to the BeforeUpdate or
ValidationRule property for thi Field is prevent Microsoft Access from saving
the data in the field.

So, I thought maybe since I had ANumber set to "No Duplicates" that was
creating a problem so I changed it to allow duplicates. That didn't help
either. This is my last effort and then they'll just have to look up the
record themselves. Thanks for all your help.




:

Sorry. I didn't know that ANumber was text. My code assumed it was
numeric. This should work:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= Me.ANumber.text
If ANumber= DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = '" & ANumber & "'"
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

All code is like this (I dimmed out what wasn't working) When I purposely
type in a duplicate number and tab out of the field I get a Run-time error
'13": Type mismatch. When I run my cursor over it the highlighted line,
it

is the (A_Number = CLng(Me.A_Number.Text)) Clng that comes up with a
type

mismatch.


Private Sub A_Number_BeforeUpdate(Cancel As Integer)

If ANumber = DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
MsgBox "You have entered a duplicate number."
Cancel = True
Me.A_Number.SelStart = 0
Me.A_Number.SelLength = Len(Me.ANumber)
End If
'In that case, try something like this:

'Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
'Dim Response As Integer
'Dim ANumber As Long
'Dim rs As Object
'
' If Me.NewRecord Then
' A_Number = CLng(Me.A_Number.Text)
' If ANumber = DLookup("ANumber", "tblNewApplicants ", "[ANumber] = " &
ANumber) Then
' MsgBox "Value Exists - Open Existing?", vbYesNo
' If Response = vbYes Then
' Me.A_Number.Undo
' DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
'
' Set rs = Me.Recordset.Clone
' rs.FindFirst "[ANumber] = " & ANumber
' Me.Bookmark = rs.Bookmark
' Else
' Cancel = True
' Me.A_Number.SelStart = 0
' Me.A_Number.SelLength = Len(Me.ANumber)
'End If
'End If
'End If
'
End Sub


:


Exactly what does your code look like and what is the exact error
message?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger sorry, but when it debugs, this is the line it doesn't like.

When I

move over it, it shows the ANumber I put in, but it continues to
highlight

it

and say it needs to be deguged. I'm not sure what I'm doing wrong, as
one

time it says it is a Type MisMatch but when I saved it and opened a
new

form

it still says it needs to be degugged but returns the number I
entered?

It

isn't worth a lot of time but if you have a moment to look at it I'd
appreciate it.


:


In that case, try something like this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= CLng(Me.ANumber.text)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " &
ANumber)

Then

Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, ,
acMenuVer70

Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = " & ANumber
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub

Note: I added the IF ME.NEWRECORD bit because this only makes sense
if

it

happens in a new record. Without this, it will happen if you try to

change

the value of an existing record, which may have unforeseen results.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




Roger, that's exactly what I thought! I wondered if I could ask

another

quick question. Someone else who wrote gave me code saying if it
was

a

duplicate did they want to open the record, and to push yes if so.

That's

a

really good idea, but I can't make the code work. Is this
difficult

to

do?


:


Humpf! I made it up from scratch AND created a little database
to

test

it

in about 20 minutes.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger, Thank you so much! I was able to modify the code
slightly

and

it

worked! I knew there had to be simple code just couldn't get
it

in my

head

the proper syntax. I had a programmer that does work for us
tell

me

it

would

take several days to write that kind of code.

Again, I really appreciate your help!

:


There are a number of ways to do this, but here's one:

First, let's assume that your table called Table1 and your
field

is

called

ANumber. Let's further assume ANumber is a number (the code

would

be

different if it's text and I'll give both). Lastly, let's
say

the

textbox

that is bound to ANumber is called txtANumber.

In the BeforeUpdate event of the txtANumber textbox, add an

Event

Procedure

with the following code:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " &

txtANumber)

Then

MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

If ANumber is a text field, the code would vary slightly to

this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = '" &

txtANumber &

"'") Then
MsgBox "Value exists. Please enter another."
Cancel = True
Me.txtANumber.SelStart = 0
Me.txtANumber.SelLength = Len(Me.txtANumber)
End If
End Sub

NOTE: watch for word-wrap the "If" and "Then" need to be on
one

line.

Just replace your table, field, and textbox names in the
above

and

you

should be good.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



message


and, if possible show the record with the duplicate

number. I

have

a

basic

table that has a field called ANumber that restricts

 

[Guest]

David,

It is a text field that is not indexed. I tried the Value property and it
did not work either. I have tried everything I know (which isn't much) so
will just use the code that at least returns the message that they have
entered a duplicate.

Thanks for your help.

..Text? what type of control is A_Number and have you tried the .Value
property instead?

Roger, It still didn't like this line: ANumber = Me.A_Number.Text same
error message. If I change it to A_Number then I get a Run-time error
-'21473567 (80020009)': The macro or function set to the BeforeUpdate or
ValidationRule property for thi Field is prevent Microsoft Access from saving
the data in the field.

So, I thought maybe since I had ANumber set to "No Duplicates" that was
creating a problem so I changed it to allow duplicates. That didn't help
either. This is my last effort and then they'll just have to look up the
record themselves. Thanks for all your help.




:

Sorry. I didn't know that ANumber was text. My code assumed it was
numeric. This should work:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= Me.ANumber.text
If ANumber= DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = '" & ANumber & "'"
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




All code is like this (I dimmed out what wasn't working) When I purposely
type in a duplicate number and tab out of the field I get a Run-time error
'13": Type mismatch. When I run my cursor over it the highlighted line,

it

is the (A_Number = CLng(Me.A_Number.Text)) Clng that comes up with a

type

mismatch.


Private Sub A_Number_BeforeUpdate(Cancel As Integer)

If ANumber = DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
MsgBox "You have entered a duplicate number."
Cancel = True
Me.A_Number.SelStart = 0
Me.A_Number.SelLength = Len(Me.ANumber)
End If
'In that case, try something like this:

'Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
'Dim Response As Integer
'Dim ANumber As Long
'Dim rs As Object
'
' If Me.NewRecord Then
' A_Number = CLng(Me.A_Number.Text)
' If ANumber = DLookup("ANumber", "tblNewApplicants ", "[ANumber] = " &
ANumber) Then
' MsgBox "Value Exists - Open Existing?", vbYesNo
' If Response = vbYes Then
' Me.A_Number.Undo
' DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
'
' Set rs = Me.Recordset.Clone
' rs.FindFirst "[ANumber] = " & ANumber
' Me.Bookmark = rs.Bookmark
' Else
' Cancel = True
' Me.A_Number.SelStart = 0
' Me.A_Number.SelLength = Len(Me.ANumber)
'End If
'End If
'End If
'
End Sub


:


Exactly what does your code look like and what is the exact error

message?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger sorry, but when it debugs, this is the line it doesn't like.

When I

move over it, it shows the ANumber I put in, but it continues to

highlight

it

and say it needs to be deguged. I'm not sure what I'm doing wrong, as

one

time it says it is a Type MisMatch but when I saved it and opened a

new

form

it still says it needs to be degugged but returns the number I

entered?

It

isn't worth a lot of time but if you have a moment to look at it I'd
appreciate it.


:


In that case, try something like this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= CLng(Me.ANumber.text)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " &

ANumber)

Then

Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, ,

acMenuVer70

Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = " & ANumber
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub

Note: I added the IF ME.NEWRECORD bit because this only makes sense

if

it

happens in a new record. Without this, it will happen if you try to

change

the value of an existing record, which may have unforeseen results.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




Roger, that's exactly what I thought! I wondered if I could ask

another

quick question. Someone else who wrote gave me code saying if it

was

a

duplicate did they want to open the record, and to push yes if so.

That's

a

really good idea, but I can't make the code work. Is this

difficult

to

do?


:


Humpf! I made it up from scratch AND created a little database

to

test

it

in about 20 minutes.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger, Thank you so much! I was able to modify the code

slightly

and

it

worked! I knew there had to be simple code just couldn't get

it

in my

head

the proper syntax. I had a programmer that does work for us

tell

me

it

 

[David C. Holley]

What value are you trying to put where? Are you trying to take the value
of one CONTROL and put it into another CONTROL? Are you trying to take
the value from a recordset and put it into a CONTROL?

Is ANumber the name of a CONTROL and the name of a field in the
underlying recordset?

Try changing the statement to ANumber= "TEST". If doing so allows the
code to run properly then you've narrowed it down to the Me.ANumber.text
portion of the statement.

David H

David,

It is a text field that is not indexed. I tried the Value property and it
did not work either. I have tried everything I know (which isn't much) so
will just use the code that at least returns the message that they have
entered a duplicate.

Thanks for your help.


:

..Text? what type of control is A_Number and have you tried the .Value
property instead?

Roger, It still didn't like this line: ANumber = Me.A_Number.Text same
error message. If I change it to A_Number then I get a Run-time error
-'21473567 (80020009)': The macro or function set to the BeforeUpdate or
ValidationRule property for thi Field is prevent Microsoft Access from saving
the data in the field.

So, I thought maybe since I had ANumber set to "No Duplicates" that was
creating a problem so I changed it to allow duplicates. That didn't help
either. This is my last effort and then they'll just have to look up the
record themselves. Thanks for all your help.




:



Sorry. I didn't know that ANumber was text. My code assumed it was
numeric. This should work:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= Me.ANumber.text
If ANumber= DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = '" & ANumber & "'"
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L





All code is like this (I dimmed out what wasn't working) When I purposely
type in a duplicate number and tab out of the field I get a Run-time error
'13": Type mismatch. When I run my cursor over it the highlighted line,

it


is the (A_Number = CLng(Me.A_Number.Text)) Clng that comes up with a

type


mismatch.


Private Sub A_Number_BeforeUpdate(Cancel As Integer)

If ANumber = DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
MsgBox "You have entered a duplicate number."
Cancel = True
Me.A_Number.SelStart = 0
Me.A_Number.SelLength = Len(Me.ANumber)
End If
'In that case, try something like this:

'Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
'Dim Response As Integer
'Dim ANumber As Long
'Dim rs As Object
'
' If Me.NewRecord Then
' A_Number = CLng(Me.A_Number.Text)
' If ANumber = DLookup("ANumber", "tblNewApplicants ", "[ANumber] = " &
ANumber) Then
' MsgBox "Value Exists - Open Existing?", vbYesNo
' If Response = vbYes Then
' Me.A_Number.Undo
' DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
'
' Set rs = Me.Recordset.Clone
' rs.FindFirst "[ANumber] = " & ANumber
' Me.Bookmark = rs.Bookmark
' Else
' Cancel = True
' Me.A_Number.SelStart = 0
' Me.A_Number.SelLength = Len(Me.ANumber)
'End If
'End If
'End If
'
End Sub


:



Exactly what does your code look like and what is the exact error

message?


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




Roger sorry, but when it debugs, this is the line it doesn't like.

When I


move over it, it shows the ANumber I put in, but it continues to

highlight


it


and say it needs to be deguged. I'm not sure what I'm doing wrong, as

one


time it says it is a Type MisMatch but when I saved it and opened a

new


form


it still says it needs to be degugged but returns the number I

entered?


It


isn't worth a lot of time but if you have a moment to look at it I'd
appreciate it.


:



In that case, try something like this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= CLng(Me.ANumber.text)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " &

ANumber)


Then


Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, ,

acMenuVer70


Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = " & ANumber
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub

Note: I added the IF ME.NEWRECORD bit because this only makes sense

if


it


happens in a new record. Without this, it will happen if you try to

change


the value of an existing record, which may have unforeseen results.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L





Roger, that's exactly what I thought! I wondered if I could ask

another


quick question. Someone else who wrote gave me code saying if it

was


a


duplicate did they want to open the record, and to push yes if so.

That's


a


really good idea, but I can't make the code work. Is this

difficult


to


do?


:



Humpf! I made it up from scratch AND created a little database

to


test


it


in about 20 minutes.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




Roger, Thank you so much! I was able to modify the code

slightly


and


it


worked! I knew there had to be simple code just couldn't get

it


in my


head


the proper syntax. I had a programmer that does work for us

tell


me


it

 

[David C. Holley]

FYI - By control I was referring to wether or not it was a text field,
comboBox, listbox, etc. appearing on a form.

David,

It is a text field that is not indexed. I tried the Value property and it
did not work either. I have tried everything I know (which isn't much) so
will just use the code that at least returns the message that they have
entered a duplicate.

Thanks for your help.


:

..Text? what type of control is A_Number and have you tried the .Value
property instead?

Roger, It still didn't like this line: ANumber = Me.A_Number.Text same
error message. If I change it to A_Number then I get a Run-time error
-'21473567 (80020009)': The macro or function set to the BeforeUpdate or
ValidationRule property for thi Field is prevent Microsoft Access from saving
the data in the field.

So, I thought maybe since I had ANumber set to "No Duplicates" that was
creating a problem so I changed it to allow duplicates. That didn't help
either. This is my last effort and then they'll just have to look up the
record themselves. Thanks for all your help.




:



Sorry. I didn't know that ANumber was text. My code assumed it was
numeric. This should work:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= Me.ANumber.text
If ANumber= DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = '" & ANumber & "'"
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L





All code is like this (I dimmed out what wasn't working) When I purposely
type in a duplicate number and tab out of the field I get a Run-time error
'13": Type mismatch. When I run my cursor over it the highlighted line,

it


is the (A_Number = CLng(Me.A_Number.Text)) Clng that comes up with a

type


mismatch.


Private Sub A_Number_BeforeUpdate(Cancel As Integer)

If ANumber = DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
MsgBox "You have entered a duplicate number."
Cancel = True
Me.A_Number.SelStart = 0
Me.A_Number.SelLength = Len(Me.ANumber)
End If
'In that case, try something like this:

'Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
'Dim Response As Integer
'Dim ANumber As Long
'Dim rs As Object
'
' If Me.NewRecord Then
' A_Number = CLng(Me.A_Number.Text)
' If ANumber = DLookup("ANumber", "tblNewApplicants ", "[ANumber] = " &
ANumber) Then
' MsgBox "Value Exists - Open Existing?", vbYesNo
' If Response = vbYes Then
' Me.A_Number.Undo
' DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
'
' Set rs = Me.Recordset.Clone
' rs.FindFirst "[ANumber] = " & ANumber
' Me.Bookmark = rs.Bookmark
' Else
' Cancel = True
' Me.A_Number.SelStart = 0
' Me.A_Number.SelLength = Len(Me.ANumber)
'End If
'End If
'End If
'
End Sub


:



Exactly what does your code look like and what is the exact error

message?


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




Roger sorry, but when it debugs, this is the line it doesn't like.

When I


move over it, it shows the ANumber I put in, but it continues to

highlight


it


and say it needs to be deguged. I'm not sure what I'm doing wrong, as

one


time it says it is a Type MisMatch but when I saved it and opened a

new


form


it still says it needs to be degugged but returns the number I

entered?


It


isn't worth a lot of time but if you have a moment to look at it I'd
appreciate it.


:



In that case, try something like this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= CLng(Me.ANumber.text)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " &

ANumber)


Then


Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, ,

acMenuVer70


Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = " & ANumber
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub

Note: I added the IF ME.NEWRECORD bit because this only makes sense

if


it


happens in a new record. Without this, it will happen if you try to

change


the value of an existing record, which may have unforeseen results.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L





Roger, that's exactly what I thought! I wondered if I could ask

another


quick question. Someone else who wrote gave me code saying if it

was


a


duplicate did they want to open the record, and to push yes if so.

That's


a


really good idea, but I can't make the code work. Is this

difficult


to


do?


:



Humpf! I made it up from scratch AND created a little database

to


test


it


in about 20 minutes.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




Roger, Thank you so much! I was able to modify the code

slightly


and


it


worked! I knew there had to be simple code just couldn't get

it


in my


head


the proper syntax. I had a programmer that does work for us

tell


me


it

 

[Guest]

Dave,

ANumber is a text field. What I am trying to do is have a message box that
comes up if they enter a duplicate stating that and then asking if they would
like to open the record (so they see if it is the same record).

The code to check for duplicates and the message work fine. It is the
coding to ask Yes or No do they want to open the record is what isn't
working. I don't know enough about programming to know why it doesn't work.

..Text? what type of control is A_Number and have you tried the .Value
property instead?

Roger, It still didn't like this line: ANumber = Me.A_Number.Text same
error message. If I change it to A_Number then I get a Run-time error
-'21473567 (80020009)': The macro or function set to the BeforeUpdate or
ValidationRule property for thi Field is prevent Microsoft Access from saving
the data in the field.

So, I thought maybe since I had ANumber set to "No Duplicates" that was
creating a problem so I changed it to allow duplicates. That didn't help
either. This is my last effort and then they'll just have to look up the
record themselves. Thanks for all your help.




:

Sorry. I didn't know that ANumber was text. My code assumed it was
numeric. This should work:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= Me.ANumber.text
If ANumber= DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = '" & ANumber & "'"
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




All code is like this (I dimmed out what wasn't working) When I purposely
type in a duplicate number and tab out of the field I get a Run-time error
'13": Type mismatch. When I run my cursor over it the highlighted line,

it

is the (A_Number = CLng(Me.A_Number.Text)) Clng that comes up with a

type

mismatch.


Private Sub A_Number_BeforeUpdate(Cancel As Integer)

If ANumber = DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
MsgBox "You have entered a duplicate number."
Cancel = True
Me.A_Number.SelStart = 0
Me.A_Number.SelLength = Len(Me.ANumber)
End If
'In that case, try something like this:

'Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
'Dim Response As Integer
'Dim ANumber As Long
'Dim rs As Object
'
' If Me.NewRecord Then
' A_Number = CLng(Me.A_Number.Text)
' If ANumber = DLookup("ANumber", "tblNewApplicants ", "[ANumber] = " &
ANumber) Then
' MsgBox "Value Exists - Open Existing?", vbYesNo
' If Response = vbYes Then
' Me.A_Number.Undo
' DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
'
' Set rs = Me.Recordset.Clone
' rs.FindFirst "[ANumber] = " & ANumber
' Me.Bookmark = rs.Bookmark
' Else
' Cancel = True
' Me.A_Number.SelStart = 0
' Me.A_Number.SelLength = Len(Me.ANumber)
'End If
'End If
'End If
'
End Sub


:


Exactly what does your code look like and what is the exact error

message?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger sorry, but when it debugs, this is the line it doesn't like.

When I

move over it, it shows the ANumber I put in, but it continues to

highlight

it

and say it needs to be deguged. I'm not sure what I'm doing wrong, as

one

time it says it is a Type MisMatch but when I saved it and opened a

new

form

it still says it needs to be degugged but returns the number I

entered?

It

isn't worth a lot of time but if you have a moment to look at it I'd
appreciate it.


:


In that case, try something like this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= CLng(Me.ANumber.text)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " &

ANumber)

Then

Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, ,

acMenuVer70

Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = " & ANumber
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub

Note: I added the IF ME.NEWRECORD bit because this only makes sense

if

it

happens in a new record. Without this, it will happen if you try to

change

the value of an existing record, which may have unforeseen results.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




Roger, that's exactly what I thought! I wondered if I could ask

another

quick question. Someone else who wrote gave me code saying if it

was

a

duplicate did they want to open the record, and to push yes if so.

That's

a

really good idea, but I can't make the code work. Is this

difficult

to

do?


:


Humpf! I made it up from scratch AND created a little database

to

test

it

in about 20 minutes.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger, Thank you so much! I was able to modify the code

slightly

and

it

worked! I knew there had to be simple code just couldn't get

it

in my

head

the proper syntax. I had a programmer that does work for us

tell

me

it

 

[Guest]

When I tried "Test" it didn't work at all

..Text? what type of control is A_Number and have you tried the .Value
property instead?

Roger, It still didn't like this line: ANumber = Me.A_Number.Text same
error message. If I change it to A_Number then I get a Run-time error
-'21473567 (80020009)': The macro or function set to the BeforeUpdate or
ValidationRule property for thi Field is prevent Microsoft Access from saving
the data in the field.

So, I thought maybe since I had ANumber set to "No Duplicates" that was
creating a problem so I changed it to allow duplicates. That didn't help
either. This is my last effort and then they'll just have to look up the
record themselves. Thanks for all your help.




:

Sorry. I didn't know that ANumber was text. My code assumed it was
numeric. This should work:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= Me.ANumber.text
If ANumber= DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = '" & ANumber & "'"
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




All code is like this (I dimmed out what wasn't working) When I purposely
type in a duplicate number and tab out of the field I get a Run-time error
'13": Type mismatch. When I run my cursor over it the highlighted line,

it

is the (A_Number = CLng(Me.A_Number.Text)) Clng that comes up with a

type

mismatch.


Private Sub A_Number_BeforeUpdate(Cancel As Integer)

If ANumber = DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
MsgBox "You have entered a duplicate number."
Cancel = True
Me.A_Number.SelStart = 0
Me.A_Number.SelLength = Len(Me.ANumber)
End If
'In that case, try something like this:

'Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
'Dim Response As Integer
'Dim ANumber As Long
'Dim rs As Object
'
' If Me.NewRecord Then
' A_Number = CLng(Me.A_Number.Text)
' If ANumber = DLookup("ANumber", "tblNewApplicants ", "[ANumber] = " &
ANumber) Then
' MsgBox "Value Exists - Open Existing?", vbYesNo
' If Response = vbYes Then
' Me.A_Number.Undo
' DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
'
' Set rs = Me.Recordset.Clone
' rs.FindFirst "[ANumber] = " & ANumber
' Me.Bookmark = rs.Bookmark
' Else
' Cancel = True
' Me.A_Number.SelStart = 0
' Me.A_Number.SelLength = Len(Me.ANumber)
'End If
'End If
'End If
'
End Sub


:


Exactly what does your code look like and what is the exact error

message?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger sorry, but when it debugs, this is the line it doesn't like.

When I

move over it, it shows the ANumber I put in, but it continues to

highlight

it

and say it needs to be deguged. I'm not sure what I'm doing wrong, as

one

time it says it is a Type MisMatch but when I saved it and opened a

new

form

it still says it needs to be degugged but returns the number I

entered?

It

isn't worth a lot of time but if you have a moment to look at it I'd
appreciate it.


:


In that case, try something like this:

Private Sub txtANumber _BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumberAs Long
Dim rs As Object

If Me.NewRecord Then
ANumber= CLng(Me.ANumber.text)
If ANumber= DLookup("ANumber", "Table1 ", "[ANumber] = " &

ANumber)

Then

Response = MsgBox("Value Exists - Open Existing?", vbYesNo)
If Response = vbYes Then
Me.ANumber.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, ,

acMenuVer70

Set rs = Me.Recordset.Clone
rs.FindFirst "[ANumber] = " & ANumber
Me.Bookmark = rs.Bookmark
Else
Cancel = True
Me.AuID.SelStart = 0
Me.AuID.SelLength = Len(Me.ANumber)
End If
End If
End If
End Sub

Note: I added the IF ME.NEWRECORD bit because this only makes sense

if

it

happens in a new record. Without this, it will happen if you try to

change

the value of an existing record, which may have unforeseen results.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




Roger, that's exactly what I thought! I wondered if I could ask

another

quick question. Someone else who wrote gave me code saying if it

was

a

duplicate did they want to open the record, and to push yes if so.

That's

a

really good idea, but I can't make the code work. Is this

difficult

to

do?


:


Humpf! I made it up from scratch AND created a little database

to

test

it

in about 20 minutes.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger, Thank you so much! I was able to modify the code

slightly

and

it

worked! I knew there had to be simple code just couldn't get

it

in my

head

the proper syntax. I had a programmer that does work for us

tell

me

it