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

David C. Holley · Jan 25, 2006

But of course it fails, because I failed to notice that the variable was
DIM'd as LONG. My bad.

In that case, and if you haven't already, change Dim ANumber As Long to
Dim ANumber As String.

Also the statement, [ANumber= Me.ANumber.text] is not neccessary since
you can make the DLookup() use Me.ANumber in the WHERE statement. Note
that its not neccessary to specify the .TEXT property since Access will
automatically grab the value in a control when you reference the control.

If removing that statement doesn't fix the problem, I'm at a bit of a
loss, based on your comments. I can see how the problem *might* be
related to the .Undo statement and the DoCmd that follows it. But your
comment suggests that VBA is breaking at the ANumber=Me.ANumber.text
statement. At any rate try the following...

1) Comment out those lines and see if works
2) If it doesn't work, then the next step that I would do is to rename
the SUB to something generic (mySub) and then execute the code via a
temporary command button on the form. This will isolate your code from
the events and determine whether or not the issue is with the code or if
the issue is with how the code runs in conjuction with the events. I've
had a couple of instances where I've created a conflict for Access and
have to figure out where and what the conflict was.

David H

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

Click to expand...

Click to expand...

- Me.FilterOn = False
- Me.Filter = "lngTransportId = " & Me.txtGotoRecord
- Me.FilterOn = True

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

Click to expand...

Click to expand...

Roger Carlson · Jan 25, 2006

I'm sorry you're having such trouble with this. It's probably something
simple. If you email me directly, (see the address on the website below),
I'll send you a small sample that I built to test the code. Perhaps if you
compared this to your actual system, you can see where the problem lies.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com

Catlady said:
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.

Roger Carlson said:

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

Catlady said:

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

Click to expand...

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,

Click to expand...

as
one

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

Click to expand...

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

Click to expand...

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

Click to expand...

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

Click to expand...

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

Click to expand...

get
it

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

Click to expand...

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

Click to expand...

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,

Click to expand...

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

Click to expand...

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

Click to expand...

Click to expand...

Guest · Jan 25, 2006

Dave,

This is the code I have and it seems to be working until I hit "yes" I want
to open the duplicate file. Then I get a run-time error '-2147352567
(80020009)': that says: The macro or function set to the BeforeUpdate or
ValidationRule property for this field is preventing Microsoft Access from
saving the data in the field.

So I went to my field ANumber and changed it to not indexed (it was indexed
allow no duplicates). Still didn't work. Then I tried "dimming out" the
"undo" portion. That didn't work at all.
Then when I ran it it comes back and says Run-time error '3021': No current
record (and there is definitely a record). Does it have something to do with
the "undo" command? I tried dimming that out and it didn't work either.

Now the debugger is highlighting : Me.Bookmark = rs.Bookmark and saying
there is no record. Any idea what gives? (I don't know if you can tell but
the duplicate line with a variation is dimmed out. Thought that might make a
difference but it didn't).

Private Sub A_Number_BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumber As String
Dim rs As Object

If Me.NewRecord Then
ANumber = Me.A_Number.Value
If ANumber = DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
'If ANumber = DLookup("ANumber", "tblNewApplicants ", "[ANumber] = " &
ANumber) Then
Response = 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

David C. Holley said:
..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

Click to expand...

Click to expand...

David C. Holley · Jan 26, 2006

See my comments inline

Dave,

This is the code I have and it seems to be working until I hit "yes" I want
to open the duplicate file. Then I get a run-time error '-2147352567
(80020009)': that says: The macro or function set to the BeforeUpdate or
ValidationRule property for this field is preventing Microsoft Access from
saving the data in the field.

So I went to my field ANumber and changed it to not indexed (it was indexed
allow no duplicates).

Changing the INDEX on the field at the table level isn't the issue.

Still didn't work. Then I tried "dimming out" the

"undo" portion. That didn't work at all.
Then when I ran it it comes back and says Run-time error '3021': No current
record (and there is definitely a record). Does it have something to do with
the "undo" command? I tried dimming that out and it didn't work either.

