Sub Report vba restricted

[mveijndh]

After I've added an invoice, I need to create a report sending the Invoice details to the accountant. In the report I need to mention the percentage invoiced to date, not including the last one. This is on a subreport, but I need to prevent the last invoice to be added to the list. I know what I need to limit and to what value, as I have the InvoiceId value, so if I limit the query to <InvoiceID, it does not show or sum the last invoice, job done. I just don't know how to limit the subReport as that query needs to be set with a Where clause when the main report is opend.
Anyone??

 

[sneuberg]

The DoCmd.OpenReport https://msdn.microsoft.com/en-us/library/office/ff192676.aspx has a where condition parameter. Have you tried putting your condition there?

 

[mveijndh]

Hi,

I knew that one, but that filter has to go on the Sub report, embedded in the report. How can I write the filter on that one?

Kind regards,

 

[sneuberg]

Maybe a filter in the subreport would do it. Let's say you have the last invoice id in a text box named InvoiceId on a form name frmInvoices, then in the sub report's load event trying putting in.

Me.Filter = "InvoiceID < & Forms!sfrsInvoices!InvoiceID
Me.FilterOn = True

If you can't get that working upload your database and I'll see what I can do. If your database is large or has sensitive info take a look at http://www.access-programmers.co.uk/forums/showthread.php?t=276413

 

[mveijndh]

Hi, the filter did not work, I'd put this in the subReport, but this prohibited the report to populate the fields.

 

[sneuberg]

This probably needs some tweaking or may even need to go in the main report in which case the references would be different. How about uploading your database. As long as the last InvoiceID is available on some open form, I'm pretty sure I could get something working for you.

 

[mveijndh]

Hi pitty, It's a pretty extensive database with a lot of tables. The requisation uses many of them. I've read the guidlines, but that would mean a lot of work.

 

[mveijndh]

How do I upload the database??

 

[sneuberg]

Hi pitty, It's a pretty extensive database with a lot of tables. The requisation uses many of them. I've read the guidlines, but that would mean a lot of work.

In that case hold off for now. I think I can come up with something easier to implement than this filtering crap. This is just a matter of excluding the last record in the record set for the subreport where by last I mean one with the largest InvoiceID. Right?

I'll get back to you soon.

 

[sneuberg]

First if the subreport's record source is a table rather than a query you will need to make it a query. That's easy just make a query based on the table. Now with this query you can exclude the last InvoiceNo by using DMax in the criteria for the InVoiceNo something like:

<DMax("[Invoice]![InvoiceID]","[Invoice]")

as I have done in the attached simple database.

 

[mveijndh]

Hi,

That's correct, I need all invoiced but the last as that one is set in the module calling the report!

 

[sneuberg]

How do I upload the database??

If it comes to that look at http://www.access-programmers.co.uk/forums/faq.php?faq=vb3_reading_posting#faq_vb3_attachments

 

[sneuberg]

Hmm, is this a multiuser system? If so my last suggestion might not do it as it excludes the last invoice and not the one set in the module. If another user adds an invoice through some other means the last one won't be the one set in the module.

 

[mveijndh]

Hi,
I'd put in <DMax([tblInvoice]![InvoiceID];[InvoiceID]) in the query criteria field for the InvoiceID, but that's generating an error the database can't find the table of -query 1 all up to -query8 Their are 8 fields in the query.
https://www.dropbox.com/s/tsl388txrpot96r/queryDesign Dmax.jpg?dl=0

It's not multiuser, but I can't sent the database as the attachment has to be smaller than 2 mB it's 4 already, compacted and backed up.

 

[sneuberg]

Sorry I should have explained to you that you need to adapt that Dmax, which was from the posted database, to what's in your system. It should be

< DMax( "[fieldnameofInvoiceId]" , "[tablenameofinvoices]")

where fieldnameofInvoiceId and tablenameofinvoices are whatever they are on your system.

 

[mveijndh]

Hi,
This worked perfectly!! thanks.

 

[sneuberg]

The entire report or just the subreport part of the report? How is the subreport query (Record Source) connected with the query (Record Source) of the main report. Maybe if you upload some screen shots of the queries in design view and also screen shots of the tables involved in design view I might see something.

 

[mveijndh]

I's changed the wrong query. I had to change the query of the SubReport, not the Report