Filter on Recordset not working

[DubboPete]

Hi all,

thanks to Allen Browne's help, I managed to get a recordset from an
option group, for a report.

I am trying to further filter the report down to a department number
(FacilityID). There are two departments I am trying to isolate, nos 18
and 19. They are the only ones to have records matching the option
group criteria [Information Management]. I am just trying to split
into two reports and show them separately.

This is what I thought would work, but for some reason it shows all
records.

The form starts with a combo box of all departments numbered 1-24
After update of combo box user selects from Option Group.
User then selects to preview report.

It should split the 42 matching [Information management] records down
to 27 records for [facilityID] 18, and 15 records for [facilityID] 19.

Heres the code:


Dim db As Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblActivityResultsEQuIPFunction",
dbOpenDynaset)

Dim strField As String
Dim strWhere As String

With rs
Filter = "[FacilityID]= " & Me.Combo38.Value & ""
Set rs2 = .OpenRecordset

With rs2
Select Case Me.[Frame9]
Case 1
strField = "[Continuum Of Care]"
Case 2
strField = "[Leadership & Management]"
Case 3
strField = "[Human Resources Management]"
Case 4
strField = "[Information Management]"
Case 5
strField = "[Safe Practice & Environment]"
Case 6
strField = "[Service Delivery (Area Office only)]"
End Select

If Len(strField) > 0 Then
strWhere = strField & " = True"
End If

Me.RecordSource = "Select * from TblActivityResultsEQuIPFunction
where " & strWhere

DoCmd.OpenReport "RptEquipFull", acViewPreview, , strWhere
End With
End With
End Sub


Any clues peoples?

DubboPete

 

[Wolfgang Kais]

HelloDubboPete.

thanks to Allen Browne's help, I managed to get a recordset from
an option group, for a report.

I am trying to further filter the report down to a department number
(FacilityID). There are two departments I am trying to isolate, nos 18
and 19. They are the only ones to have records matching the option
group criteria [Information Management]. I am just trying to split
into two reports and show them separately.

This is what I thought would work, but for some reason it shows all
records.

The form starts with a combo box of all departments numbered 1-24
After update of combo box user selects from Option Group.
User then selects to preview report.

It should split the 42 matching [Information management] records down
to 27 records for [facilityID] 18, and 15 records for [facilityID] 19.

Heres the code:


Dim db As Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblActivityResultsEQuIPFunction",
dbOpenDynaset)

Dim strField As String
Dim strWhere As String

With rs
Filter = "[FacilityID]= " & Me.Combo38.Value & ""
Set rs2 = .OpenRecordset

With rs2
Select Case Me.[Frame9]
Case 1
strField = "[Continuum Of Care]"
Case 2
strField = "[Leadership & Management]"
Case 3
strField = "[Human Resources Management]"
Case 4
strField = "[Information Management]"
Case 5
strField = "[Safe Practice & Environment]"
Case 6
strField = "[Service Delivery (Area Office only)]"
End Select

If Len(strField) > 0 Then
strWhere = strField & " = True"
End If

Me.RecordSource = "Select * from TblActivityResultsEQuIPFunction
where " & strWhere

DoCmd.OpenReport "RptEquipFull", acViewPreview, , strWhere
End With
End With
End Sub

Why the recordsets and db variables? Try this instead (untested):
'--- Code ---
Dim strField as String
Dim strWhere as String
strWhere = BuildCriteria("FacilityID", dbLong, Me.Combo38.Value)

Select Case Me.[Frame9]
Case 1
strField = "[Continuum Of Care]"
Case 2
strField = "[Leadership & Management]"
Case 3
strField = "[Human Resources Management]"
Case 4
strField = "[Information Management]"
Case 5
strField = "[Safe Practice & Environment]"
Case 6
strField = "[Service Delivery (Area Office only)]"
End Select

If Len(strField) > 0 Then
strWhere = strWhere & " AND " & BuildCriteria(strField,dbBoolean,
True)
End If

DoCmd.OpenReport "RptEquipFull", acViewPreview, , strWhere
'--- End Code ---

And you should make variable declaration mandatory (Tools, Options),
It seems to me that you have missed a dot before "Filter = ..."

 

[DubboPete]

Wolfgang

Schön !

it worked great mate. thank you for your help. Now I just need to work on
error_message handling....
Just a couple of comments on your comments...

Why the recordsets and db variables?

Recordsets and variables appear because this code has been built slowly
using different bits of code from many sources, to come to this result.

It seems to me that you have missed a dot before "Filter = ..."

A dot before filter? I think I will pass on that one now !

Regards,
Wolfgfang

danke

Peter