Duplicate Problem with control

I have form with a control that is named AccNum. It does not allow
duplicates. When a user types in a number in the AccNum control it
displays a duplicates not allowed window. At this time the user must
type in a bogus number and then delete the record.

Is there a way to stop this from happening when a duplicate is typed in the
control. Maybe a window that notifies the user and then it returns to
a previous record and then cancells the data input in the AccNum control.
I had looked at the duplicates on this issue and can't get any code to work.

"Duane Hookom" DuaneAtNoSpanHookomDotNet wrote:

Teach your users to press the Escape key a couple times.


So simple of a solution. Thanks.



I just found this one on google and made it work.
I just changed strStudentNumber to what I use AccNum.




By placing the following code sample into the Before Update event of the
Student Number field in the main form, this will prevent the duplication,
raise a custom error message and return the user to the original record for
the Student:
Private Sub strStudentNumber_BeforeUpdate(Cancel As Integer)

'*********************************
'Code sample courtesy of srfreeman
'*********************************

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strStudentNumber.Value
stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

'Check StudentDetails table for duplicate StudentNumber
If DCount("strStudentNumber", "tblStudentDetails", stLinkCriteria) 0
Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Student Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
'Go to record of original Student Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub

Teach your users to press the Escape key a couple times.

--
Duane Hookom
MS Access MVP

"Jay" wrote in message
news:jlZWg.1131$rS.133@fed1read05...
I have form with a control that is named AccNum. It does not allow
duplicates. When a user types in a number in the AccNum control it
displays a duplicates not allowed window. At this time the user must
type in a bogus number and then delete the record.

Is there a way to stop this from happening when a duplicate is typed in
the
control. Maybe a window that notifies the user and then it returns to
a previous record and then cancells the data input in the AccNum control.
I had looked at the duplicates on this issue and can't get any code to
work.

I have an Order menu that when the user types in a duplicate OrderID a
custom message comes up. This gives them the option to put in a valid Order
number since they are TRYING to enter a new Order anyway.

On the Form_Error event...

Dim strMsg As String

If DataErr = 3022 Then
Response = acDataErrContinue
strMsg = "The Order Number you selected is already in use, " _
& "you MUST select another Order Number!"
MsgBox strMsg
DoCmd.GoToControl "txtOrderID"
End If

HTH,
Gina Whipp

"Jay" wrote in message
news:jlZWg.1131$rS.133@fed1read05...
I have form with a control that is named AccNum. It does not allow
duplicates. When a user types in a number in the AccNum control it
displays a duplicates not allowed window. At this time the user must
type in a bogus number and then delete the record.

Is there a way to stop this from happening when a duplicate is typed in
the
control. Maybe a window that notifies the user and then it returns to
a previous record and then cancells the data input in the AccNum control.
I had looked at the duplicates on this issue and can't get any code to
work.

Gina Whipp wrote:

I have an Order menu that when the user types in a duplicate OrderID a
custom message comes up. This gives them the option to put in a valid
Order number since they are TRYING to enter a new Order anyway.

On the Form_Error event...

Dim strMsg As String

If DataErr = 3022 Then
Response = acDataErrContinue
strMsg = "The Order Number you selected is already in use, " _
& "you MUST select another Order Number!"
MsgBox strMsg
DoCmd.GoToControl "txtOrderID"
End If

HTH,
Gina Whipp

"Jay" wrote in message
news:jlZWg.1131$rS.133@fed1read05...
I have form with a control that is named AccNum. It does not allow
duplicates. When a user types in a number in the AccNum control it
displays a duplicates not allowed window. At this time the user must
type in a bogus number and then delete the record.

Is there a way to stop this from happening when a duplicate is typed in
the
control. Maybe a window that notifies the user and then it returns to
a previous record and then cancells the data input in the AccNum control.
I had looked at the duplicates on this issue and can't get any code to
work.



Thank you. I will use it too.

Welcome, thanks for posting back!

Gina Whipp

"Jay" wrote in message
news:XjEXg.1265$rS.369@fed1read05...
Gina Whipp wrote:

I have an Order menu that when the user types in a duplicate OrderID a
custom message comes up. This gives them the option to put in a valid
Order number since they are TRYING to enter a new Order anyway.

On the Form_Error event...

Dim strMsg As String

If DataErr = 3022 Then
Response = acDataErrContinue
strMsg = "The Order Number you selected is already in use, " _
& "you MUST select another Order Number!"
MsgBox strMsg
DoCmd.GoToControl "txtOrderID"
End If

HTH,
Gina Whipp

"Jay" wrote in message
news:jlZWg.1131$rS.133@fed1read05...
I have form with a control that is named AccNum. It does not allow
duplicates. When a user types in a number in the AccNum control it
displays a duplicates not allowed window. At this time the user must
type in a bogus number and then delete the record.

Is there a way to stop this from happening when a duplicate is typed in
the
control. Maybe a window that notifies the user and then it returns to
a previous record and then cancells the data input in the AccNum
control.
I had looked at the duplicates on this issue and can't get any code to
work.



Thank you. I will use it too.