Filtering Form With Combo Boxes

[Jeff Garrison]

I've been racking my brains on figuring this out, so now I'm asking the
experts...

I have a form that has, as a control source, a table - tblSales. As far as
the layout of the table, I have a field called StoreNumber, one called Year,
and then various fields after that. A StoreNumber can have multiple records
distinguished by Year.

On the form, I have a Combo Box for StoreNumber, which lets my users lookup
Store Numbers. That works fine. The other Combo Box is for Year. On the
AfterUpdate of the Year Combo box, I have the event
Me.FilterOn = False
Me.Filter = "Year=" & Me![Year]
Me.FilterOn = True
The first releases whatever filter is on there at the time of the
AfterUpdate. Then the Year is the acutal filter, then turning on the
filter. Again, that all works fine, but when I change the Year filter, it
takes me back to the first record in the recordset. What I want to do is to
be able to stay on whatever Store Number that I'm on, change the Year, and
still stay on the same Store Number.

The Row Source code for the StoreNumber Combo Box is

SELECT DISTINCT tblStoreSales.StoreNumber FROM tblStoreSales ORDER BY
tblStoreSales.StoreNumber;

The code for the After Update for the Store Number Combox Box is as follows,
which is the standard Combox Box setting, but I added the Year on to the
FindFirst in order to anchor the Year instead of pulling the first one it
comes to:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[StoreNumber] = " & Str(Nz(Me![StoreNumberLookup], 0)) & "
and [Year]=" & Me!Year
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Please let me know what I'm missing.....my brain cells are checking out on
me.

Thanks in advance

JeffG

 

[Barry Gilbert]

Couldn't you move your store find code to a seperate routine and call it
again after you set the Year filter?

Barry

 

[Jeff Garrison]

What I want to do is to be able to stay within the same store, but be able
to change the Year filter without losing the Store Number that I was on. As
it stands right now, if I'm in a particular Store Number and I change the
Year filter, the Store Number goes back to the first record, losing the
Store Nubmer I was on.

Couldn't you move your store find code to a seperate routine and call it
again after you set the Year filter?

Barry

I've been racking my brains on figuring this out, so now I'm asking the
experts...

I have a form that has, as a control source, a table - tblSales. As far
as
the layout of the table, I have a field called StoreNumber, one called
Year,
and then various fields after that. A StoreNumber can have multiple
records
distinguished by Year.

On the form, I have a Combo Box for StoreNumber, which lets my users
lookup
Store Numbers. That works fine. The other Combo Box is for Year. On
the
AfterUpdate of the Year Combo box, I have the event
Me.FilterOn = False
Me.Filter = "Year=" & Me![Year]
Me.FilterOn = True
The first releases whatever filter is on there at the time of the
AfterUpdate. Then the Year is the acutal filter, then turning on the
filter. Again, that all works fine, but when I change the Year filter,
it
takes me back to the first record in the recordset. What I want to do is
to
be able to stay on whatever Store Number that I'm on, change the Year,
and
still stay on the same Store Number.

The Row Source code for the StoreNumber Combo Box is

SELECT DISTINCT tblStoreSales.StoreNumber FROM tblStoreSales ORDER BY
tblStoreSales.StoreNumber;

The code for the After Update for the Store Number Combox Box is as
follows,
which is the standard Combox Box setting, but I added the Year on to the
FindFirst in order to anchor the Year instead of pulling the first one it
comes to:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[StoreNumber] = " & Str(Nz(Me![StoreNumberLookup], 0))
& "
and [Year]=" & Me!Year
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Please let me know what I'm missing.....my brain cells are checking out
on
me.

Thanks in advance

JeffG

 

[Barry Gilbert]

Changing the filter setting always refreshes the data, so this behavior is
expected. What I'm suggesting is re-running the code that finds the store
number after you set the filter. If this isn't a valid solution, I might not
be getting what you're saying.

Barry

