I'm running code that creates a recordset which produces 1 record. However, when I take the SQL statement which creates the recordset and paste it in a query (in QBE) I get two records which is what I am expecting. Part of the code follows. I have a Debug pause at "Do While Not .EOF", and when I check, the RecordCount=1. I'm really stumped. Any suggestion as to why I would have this issue? I'm running Access 2003 on XP Pro, but the database is actually 2000.

jsSQL = "SELECT qryMnthlyForeignCallsCheck.[Activity_PK]"
jsSQL = jsSQL & ", qryMnthlyForeignCallsCheck.[APO_FPO_Address]"
jsSQL = jsSQL & ", qryMnthlyForeignCallsCheck.[DSNPhone]"
jsSQL = jsSQL & ", qryMnthlyForeignCallsCheck.[Phone]"
jsSQL = jsSQL & " FROM qryMnthlyForeignCallsCheck WHERE qryMnthlyForeignCallsCheck.[Country] Is Null"

Set joRS = New ADODB.Recordset
With joRS
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open jsSQL

Do While Not .EOF

Welcome to the forums

When you set the rs, Access only thinks there is the one record that it is currently on (the first one). To get around this, use the .movenext action. Hope that gets you closer at least.

See Allen Browne's site for more help if needed.

Thanks, nckdryr. I just didn't show the .MoveNext. The fact is before I even start processing the recordset, I check .RecordCount and it only shows 1 record when I should be getting 2 records. It just seems to me any recordset you create in code should be the same as if you take that same SQL and create a query in QBE. It's probably some esoteric thing with Access (query vs recordset) I just haven't found out yet.

Thanks,

G

gjglick,

