Remove Filter/Sort and subform

  • Thread starter Thread starter Leif
  • Start date Start date
L

Leif

I have a tabbed form. On the first tab is my project
information. On the second tab is project details. I've
set up a 1 to many relationship between between project
and project details. This is done through the subform
Link Child/Master fields.

I can filter just fine, on either the master or detail
records. The master and detail information are drawn from
different tables.

However, I was very surprised to find that if I do a
"Remove Filter/Sort" operation on the project details that
the record position in both the project and project
details get set back to record 1. I'm not surprised that
happens in the detail, but I am suprised that it happens
in the master data.

Can anyone explain why this is the case? Is there a work-
around?

Best Regards,
Leif
 
When you remove a filter, a different set of records is loaded into the
form, so like a requery or opening again, the everything is reloaded, and
you start at the first record.

To find the same record again, save the primary key value before you remove
the filter, and then find that record again. This example assumes a numeric
primary key named "ID":

Dim varPK as Variant
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
varPK = Me.ID 'Whatever your p.k. is called.
Me.FilterOn = False 'Remove the filter.
If IsNull(varPK) Then 'Must have been a new record.
If Not Me.NewRecord Then
RunCommand acCmdRecordsGotoNew
End If
Else 'Find the record again
With Me.RecordsetClone
.FindFirst "ID = " & varPK
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
 
Thanks Allen for your reply.

I've been trying to work with .bookmark, rather then a
primary key. However, in some cases I'm getting an
invalid bookmark message so your approach looks better.

How do I connect this code into the Access right-click
(short cut) standard menu "Remove Filter/Sort"?

Thanks,
Leif
 
The default menu just removes the filter. That's not what you want, so you
will need to create your own shortcut menu that replaces the item with a
call to your own function.
 
Hi Allen,

I figured out a way to handle it. I've activated the
ApplyFilter event for the form. If the ApplyType argument
is equal to acShowAllRecords then I know the "Remove
Filter/Sotrt" option from the menu was selected. This way
the user can use the standard short cut menu, the icon, or
the Remove Filter/sort option from the Records menu and I
can get control and utilize your code.

Thanks for your help.

Best Regards,
Leif
 
Back
Top