Base: How to make a form-based filter apply to the subform as well

English
, ,
#1

I display the list of payments and receipts in a tabular control on a form based on a table.

I want to display the totals of the two columns at the bottom.

If I apply a form-based filter to display only a subset of the entire table, I want the totals at the bottom of the form to display the totals only of the items displayed.

The main form can not be based on a query because I want the option of changing the data displayed. I realize that I would then have to refresh the form for the totals to be adjusted.

#2

Hello,

Going to say that this may be an incorrect statement:

I want the totals at the bottom of the form to display the totals only of the items displayed.

Seems you are displaying the payments & receipts in a grid. Would think you want a total of all in this grid whether currently displayed or not. Grid may not be large enough to display all at once.

Will base this on all available in grid.

All that is needed is to create another form - sub sub form as it is. Your main form, then a selection there produces records in a sub form. Now place another form below that as a sub sub form. The basis is a query which contains totals for all records in the Form. The sub form totals are displayed via link of the ID.

The best way to understand is a sample. The sample has only one total but it is simple enough to create as many as needed. See → Form w Subform, inserting, updating,deleting subform results

The Query used is TotalQuery and the form is Guests_Equipment_Rentals.

#3

I am amazed at how quickly you responded. Thank you. I will look at your solution as soon as I can and will let you know how I do.

#4

With the Rentals.odb, it is clear that the total to be displayed is the sum of the costs for a particular guest, so the query groups on the guest ID.

In my database, I have a table of transactions. I bought something or I received income. The table control on the form displays all of the transactions (with a scroll bar so I can see them all.) I can easily display the sum totals of all of the receipts and payments. Using a form-based filter I can ask that the form only display receipts from a particular vendor, for example. I would like the total of those receipts to be displayed at the bottom of the form. Is there a trick to identifying the group that I’ve created so I can see the total for that group? Or is there another way to approach this problem?

Thanks.

#5

@wyomason,

Should be a Query based upon the same methodology used to get the receipts for the vendors. So it would seem if the selection is by Vendor, the Query should be totals by Vendor & use that as the link.

Only other way to help make this clearer is for you to post a sample so this can be applied. Doesn’t sound any different than what was presented in Rentals.odb - just maybe another level down.

#6

Form Based Filter zip file

I have put together 6 screen shots into a Zip file which you can download and open from the link above. These screen shots show a Cash Records form which is what I am currently trying to get to work where all of my cash transactions are listed when the form is opened. At the bottom I have placed a text box to show the total of all cash receipts. Screen shot #2 shows the Form Navigator for the form. Screen shot #3 shows the Form Based filters icon selected. When the icon is clicked, the Filter Navigator panel opens (Screen shot #4), which shows how I am asking for the data to be filtered. In this case, I want to see all cash transactions with “A-1 Services”. Screen shot #5 shows the “Apply Filter” icon selected. When I click on that icon, the results are displayed (Screen shot #6). However, the text box at the bottom is showing the total for all cash records instead of the total for the records displayed

#7

@wyomason,

Images are NOT a way to determine where a problem lies. It does not provide what SQL you are using, how you have linked the form and sub form, table names, fields names and more.

This is a bit of guessing and certainly the names are probably incorrect, but the SQL (Query) for the sub form is:

SELECT "To_from", SUM( "Cash Receipt" ) FROM "CashRecordsTable" GROUP BY "To_from"

Then you link “To_from” in the sub form to “To_from” in the form.

Testing with my personal data reveals no problem.

#8

Thanks again for your reply and trying to help me. My question is, how do you use the form-based filter to create the SQL query you suggest? I don’t want the user to have to write SQL statements. Rather, I want them to have the ability to use the form-based filter GUI interface.

#9

The Query is written just once. It works for any selection made in To_From field.

Should also note, when there is no filtering applied, the total amount is based upon the selected record.

#10

The form-based filter allows you to use any of the columns to construct your filter. So I may want to filter using the To_From field as well as the date field, for example. What would your query look like to include that possibility?

#11

Involves writing macro.

#12

Oh, No! Well, I’ve had to compose a few macros already to get my program to work the way I want. Are you aware of any examples I could study to see how they could be adapted to work in my program?

#13

Don’t know of example. Would need to take filter information from selection and apply to macro SQL statement. This can become complex depending upon the depth of different fields selected.

#14

Although it requires some testing, upon a change in the form (filter was applied), read the form property for the filter applied and use that in an SQL statement in the macro such as:

SELECT "To_from", SUM( "Cash Receipt" ) FROM "CashRecordsTable" WHERE " & selected filter information here & "GROUP BY "To_from"

Execute the SQL in the macro and move the result into the control on the form.

#15

Thank you for understanding what I am trying to do and providing a proposed solution. I will have to determine where I want to go from this point. I will consider the question answered.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.