Passing form filters to reports in Access 2007

[Brian]

I have a database which I just upgraded to Access 2007. ACC2007 has a very
nice feature in that you can right click fields on continuous forms and apply
multiple filters to narrow down the records on display. My question is how
to pass those filters to the report?

Prior to access 2007, using VBA, I used to open a report in design mode and
pass the forms filter to the report filter, then send the report to the
printer. It always printed correctly. But it seems not to work anymore.

Any help would be appreciated.

Brian

 

[Allen Browne]

Brian, you may find that you can put a command button on your form, and pass
the form's filter to the report like this:

Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

That should work for simple filters. There's a bit more work if your filter
uses a combo box where the display value is not the bound column. In this
case you will find that the form's Filter contains something like:
Lookup_State = "Texas"
even though the State field contains TX or a number.

Post back if you need further info about this.

 

[Allen Browne]

Brian, you may find that you can put a command button on your form, and pass
the form's filter to the report like this:

Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

That should work for simple filters. There's a bit more work if your filter
uses a combo box where the display value is not the bound column. In this
case you will find that the form's Filter contains something like:
Lookup_State = "Texas"
even though the State field contains TX or a number.

Post back if you need further info about this.

 

[eyalbd]

Hi Allen,

I saw your post in regards to filter on form. I am trying to find a solution to filtering on combox boxes but without success.

The report does not recognize lookup_ table name of combobox [lookup_cboName].[CompanyName]

Can you please help?

Thanks
Eyal