Filter report using VBA

[daSpud]

I am new to Access and cannot find an example for what I want to do.



I would like to run a report that reads a table record by record and triggers the detail band when certain complex criteria are met. I wanted to use VBA to examine each record and say yes or no. If yes then allow the detail to print. The fields to be printed are not directly in the table but are combinations of table fields.

I appreciate any clues as to what to do. None of the Events for the report seem to get triggered for each record. On Current only triggers when I click on a detail band of an unfiltered report.

Thank you.

[ranman256]

Not sure how you're trying to complicate this....
make a query that has all the tables needed, the report then uses this.

[pbaldy]

I agree with using a query to filter the records, perhaps with VBA applied there. If it really needs to be done in the report, you can use the detail section's On Format event, toggling the Visible property of the detail section.

[daSpud]

I agree with using a query to filter the records, perhaps with VBA applied there. If it really needs to be done in the report, you can use the detail section's On Format event, toggling the Visible property of the detail section.

I don't want a query. I am taking over a project and there are tons of queries for every little thing. I would like to have one report with programming logic to determine the data content.

On Format does not trigger with each table entry. It goes through the entire report without stopping at the msgbox in On Format.

Maybe I should just have one query and like you say use the VBA there and the report always prints 100% of the query results. I will try that. Thank you

[pbaldy]

The detail format event fires in preview and print modes, a different event can be used for report view. I tested code in the format event in preview mode earlier and it would correctly hide a record as desired. If it's not working for you, perhaps you could post the code, or the relevant part that would hide the record.

That said, if you do it the way you describe you're pulling all records across the network and then filtering, as opposed to letting the database engine do the work and only pulling desired records (though if VBA is doing the filtering, that advantage is lost).

[daSpud]

The detail format event fires in preview and print modes, a different event can be used for report view. I tested code in the format event in preview mode earlier and it would correctly hide a record as desired. If it's not working for you, perhaps you could post the code, or the relevant part that would hide the record.

That said, if you do it the way you describe you're pulling all records across the network and then filtering, as opposed to letting the database engine do the work and only pulling desired records (though if VBA is doing the filtering, that advantage is lost).

Thank you for the help. I am learning things.

There are 170 records in the database and it is run on the local PC so there is no volume and no network to cross.

I have not worked on hiding anything yet. I just wanted to see if I could see the records as they were processed and it appears not. But maybe I am not using the right terminology being new to Access since you talk about different modes in the report.

In the bottom left corner of the report screen it says "Report View". That was where I was. I tried Print Preview and format works, but I am not seeing the table fields. I get an error Object Required. https://www.screencast.com/t/z9aNW0t0qnqv

[pbaldy]

Just refer to the name of the field in the report's record source. If the field isn't in the record source, you can't refer to it directly. If you're still stuck, can you attach the db here?

[daSpud]

Just refer to the name of the field in the report's record source....

That is what I thought I was doing, but clearly Access has other ideas. https://www.screencast.com/t/7HEpujXds5 I tried with and without brackets and without table qualifier. Got message "cannot find the field Lot_No referred to in your expression"

[isladogs]

It seems a very odd idea to show a message box on a report format event
Not sure I understand the point but I think the issue is the use of '+'.
Instead try

MsgBox "on format " & [Lot_No]

[daSpud]

MsgBox was only to verify that the code was executing. It will not be in production. In addition I was having trouble with the naming convention for fields.

I got mixed up with concatenation from another language. That said changing to "&" didn't help a lot. Now it says cannot find field "Lot_No" when I tried Lot_No and [Lot_No] and cannot find field "|1". when I used [tblHomeOwner]![Lot_No]

If I leave off the msgbox the report runs and gives me the 30 records that have no email. That is out of the 170 records with lot numbers under 171 And you can see that Lot_No is referenced and used in the filter.

In order to add the rest of the logic to do the filtering, I need to know how to reference the fields in the table.

[pbaldy]

Can you attach the db here?

[daSpud]

Thanks, but unfortunately not. It contains names, phone numbers and addresses. Maybe later I can create a copy with altered data, but not today I think.

[pbaldy]

I understand. Make sure the field is in the report's record source.

[daSpud]

I understand. Make sure the field is in the report's record source.

That is the crazy thing. It is. Lot_no is in the report filter and it works. I get the correct records. I need to filter more and then build the label info.

Speaking of label info. Can I ask how to define "calculated" fields for the report and then fill those fields from VBA? Access is a mystery. Maybe "calculated" is not the right name for them. I want to have strings that I can puton the report but ath do not exist in any table. I will fill them from programming.

[pbaldy]

Is there a control for it? My extremely brief test used a control that had the value:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  If Me.ResID = 3 Then
    Me.Detail.Visible = False
  Else
    Me.Detail.Visible = True
  End If
End Sub

I'm not clear on exactly what you're trying to do. You can put literal text in a report textbox with:

="your text here"

you can combine that with field values by concatenating:

="Dear " & [FirstName] & ", thank you for blah"

You can do similar things with VBA. Knowing more about what you want to do may help guide us to the best solution for you.