Querydefs not working?

[Paul Fenton]

I have a query with this parameter in the field [ExtractedSSN]:

forms!frmDuplicateSubjects!ExtractedSSN

When I run that from the query window, it shows me 3 records.

Now, I have this code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qDuplicateSubjects")

' find out how many subjects are already there with this ID

With qdf
.Parameters("forms!frmDuplicateSubjects!ExtractedSSN") =_
Forms!frmDuplicateSubjects!ExtractedSSN
Set rsSubject = .OpenRecordset()
End With

rssubject.recordsource only shows ONE record!

What am I missing?


Paul Fenton
(e-mail address removed)

 

[Allen Browne]

The RecordCount returns the number of records accessed so far. When you
first OpenRecordset, that is typically 1 if there are any, or 0 if there are
not.

To get the actual count:
rsSubject.MoveLast
Debug.Print rsSubject.RecordCount

This one is #2 in the list of traps in article:
VBA Traps: Working with Recordsets - Bugs you need to avoid
at:
http://members.iinet.net.au/~allenbrowne/ser-29.html

 

[Paul Fenton]

Thank Allen, that did the trick.

Paul Fenton