Recordcount property

[Joe Bohen]

I need to know if a query has any records before my form
updates a table. I have the following code set in the
before update property of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim cnn As Connection
Dim rstCards As New ADODB.Recordset

Set cnn = CurrentProject.Connection
rstCards.Open "qryCard_Validate", cnn, adOpenKeyset,
adLockOptimistic, adCmdTableDirect

If rstCards.RecordCount >= 1 Then
MsgBox "This record will create an overlap."
End If

End Sub

The run time error:
'-2147217904 (80040e10)': is generated when the code is
triggered.

I have spent Sunday afternoon trying to get my head round
this (sad) have I totaly missed the plot here or am I just
a parameter short of a program.

Help Appreciated

 

[Ken Snell]

Probably would be easier to use the DCount function to get the number of
rows. When you open a recordset, the RecordCount property usually does not
contain the actual number of rows unless you do a MoveLast command to force
ACCESS to load the entire recordset.

However, the error that you're getting does not seem to be related to this.

So, try this code instead:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "qryCard_Validate") > 0 Then _
MsgBox "This record will create an overlap."
End Sub

 

[TC]

I prefer DLookup in these cases. It is more efficient in the case where
there could be *many* matching records. DCount will count them all. DLookup
will just hit one at random.

TC

 

[Ken Snell]

That is fine. It all depends upon what you want to know: the total number
of rows, or is there at least one matching row. Just don't "overlook" when
reviewing the code at some later time that the DLookup isn't being used to
actually return a unique value (document in comments!).

 

[TC]

Agreed. But when you say:

if dcount... > 0

it is fairly clear that you are only interested in the existance of a
matching row - not in the total # of rows

TC

 

[Ken Snell]

good point!

--
Ken Snell
<MS ACCESS MVP>

Agreed. But when you say:

if dcount... > 0

it is fairly clear that you are only interested in the existance of a
matching row - not in the total # of rows

TC


number