Form filtering question

[chrish47]

Hi,

I have a form setup (Journal Header) with a subform (Journal Lines), and I am trying to apply a filter to the form via a macro, to only show records where the Journal Amount from the Header doesn't equal the sum of the Journal Line amounts.

I have a query that summarizes the header and line amounts and shows the difference (most are zero), I then built a query with all fields from the form and joined it to the first query by the Key, so that only the rows that have a difference show up. However when I apply this query to the form as a filter (in a Macro), I am still seeing ALL the records without any filtering.

Am I doing something wrong here, or is there a better way to accomplish this? I have also tried adding a field for the difference to the form, but always end up with an #ERROR or ?NAME rather than the actual difference.

I want the same form to be usable for different purposes, but to do that I need to be able to apply filters to the form for specific purposes (like filling out the journal lines that are missing - which would be the lines where the sum of the line amounts doesn't = the header amount).

Any help would be greatly appreciated.

 

[SydneyGeek]

Chris, to clarify:

Do you want to filter the main form or the subform? Filtering the main form is straightforward but filtering the subform is a bit tricky. I usually get around displaying different subform data by having multiple subforms, superimposed on each other. I then use buttons to toggle their visibility. As an example:

Using generic names, let's say you have frmMain with subform subAll (displays all related records) and you also need subNoMatch (displays only those records where the header detail doesn't match). Do this:
Create a copy of the query on which subAll is based. Call it qry[the name you used]_filter . In the Criteria row below the Amount field, enter <>Forms![frmMain]![TheFieldNameInTheMainForm]. Save.
Duplicate subAll by CTRL-dragging its icon, name it subFilter, and change the record source to the filter query. Save.

Now... in Design view on the main form, select the subform. Check its name in the Name box on the toolbar -- write it down. Drag and drop subFilter. You'll know that Access has connected them because the form goes into reverse video. Click the Properties, and set the Master and Child fields to the ID fields that you will use to synchronise the forms. Get this sub form and the original one to EXACTLY overlay each other. Check the name of the second subform as above.

Finally, you need 2 buttons. Turn the control wizard OFF. Drag a button onto the main form, and position it just above the subforms. Change the caption to suit (Show All). Right-click, select Properties, go to the Events tab, and double-click the blank line for On Click. You'll see [Event Procedure]. Click the Builder button (...) to go to the code screen.

Enter this code:
[subAll].Visible = True
[subFilter].Visible = False

(use the names that you wrote down).

Another button with the caption Show Mismatches, will have code like:
[subAll].Visible = False
[subFilter].Visible = True

Once it's working, the concept can be extended to several subforms. Just make sure that the code on the buttons toggles the visibility of EVERY subform.

Denis