SOLVED

Setting a report to use only database records from an ad-hoc filtered view

Copper Contributor

Hi - can any one help me - I'm stumped with what seems a simple thing to do with Access 365.

I have a database of newspaper articles - I use 'Filter by Form' to view only the database records I want to see.

I have a report setup to view/print but it always uses all of the records in the database not just the currently filtered ones.

I use the 'Filter by Form' on an ad-hoc basis to select the records I want but I can't work out how to get the report to use the current filtered view of the database.

I've looked at the property/database setting in the reports design which has a filter option but can't see how a lookup command might work for this.

kantblue

 

4 Replies

Hi,

 

You can pass the form's current filter on to the report using VBA. If you e.g. have a button on the form to open the report, the code in the click event of the button could look like this:

 

DoCmd.OpenReport "MyWonderfulReport", acViewPreview
Reports!MyWonderfulReport.Filter = Me.Filter
Reports!MyWonderfulReport.FilterOn = True

 

Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon

 

Hi Karl - Thanks very much for your reply. I've been thrown in at the deep end trying to help a friend - I have programming experience but not in VB.

I have tried creating a button on my Form and added the following text to the OnClick button (the report I have created is called Perpagereport).

DoCmd.OpenReport "Perpagereport", acViewPreview
Reports!Perpagereport.Filter = Me.Filter
Reports!Perpagereport.FilterOn = True

But I get the following message:
Johns Database cannot find the object 'DoCmd'
If 'DoCmd' is a new macro or macro group, make sure you have saved it that you have typed its name correctly.
Any help would be appreciated!
best response confirmed by kantblue (Copper Contributor)
Solution

Hi,

 

It sounds like you typed the code in the property line. But it belongs in the VBA editor:

 

  1. In the property window go to the "On click" property of the button. Click into the property line. Then a small button with 3 dots (elipsis) appears at the right edge of the line. Click on this small button and select the code editor from the dialog that pops up.

  2. Paste the 3 lines of code exactly where the cursor is in the VBA code editor. Save the code (e.g. with Ctrl+S) and close the code editor.

The property line should now only say "Event Procedure". Then the button will execute this procedure and you are now a VBA programmer. ;)

 

Servus
Karl
****************

Access Bug Trackers

Access News
Access DevCon

Hi Karl - that worked - THANK YOU so much for your clear explanations - I'm not going to put VBA programmer into my linkedin profile just yet :) This has given me some other ideas too - so you might get another help request - only joking (maybe!).
Thanks again for your help.

1 best response

Accepted Solutions
best response confirmed by kantblue (Copper Contributor)
Solution

Hi,

 

It sounds like you typed the code in the property line. But it belongs in the VBA editor:

 

  1. In the property window go to the "On click" property of the button. Click into the property line. Then a small button with 3 dots (elipsis) appears at the right edge of the line. Click on this small button and select the code editor from the dialog that pops up.

  2. Paste the 3 lines of code exactly where the cursor is in the VBA code editor. Save the code (e.g. with Ctrl+S) and close the code editor.

The property line should now only say "Event Procedure". Then the button will execute this procedure and you are now a VBA programmer. ;)

 

Servus
Karl
****************

Access Bug Trackers

Access News
Access DevCon

View solution in original post