The issue is that something was going on that was creating a conflict
for Access, which I believe is the UNDO statements. If I understand
things correctly, I suspect that you're issuing a UNDO command was
confusing Access since that command was withing a BeforeUpdate Event.
Basically you're telling Access something to the effect of - "save this
information, no don't save it" and Access is confused.

Now the debugger is highlighting : Me.Bookmark = rs.Bookmark and saying
there is no record. Any idea what gives? (I don't know if you can tell but
the duplicate line with a variation is dimmed out. Thought that might make a
difference but it didn't).

Given the problems I would try replacing the code in that portion of the
If...Then with the following which uses the Filter property to find the
information. I've never really used the .FindFirst and .Bookmark
property so I'm not the expert on what's going on. One result of this is
that you'll end up displaying all of the records that match the criteria
if there are more than one, however if the table field is Indexed(No
Duplicates) it will only return the 1 record that exists.

Me.FilterOn = False
Me.Filter = "[ANumber] = '" & ANumber & "'"
Me.FilterOn = True

You *might* be able to go so far as to use the above three lines in the
following manner...

Private Sub A_Number_AfterUpdate()
ANumber = [Form Control w/value for ANumber]
Me.FilterOn = False
Me.Filter = "[ANumber] = '" & ANumber & "'"
Me.FilterOn = True
If Me.NewRecord = False then Msgbox("One or more record(s) exist
with that value. All records found are currently available for review.")
Else
[Form Control that should grab the value] = ANumber
End if
End Sub

I developed the original three lines of code to allow me to jump around
quickly on a form using the record ID. I tested the If...Then using that
form and found that the If...Then works properly if no record is found.
I added the ANumber = and its companion statement to ensure that the
value entered is captured if no record is found since the value would
otherwise be lost. Note also that I moved the code to the _AfterUpdate
event.

David H

Private Sub A_Number_BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumber As String
Dim rs As Object

If Me.NewRecord Then
ANumber = Me.A_Number.Value
If ANumber = DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
'If ANumber = DLookup("ANumber", "tblNewApplicants ", "[ANumber] = " &
ANumber) Then
Response = 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

:

..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

Click to expand...

Click to expand...

Guest · Jan 26, 2006

David, Thank you so much! It worked the first time. I appreciate your help
very, very much.

David C. Holley said:
See my comments inline

Dave,

This is the code I have and it seems to be working until I hit "yes" I want
to open the duplicate file. Then I get a run-time error '-2147352567
(80020009)': that says: The macro or function set to the BeforeUpdate or
ValidationRule property for this field is preventing Microsoft Access from
saving the data in the field.

So I went to my field ANumber and changed it to not indexed (it was indexed
allow no duplicates).

Click to expand...

Changing the INDEX on the field at the table level isn't the issue.

Still didn't work. Then I tried "dimming out" the

"undo" portion. That didn't work at all.
Then when I ran it it comes back and says Run-time error '3021': No current
record (and there is definitely a record). Does it have something to do with
the "undo" command? I tried dimming that out and it didn't work either.

Click to expand...

The issue is that something was going on that was creating a conflict
for Access, which I believe is the UNDO statements. If I understand
things correctly, I suspect that you're issuing a UNDO command was
confusing Access since that command was withing a BeforeUpdate Event.
Basically you're telling Access something to the effect of - "save this
information, no don't save it" and Access is confused.

Now the debugger is highlighting : Me.Bookmark = rs.Bookmark and saying
there is no record. Any idea what gives? (I don't know if you can tell but
the duplicate line with a variation is dimmed out. Thought that might make a
difference but it didn't).

Click to expand...

Given the problems I would try replacing the code in that portion of the
If...Then with the following which uses the Filter property to find the
information. I've never really used the .FindFirst and .Bookmark
property so I'm not the expert on what's going on. One result of this is
that you'll end up displaying all of the records that match the criteria
if there are more than one, however if the table field is Indexed(No
Duplicates) it will only return the 1 record that exists.

Me.FilterOn = False
Me.Filter = "[ANumber] = '" & ANumber & "'"
Me.FilterOn = True

You *might* be able to go so far as to use the above three lines in the
following manner...

Private Sub A_Number_AfterUpdate()
ANumber = [Form Control w/value for ANumber]
Me.FilterOn = False
Me.Filter = "[ANumber] = '" & ANumber & "'"
Me.FilterOn = True
If Me.NewRecord = False then Msgbox("One or more record(s) exist
with that value. All records found are currently available for review.")
Else
[Form Control that should grab the value] = ANumber
End if
End Sub

I developed the original three lines of code to allow me to jump around
quickly on a form using the record ID. I tested the If...Then using that
form and found that the If...Then works properly if no record is found.
I added the ANumber = and its companion statement to ensure that the
value entered is captured if no record is found since the value would
otherwise be lost. Note also that I moved the code to the _AfterUpdate
event.

David H

Private Sub A_Number_BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumber As String
Dim rs As Object

If Me.NewRecord Then
ANumber = Me.A_Number.Value
If ANumber = DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
'If ANumber = DLookup("ANumber", "tblNewApplicants ", "[ANumber] = " &
ANumber) Then
Response = 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

:

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

Catlady wrote:

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)

Click to expand...

Click to expand...

David C. Holley · Jan 26, 2006

Just goes to show that in programming there's more than 1 way to skin a cat.

*HA!* I crack myself up

David, Thank you so much! It worked the first time. I appreciate your help
very, very much.

:

See my comments inline

Dave,

This is the code I have and it seems to be working until I hit "yes" I want
to open the duplicate file. Then I get a run-time error '-2147352567
(80020009)': that says: The macro or function set to the BeforeUpdate or
ValidationRule property for this field is preventing Microsoft Access from
saving the data in the field.

So I went to my field ANumber and changed it to not indexed (it was indexed
allow no duplicates).

Click to expand...

Changing the INDEX on the field at the table level isn't the issue.

Still didn't work. Then I tried "dimming out" the

"undo" portion. That didn't work at all.
Then when I ran it it comes back and says Run-time error '3021': No current
record (and there is definitely a record). Does it have something to do with
the "undo" command? I tried dimming that out and it didn't work either.

Click to expand...

The issue is that something was going on that was creating a conflict
for Access, which I believe is the UNDO statements. If I understand
things correctly, I suspect that you're issuing a UNDO command was
confusing Access since that command was withing a BeforeUpdate Event.
Basically you're telling Access something to the effect of - "save this
information, no don't save it" and Access is confused.

Now the debugger is highlighting : Me.Bookmark = rs.Bookmark and saying
there is no record. Any idea what gives? (I don't know if you can tell but
the duplicate line with a variation is dimmed out. Thought that might make a
difference but it didn't).

Click to expand...

Given the problems I would try replacing the code in that portion of the
If...Then with the following which uses the Filter property to find the
information. I've never really used the .FindFirst and .Bookmark
property so I'm not the expert on what's going on. One result of this is
that you'll end up displaying all of the records that match the criteria
if there are more than one, however if the table field is Indexed(No
Duplicates) it will only return the 1 record that exists.

Me.FilterOn = False
Me.Filter = "[ANumber] = '" & ANumber & "'"
Me.FilterOn = True

You *might* be able to go so far as to use the above three lines in the
following manner...

Private Sub A_Number_AfterUpdate()
ANumber = [Form Control w/value for ANumber]
Me.FilterOn = False
Me.Filter = "[ANumber] = '" & ANumber & "'"
Me.FilterOn = True
If Me.NewRecord = False then Msgbox("One or more record(s) exist
with that value. All records found are currently available for review.")
Else
[Form Control that should grab the value] = ANumber
End if
End Sub

I developed the original three lines of code to allow me to jump around
quickly on a form using the record ID. I tested the If...Then using that
form and found that the If...Then works properly if no record is found.
I added the ANumber = and its companion statement to ensure that the
value entered is captured if no record is found since the value would
otherwise be lost. Note also that I moved the code to the _AfterUpdate
event.

David H

Private Sub A_Number_BeforeUpdate(Cancel As Integer)
Dim Response As Integer
Dim ANumber As String
Dim rs As Object

If Me.NewRecord Then
ANumber = Me.A_Number.Value
If ANumber = DLookup("ANumber", "tblNewApplicants", "[ANumber] = '" &
ANumber & "'") Then
'If ANumber = DLookup("ANumber", "tblNewApplicants ", "[ANumber] = " &
ANumber) Then
Response = 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

:

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

Catlady wrote:

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)

Click to expand...

Click to expand...

Guest · Jan 26, 2006

Roger, I copied the event procedure you provided to the original question
into my A2003 Form. The procedure does alert me to a duplicate IDNumber as
soon as I Tab into the next cell. However, if I enter an IDNumber that does
NOT exist, I get a validation rule error: "Value in Field or Record violates
validation rule for record or field." My IDNumber field is a number (Long
Integer). Any ideas? Anyone? Thanks.

