After removing filter on subform, how to maintain synchronicity

[Edward G]

Hi all,

I have a database I use for a racquet stringing business. I work mostly off
one main form called Customers that has several tabbed pages. On the first
page is a list box with customer names. Selecting a name brings up the
customers data on the other pages where I have subforms with Stringing
history, Regrip history, Personal info, etc. The code for the After Update
event on the list box is:

Private Sub List13_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[CustId] = '" & Me![List13] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

The problem I run into is this: Let's say I have selected the name Roger
Petersman on my list box. Now I go to a tabbed page called Stringing
Records. There is a subform there with a bunch of records spanning several
years with a bunch of different racquet names, strings, dates, serial
numbers, etc that Roger has used. Let's say I decide to filter on one
particular serial number. I filter by selection and viola! , only records
for that serial number.
But.....then I click the remove filter button and now my subform shows me
records for Tom Allen because his is the first name in my Customer table.
And even though Roger Petersman's name is still selected in my list box, all
the information on the various tabbed pages is now for Tom Allen.
Now mind you, all I have to do is click on Rogers name on the list box on
the first page to straighten things out, but it would be nice if this thing
operated smoothly.
Any ideas?
Thanks,
Edward J

 

[Jack MacDonald]

When you remove the filter, the main form's ApplyFilter event is fired
with the ApplyType parameter set to zero. You can write some VBA code
in that event to simulate clicking the ListBox to reapply the correct
filter.

Hi all,

I have a database I use for a racquet stringing business. I work mostly off
one main form called Customers that has several tabbed pages. On the first
page is a list box with customer names. Selecting a name brings up the
customers data on the other pages where I have subforms with Stringing
history, Regrip history, Personal info, etc. The code for the After Update
event on the list box is:

Private Sub List13_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[CustId] = '" & Me![List13] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

The problem I run into is this: Let's say I have selected the name Roger
Petersman on my list box. Now I go to a tabbed page called Stringing
Records. There is a subform there with a bunch of records spanning several
years with a bunch of different racquet names, strings, dates, serial
numbers, etc that Roger has used. Let's say I decide to filter on one
particular serial number. I filter by selection and viola! , only records
for that serial number.
But.....then I click the remove filter button and now my subform shows me
records for Tom Allen because his is the first name in my Customer table.
And even though Roger Petersman's name is still selected in my list box, all
the information on the various tabbed pages is now for Tom Allen.
Now mind you, all I have to do is click on Rogers name on the list box on
the first page to straighten things out, but it would be nice if this thing
operated smoothly.
Any ideas?
Thanks,
Edward J

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Edward G]

Thanks for the explanation and suggestion. I am using Access 97.
This isn't the first time I wished that "Click" was an action available
for a Macro or DoCmd. Are there more actions available in the Macro
builder on later versions? I figure it would take me a few months to
understand VBA well enough to write code that simulates a mouse click at
this point.

Ed



more recent versions of Access include "Click" as

When you remove the filter, the main form's ApplyFilter event is fired
with the ApplyType parameter set to zero. You can write some VBA code
in that event to simulate clicking the ListBox to reapply the correct
filter.

Hi all,

I have a database I use for a racquet stringing business. I work mostly off
one main form called Customers that has several tabbed pages. On the first
page is a list box with customer names. Selecting a name brings up the
customers data on the other pages where I have subforms with Stringing
history, Regrip history, Personal info, etc. The code for the After Update
event on the list box is:

Private Sub List13_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[CustId] = '" & Me![List13] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

The problem I run into is this: Let's say I have selected the name Roger
Petersman on my list box. Now I go to a tabbed page called Stringing
Records. There is a subform there with a bunch of records spanning several
years with a bunch of different racquet names, strings, dates, serial
numbers, etc that Roger has used. Let's say I decide to filter on one
particular serial number. I filter by selection and viola! , only records
for that serial number.
But.....then I click the remove filter button and now my subform shows me
records for Tom Allen because his is the first name in my Customer table.
And even though Roger Petersman's name is still selected in my list box, all
the information on the various tabbed pages is now for Tom Allen.
Now mind you, all I have to do is click on Rogers name on the list box on
the first page to straighten things out, but it would be nice if this thing
operated smoothly.
Any ideas?
Thanks,
Edward J

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Jack MacDonald]

Sorry - I assumed that since you said that you clicked the name in the
listbox that you had programmed something onto the Click event of the
listbox to invoke the filter.

If so, you can call that procedure from the ApplyFilter event of the
form. How?

