Recordset validation

[Guest]

I am trying to validate records in a record set from a query to eliminate
duplication of one field. I can establish the duplication from VB code
attached to the before update event. However once the error has been
established and the focus is returned to the error field in question and the
value is changed by the user, the VB code still takes the original value from
the recordset and returns the same error. I'm certain this is something to do
with the recordsetclone not being modified by the new data entered by the
user. How can I overcome this, so that once the corrected data is entered I
can check it prior to update?

 

[Guest]

I'm trying to do the same thing....would you mind sharing your vb code that
checks for duplicates?

 

[Guest]

Here it is warts and all!!! I hope it helps. I know it's not the most elegant
code yet because I just want to get it to work at the moment.

You will see that it is a field called SPsequence that I am checking. The
middle message boxes are just there to display the counters to ensure the
routine is working correctly. Obviously these will be taken out when
everything is working.

chkStart:
Dim stSP As String

stSP = Nz(Me.SP, "0")

If ((IsNull(Me.SPsequence)) Or (Me.SPsequence = " ") Or (Me.SPsequence =
"0")) And stSP = "-1" Then
MsgBox "Please choose an SP sequence number for this support plan
dependent", vbExclamation, "Required Field"
DoCmd.CancelEvent
GoTo chkEnd
End If

If Me.SPsequence > 0 And Me.SPsequence < 8 And stSP <> "-1" Then
MsgBox "You cannot select a sequence number for this support plan dependent
unless SP is ticked", vbExclamation, "Required Field"
DoCmd.CancelEvent
GoTo chkEnd
End If

Dim db As Database
Dim rec As Recordset
Dim strSQL As String
Dim strMatches As String
Dim strMsg As String
'chkCount is the counter that moves up the recordset until all records are
checked
Dim chkCount As Integer
Dim recCount As Integer
Dim secCheck As Integer
Dim intCheck As Integer
Dim readCount As Integer

strSQL = "SELECT * FROM Dependents WHERE ClientID = " & Me.ClientID
Set db = CurrentDb()
Set rec = Me.RecordsetClone
rec.MoveLast
MsgBox "Records are" & " " & rec.RecordCount
chkCount = 0
intCheck = 0
readCount = 0
recCount = rec.RecordCount

If recCount = 0 Then
GoTo chkEnd
End If

StartSP:
rec.MoveFirst
readCount = 1
Do Until intCheck = chkCount
rec.MoveNext
intCheck = intCheck + 1
readCount = readCount + 1
Loop

intCheck = 0

If readCount = recCount Then
GoTo chkEnd
End If

If IsNull(rec!SPsequence) Or rec!SPsequence = 0 Then
rec.MoveNext
readCount = readCount + 1
chkCount = chkCount + 1
GoTo StartSP
End If
MsgBox "SPsequence secCheck 1st " & rec!SPsequence & "secCheck " & secCheck
& "ReadCount " & readCount
secCheck = rec!SPsequence

MsgBox "SPsequence secCheck 2nd " & rec!SPsequence & "secCheck " & secCheck
& "ReadCount " & readCount
StartComp:
rec.MoveNext
readCount = readCount + 1
MsgBox "SPsequence secCheck 3rd " & rec!SPsequence & "secCheck " &
secCheck & "ReadCount " & readCount
If rec!SPsequence = secCheck Then
MsgBox "You have duplicate record form sequence numbers please recheck
" & rec!SPsequence & " " & secCheck

rec.Close
DoCmd.CancelEvent

GoTo chkEnd
End If

If readCount = recCount Then
MsgBox "You have EOF " & rec!SPsequence & " " & secCheck
GoTo CompEnd
Else: GoTo StartComp
End If

CompEnd:
chkCount = chkCount + 1

If chkCount <> recCount Then
GoTo StartSP
End If

chkEnd:
End Sub