Solved Data from form not showing on Statement of Account report (1 Viewer)

[GaP42]

I am struggling with getting a report populated with all required data and it may be due to a number of issues or the approach being used.
I am trying to develop a Statement of Account report. The Person is selected using a form where the year and month of interest is selected.
The Statement of Account report includes a subreport where the transactions for the month are shown based on a union query.

1. I found that the only way I could get the subreport to display all the required transactions was to open a form (immediately prior to opening the report) paralleling the report with a subform containing the same transactions. I presumed/rationalised this as due to the order in which the report loads - the subreport loading before the report - and hence not having the parameters/values for the month/yr needed to select the records to display. The form can / will be hidden. But is this a typical approach to this problem?

2. On the report I also have a value for the balance for the month prior. This is calculated based on 2 subforms on the "hidden" form, using two summation queries - one for invoices, one for payments - and then calculating the Previous Balance as a control on the form. The control for the previous balance on the Statement of Account report then refences the control on that form. The problem is that the Statement of Account Outstanding Balance value is always $0, until refresh all is used (or go from PrintPreview to Report view) , which then shows the value from the form. I would like to present the report to the user in Print Preview mode (and automate saving to pdf), in which case Refresh all is not available.

To illustrate:
Before refresh:

After Refresh:

There is a third problem - which only occurs after refresh: the display of the Statement of Account period disappears:
Before:

After refresh or change from print preview to Report view

Hmm!? Hints / suggestions
Thanks

This is from the rough and ready form that opens before the statement:

 

[plog]

Reports shouldn't be that complicated--I don't mean that in a sarcastic, figurative, derogatory way. I mean this report sounds like it has a lot of timing and sub-systems required to get it to produce what you want, it literally is complicated. There has got to be a way to just click a button on your form and open it to what you want without helper forms or UNION queries or refreshing or other hacks or unnecessary parts.

I think we need a copy of your database. Can you strip down a version and upload it? Leave all your tables in it (although you can populate it with dummy data if needed) but delete all unnecessary queries, forms and reports so we can easily see the parts to work with.

 

[GaP42]

Thanks @plog - I'll get to it - may take a little while to prepare: obfuscate/clean/check.

 

[Gasman]

Randomizing Data for Posted db's

We often ask Original Poster to upload a copy of their db but the issue might be that it contains sensitive data. Complete data removal may not allow us to troubleshoot the OP's issue because queries, forms, etc. need records in order to observe or trouble shoot the problem. Here's a...

www.access-programmers.co.uk

 

[ebs17]

It is correct that a subreport is loaded before the main report. Therefore, passing filter parameters to the subreport or its RecordSource becomes a little more difficult, since you only open the main report specifically.

