[Solved] Generate report by clicking button in form

[rainclear]

Hi,

This question is related to both Form and Report. I think it may be more relevant to Report.

I am working on a personal bookkeeping database that tracks multiple bank accounts, etc., which is attached.

I have watched many videos on Base at thefrugalcomputerguy.com. "Group 19 - Subform Filter" helps me learn how to get the desired transactions and I implemented it in "frmT_EditExistingTransactions" in the attached database.

What I want to do now is to generate an SQL query based report by clicking a button in the form. It will be similar to "frmT_EditExistingTransactions", but instead of refreshing the transactions in the subform by clicking a button, how can I generate a report by clicking a button after setting the filtering parameters? The report may need to be based on an SQL query because I want to calcuate the balance after each transaction and use the filtering parameters. Also I prefer the report to be in a spreadsheet format, but that is optional.

Do you know where there are such kind of tutorials or instructions? Or could you please briefly tell me how to do it if it is convenient for you?

Thanks,
Brian

[Villeroy]

How to filter a form by a parent form ("power filtering"): https://www.mediafire.com/file/pwbvasav ... r.odb/file
Open the form, apply a filter, open the report

Unfortunately (and surprisingly), there is no way to open a form or report without macro code.

Code: Select all

Sub Open_Report_Button(e)
REM specify the hierarical name in the button's "Additional info" field
	sName = e.Source.Model.Tag
	OpenEmbedded(ThisDatabaseDocument, sName, bReport:=True)
End Sub

Sub Open_Form_Button(e)
REM specify the hierarical name in the button's "Additional info" field
	sName = e.Source.Model.Tag
	OpenEmbedded(ThisDatabaseDocument, sName, bReport:=False)
End Sub

Sub OpenEmbedded(odb, sHierachicalName$, bReport As Boolean)
	if bReport then
		container = odb.ReportDocuments
	else	
		container = odb.FormDocuments
	endif
	obj = container.getByHierarchicalName(sHierachicalName)
	obj.open()
End Sub

[rainclear]

Thanks, Villeroy. Automatically updating the report should be good enough for me now. I will study the file you provided.

[Villeroy]

Power filtering recipe:
SELECT * FROM "Filter" WHERE "ID"=0 selects one particular row from the table which stores filter criteria.
The filtering main form is bound to that row with the following settings:
-- no navigation bar for single record.
-- disallow row deletion
-- disallow row insertion
-- allow modification only, so the user can't delete the criteria row or tab into another row.
The subform links corresponding master and slave fields.

Because the filter is stored in one particular table row, it is easy to query the relevant source data for the report or for another form.