SQL String Returning Wrong Number of Records

[John Baker]

Hello All:

Hoping you can help me with a problem I am having with trying to execute a SQL string in my code.

I have a table (tbl_rhd_managers) that identifies all managers within a department. I want to write a SQL statement that will return ALL managers within a specified region:

strSQL = "SELECT * From tbl_rhd_managers WHERE [region] = " & """" & strRegionName & """"
Set rs1 = db.OpenRecordset(strSQL)

The recordset always returns 1 record - regardless of how many managers are defined in the table who fall within the respective region.

Can anyone advise me as to what I may be doing wrong?

Thanks!
John

 

[Mile-O]

strSQL = "SELECT * From tbl_rhd_managers WHERE [region] = """ & strRegionName & """"
Set rs1 = db.OpenRecordset(strSQL)
rs1.MoveLast
MsgBox rs1.Recordcount

 

[John Baker]

Additional Code

SJ - Here is the rest of my code. From the example in my original post, intRecordCount always returns 1.


strSQL = "SELECT * From tbl_rhd_managers WHERE [region] = " & """" & strRegionName & """"
Set rs1 = db.OpenRecordset(strSQL)

intRecordCount = rs1.RecordCount
For intCounter = 1 To intRecordCount
rs1.MoveFirst

strManagerName = rs1!manager_name
strManagerEmail = rs1!manager_email_address

strMessageText = "Message Text"
strMessageSubject = "Message Subject"
DoCmd.SendObject acSendReport, strReportName, strOutputFormat, strManagerEmail, , , strMessageSubject, strMessageText, 0

rs1.MoveNext

Next intCounter

rs1.Close

 

[DaveJHPT]

I think SJ's point is that you need to do a .MoveLast before .RecordCount shows the correct count.

Dave

 

[filo65]

it is not missing a semicolon at the end of your strSQL as well?

As the others said:

strSQL = "SELECT * From tbl_rhd_managers WHERE [region] = " & """" & strRegionName & """"
Set rs1 = db.OpenRecordset(strSQL)
rs1.MoveLast
rs1.MoveFirst

intRecordCount = rs1.RecordCount
For intCounter = 1 To intRecordCount
'rs1.MoveFirst

strManagerName = rs1!manager_name
strManagerEmail 
....

 

[John Baker]

Problem Solved!

Thanks, Dave. Problem solved!! I never realized you had to go the the last record first in order to get the # of records returned.

John

rs1.MoveLast
intRecordCount = rs1.RecordCount

 

[Mile-O]

It's a quirk of DAO. You open the recordset but only the first record is initially loaded to save time (a recordset with thousands could be slow) so you need to got the last record in order to "populate" the recordset.

 

[modest]

I never realized you had to go the the last record first in order to get the # of records returned.

You don't. As SJ has pointed out, it ensures that all the records are loaded into memory before any action is taken.

An smaller tables, this isn't necessary, however I always recommend you do this.

As a reminder, once you call .Movelast, the pointer will be looking at the last record in the database, in certain cases you may be looking at data chronologically or creating your own searches that you need to start at the first record. In these cases you will need to point back to the first record. This appears odd, but it is necessary.

So you would call:
rst.Movelast
rst.Movefirst
'then continue with your data