Roger Carlson said:
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

Catlady said:

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!

Click to expand...

Roger Carlson · Jan 26, 2006

Questions:
1) is it an autonumber field?
2) are there any validation rules on the field?
3) are you entering a valid value?

--
--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

Ranch Hand said:
Roger, I copied the event procedure you provided to the original question
into my A2003 Form. The procedure does alert me to a duplicate IDNumber as
soon as I Tab into the next cell. However, if I enter an IDNumber that does
NOT exist, I get a validation rule error: "Value in Field or Record violates
validation rule for record or field." My IDNumber field is a number (Long
Integer). Any ideas? Anyone? Thanks.

Roger Carlson said:

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

Catlady said:

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

Click to expand...

However
in

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

Click to expand...

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!

Click to expand...

Click to expand...

Guest · Jan 27, 2006

Roger,
Is not autonumber; Is the primary field; no validation rules; am entering
valid value.

Roger Carlson said:
Questions:
1) is it an autonumber field?
2) are there any validation rules on the field?
3) are you entering a valid value?

--
--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

Ranch Hand said:

Roger, I copied the event procedure you provided to the original question
into my A2003 Form. The procedure does alert me to a duplicate IDNumber as
soon as I Tab into the next cell. However, if I enter an IDNumber that does
NOT exist, I get a validation rule error: "Value in Field or Record violates
validation rule for record or field." My IDNumber field is a number (Long
Integer). Any ideas? Anyone? Thanks.