There are different ways to solve these problems. I describe one way of doing this below.
The idea is to make yourself independent of the sequence of events described above and to immediately give everyone the information that they need immediately for their own activity. This can be implemented using public functions (also using TempVars, but I don't use them).
RecordSource: The filter is built into the query:

SELECT * FROM Tab WHERE DateField BETWEEN fctStartDate() AND fctEndDate()

For this purpose, the functions are defined in a standard module, which simply accept and pass on content from form controls:

Public Function fctStartDate()
     fctStartDate = Forms.YourCallingForm.txtStartDate
End Function

In the same way, you can pass information from the form to report controls. The form just needs to remain open here, and of course only checked and validated values should be offered for retrieval.

 

[GaP42]

A cut down version of the BE and FE db is attached as zipped. 64bit / Access 2016. Relinking is required.
After unzip, open the CHK-FE.accdb - after the splash screen the menu is displayed: Select System Admin, then System Control button. Then [Change db Connection] to locate the CHK_BE.accdb.

CHK-FE is too large despite being chopped down - zip file is 10.6MB - don't know how close it is to being accepted.

 

[GaP42]

Still unable to upload the FE - less than 10MB zip after heavy pruning mods. Carried out decompile / compact. Don't think there is any more pruning to do.

 

[GaP42]

And then you do some really radical hacking..
As advised above, relinking required to BE.
Had to remove form(s) to facilitate creation of sales and payments, however the statement against existing records can be done.
Open the Generate Statement Report
- to locate a relevant customer record use the following and click on the hyperlink for Snowy BEA. Change year/month as required to generate the statement - with the issues described earlier.

Thanks for any assistance rendered.

 

[June7]

Not what I would call a cut-down db, minimized to issue of interest. Crashed my computer first time I tried to open. Then I used shift-key bypass to open. Re-linking tables was not working so I deleted all links and re-established.

Now I get errors about not able to open png files. I see a doubled \ in png path. I modified code to eliminate error.
C:\Users\Administrator\June\Forums\\01_Graphics\

Can a Person have multiple PersonDetails records? If not, why split into 2 tables?

frmStatementofAcct opens with a warning that required parameters have not been provided and report might not be valid. So what steps should I follow in the process of generating report?

 

[GaP42]

Thanks @June7 - apoogies fro the broken links.
In some circumstances the historical person details are important - although not in this implementation.
the from for generating a statement of account requires the person and the year/month to be selected at the top of the form - clicking on the person name in the form passes the criteria to the report. see the screen grab above:
Filter the Customer list
Select year
Select month
Click on the customer

 

[June7]

Are you familiar with Nz() function?
This
=IIf(IsNull([frmsubRptStatementPrevBal].[Form]![TotPriorPurchases]),0,[frmsubRptStatementPrevBal].[Form]![TotPriorPurchases])
could be
=Nz([frmsubRptStatementPrevBal].[Form]![TotPriorPurchases],0)

"Open the Generate Statement Report" - I cannot find a form or report by this name. I did find frmAccountYrMonth which appears to be the form in image. No "Snowy BEA" but there is "Snowy BIRK". That does generate report without warnings and numbers are showing in PrintPreview. So, at this point, not sure what issue is.

Instead of referencing main report for start balance, why not do what the main report does - reference form. Or instead of referencing form, pull from table or query. Most examples I see for starting balance use domain aggregate function - DLookup() or DSum().

 

[GaP42]

frmAccountYrMonth is the form - accessed from the startup menu - that is used to capture the criteria to submit when opening the report.
Re Nz, yes - can't recall if a I tried it there earlier. Will check again.
The report does reference the form for the start balance? Will also look at using DSUM for the aggregate of starting balance (x2) - Purchases and Payments.

 

[June7]

Main report references form for start bal, subreport references main report.

Here's another trick - a shortcut for referencing main report:
=[Parent]![BalanceStart]+[Amt]

 

[June7]

Dang, I swear that report opened straight to PrintPreview with start bal from form but now it's not. Will look at this some more tomorrow.

 

[GaP42]

That does generate report without warnings and numbers are showing in PrintPreview. So, at this point, not sure what issue is.

The OP indicated the issue = it was not an error message - principally the starting balance always showed $0 initially, even tho a subsequent refresh the showed the correct balance..
gotta do some other stuff right now - will check out suggestions tonight i hope.

 

[CJ_London]

Have you considered using a single query for your report and use report grouping?

A (cutdown) query might be something like this (assumes your form statement date is the end of the month)

SELECT FirstName,
LastName,
Reg_Address,
Phone1,
iif(TranDate<Forms!MyForm!StatementDate-day(Forms!MyForm!StatementDate)+1,Forms!MyForm!StatementDate-day(Forms!MyForm!StatementDate)+1 AS tDate,
iif(TranDate<Forms!MyForm!StatementDate-day(Forms!MyForm!StatementDate)+1,"Opening Balance",TranDetail) AS Detail,
TranAmt,
(SELECT sum(TranAmt) FROM tblTransactions AS T WHERE T.TranDate<=tblTransactions.TranDate AND  T.ParticipantFK= tblTransactions.ParticipantFK) AS Balance
sum((iif(TranDate<Forms!MyForm!StatementDate-day(Forms!MyForm!StatementDate)+1,Amt)) AS OpeningBalance,
sum(TranAmt) as ClosingBalance

FROM tblParticipants INNER JOIN tblTransactions ON  tblParticipants.ParticipantPK =  tblTransactions.ParticipantFK

WHERE TranDate<=Forms!MyForm!StatementDate AND tblParticipants.ParticipantPK=Forms!MyForm!.ParticipantPK

GROUP BY
FirstName,
LastName,
Reg_Address,
Phone1.
iif(TranDate<Forms!MyForm!StatementDate-day(Forms!MyForm!StatementDate)+1,Forms!MyForm!StatementDate-day(Forms!MyForm!StatementDate)+1,TranDate),
iif(TranDate<Forms!MyForm!StatementDate-day(Forms!MyForm!StatementDate)+1,"Opening Balance",TranDetail),
TranAmt,
(SELECT sum(TranAmt) FROM tblTransactions AS T WHERE T.TranDate<=tblTransactions.TranDate AND  T.ParticipantFK= tblTransactions.ParticipantFK)

FirstName, LastName, Reg_Address, Phone1 OpeningBalance and perhaps ClosingBalance go in the group header, the rest in the detail section

 

[GaP42]

Thanks @CJ_London - my SQL is not as "strong" as yours, and so development was in steps towards the goal. However, there appears to be, in your SQL, some misunderstandings re the db tables.
Participants at events are not the source of the orders, although a later development will be to generate orders and invoices from participants (with some intervention /selection). Anyone in the Persons table can be the source of an order.
A transactions table does not exist: although the query supporting the subreport/subform supports this. The transactions are the combination / union query - of orders and payments.

 

[CJ_London]

I was demonstrating the principle, substitute as required. change participants to persons, transactions to your union query, myform to the name of your form, etc.

As a principle of a more efficient design, you might want to consider a transactions table instead of separate tables for orders and payments but going into the whys and wherefores are outside the scope of this thread. Sometimes it's not possible and you way is the way to go

 

[June7]

Quick fix:

1. textboxes on report - can be not visible:
=Nz(DSum("PmtAmt","tblPmt","Format(PmtDt,'yyyymm')<'" & Format([TestDt],"yyyymm") & "' AND PmtByID=" & [txtPID]),0)
=Nz(DSum("TotalExtdPrice","tblOrders","Format(OrderDate,'yyyymm')<'" & Format([TestDt],"yyyymm") & "' AND PurchasedByID=" & [txtPID]),0)

2. BalanceStart calculation: =[OrderStart]-[PmtStart]

3. This almost removes frmStatementOfAcct from the process. Get a couple of popups if it is not open. These reference textboxes for date range criteria in query. Suggest modifying this to refer to frmAccountYrMonth instead.

 

[GaP42]

I was demonstrating the principle, substitute as required. change participants to persons, transactions to your union query, myform to the name of your form, etc.

As a principle of a more efficient design, you might want to consider a transactions table instead of separate tables for orders and payments but going into the whys and wherefores are outside the scope of this thread. Sometimes it's not possible and you way is the way to go

Appreciated - will investigate