Find Record 1

[SiJP]

I have an interesting problem (well as interesting as coding gets) for which I need some help on. To set the seen, I have a basic form with four fields:

StoreIDCode
WorkDate
LineNumber
Matchkey

When a user enters the first three fields, a match key is created which literally is:

MatchKey = StoreIDCode & WorkDate & LineNumber

My problem is that once the match key is created I would like to check the rest of the records to see if the match key already exists, i.e identifying duplicate records that have the same storeidcode, workdate and linenumber.

Here is the code I have in place at the moment:

Function DuplicateCheck()

'firstly, check to see that an entry has been put into the three matchkey fields
'StoreIDCode, WorkDate and LineNumber.

If IsNull(Me.StoreIDCode) Then
ElseIf IsNull(Me.WorkDate) Then
ElseIf IsNull(Me.WorkDate) Then
Else

'Assuming all these are there, create a matchkey
MatchKey = StoreIDCode & WorkDate & LineNumber
End If

'once matchkey has been created, see if any other match keys of the same sort exists

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

'WHAT GOES HERE!!!


'if so, delete the record and display message to user saying
'it has been deleted due to duplicate

End Function

Can I please have some suggestions as to how I can do this. My head is dead right now and Im probably missing something obvious, but hey-ho... thank you!

Simon

 

[Bigpapou]

here's what you could do:

If IsNull(Me.StoreIDCode) Then
ElseIf IsNull(Me.WorkDate) Then
ElseIf IsNull(Me.WorkDate) Then
Else

'Assuming all these are there, create a matchkey
MatchKey = StoreIDCode & WorkDate & LineNumber
End If

'once matchkey has been created, see if any other match keys of the same sort exists

Dim rs As DAO.Recordset
dim SQL as string
dim something as integer

SQL = "SELECT yourTable.matchKey FROM yourTable WHERE (yourTable.Matchkey = """ & matchKey & """"

set rst = currentdb.openRecordset(SQL, dbOpenDynaset)
'this part of code is to protect you when there is no record found
on error resume next
rst.movelast
rst.movefirst
on error goto BUG

something = rst.recordcount
if something = 0 then
' "you have found no match, hence there's no duplicate
else
'delete the record and display message to user saying
'it has been deleted due to duplicateend if
end if
exit function

BUG:
msgbox err.description
exit function

End Function

You'll probably have to play with the code since I did not test it but when it'll be working, it should be doing what you want.
If you need assitance on how to delete the record or anything else just ask... I'll be notify via e-mail!!!

 

[SiJP]

Thanks for the response! I've had to change the counter o reflect that the record will already exist when the code is invoked (therefore giving me an error for every record).

Other than that - spot on for coding!