If duplicate record, go to original record

[islandboy]

hello guys, i am new to this site and new to access.

i am using Access 2007 to create a DB for my IGCSE project.

i wanted to create a msg box if a user enter a duplicate data.

but now even if i enter diff data it still says that the number is being used.

here is the code..by the way this code won't take me to the to the record even if i enter a used number.



Thanks.

Private Sub PostNumber_BeforeUpdate(Cancel As Integer)

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

Set rsc = Me.RecordsetClone

SID = Me.[Post Number].Value
stLinkCriteria = "[PostNumber]=" & "'" & SID & "'"

'Check Employee Table table for duplicate
If DCount("PostNumber", "Employee Table", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo 'Undo this entry.
'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

[RuralGuy]

If you want to go to the previous record for the entry then use the AfterUpdate event rather than the BeforeUpdate event of the control.

[islandboy]

If you want to go to the previous record for the entry then use the AfterUpdate event rather than the BeforeUpdate event of the control.

hey mate,

am new to access can you tell me how to change it?

[RuralGuy]

Move the code you have to the AfterUpdate event of the same control and un-comment the lines and give it a try.

[islandboy]

Move the code you have to the AfterUpdate event of the same control and un-comment the lines and give it a try.

sorry but can u be more specific if u dnt mind?

[RuralGuy]

Can you move the code in the BeforeUpdate event to the AfterUpdate event?

[islandboy]

Can you move the code in the BeforeUpdate event to the AfterUpdate event?

ok in the m on the properth sheet and i see the event....

you mean copy the code and change to the AfterupDate?

what i need to change in the code so it can work?

[islandboy]

ok in the m on the properth sheet and i see the event....

you mean copy the code and change to the AfterupDate?

what i need to change in the code so it can work?

i get this error when i do that.

Private Sub PostNumber_AfterUpdate()

[RuralGuy]

In solving this problem how about telling us what an IGCSE project is? Do you have an error message? Maybe an error Number? Post what you have starting with Private and ending with End Sub.

[islandboy]

In solving this problem how about telling us what an IGCSE project is? Do you have an error message? Maybe an error Number? Post what you have starting with Private and ending with End Sub.

the project is one the i choose to make about HR.


Private Sub PostNumber_AfterUpdate()

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

Set rsc = Me.RecordsetClone

SID = Me.PostNumber.Value
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



even with this code i still get the box that says "Dublicate Information" Warning Post Number 11 has already been entered. You Will now been taken to the record.

but the record 11 isn't there i am sure of that..

[RuralGuy]

What does IGCSE mean?

[islandboy]

What does IGCSE mean?

http://www.cie.org.uk/qualifications/academic/middlesec/igcse/subject?assdef_id=844

[RuralGuy]

Is the [PostNumber] field a NUMERIC or TEXT field?

[islandboy]

Is the [PostNumber] field a NUMERIC or TEXT field?

Numeric (Number) my friend

[RuralGuy]

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