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
It is however working for the following one
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.
Here are the two it isn't working for
Code:
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
Code:
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
Code:
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.
Last edited: