Filterinfg split forms

[idealman]

Hi, I have a form which I can enter customer transaction details and below
the datasheet (split form) shows all transactions. The form has a combo box
to select a customer for each transaction. Is it possible to filter the
records in the datasheet by the selection in the combo box to display only
one customers transactions

 

[Allen Browne]

Could you use a separate unbound combo on the main form to perform the
filtering? I think that would be a less confusing interface than trying to
use one combo for both data entry and also for filtering.

The key concept here is that a split form has one filter, and it is applied
to both the Form view and the Datasheet (so stepping through the records in
the Form part of in the Datasheet part gives the same results.) So, you can
apply a filter to the form, and the datasheet will show that. However, there
are problems with that:

a) There is a timing problem with applying the filter. The filter cannot be
applied until the current edits are saved. If you use (say) the AfterUpdate
event of the Customer combo, you are forcing the record to be saved, and
that may not be appropriate. For example, there might be another required
field that has not been filled in yet.

b) Applying the filter reloads the data, which takes you back to the first
record. You would therefore have to save the primary key value before
applying the filter, and then FindFirst in the form's RecordSetClone, and
match the Bookmark. This might work okay if there's a limited number of
transactions (just a few thousand), and a very limited number of users, but
would not be a good design in a multi-user database shared across a network
with lots of transactions.

Another possibility might be to use a form and a subform, where the
LinkMasterFields refers to the Customer combo, and the LinkChildFields
refers to the customer field in the subform. This would work as soon as you
change the customer, even before the record is saved.

 

[idealman]

As the form is based on a query would it be more practical to filter the
query results, if so how would i do this

 

[Allen Browne]

If you provide the extra (unbound) combo to do the filtering, you can refer
to it in your query.

The criteria row in query design will read something like this:
[Forms].[Form1].[Combo0]

However, doing it that way has some disadvantages. You can't control the
timing of when Access applies this (which was the problem you posted about,
I think), and you can't reuse that query for other things (since it refers
to one particular form.) Therefore, I would not design it that way.