Report Filter Issues

[PNGBill]

Hi Forum, access 2010 accdb.

A Report has an option to be emailed by DoCmd.SendObject which means we can not use the normal Where clause to filter the data.

I got around this by using TempVars and checking for same in the Report Open Event.
A filter will then be used if so required.

This worked fine for a single report but is throwing error 3097 claiming the field could refer to more then one table.
The Report SQL does not indicate this to be the case.
The report has a sub report - could this be the issue ?

I resolved the issue by changing the record source instead of a filter.
The alternative record source is the same as the original one except for the criteria on one field that refers to the TempVars.

If the filter had a clash, why then doe the sql not throw the same error ?

If the sub report is the issue, again why does the record work ?

Could it be something to do with the timing of the filter and the subreport that isn't a case when you change the record source ??

 

[vbaInet]

When you apply the filter, include the table name and see if this resolves the problem:

TempVars.TempID = "[COLOR=Red]TableName.[/COLOR]FieldName = " & somevalue

Also add DoEvents right after SendObject

 

[PNGBill]

Thanks vbaInet but doesn't seem to work. Still throws 3079 error - didn't use DoEvents

Filter should be

Me.Filter = "TBLOAN.LDPK = " & TempVars!LoanDocRef
Me.Filter = True

This method works for a "simple report but throws error 3079 for the merged report.
I wonder if the subreport sql is causing the problem ?

I havn't used DoEvents so not sure how to do so. Will this slow the system down and allow the filter to work ??

By replacing the main report sql the problem is resolved by but why the filter won't work

 

[vbaInet]

Can you throw us a sample db?

It doesn't slow it down it just gives control to the processor for a very short while. So in your case, it will allow it complete the SendObject in enough time.

 

[PNGBill]

Can you throw us a sample db?

It doesn't slow it down it just gives control to the processor for a very short while. So in your case, it will allow it complete the SendObject in enough time.

Hi vbaInet,
Took quite a while to get a sample that replicated the issue as the report requires data from many tables. see attached two zipped files.

frmLoanIssueData. click Loan Agreement ... and you should get an error #3079.

Appreciate any advice on why the filter will not work.

I can't use Where clause because the report needs to open with it's own filter so DoCmd.SendObject can email.

 

[vbaInet]

Interesting little error there PNGBill. The problem was coming from the SQL statement used in the report. It's normally adviseable to use queries as record sources instead of SQL statements because the query builds and qualifies the fields before presenting it to the object. So the Filter was finding it difficult to find the field (even though it was properly referenced). It seems your SQL statement was a bit too complex for it to handle or the report got corrupt.

So the resolution here was to move your SQL statement to a query and set this query as the record source of your report. In effect LDPK now became part of the query so there was no need to qualify the field name.

A few things I noticed or did:

Private Sub Form_Current()
'Get details for the loan to be issued...
    sbPopulateLoanIssueHeader
  
End Sub

Is causing your form to be in edit mode when it loads.

I moved the On Open event of the report to the On Load event. When you're dealing with data it's the Load event you need. The Open event is useful for setting properties of controls that are non-data related.

 

[PNGBill]

Thanks vbaInet, Appreciate your advice and effort.

I guess the "rule" is to use inbedded SGL's where possible in Forms and Reports but be aware that it may not always be possible.

To have all Forms and Reports based on Queries would cause a mass of clutter

I did have one or two other situations where the sql was so big it just wouldn't fit but this example is the first, for me, where it does fit but won't work.

I need to continue learning the differences in the Events available.

One issue I didn't mention.. did you notice the third blank page.
This occured when upgrading from 2000 to 2010 and I just can not find what is causing it.

Any ideas ?

Shame access doesn't have a way to extract part of it for viewing without having to include all the connected bits.

Imagine if you have to send your lounge furniture to the repair shop when your tv is in for service (as if you can get them serviced now days)

 

[vbaInet]

I didn't notice the extra blank page. It's two pages on mine.

Check the Page Setup and compare the settings to that of an A4 page. Also ensure the paper size is A4.