If duplicate record, go to original record

[islandboy]

So you do not want the single quotes:

Private Sub PostNumber_AfterUpdate()
   Dim SID As String
   Dim stLinkCriteria As String
   Dim rsc As DAO.Recordset
   Set rsc = Me.RecordsetClone
   SID = Me.PostNumber
   stLinkCriteria = "[PostNumber]=" & SID
   'Check Employee Table table for duplicate
   If DCount("PostNumber", "Employee Table", stLinkCriteria) > 0 Then
      'Undo duplicate entry
      Me.Undo
      'Message box warning of duplication
      MsgBox "Warning Post Number " _
             & SID & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
             vbInformation, "Duplicate Information"
      'Go to record of original record
      rsc.FindFirst stLinkCriteria
      Me.Bookmark = rsc.Bookmark
   End If
   Set rsc = Nothing
   
End Sub

i still get the error my friends...is it possible that the error can be somewhere before the Post Number???

[RuralGuy]

What error are you getting?

[islandboy]

What error are you getting?

"Dublicate Information" Warning Post Number 11 has already been entered. You Will now been taken to the record. i get this even if a change 100 numbers.

[RuralGuy]

Hmm...any chance you can post the db or at least enough of it for me to play with?

[islandboy]

Hmm...any chance you can post the db or at least enough of it for me to play with?

ok i try n do that tomorrow am off to bed now my friend, thanks for your help.

[thhui]

Try my code instead:-
Still use the beforeupdate event
The logic of original code is ok.
I have seen a few similar examples before.
The change is highlighted for easy reference.

Private Sub PostNumber_BeforeUpdate(Cancel As Integer)
 
   Dim SID As Long
   Dim stLinkCriteria As String
   Dim rsc As DAO.Recordset
   Set rsc = Me.RecordsetClone
   SID = Me.PostNumber
   stLinkCriteria = "[PostNumber]=" & SID
   'Check Employee Table table for duplicate
   If DCount("PostNumber", "Employee Table", stLinkCriteria) > 0 Then
      'Undo duplicate entry
      Cancel = True
      Me.Undo
      'Message box warning of duplication
      MsgBox "Warning Post Number " _
             & SID & " has already been entered." _
             & vbCr & vbCr & "You will now be taken to the original record.", _
             vbInformation, "Duplicate Information"
      'Go to record of original record
      rsc.FindFirst stLinkCriteria
      Me.Bookmark = rsc.Bookmark
   End If
   Set rsc = Nothing
End Sub

[islandboy]

i still get the error even if i change it the way u told me.

if u guys dnt mind pm you mail addss and i will send u my DB its about 1.5Mb

thanks

[RuralGuy]

You can zip it up and attach it to a post here. Just scroll down to Manage Attachments.

[thhui]

Pls upload the file in access 2000 format

I've tried my code in my access db and it works as expected.

By the way, Have you enabled DAO by reference?

[islandboy]

You can zip it up and attach it to a post here. Just scroll down to Manage Attachments.

i tried to do that earlier while i was @ work it didn't work i will try again am home nw.

i know the database must be a mess...but just remember i am new to it...lol.

[RuralGuy]

Try attaching the db directly. The zip you posted is not valid. This forum can handle up to a 2MB file.

[islandboy]

Try attaching the db directly. This zip is not valid. THis form can handle up to a 2MB file.

i change the file extension to from *.accdb to mdb so i can upload it.

hope it work now.

[islandboy]

sorry here it is...

[RuralGuy]

I do not see a PostNumber control anywhere on the form nor any of the code we have been discussing.

[islandboy]

I do not see a PostNumber control anywhere on the form nor any of the code we have been discussing.

when u open the DB you dnt see the post number filed? under employee tab?