Code to take me to a Duplicate Record (1 Viewer)

jamie23

New member
Local time
Today, 15:56
Joined
Aug 13, 2013
Messages
9
Hi, i am new to Access

I have a form based on a query and have written code to display a msgbox if a duplicate entry is inputted in the NHS_Number field in the form. I have also added code "me.undo" to clear the form so that it is not saved. I am looking for a piece of code that will display the duplicate record. Here is my code thus far:

Private Sub NHs_Number_BeforeUpdate(Cancel As Integer)
Dim dbs As Database
Dim rst As DAO.Recordset
Dim x As Integer
Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("Info 4 Access to medical records M", dbOpenTable, dbReadOnly)
With rst
For x = 1 To rst.RecordCount

If rst![NHs_Number] = Forms![FRM Info 4 Access to medical records M]![NHS Number] Then
Me.Undo
MsgBox "duplicate NHS NUmber"
End If
.MoveNext
Next x
.Close
End With

Set rst = Nothing
Set dbs = Nothing
End Sub

Any help will be greatly appreciated!!
 

Mihail

Registered User.
Local time
Today, 17:56
Joined
Jan 22, 2011
Messages
2,373
Hi, i am new to Acces
And to the forum too. So, WELCOME !

Hi, i am new to Access
I've quoted this again because a newbie (in Access because seems that you have some experience in VBA, probably from Excel) do not start by coding.
You must start by using the Access power.

In a well designed database, is no reason for your code because Access has tools to handle duplicates (deny or find if already exists) with no code.

So, tell us what you have and what you wish to achieve.

Cheers.
 

jamie23

New member
Local time
Today, 15:56
Joined
Aug 13, 2013
Messages
9
Hi Thanks for getting back to me so quickly and for the Welcome.

The coding that I have used so far has come from various sources on the internet and my manager (She has a bit more Knowledge than me!) I will explain exactly what I have done and what I am hoping to achieve. Please bear in mind that my manager is keen on me using coding (VBA) so I get some experience using it though any Access tips along the way would be great.

Background

I work for the National Health Service and we respond to requests from Doctors Surgeries when they ask us to send a patient’s medical records to them. We need to record each request due to Data protection and our performance indicators so that we have an audit trail.

What I have done so far

I have designed a form based on a query that updates a table when the record has been saved. To avoid duplicates, I tried going into the table design for the NHS_Number field and set the Indexed marker to no (Avoid Duplicates). This worked when a duplicate NHS Number was entered but gave an Access Standardised Message and we wanted our own message to be displayed. The first bit of code I quoted does this. Wahoo. Problem 1 solved J

We have an ID Field in the form that is formatted to Auto Number. The problem with this is, even if a duplicate NHS Number was typed in, it would still want to save the record as data had been entered into the form. I then added the code me.undo to clear the form if the NHS Number was a duplicate to avoid the record being saved. Again, up to this point, everything works fine!

Moving Forward

What I would like now is, once a duplicate NHS Number has been inputted into the NHS_Number field and our own message box has been cleared (Ok pressed to clear the message box and form cleared with the me.undo code), I would like Access to take the user to the Duplicate Record in the table or display it in the form so that the user can check it.

I hope this gives you enough information, ive been hunting down was to do this for a while now and am having no luck. Any help or guidance would be wonderful!

Many Thanks J

 

Mihail

Registered User.
Local time
Today, 17:56
Joined
Jan 22, 2011
Messages
2,373
Unfortunately, I don't fully understand your English (I'm wonder why is so bad ??? :) ???? )
But I have an idea:

in this code
Code:
If rst![NHs_Number] = Forms![FRM Info 4 Access to medical records M]![NHS Number] Then
   Me.Undo
   MsgBox "duplicate NHS NUmber"
....... etc
catch the user ID
Code:
If rst![NHs_Number] = Forms![FRM Info 4 Access to medical records M]![NHS Number] Then
  UserID = rst![FieldNameForUserID])
  Me.Undo
   MsgBox "duplicate NHS NUmber"
........ etc
Then use the UserID value to open the form that display the information for that user:
Code:
DoCmd.OpenForm "FormNameThatDisplayTheUsersInformations", Where: "UserID = " & UserID
At all your code should look like this (not tested, of course)
Code:
Dim UserID As Long 'I assume that is an AutoNumber

Private Sub NHs_Number_BeforeUpdate(Cancel As Integer)
Dim dbs As Database
Dim rst As DAO.Recordset
Dim x As Integer
  Set dbs = CurrentDb
 
  Set rst = dbs.OpenRecordset("Info 4 Access to medical records M", dbOpenTable, dbReadOnly)
  With rst
    .MoveFirst
    Do While Not .EOF()
      If rst![NHs_Number] = Forms![FRM Info 4 Access to medical records M]![NHS Number] Then
        UserID = rst![FieldNameForUserID])
        Me.Undo
        MsgBox "duplicate NHS NUmber"
        [B]DoCmd.OpenForm "FormNameThatDisplayTheUsersInformations", Where: "UserID = " & UserID[/B]
    Exit Do 'Is no reason to continue
      End If
      .MoveNext
     Loop
  End With
 
  rst,Close
  Set rst = Nothing
  Set dbs = Nothing

End Sub
Take a look to this thread (post #6) in order to learn how to manipulate a recordset (I've made the necessary changes in your code)

Hope this post will help you.
Cheers !
 

Cronk

Registered User.
Local time
Tomorrow, 02:56
Joined
Jul 4, 2013
Messages
2,770
Jamie

I would suggest you set the No Duplicates index on your field.

You can replace the standard (non clear) MS message by trapping the error in the BeforeInsert event (on the BeforeUpdate event if an existing record is being editted) and providing your users with your custom error message. NB Set Cancel to True in the sub to stop the Access action.
 

Users who are viewing this thread

Top Bottom