Roger Carlson said:

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!

Click to expand...

Click to expand...

David C. Holley · Jan 27, 2006

I would presume that 'Is the primary field' the key. If by that you mean
that it is the key field, then field is INDEXED(No Duplicates) which is
probably where the issue lies.

Ranch said:
Roger,
Is not autonumber; Is the primary field; no validation rules; am entering
valid value.

:

Questions:
1) is it an autonumber field?
2) are there any validation rules on the field?
3) are you entering a valid value?

--
--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, I copied the event procedure you provided to the original question
into my A2003 Form. The procedure does alert me to a duplicate IDNumber
as

soon as I Tab into the next cell. However, if I enter an IDNumber that
does

NOT exist, I get a validation rule error: "Value in Field or Record
violates

validation rule for record or field." My IDNumber field is a number (Long
Integer). Any ideas? Anyone? Thanks.

:

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!

Click to expand...

Click to expand...

Guest · Jan 27, 2006

Yes, field is Indexed and I do get the appropriate message that "that number
already exists ... " but following that, I get the "violates validation"
message.

David C. Holley said:
I would presume that 'Is the primary field' the key. If by that you mean
that it is the key field, then field is INDEXED(No Duplicates) which is
probably where the issue lies.

Ranch said:

Roger,
Is not autonumber; Is the primary field; no validation rules; am entering
valid value.

:

Questions:
1) is it an autonumber field?
2) are there any validation rules on the field?
3) are you entering a valid value?

--
--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, I copied the event procedure you provided to the original question
into my A2003 Form. The procedure does alert me to a duplicate IDNumber

as

soon as I Tab into the next cell. However, if I enter an IDNumber that

does

NOT exist, I get a validation rule error: "Value in Field or Record

violates

validation rule for record or field." My IDNumber field is a number (Long
Integer). Any ideas? Anyone? Thanks.

:

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!

Click to expand...

Click to expand...

Roger Carlson · Jan 27, 2006

That's odd. Can you list your actual code?

--
--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

Ranch Hand said:
Roger,
Is not autonumber; Is the primary field; no validation rules; am entering
valid value.

Roger Carlson said:

Questions:
1) is it an autonumber field?
2) are there any validation rules on the field?
3) are you entering a valid value?

--
--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

