Filtering on a form resets?

[deejay_totoro]

Hello all,

I have a main form and a subform.

The main form has a tab control. In this tab control users can select a record by clicking on a listbox showing all the records.

Other tabs then show various details about the record, including linked subforms with related data.

I have noticed that when users use "filter by selection" on any related data in a subform, the records is correctly filtered.

However when the user removes the filter, the main record selector automatically jumps back to record number one in the database.

How may I stop this behaviour? I want users to be able to filter subforms without reseting the master record they are looking at?

Thanks!

dj_T

 

[adam_fleck]

Tricky one

I think the problem you've run up against can be solved, though it's quite tricky.

In outline, you'll need to set up a custom right-click pop-up menu for your subforms (View, Toolbars, Customize, Shortcut Menus...)

The custom menu will be the same as the standard pop-up menu, except that "Remove Filter/Sort" will be a custom option and will call a function called, say, "remove_SubForm_Filter".

The main line in remove_SubForm_Filter() is this:

Forms("main_Form")("sub_form").Form.RecordSource = subForm_SQL

Where subForm_SQL is the SQL string that sets the subform's record source.

You might find that

Forms("main_Form")("sub_form").Form.RecordSource = Forms("main_Form")("sub_form").Form.RecordSource

does the trick.

It's a tricky problem, and it bothered me for several months. This is just an oultine of a solution that I managed to implement on one of the applications I'm responsible for, and it might be difficult to translate it to the applicataion you're working on.

If this doesn't help you towards a solution just say, and I'll post a zip file with more details.

See: http://www.access-programmers.co.uk/forums/showthread.php?t=65920