How to trap duplicate key

[Guest]

I have tried several things to try to provide the user with a custom error
message when they enter a duplicate key into a key field when creating a new
record. Access's own error message always pops up first, which I don't want.
I tried the following code in several different control or form events to no
avail:

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[FundNo] = " & Str(Me![FundNo])
If rs.NoMatch Then
' Do nothing
Else
MsgBox "Fund of this number already exists ", vbOKOnly +
vbExclamation, "Enter different fund number . . ."
Cancel = True: Exit Sub
End If

Perhaps this code isn't going to work. I've also tried an error handling
routine, but Access always catches the problem first and displays its own
message. The error handler isn't being tripped.

ctdak

 

[John W. Vinson]

I have tried several things to try to provide the user with a custom error
message when they enter a duplicate key into a key field when creating a new
record. Access's own error message always pops up first, which I don't want.
I tried the following code in several different control or form events to no
avail:

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[FundNo] = " & Str(Me![FundNo])
If rs.NoMatch Then
' Do nothing
Else
MsgBox "Fund of this number already exists ", vbOKOnly +
vbExclamation, "Enter different fund number . . ."
Cancel = True: Exit Sub
End If

Perhaps this code isn't going to work. I've also tried an error handling
routine, but Access always catches the problem first and displays its own
message. The error handler isn't being tripped.

ctdak

Where are you putting the code? If it is in the Form's BeforeUpdate event it
should work. The only caveat is the odd use of Str(); if FundNo is a Text
datatype field, you should be able to just use

rs.FindFirst "[FundNo] = '" & Me![FundNo] & "'"

delimiting the search term with ' marks.

I'd also lose the colon single-line concatenation and the unneeded Exit Sub:

Else
MsgBox "yadda yadda...
Cancel = True
End If

John W. Vinson [MVP]

 

[BSK]

I don't know what kind of error you are getting but you can also try
simply turning off the access warnings. just type in
'docmd.setwarnings false' before the event and 'docmd.setwarnings
true' after the event. this may or may not work depending on what
type of warning it is. It also may be just as easy to put the code in
the beforeupdate event as john said.

 

[Guest]

John,

I'm putting this code in the form's BeforeUpdate event. I tried your string
construct, but it doesn't work either. The problem is that FindFirst isn't
finding a match when there is one. Any other ideas why this might be? Are
my Dim and Set statements OK here?

ctdak

I have tried several things to try to provide the user with a custom error
message when they enter a duplicate key into a key field when creating a new
record. Access's own error message always pops up first, which I don't want.
I tried the following code in several different control or form events to no
avail:

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[FundNo] = " & Str(Me![FundNo])
If rs.NoMatch Then
' Do nothing
Else
MsgBox "Fund of this number already exists ", vbOKOnly +
vbExclamation, "Enter different fund number . . ."
Cancel = True: Exit Sub
End If

Perhaps this code isn't going to work. I've also tried an error handling
routine, but Access always catches the problem first and displays its own
message. The error handler isn't being tripped.

ctdak

Where are you putting the code? If it is in the Form's BeforeUpdate event it
should work. The only caveat is the odd use of Str(); if FundNo is a Text
datatype field, you should be able to just use

rs.FindFirst "[FundNo] = '" & Me![FundNo] & "'"

delimiting the search term with ' marks.

I'd also lose the colon single-line concatenation and the unneeded Exit Sub:

Else
MsgBox "yadda yadda...
Cancel = True
End If

John W. Vinson [MVP]

 

[John W. Vinson]

John,

I'm putting this code in the form's BeforeUpdate event. I tried your string
construct, but it doesn't work either. The problem is that FindFirst isn't
finding a match when there is one. Any other ideas why this might be? Are
my Dim and Set statements OK here?

Please post your actual code, and examples of the data in the relevant fields.
If any of the fields are Lookup fields that's critical to know; in any case,
please post the datatypes of the fields involved.

John W. Vinson [MVP]

 

[Guest]