Ranch Hand said:

Roger, I copied the event procedure you provided to the original question
into my A2003 Form. The procedure does alert me to a duplicate

Click to expand...

IDNumber
as

soon as I Tab into the next cell. However, if I enter an IDNumber

Click to expand...

that
does

NOT exist, I get a validation rule error: "Value in Field or Record violates
validation rule for record or field." My IDNumber field is a number (Long
Integer). Any ideas? Anyone? Thanks.

:

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] = " &

Click to expand...

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

Click to expand...

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

Click to expand...

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

Click to expand...

the
number

has been entered?

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

Click to expand...

Click to expand...

Guest · Jan 27, 2006

Private Sub IDNumber_BeforeUpdate(Cancel As Integer)
If IDNumber = DLookup ("IDNumber" , "My Database", "[IDNumber] = "IDNumber)
Then MsgBox "That number already exists."

Cancel = True

Me.IDNumber.SelStart = 0

Me.IDNumber.SelLength = Len(Me.IDNumber)

End If

End Sub

The IDNumber is the key field; no duplicates; also, I have a second number
that we look at, call it IDNumberB, which is indexed to the IDNumber; The
IDNumberB can be empty but no duplicates.

Roger Carlson said:
That's odd. Can you list your actual code?

--
--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

Ranch Hand said:

Roger,
Is not autonumber; Is the primary field; no validation rules; am entering
valid value.

Roger Carlson said:

Questions:
1) is it an autonumber field?
2) are there any validation rules on the field?
3) are you entering a valid value?

--
--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, I copied the event procedure you provided to the original question
into my A2003 Form. The procedure does alert me to a duplicate IDNumber
as
soon as I Tab into the next cell. However, if I enter an IDNumber that
does
NOT exist, I get a validation rule error: "Value in Field or Record
violates
validation rule for record or field." My IDNumber field is a number (Long
Integer). Any ideas? Anyone? Thanks.

:

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!

Click to expand...

Click to expand...

Roger Carlson · Jan 27, 2006

Actually, I don't see how this runs at all. Did you copy and paste it or
re-type it? I suspect the latter, because as it stands, it would give you a
compile error. I'll assume it really looks like this:

Private Sub IDNumber_BeforeUpdate(Cancel As Integer)
If IDNumber = DLookup("IDNumber", "My Database", "[IDNumber] = " & IDNumber)
Then
MsgBox "That number already exists."
Cancel = True
Me.IDNumber.SelStart = 0
Me.IDNumber.SelLength = Len(Me.IDNumber)
End If
End Sub

This should work perfectly. I pasted it into a form bound to a table with a
primary key of long int and it worked fine. I suspect it has to do with the
second number IDNumber2. I don't know how you are looking at that.

--
--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

Ranch Hand said:
Private Sub IDNumber_BeforeUpdate(Cancel As Integer)
If IDNumber = DLookup ("IDNumber" , "My Database", "[IDNumber] = "IDNumber)
Then MsgBox "That number already exists."

Cancel = True

Me.IDNumber.SelStart = 0

Me.IDNumber.SelLength = Len(Me.IDNumber)

End If

End Sub

The IDNumber is the key field; no duplicates; also, I have a second number
that we look at, call it IDNumberB, which is indexed to the IDNumber; The
IDNumberB can be empty but no duplicates.

Roger Carlson said:

That's odd. Can you list your actual code?

--
--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

Ranch Hand said:

Roger,
Is not autonumber; Is the primary field; no validation rules; am entering
valid value.

:

Questions:
1) is it an autonumber field?
2) are there any validation rules on the field?
3) are you entering a valid value?

--
--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, I copied the event procedure you provided to the original question
into my A2003 Form. The procedure does alert me to a duplicate IDNumber
as
soon as I Tab into the next cell. However, if I enter an IDNumber that
does
NOT exist, I get a validation rule error: "Value in Field or Record
violates
validation rule for record or field." My IDNumber field is a

Click to expand...

number
(Long

Integer). Any ideas? Anyone? Thanks.

:

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

Click to expand...

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

Click to expand...

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

Click to expand...

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

Click to expand...

the
ADD

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

Click to expand...

How
can I

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

Click to expand...

as
the

