Help! RecordCount Problem ADODB

[RainX]

I have a recordset and the problem is that it isnt counting the records for two of the 3 queries at all. Basically the RecordCount is 0 when it really isnt

Here are the two it isn't working for

SELECT s.idnum,s.precinct,s.grp, s.direction as dir,s.street_name,s.type,s.post_dir,s.city,s.low_range,s.high_range,s.odd_even as [O/E/B],s.zipcode,s.ppm_id FROM street AS s INNER JOIN office AS o ON (s.grp = o.grp) AND (s.precinct = o.precinct) GROUP BY s.street_name, s.type, s.city, s.low_range, s.high_range, s.direction, s.idnum, s.precinct, s.grp, s.type, s.post_dir, s.odd_even, s.zipcode, s.ppm_id, o.precinct, o.type HAVING  ([o.precinct] LIKE '72*' AND (o.type='62')) ORDER BY s.street_name, s.direction, s.city, s.low_range, s.type

SELECT s.idnum,s.precinct,s.grp, s.direction as dir,s.street_name,s.type,s.post_dir,s.city,s.low_range,s.high_range,s.odd_even as [O/E/B],s.zipcode,s.ppm_id FROM street AS s INNER JOIN office AS o ON (s.grp = o.grp) AND (s.precinct = o.precinct) GROUP BY s.street_name, s.type, s.city, s.low_range, s.high_range, s.direction, s.idnum, s.precinct, s.grp, s.type, s.post_dir, s.odd_even, s.zipcode, s.ppm_id, o.precinct HAVING  ([o.precinct] LIKE '72*') ORDER BY s.street_name, s.direction, s.city, s.low_range, s.type

It is however working for the following one

SELECT s.idnum,s.precinct,s.grp, s.direction as dir,s.street_name,s.type,s.post_dir,s.city,s.low_range,s.high_range,s.odd_even as [O/E/B],s.zipcode,s.ppm_id FROM street AS s INNER JOIN office AS o ON (s.grp = o.grp) AND (s.precinct = o.precinct) GROUP BY s.street_name, s.type, s.city, s.low_range, s.high_range, s.direction, s.idnum, s.precinct, s.grp, s.type, s.post_dir, s.odd_even, s.zipcode, s.ppm_id, o.precinct, o.officecode HAVING  o.officecode in('03-0060-000') ORDER BY s.street_name, s.direction, s.city, s.low_range, s.type

Dim Db As ADODB.Connection
Dim rsx As ADODB.Recordset
Set Db = CurrentProject.AccessConnection
Set rsx = New ADODB.Recordset
With rsx
Set .ActiveConnection = Db
.Source = s$ 'sql String from above depending on which search criteria
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseServer
.Open
If .RecordCount Then
DoCmd.OpenForm "Search Results"
Forms![Search Results]!List1.RowSource = s$
Forms![Search Results]!List1.Requery
Else
MsgBox "No Results Found"
End If
End With
rsx.Close
Set rsx = Nothing

The only difference that i see between the three is the HAVING Criteria.

Also all the three queries work fine outside (in a regular query outside of forms and inside of rowsource for the listboxes etc) of the recordset. So i am really stumped on this.

 

[Moniker]

adLockOptimistic will not return a correct record count, and it's coincidental that it returns correctly on one of them.

Use adLockReadOnly.

 

[RainX]

^^
Thanks for replying.
I changed that but its still showing up as empty or 0 for the record count
Do RecordSets Allow the criteria LIKE to be used as the source?

 

[Moniker]

Yes, the LIKE operator is fine.

However, you're using If .RecordCount Then as a construct, assuming anything not a 0 is TRUE. Before that line, put this:

MsgBox "RecordCount = " & .RecordCount

And see what you get.

 

[Moniker]

FYI, cleaner code to use (equivalent to yours, but cleaned up):

Dim rsx As ADODB.Recordset

Set rsx = New ADODB.Recordset

With rsx
    .Open s$,CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    If Not(.EOF) Then
        DoCmd.OpenForm "Search Results"
        Forms![Search Results]!List1.RowSource = s$
        Forms![Search Results]!List1.Requery
    Else
        MsgBox "No Results Found"
    End If
End With

rsx.Close
Set rsx = Nothing

 

[RainX]

Thanks ill give it a try!

Well its still showing up as No Results Found.

If i change
HAVING ([o.precinct] LIKE '72*' AND (o.type='62'))

To

HAVING left([o.precinct],2)=72 AND (o.type='62')

it works fine though. Any idea why?

 

[Moniker]

Change:

HAVING ([o.precinct] LIKE '72*' AND (o.type='62'))

To:

HAVING ([o.precinct] LIKE '72*') AND (o.type='62')

 

[RainX]

That didn't work either. Weird eh?


Thanks once again for helping me in trying to figure this out.

 

[Moniker]

What is the data type of precinct? There's something not right.

 

[RainX]

I believe the precinct type is number. Would that cause a problem?

Take care