This IS the actual code. There are no lookup fields involved. The first
[FundNo] in the FindFirst statement is the index key field of the table and
is a long integer. The second [FundNo] is a text box control for the
contents of the key field. An example would be a fund number of 18. If a
user attempts to add a record of fund number 18 without realizing that fund
number already exists in the table, then this code is supposed to detect it
as a duplicate and tell the user to enter a different fund number.

 

[John W. Vinson]

This IS the actual code. There are no lookup fields involved. The first
[FundNo] in the FindFirst statement is the index key field of the table and
is a long integer. The second [FundNo] is a text box control for the
contents of the key field. An example would be a fund number of 18. If a
user attempts to add a record of fund number 18 without realizing that fund
number already exists in the table, then this code is supposed to detect it
as a duplicate and tell the user to enter a different fund number.

What is the Datatype of FundNo in the table? Is it a Number, an Autonumber,
text...?

John W. Vinson [MVP]

 

[Guest]

Whops, I thought I wa stelling you the data type by telling you it is a long
integer, but I see my error because that could be either a number or
Autonumber. This index FundNo is a Number.

This IS the actual code. There are no lookup fields involved. The first
[FundNo] in the FindFirst statement is the index key field of the table and
is a long integer. The second [FundNo] is a text box control for the
contents of the key field. An example would be a fund number of 18. If a
user attempts to add a record of fund number 18 without realizing that fund
number already exists in the table, then this code is supposed to detect it
as a duplicate and tell the user to enter a different fund number.

What is the Datatype of FundNo in the table? Is it a Number, an Autonumber,
text...?

John W. Vinson [MVP]

 

[AccessVandal via AccessMonster.com]

Hi,

"Dim rs As Object"? Should it be "Dim rs As DAO.Recordset"?

ctdak wrote:
This IS the actual code. There are no lookup fields involved. The first
[FundNo] in the FindFirst statement is the index key field of the table and
is a long integer. The second [FundNo] is a text box control for the
contents of the key field. An example would be a fund number of 18. If a
user attempts to add a record of fund number 18 without realizing that fund
number already exists in the table, then this code is supposed to detect it
as a duplicate and tell the user to enter a different fund number.

 

[John W. Vinson]

Whops, I thought I wa stelling you the data type by telling you it is a long
integer, but I see my error because that could be either a number or
Autonumber. This index FundNo is a Number.

Sorry, missed that in your post. One possibility is a name ambiguity: if you
have a textbox FundNo bound to a field FundNo, then the expression Me!FundNo
is ambiguous. I'm not sure which Access will look at!

Try setting a breakpoint in the code and updating the record. What is the
value of Me.FundNo?

Just fwiw, here's how I'd write the code (which is not to say it's the only
way to write the code, yours very well may work):

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[FundNo] = " & Me![FundNo]
If rs.NoMatch Then
' Do nothing
Else
MsgBox "Fund of this number already exists ", vbOKOnly +
vbExclamation, "Enter different fund number . . ."
Cancel = True
Me!FundNo.Undo ' erase the input
Me!FundNo.SetFocus
End If
Set rs = Nothing
End Sub

Note that if the Form is filtered, or is based on a query returning a subset
of the funds, you won't find the fund unless it passes the filter; you would
need instead:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("YourTableName", dbOpenSnapshot)
rs.FindFirst "[FundNo] = " & Me![FundNo]
If rs.NoMatch Then
' Do nothing
Else
MsgBox "Fund of this number already exists ", vbOKOnly +
vbExclamation, "Enter different fund number . . ."
Cancel = True
Me!FundNo.Undo ' erase the input
Me!FundNo.SetFocus
End If
rs.Close
Set rs = Nothing
End Sub


John W. Vinson [MVP]

 

[Guest]

Well, I appreciate your help, but so far nothing. I've tried everything you
suggested and more. No matter what I do, the rs.NoMatch conditional will not
find a match.

The table behind the form is not filtered and is not a query returning a
subset. Code is not modifying the recordset in any way. The full table is
the form's data record source. I changed the control name to avoid any
ambiguity. I changed the Dim and Set statements. I experimented with
different forms of strings for the rs.FirstFind statement. I have used a
breakpoint and the value of the control is what you would expect, but a match
is never found when there is one.

