Duplicate Records in a field

[zacharyjunior]

Hello All,

I have a warranty database that tracks automotive warranty claims. I have a need to provide the user an allert or a notice that will allert the user that the VIN number typed in was previously entered in the database. I want to allow duplicate VIN numbers but I want to make sure the user does not enter a claim that has already been entered.

What do ya think I can do here?

Thanks in advance.

 

[Dave Eyley]

I've just done something similar...

On the AFTERUPDATE property of the VIN field select [Event Procedure] and use the following code -
It starts by creating a full recordset of the table where your VIN No. and associated data is stored. The Recordset is called MySet.
Then it moves to the first record and checks the VIN No. against the VIN entered in the form.
If it's not the same, it moves on to the next and so on.
If, after it gets to the end, it's found a match, then it tells you there's a duplicate and asks if you want to continue.
If the answer is Yes, the routine ends leaving the VIN No. on the form for you to continue.
If the answer is No, it clears the VIN No. and the routine ends leaving you to start over.
If it doesn't find a match it does nothing.


Dim MyDb As Database,MySet As RecordSet
Dim Flag As Integer, Reply As Integer
Flag=0

Set MyDb=DBEngine.Workspaces(0).Databases(0)
Set MySet=MyDb.OpenRecordSet ("MyTable") 'your table

MySet.MoveFirst
Do Until MySet.EOF
If MySet![VIN]=Me!VIN Then Flag=1 'Found duplicate
MySet.MoveNext
Loop

If Flag=1 Then
Reply=MsgBox("This VIN No. already exists. Do you want to continue?",VBYesNo,"Duplicate VIN")
If Reply=7 Then
Me!VIN=Null
MySet.Close
Exit Sub
End If
End If

MySet.Close
MyDb.Close

Exit Sub


You probably would want to do your own thing from the result of 'Reply' (user reply from MsgBox - 6=Yes, 7=No) - I think.
You could open a new pop-up form to show the record with the same VIN No. to see if its relevant, or whatever.

Hope this has been of some help...

Dave E

 

[zacharyjunior]

The code worked great. I am intersted in having a report or form pop up with the records that have the duplicate records. How can I do this?

Thanks

Zacharyjunior

 

[Dave Eyley]

Now look 'ere,

I'm writing emails with answers and you're sneaking on the forum asking the question I've just answered.
This method is better - please see the email and let me know if it's what you're after.

Dave E