number
has been entered?

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

Click to expand...

Click to expand...

Guest · Jan 27, 2006

Roger, I retyped it. However, this time I copied your code and pasted it;
after a couple of adjustments, it works fine with one exception.

My table is set up with SSN's: IDNumber is the client's SSN; IDNumber2 is
the spouse's SSN. I had the two of them indexed per the MSN instructions to
"prevent duplicate values from being entered into a combination of fields."
My objective was to catch a duplication if the spouse's SSN was entered in
the client's control. I had to remove that index before the program would
run.

After pasting the code this time, --with the indexing removed-- the program
will now run if I enter a non-dupl; previously, it wouldn't go past the first
IDNumber whether it was a dupl or not. However, if I enter a dupl in the
IDNumber control, I get the error message I constructed --which I want-- but
also get the Access msg "...validates validation rule for record or field..."
If I can't avoid that second msg, I can live with it but it makes me think
I'm still doing something wrong.

If I enter a dupl in IDNumber2, I get my msg "tt number alrdy exists" but I
don't get the validation error. Both IDNumber and IDNumber2 are formatted in
the table as Long Integer and both are "no duplicates." IDNumber is the
primary (key) field.

I'm miles ahead of where I was at this time yesterday. Thanks for your
help.

Roger Carlson said:
Actually, I don't see how this runs at all. Did you copy and paste it or
re-type it? I suspect the latter, because as it stands, it would give you a
compile error. I'll assume it really looks like this:

Private Sub IDNumber_BeforeUpdate(Cancel As Integer)
If IDNumber = DLookup("IDNumber", "My Database", "[IDNumber] = " & IDNumber)
Then
MsgBox "That number already exists."
Cancel = True
Me.IDNumber.SelStart = 0
Me.IDNumber.SelLength = Len(Me.IDNumber)
End If
End Sub

This should work perfectly. I pasted it into a form bound to a table with a
primary key of long int and it worked fine. I suspect it has to do with the
second number IDNumber2. I don't know how you are looking at that.

--
--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

Ranch Hand said:

Private Sub IDNumber_BeforeUpdate(Cancel As Integer)
If IDNumber = DLookup ("IDNumber" , "My Database", "[IDNumber] = "IDNumber)
Then MsgBox "That number already exists."

Cancel = True

Me.IDNumber.SelStart = 0

Me.IDNumber.SelLength = Len(Me.IDNumber)

End If

End Sub

The IDNumber is the key field; no duplicates; also, I have a second number
that we look at, call it IDNumberB, which is indexed to the IDNumber; The
IDNumberB can be empty but no duplicates.

Roger Carlson said:

That's odd. Can you list your actual code?

--
--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,
Is not autonumber; Is the primary field; no validation rules; am entering
valid value.

:

Questions:
1) is it an autonumber field?
2) are there any validation rules on the field?
3) are you entering a valid value?

--
--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, I copied the event procedure you provided to the original
question
into my A2003 Form. The procedure does alert me to a duplicate
IDNumber
as
soon as I Tab into the next cell. However, if I enter an IDNumber
that
does
NOT exist, I get a validation rule error: "Value in Field or Record
violates
validation rule for record or field." My IDNumber field is a number
(Long
Integer). Any ideas? Anyone? Thanks.

:

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!

Click to expand...

Click to expand...

Roger Carlson · Jan 30, 2006

You need to have similar code in the BeforeUpdate of each textbox. Except,
of course it will differ by referencing the appropriate field and control
for that event.

Do you?

--
--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

Ranch Hand said:
Roger, I retyped it. However, this time I copied your code and pasted it;
after a couple of adjustments, it works fine with one exception.

My table is set up with SSN's: IDNumber is the client's SSN; IDNumber2 is
the spouse's SSN. I had the two of them indexed per the MSN instructions to
"prevent duplicate values from being entered into a combination of fields."
My objective was to catch a duplication if the spouse's SSN was entered in
the client's control. I had to remove that index before the program would
run.

After pasting the code this time, --with the indexing removed-- the program
will now run if I enter a non-dupl; previously, it wouldn't go past the first
IDNumber whether it was a dupl or not. However, if I enter a dupl in the
IDNumber control, I get the error message I constructed --which I want-- but
also get the Access msg "...validates validation rule for record or field..."
If I can't avoid that second msg, I can live with it but it makes me think
I'm still doing something wrong.