I'm totally stumped! I guess I'll just have to forget it and allow the user
to get the stock Access error message, even though they will not likely know
why they are getting it.

ctdak

Whops, I thought I wa stelling you the data type by telling you it is a long
integer, but I see my error because that could be either a number or
Autonumber. This index FundNo is a Number.

Sorry, missed that in your post. One possibility is a name ambiguity: if you
have a textbox FundNo bound to a field FundNo, then the expression Me!FundNo
is ambiguous. I'm not sure which Access will look at!

Try setting a breakpoint in the code and updating the record. What is the
value of Me.FundNo?

Just fwiw, here's how I'd write the code (which is not to say it's the only
way to write the code, yours very well may work):

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[FundNo] = " & Me![FundNo]
If rs.NoMatch Then
' Do nothing
Else
MsgBox "Fund of this number already exists ", vbOKOnly +
vbExclamation, "Enter different fund number . . ."
Cancel = True
Me!FundNo.Undo ' erase the input
Me!FundNo.SetFocus
End If
Set rs = Nothing
End Sub

Note that if the Form is filtered, or is based on a query returning a subset
of the funds, you won't find the fund unless it passes the filter; you would
need instead:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("YourTableName", dbOpenSnapshot)
rs.FindFirst "[FundNo] = " & Me![FundNo]
If rs.NoMatch Then
' Do nothing
Else
MsgBox "Fund of this number already exists ", vbOKOnly +
vbExclamation, "Enter different fund number . . ."
Cancel = True
Me!FundNo.Undo ' erase the input
Me!FundNo.SetFocus
End If
rs.Close
Set rs = Nothing
End Sub


John W. Vinson [MVP]

 

[AccessVandal via AccessMonster.com]

Hi,

Keyin this line just before the "rs.FindFirst "[FundNo] = " & Me![FundNo]"

Debug.Print Me![FundNo]

See the immediate window of the result and show us the result here.

or
MsgBox "Result " & Me![FundNo]
to display a message box.

 

[Guest]

The result is exactly what I typed into the FundNo control. No mystery there.

However, I just realized that the problem must be related to the fact that
my code is opening this form in Add mode. I never mentioned that before (I
assumed it) and didn't think about that having an effect on setting up a
recordest clone in the form's code. What is going on with the form's
underlying table when you open a form in Add mode? Can you do what I'm
trying to do code-wise when in Add mode? Is this why FindFirst isn't finding
anything?

ctdak

Hi,

Keyin this line just before the "rs.FindFirst "[FundNo] = " & Me![FundNo]"

Debug.Print Me![FundNo]

See the immediate window of the result and show us the result here.

or
MsgBox "Result " & Me![FundNo]
to display a message box.

ctdak wrote:
Well, I appreciate your help, but so far nothing. I've tried everything you
suggested and more. No matter what I do, the rs.NoMatch conditional will not
find a match.

The table behind the form is not filtered and is not a query returning a
subset. Code is not modifying the recordset in any way. The full table is
the form's data record source. I changed the control name to avoid any
ambiguity. I changed the Dim and Set statements. I experimented with
different forms of strings for the rs.FirstFind statement. I have used a
breakpoint and the value of the control is what you would expect, but a match
is never found when there is one.

I'm totally stumped! I guess I'll just have to forget it and allow the user
to get the stock Access error message, even though they will not likely know
why they are getting it.

 

[John W. Vinson]

However, I just realized that the problem must be related to the fact that
my code is opening this form in Add mode. I never mentioned that before (I
assumed it) and didn't think about that having an effect on setting up a
recordest clone in the form's code. What is going on with the form's
underlying table when you open a form in Add mode? Can you do what I'm
trying to do code-wise when in Add mode? Is this why FindFirst isn't finding
anything?

Yes. If you're in Add mode, I'm pretty sure that the form's Recordset.Clone is
an empty recordset (with just the newly created record).

Open a recordset based on the table itself instead, or use DLookUp.

John W. Vinson [MVP]

 

[Guest]

Yes, I've proven that this was indeed the problem all along. I obviously
didn't paint my scenario well enough at the start. Thanks to both responders
for hanging in there with me.

ctdak