RecordsetClone behaving oddly in Form_BeforeUpdate

[Rémi]

Good afternoon, gurus.
I've got bound form and subform. On the subform's BeforeUpdate event,
I want to do some validation - specifically, I want to see how many
records from the current set have a specific value set to true - and,
if greater than the maximum recommended, ask the user whether or not to
continue. (If not, then I cancel the update.)

Thing is, my RecordsetClone is acting funny. RecordCount says I've got
3 records, but the first record is <No current record>. If I move over
to the second, it's fine. I had been wondering if that was 'cause the
edited record is locked up for the update, but the first record is
*not* the record I'm editing.

Can anyone shed some light on this issue?

Here's my code in Form_BeforeUpdate, and my GetAdultCount() function.
(BeforeUpdate is setup to only check for new records, or when my
checkbox has just been turned on - otherwise, no need.)


Private Sub Form_BeforeUpdate(Cancel As Integer)
If NewRecord Or (chkIsAnAdult And Not chkIsAnAdult.OldValue) Then
Dim intAdultCount As Integer
intAdultCount = GetAdultCount()

If intAdultCount > 2 Then
Dim enumResponse As VbMsgBoxResult
enumResponse = MsgBox("Warning: this is the " & _
intAdultCount & _
" adult being entered on this membership. Continue?", _
vbApplicationModal Or vbYesNo Or vbInformation, _
"Warning")

If enumResponse = vbNo Then
Cancel = True
Exit Sub
End If
End If
End If
End Sub


Private Function GetAdultCount() As Integer
Dim objRecs As DAO.Recordset
Set objRecs = RecordsetClone

If Not objRecs.BOF Then _
objRecs.MoveFirst

Dim intAdultCount As Integer

Do Until objRecs.EOF
If objRecs.Fields("isAdult").Value Then _
intAdultCount = intAdultCount + 1

objRecs.MoveNext
Loop

Set objRecs = Nothing

GetAdultCount = intAdultCount
End Function


Regards,
Remi.

 

[Allen Browne]

You checked if BOF is true, but not if the RecordsetClone has a valid record
pointer.

The record pointer may be where ever it was after the last operation, so it
may be EOF, or in may be undefined (e.g. after a FindFirst that resulted in
a NoMatch.) Try:
If objRecs.RecordCount > 0 Then
objRecs.MoveFirst

Have you considered what result you would want if the subform was filtered?
Might it be better to DCount() directly on the subform's table?

 

[Rémi]

You checked if BOF is true, but not if the RecordsetClone has a valid record
pointer.

The record pointer may be where ever it was after the last operation, so it
may be EOF, or in may be undefined (e.g. after a FindFirst that resulted in
a NoMatch.) Try:
If objRecs.RecordCount > 0 Then
objRecs.MoveFirst

Actually, I checked if BOF is false. (The thought was if I'm not at
the beginning of the recordset, move it there. Wouldn't have mattered
if the original record pointer was valid or not, since MoveFirst would
send it to a valid record.)

(Come to think of it, I'd have a problem if it were at BOF.)

Have you considered what result you would want if the subform was filtered?
Might it be better to DCount() directly on the subform's table?

The main form and subform are actually a many-to-many relationship, and
the subform's recordset includes the link table. DCount won't do,
though I could do it with a recordset and a bit of SQL.

In any case, I can't make the error happen again this morning. (I'm
not a fan of sporadic, inconsistent errors, but here I am. Maybe I
missed some aspect of the error.)

Thanks for your help, Allen.
Remi.