If I enter a dupl in IDNumber2, I get my msg "tt number alrdy exists" but I
don't get the validation error. Both IDNumber and IDNumber2 are formatted in
the table as Long Integer and both are "no duplicates." IDNumber is the
primary (key) field.

I'm miles ahead of where I was at this time yesterday. Thanks for your
help.

Roger Carlson said:

Actually, I don't see how this runs at all. Did you copy and paste it or
re-type it? I suspect the latter, because as it stands, it would give you a
compile error. I'll assume it really looks like this:

Private Sub IDNumber_BeforeUpdate(Cancel As Integer)
If IDNumber = DLookup("IDNumber", "My Database", "[IDNumber] = " & IDNumber)
Then
MsgBox "That number already exists."
Cancel = True
Me.IDNumber.SelStart = 0
Me.IDNumber.SelLength = Len(Me.IDNumber)
End If
End Sub

This should work perfectly. I pasted it into a form bound to a table with a
primary key of long int and it worked fine. I suspect it has to do with the
second number IDNumber2. I don't know how you are looking at that.

--
--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

Ranch Hand said:

Private Sub IDNumber_BeforeUpdate(Cancel As Integer)
If IDNumber = DLookup ("IDNumber" , "My Database", "[IDNumber] = "IDNumber)
Then MsgBox "That number already exists."

Cancel = True

Me.IDNumber.SelStart = 0

Me.IDNumber.SelLength = Len(Me.IDNumber)

End If

End Sub

The IDNumber is the key field; no duplicates; also, I have a second number
that we look at, call it IDNumberB, which is indexed to the IDNumber; The
IDNumberB can be empty but no duplicates.

:

That's odd. Can you list your actual code?

--
--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,
Is not autonumber; Is the primary field; no validation rules; am entering
valid value.

:

Questions:
1) is it an autonumber field?
2) are there any validation rules on the field?
3) are you entering a valid value?

--
--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, I copied the event procedure you provided to the original
question
into my A2003 Form. The procedure does alert me to a duplicate
IDNumber
as
soon as I Tab into the next cell. However, if I enter an IDNumber
that
does
NOT exist, I get a validation rule error: "Value in Field or Record
violates
validation rule for record or field." My IDNumber field is a number
(Long
Integer). Any ideas? Anyone? Thanks.

:

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

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

Click to expand...

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

Click to expand...

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

Click to expand...

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

Click to expand...

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

Click to expand...

hit
the

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

Click to expand...

entered.
How

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

Click to expand...

soon
as

the
number
has been entered?

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

Click to expand...

Click to expand...

Guest · Jan 31, 2006

Roger, I do now. Thanks for ALL of your help.

Roger Carlson said:
You need to have similar code in the BeforeUpdate of each textbox. Except,
of course it will differ by referencing the appropriate field and control
for that event.

Do you?

--
--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

Ranch Hand said:

Roger, I retyped it. However, this time I copied your code and pasted it;
after a couple of adjustments, it works fine with one exception.

My table is set up with SSN's: IDNumber is the client's SSN; IDNumber2 is
the spouse's SSN. I had the two of them indexed per the MSN instructions to
"prevent duplicate values from being entered into a combination of fields."
My objective was to catch a duplication if the spouse's SSN was entered in
the client's control. I had to remove that index before the program would
run.

After pasting the code this time, --with the indexing removed-- the program
will now run if I enter a non-dupl; previously, it wouldn't go past the first
IDNumber whether it was a dupl or not. However, if I enter a dupl in the
IDNumber control, I get the error message I constructed --which I want-- but
also get the Access msg "...validates validation rule for record or field..."
If I can't avoid that second msg, I can live with it but it makes me think
I'm still doing something wrong.

If I enter a dupl in IDNumber2, I get my msg "tt number alrdy exists" but I
don't get the validation error. Both IDNumber and IDNumber2 are formatted in
the table as Long Integer and both are "no duplicates." IDNumber is the
primary (key) field.

I'm miles ahead of where I was at this time yesterday. Thanks for your
help.