What I want to do is to be able to stay within the same store, but be able
to change the Year filter without losing the Store Number that I was on. As
it stands right now, if I'm in a particular Store Number and I change the
Year filter, the Store Number goes back to the first record, losing the
Store Nubmer I was on.

Couldn't you move your store find code to a seperate routine and call it
again after you set the Year filter?

Barry

I've been racking my brains on figuring this out, so now I'm asking the
experts...

I have a form that has, as a control source, a table - tblSales. As far
as
the layout of the table, I have a field called StoreNumber, one called
Year,
and then various fields after that. A StoreNumber can have multiple
records
distinguished by Year.

On the form, I have a Combo Box for StoreNumber, which lets my users
lookup
Store Numbers. That works fine. The other Combo Box is for Year. On
the
AfterUpdate of the Year Combo box, I have the event
Me.FilterOn = False
Me.Filter = "Year=" & Me![Year]
Me.FilterOn = True
The first releases whatever filter is on there at the time of the
AfterUpdate. Then the Year is the acutal filter, then turning on the
filter. Again, that all works fine, but when I change the Year filter,
it
takes me back to the first record in the recordset. What I want to do is
to
be able to stay on whatever Store Number that I'm on, change the Year,
and
still stay on the same Store Number.

The Row Source code for the StoreNumber Combo Box is

SELECT DISTINCT tblStoreSales.StoreNumber FROM tblStoreSales ORDER BY
tblStoreSales.StoreNumber;

The code for the After Update for the Store Number Combox Box is as
follows,
which is the standard Combox Box setting, but I added the Year on to the
FindFirst in order to anchor the Year instead of pulling the first one it
comes to:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[StoreNumber] = " & Str(Nz(Me![StoreNumberLookup], 0))
& "
and [Year]=" & Me!Year
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Please let me know what I'm missing.....my brain cells are checking out
on
me.

Thanks in advance

JeffG

 

[Jeff Garrison]

It worked...

Thanks!

Changing the filter setting always refreshes the data, so this behavior is
expected. What I'm suggesting is re-running the code that finds the store
number after you set the filter. If this isn't a valid solution, I might
not
be getting what you're saying.

Barry

What I want to do is to be able to stay within the same store, but be
able
to change the Year filter without losing the Store Number that I was on.
As
it stands right now, if I'm in a particular Store Number and I change the
Year filter, the Store Number goes back to the first record, losing the
Store Nubmer I was on.

Couldn't you move your store find code to a seperate routine and call
it
again after you set the Year filter?

Barry

:

I've been racking my brains on figuring this out, so now I'm asking
the
experts...

I have a form that has, as a control source, a table - tblSales. As
far
as
the layout of the table, I have a field called StoreNumber, one called
Year,
and then various fields after that. A StoreNumber can have multiple
records
distinguished by Year.

On the form, I have a Combo Box for StoreNumber, which lets my users
lookup
Store Numbers. That works fine. The other Combo Box is for Year. On
the
AfterUpdate of the Year Combo box, I have the event
Me.FilterOn = False
Me.Filter = "Year=" & Me![Year]
Me.FilterOn = True
The first releases whatever filter is on there at the time of the
AfterUpdate. Then the Year is the acutal filter, then turning on the
filter. Again, that all works fine, but when I change the Year
filter,
it
takes me back to the first record in the recordset. What I want to do
is
to
be able to stay on whatever Store Number that I'm on, change the Year,
and
still stay on the same Store Number.

The Row Source code for the StoreNumber Combo Box is

SELECT DISTINCT tblStoreSales.StoreNumber FROM tblStoreSales ORDER BY
tblStoreSales.StoreNumber;

The code for the After Update for the Store Number Combox Box is as
follows,
which is the standard Combox Box setting, but I added the Year on to
the
FindFirst in order to anchor the Year instead of pulling the first one
it
comes to:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[StoreNumber] = " & Str(Nz(Me![StoreNumberLookup],
0))
& "
and [Year]=" & Me!Year
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Please let me know what I'm missing.....my brain cells are checking
out
on
me.

Thanks in advance

JeffG