If duplicate record, go to original record

islandboy · #1 06-23-2009, 09:24 AM

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 · #2 06-23-2009, 10:57 AM

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 · #3 06-23-2009, 09:02 PM

Quote:

Originally Posted by 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.

hey mate,

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

RuralGuy · #4 06-24-2009, 05:05 AM

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

islandboy · #5 06-24-2009, 06:50 AM

Quote:

Originally Posted by RuralGuy

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 · #6 06-24-2009, 07:05 AM

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

islandboy · #7 06-24-2009, 07:29 AM

Quote:

Originally Posted by RuralGuy

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 · #8 06-24-2009, 07:32 AM

Quote:

Originally Posted by 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 · #9 06-24-2009, 07:41 AM

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 · #10 06-24-2009, 07:55 AM

Quote:

Originally Posted by 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.

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 · #11 06-24-2009, 08:13 AM

What does IGCSE mean?

islandboy · #12 06-24-2009, 08:17 AM

Quote:

Originally Posted by RuralGuy

What does IGCSE mean?

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

RuralGuy · #13 06-24-2009, 08:25 AM

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

islandboy · #14 06-24-2009, 08:32 AM

Quote:

Originally Posted by RuralGuy

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

Numeric (Number) my friend

RuralGuy · #15 06-24-2009, 08:59 AM

So you do not want the single quotes:

Code:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Duplicate record failure	bugchaser	Access	5	05-21-2009 06:38 AM
If duplicate record, go to original record	BigBear	Forms	1	03-30-2009 07:27 AM
Duplicate the record in form and subform to a different table	mwillis929	Forms	0	01-06-2009 11:17 AM
Deleting Record Contents, not complete record...	Charles Waters	Access	2	06-24-2008 10:00 AM
i need record dividers	dunston	Reports	1	11-11-2006 04:57 PM