- open the Properties of the form, find the On ApplyFilter event
- edit it
- insert this code:
IF ApplyType = 0 Then
Listxxxxx_Click
End If

That's what I meant about "simulate clicking the listbox"

Hope this helps.

Thanks for the explanation and suggestion. I am using Access 97.
This isn't the first time I wished that "Click" was an action available
for a Macro or DoCmd. Are there more actions available in the Macro
builder on later versions? I figure it would take me a few months to
understand VBA well enough to write code that simulates a mouse click at
this point.

Ed



more recent versions of Access include "Click" as

When you remove the filter, the main form's ApplyFilter event is fired
with the ApplyType parameter set to zero. You can write some VBA code
in that event to simulate clicking the ListBox to reapply the correct
filter.

Hi all,

I have a database I use for a racquet stringing business. I work mostly off
one main form called Customers that has several tabbed pages. On the first
page is a list box with customer names. Selecting a name brings up the
customers data on the other pages where I have subforms with Stringing
history, Regrip history, Personal info, etc. The code for the After Update
event on the list box is:

Private Sub List13_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[CustId] = '" & Me![List13] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

The problem I run into is this: Let's say I have selected the name Roger
Petersman on my list box. Now I go to a tabbed page called Stringing
Records. There is a subform there with a bunch of records spanning several
years with a bunch of different racquet names, strings, dates, serial
numbers, etc that Roger has used. Let's say I decide to filter on one
particular serial number. I filter by selection and viola! , only records
for that serial number.
But.....then I click the remove filter button and now my subform shows me
records for Tom Allen because his is the first name in my Customer table.
And even though Roger Petersman's name is still selected in my list box, all
the information on the various tabbed pages is now for Tom Allen.
Now mind you, all I have to do is click on Rogers name on the list box on
the first page to straighten things out, but it would be nice if this thing
operated smoothly.
Any ideas?
Thanks,
Edward J

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Edward G]

Well, nothing else in my life runs smoothly so why should this database?
How's that for sour grapes?
I gave your code a try. On a well constructed database I am sure it would
have done the trick, but
no such luck. As I said in my first post, the list box event that filters
the subforms was set to the AfterUpdate event.
I tried moving the procedure over to the Click Event and it works the same
from what I can see so far. I tried inserting your code in the main form
after that and then in the subform just for kicks. So near and yet so
far............
Thanks for your efforts. Looks like it is time for me to learn how to use
the Debug Window.

Ed

Sorry - I assumed that since you said that you clicked the name in the
listbox that you had programmed something onto the Click event of the
listbox to invoke the filter.

If so, you can call that procedure from the ApplyFilter event of the
form. How?

- open the Properties of the form, find the On ApplyFilter event
- edit it
- insert this code:
IF ApplyType = 0 Then
Listxxxxx_Click
End If

That's what I meant about "simulate clicking the listbox"

Hope this helps.

Thanks for the explanation and suggestion. I am using Access 97.
This isn't the first time I wished that "Click" was an action available
for a Macro or DoCmd. Are there more actions available in the Macro
builder on later versions? I figure it would take me a few months to
understand VBA well enough to write code that simulates a mouse click at
this point.

Ed



more recent versions of Access include "Click" as

When you remove the filter, the main form's ApplyFilter event is fired
with the ApplyType parameter set to zero. You can write some VBA code
in that event to simulate clicking the ListBox to reapply the correct
filter.

wrote:

Hi all,

I have a database I use for a racquet stringing business. I work

mostly
off

one main form called Customers that has several tabbed pages. On the first
page is a list box with customer names. Selecting a name brings up the
customers data on the other pages where I have subforms with Stringing
history, Regrip history, Personal info, etc. The code for the After Update
event on the list box is:

Private Sub List13_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[CustId] = '" & Me![List13] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

The problem I run into is this: Let's say I have selected the name Roger
Petersman on my list box. Now I go to a tabbed page called Stringing
Records. There is a subform there with a bunch of records spanning several
years with a bunch of different racquet names, strings, dates, serial
numbers, etc that Roger has used. Let's say I decide to filter on one
particular serial number. I filter by selection and viola! , only records
for that serial number.
But.....then I click the remove filter button and now my subform shows me
records for Tom Allen because his is the first name in my Customer table.
And even though Roger Petersman's name is still selected in my list

box,
all

the information on the various tabbed pages is now for Tom Allen.
Now mind you, all I have to do is click on Rogers name on the list box on
the first page to straighten things out, but it would be nice if this thing
operated smoothly.
Any ideas?
Thanks,
Edward J



**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security