RecordSet and number of records returned (1 Viewer)

[mcalex]

Hi all

I am trying to code a duplicate remover that allows the user to check that the two records really do refer to the same person before hitting the big red delete button.

I have this code:

sql = "SELECT Caller.GivenNames, Organisation.OrgName, Caller.Phone " _
    & "FROM Caller LEFT JOIN Organisation ON Caller.OrganisationID = Organisation.ID " _
    & "WHERE Caller.Surname = """ & dupeSurname & """; "
 
Set dbase = CurrentDb
Set rst = dbase.OpenRecordset(sql)
rows = rst.GetRows

 
numReturned = UBound(rows, 2) + 1
Debug.Print "Dupe sql = " & sql
Debug.Print "Number of records: " & numReturned

dupeSurname is passed in from a combo box

The output of the debug lines is as follows:

Dupe sql = SELECT Caller.GivenNames, Organisation.OrgName, Caller.Phone FROM Caller LEFT JOIN Organisation ON Caller.OrganisationID = Organisation.ID WHERE Caller.Surname = "Mitchell"; 
Number of records: 1

But when I input that query into the query designer in access I get multiple results.

What am I doing wrong with the record set, or how do I get it to give me the correct number of records returned?

many thanks

mcalex

 

[MarkK]

It looks like GetRows returns one row unless you specify something other for the NumRows parameter.

rows = rst.GetRows(12)

Cheers,
Mark

 

[mcalex]

Thanks for that Mark.

Wow, I'm sure I've used GetRows() previously to return the whole query. Saw your post and thought it was coz I'd forgotten the brackets, but even that is only returning one record.

Looks like I got a job of trawling through old code to check whether my GetRows() are actually working. (I usually use the method to ensure a single result query has just been processed ~ ouch!)

Thanks again

 

[Galaxiom]

Wow, I'm sure I've used GetRows() previously to return the whole query.

It depends on whether the recordset is DAO or ADO.

The DAO GetRows will return one record if the argument is omitted, (it would appear - I can find no reference to the parameter being optional)

ADO GetRows returns the rest of the rows after the cursor if there is no argument specified.

It is another good reason why recordsets should be unambiguously defined as DAO or ADO.

 

[Galaxiom]

It looks like the real issue might be that rst.MoveLast is required before the GetRows or the recordset is unaware of its recordcount. Just like when the you use rst.RecordCount on a DAO recordset.

Since ADO doesn't require this step you get the whole recordset. So it seems the two methods are probably much the same. (Except ADO has another two arguments to control the start row and fields.)

However I still can't find a clear reference to the DAO GetRows method that specifies the argument as optional.