nckdryr is right. From MSDN (http://msdn.microsoft.com/en-us/library/bb208624.aspx):

Not true when you use .CursorType = adOpenStatic. I have a function I use all the time to get record counts and I don't use MoveLast, I just open the recordset and reference .RecordCount. But I do apologize on my inconsistancies. I actually stepped thru my recordset processing loop and indeed there is only 1 record when I should be getting 2 records.

In ADO, I just open the recordset as such:

Dim rs as adodb.recordset
set rs = new adodb.recordset
dim strSQL as string
strSQL = "Select * from MyTable"
rs.open strsql,currentproject.connection,adopenkeyset,adlo ckreadonly
msgbox "Recordset count = " & rs.recordcount
rs.close
set rs = nothing

Note:
rs.open strsql,currentproject.connection,adopenkeyset,adlo ckreadonly - > to open it non-updatable and quickly for things such as recordcount
rs.open strsql,currentproject.connection,adopendynamic,adl ockoptimistic - > to open it updatable.

pkstormy, you appear to have a lot of experience. Forgetting about code structure, etc, have you ever come across a situation where you're expecting, say, 2 records in a recordset but only 1 shows up. So you do the logical thing and grab the SQL statement that builds the recordset and paste it in a query in Access's QBE to check that the right data is being retrieved. And your query in QBE window shows the 2 records you're expecting. That is my situation and I am stumped as to why it is occurring. I assume it has to do with the saved query I'm using (qryMnthlyForeignCallsCheck). I think people are getting sidetracked from my real problem.

Are you sure your .recordcount in your recordset is only returning 1 record?

If opening an ADO recordset as adopenstatic and adlockoptimistic, you typically need an rs.movelast to then get the correct .recordcount. (typically). I usually don't do this though and instead use the adopenkeyset and adlockreadonly which I know returns the correct recordcount.

If you get -1 for a recordcount in ADO, then you're not opening the recordset correctly to get a recordcount. I noticed your opening the recordset as adopenstatic and adopendynamic. Try opening it as adopenkeyset as your cursortype and adlockreadonly as your locktype parameters. (note: the recordset won't be updatable but you'll at least see if the recordcount is correct using a msgbox "My Recordcount = " & joRS.recordcount)

Otherwise, if you're using the adopenstatic and adopendynamic,

Try adding a message box in your looping routine:
ex:
....
Set joRS = New ADODB.Recordset
With joRS
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open jsSQL

joRS.movefirst
Do While Not .EOF
msgbox "Record here: " & joRS!MyPrimaryKeyField.
joRS.movenext

loop
.....

or (which may or may not work with the way you're currently opening the recordset)...

joRS.movefirst
joRS.movelast
msgbox "Record count: " & joRS.recordcount

If the later doesn't give you a true recordcount, again, try opening the recordset as suggested.

Note also: you're using jsSQL = jsSQL & " FROM qryMnthlyForeignCallsCheck WHERE qryMnthlyForeignCallsCheck.[Country] Is Null"

Sometimes an Is Null test can produce varying results where I've seen that a field isn't actually null but instead, has a blank value (which SHOULD be the same but sometimes doesn't recognize a Null value versus a blank value. These are rare but you can also utilize the .[Country] = "" to test as well. You can also test by len(.[Country])> 0 as another test.

I rarely use adopenstatic and adlockOptimistic together. Again, these are the 2 typical recordsets I use:
rs.open strsql,currentproject.connection,adopenkeyset,adlo ckreadonly - > to open it non-updatable and quickly for things such as recordcount
rs.open strsql,currentproject.connection,adopendynamic,adl ockoptimistic - > to open it updatable (but no recordcount)

I've tried every combination. Please ignore .RecordCount, it's not significant. My code logic is similiar to yours (see below). My debug statement only prints one key (record), yet if I take the contents of jsSQL and paste it in the QBE window and run it, I get both records I'm expecting.


jsSQL = "SELECT qryMnthlyForeignCallsCheck.[Activity_PK]"
jsSQL = jsSQL & ", qryMnthlyForeignCallsCheck.[APO_FPO_Address]"
jsSQL = jsSQL & ", qryMnthlyForeignCallsCheck.[DSNPhone]"
jsSQL = jsSQL & ", qryMnthlyForeignCallsCheck.[Phone]"
jsSQL = jsSQL & " FROM qryMnthlyForeignCallsCheck WHERE qryMnthlyForeignCallsCheck.[Country] Is Null"

Set joRS = New ADODB.Recordset
With joRS
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockReadOnly
.Open jsSQL

Do While Not .EOF
jiRead = jiRead + 1
Debug.Print .Fields(0) & ": " & jsMsg

'-- Some processing...

.MoveNext
Loop

.Close
End With


What follows is the the design of the query (qryMnthlyForeignCallsCheck) I'm using. I suspect it's something to do with my WHERE clause that causes it to behave differently in ADO than in the QBE.


SELECT qryMnthlyCallsRpt.Call_PK, tblCallerID.LName, tblCallerID.FName, tblActivity.Activity_PK, tblActivity.ActName, tblActivity.APO_FPO_Address, tblActivity.Address1, tblActivity.Address2, tblActivity.Address3, tblActivity.City, tblActivity.State, tblActivity.Zipcode, tblActivity.Country, tblCallerID.DSNPhone, tblCallerID.Phone
FROM tblActivity INNER JOIN (tblCallerID INNER JOIN qryMnthlyCallsRpt ON tblCallerID.Caller_PK=qryMnthlyCallsRpt.Caller_FK) ON tblActivity.Activity_PK=qryMnthlyCallsRpt.Activity _FK
WHERE (((tblActivity.Address1) Like "FPO*" Or (tblActivity.Address1) Like "APO*")) Or (((tblActivity.Address2) Like "FPO*")) Or (((tblActivity.Address2) Like "APO*")) Or (((tblActivity.Address3) Like "FPO*")) Or (((tblActivity.Address3) Like "APO*")) Or (((tblActivity.City) Like "FPO*")) Or (((tblActivity.City) Like "APO*")) Or (((tblActivity.Zipcode) Like "FPO*")) Or (((tblActivity.Zipcode) Like "APO*")) Or (((Left(tblCallerID.DSNPhone & " ",3)) In ('313','314','315','316','318'))) Or (((tblCallerID.Phone) Like '01*')) Or (((tblActivity.Country)<>'US' And (tblActivity.Country) Is Not Null));

Found the problem. I consider it a bug in MS-Access, Microsoft would consider it an enhancement. My underlying query, qryMnthlyForeignCallsCheck, uses wildcard * (asterisk) for some filtering. I know from experience when building and executing SQL in code, I have to use % as the wildcard character. BUT, one would expect a canned query to execute the same either way. Go figure.

Thanks for the feedback from all.

G