duplicate records

[Afrosheen]

I have a routine that when the staff id field is entered it looks to see if
it's a duplicate. What I want it to do is that if there is a duplicate and
the person enters "No" to the question then it will display either the next
or the previous record. The problem is that it doesn't. I would appreciate
any help with this. Here is the code. In other words I want to get out of the
"Add New Record"

Private Sub Staff_Id_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

If Not IsNull(DLookup("[staffid]", "qry_roster", "[staffid] = '" &
Me![StaffId] & "'")) Then
Select Case MsgBox("Sorry, This is a duplicate Staff Id Number," _
& vbCrLf & "" _
& vbCrLf & "Which means they already exist.. Try again?" _
, vbYesNo Or vbQuestion Or vbDefaultButton1, "Warning
Warning")

Case vbYes
Cancel = True
Case vbNo
DoCmd.GoToRecord , , acPrevious
End Select

End If


Err_Form_BeforeUpdate:
If Err.Number <> 2501 Then
Call LogError(Err.Number, Err.Description, "Roster form before update")
End If
End Sub

 

[bcap]

The trouble is that, at the point where you execute the DoCmd.GoToRecord
method, you've still got a dirty new record.

You could try this (warning: untested):

Me.Undo
Cancel = True
DoCmd.GoToRecord , , acPrevious

 

[Afrosheen]

Thanks Bcap. It did work. Now should I requery the table? Because when the
form first starts up it creates a query. The query consists of finding
everyone that works on "A-Days". When I tried to enter a duplicate and
answered no, it went to the last record which was on "B-Days". It was just
like the query was not there.

Here is the new changes.

Case vbNo
Me.Undo
Cancel = True
DoCmd.Requery
DoCmd.GoToRecord , , acPrevious

I don't know if the the docmd.requery is correct.

Thanks for reading my post.

The trouble is that, at the point where you execute the DoCmd.GoToRecord
method, you've still got a dirty new record.

You could try this (warning: untested):

Me.Undo
Cancel = True
DoCmd.GoToRecord , , acPrevious

I have a routine that when the staff id field is entered it looks to see if
it's a duplicate. What I want it to do is that if there is a duplicate and
the person enters "No" to the question then it will display either the
next
or the previous record. The problem is that it doesn't. I would appreciate
any help with this. Here is the code. In other words I want to get out of
the
"Add New Record"

Private Sub Staff_Id_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

If Not IsNull(DLookup("[staffid]", "qry_roster", "[staffid] = '" &
Me![StaffId] & "'")) Then
Select Case MsgBox("Sorry, This is a duplicate Staff Id Number," _
& vbCrLf & "" _
& vbCrLf & "Which means they already exist.. Try again?"
_
, vbYesNo Or vbQuestion Or vbDefaultButton1, "Warning
Warning")

Case vbYes
Cancel = True
Case vbNo
DoCmd.GoToRecord , , acPrevious
End Select

End If


Err_Form_BeforeUpdate:
If Err.Number <> 2501 Then
Call LogError(Err.Number, Err.Description, "Roster form before update")
End If
End Sub