Roger Carlson said:

Actually, I don't see how this runs at all. Did you copy and paste it or
re-type it? I suspect the latter, because as it stands, it would give you a
compile error. I'll assume it really looks like this:

Private Sub IDNumber_BeforeUpdate(Cancel As Integer)
If IDNumber = DLookup("IDNumber", "My Database", "[IDNumber] = " & IDNumber)
Then
MsgBox "That number already exists."
Cancel = True
Me.IDNumber.SelStart = 0
Me.IDNumber.SelLength = Len(Me.IDNumber)
End If
End Sub

This should work perfectly. I pasted it into a form bound to a table with a
primary key of long int and it worked fine. I suspect it has to do with the
second number IDNumber2. I don't know how you are looking at that.

--
--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

Private Sub IDNumber_BeforeUpdate(Cancel As Integer)
If IDNumber = DLookup ("IDNumber" , "My Database", "[IDNumber] =
"IDNumber)
Then MsgBox "That number already exists."

Cancel = True

Me.IDNumber.SelStart = 0

Me.IDNumber.SelLength = Len(Me.IDNumber)

End If

End Sub

The IDNumber is the key field; no duplicates; also, I have a second number
that we look at, call it IDNumberB, which is indexed to the IDNumber; The
IDNumberB can be empty but no duplicates.

:

That's odd. Can you list your actual code?

--
--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,
Is not autonumber; Is the primary field; no validation rules; am
entering
valid value.

:

Questions:
1) is it an autonumber field?
2) are there any validation rules on the field?
3) are you entering a valid value?

--
--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, I copied the event procedure you provided to the original
question
into my A2003 Form. The procedure does alert me to a duplicate
IDNumber
as
soon as I Tab into the next cell. However, if I enter an IDNumber
that
does
NOT exist, I get a validation rule error: "Value in Field or
Record
violates
validation rule for record or field." My IDNumber field is a
number
(Long
Integer). Any ideas? Anyone? Thanks.

:

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!

Click to expand...

Click to expand...

Guest · Aug 29, 2006

This information is exactly what I have been searhing for/trying to figure
out for the last couple weeks. I tried the code as you wrote it and the
problem I am having is VB keeps putting in extra spaces. I inherited this
database, so the field names are not of my choosing. The field that I am
trying to get this to work on is Invoice/Contract#. When I try the following
code, VB puts spaces on either side of the slash and thus the code won't
work. Any ideas on how to fix this other than renaming the field (which I am
trying to avoid doing)?

Private Sub Short_Summary_BeforeUpdate(Cancel As Integer)
If Invoice/Contract# = DLookup("Invoice/Contract#", "Contractual_table",
"[Invoice/Contract#] = '" & Short_Summary & "'") Then
MsgBox "That contract number is already in use."
Cancel = True
Me.Short_Summary.SelStart = 0
Me.Short_Summary.SelLength = Len(Me.Short_Summary)
End If
End Sub

John Spencer · Sep 1, 2006

What is Invoice/Contract# in this line? I would guess it is supposed to be
referencing a control on a form.
If Invoice/Contract# = DLookup("...)

VBA will think you are trying to divide two variables Invoice divide by
Contract#.
I think you might want to try
If Me.[Invoice/Contract#] = DLookup("...)

Or better yet, try changing the name of the control to txtInvoiceContract
so you aren't using special characters and spaces.

Duplicate Not Allowed, clear field	12	Jun 26, 2009
Looping and Duplicating Records Access 2007	6	Sep 6, 2009
duplicate fields	20	Apr 1, 2005
Access Access DLookup Function – more than 1 criteria	0	May 18, 2017
Duplicate Records That Contain Composite PK	4	Sep 17, 2008
Add records to to 2 tables	1	Jan 29, 2009
Duplicate record checking	1	Dec 11, 2008
How do I prevent entry of duplicate student names	1	Aug 25, 2007

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

David C. Holley

Roger Carlson

Guest

David C. Holley

Guest

David C. Holley

Guest

Roger Carlson

Guest

David C. Holley

Guest

Roger Carlson

Guest

Roger Carlson

Guest

Roger Carlson

Guest

Guest

John Spencer

Ask a Question

Similar Threads