Filter Current Month

[Arvin Meyer [MVP]]

Compact it first, then zip it. My email address it at my website:

http://www.datastrat.com

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Would it be possible for me to zip the DB up and email it to you? It might
be
easier if you could see how I have it set up.

The only possible explanation that I can easily see is that the
recordsource
for the form itself is not something like:

SELECT *
FROM MyTable
WHERE DivisionID=[Forms]![frmMyForm]![cboDivisions];

You must have code in the AfterUpdate event of cboDivisions which is
changing the recordsource. If that's the case, the AfterUpdate event
won't
fire unless you tell it to like:

Sub Form_Open(Cancel As Integer)
Me.cboMonthSelect.Value = Format(Date, "mmmm")
Me.cboDivisions.Requery
Me.cboDivisions.Value = 1
cboDivisions_AfterUpdate
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

That's exactly what I'm looking to do. Here's what I put into my Open
event
of my form:

Me.cboMonthSelect.Value = Format(Date, "mmmm")
Me.cboDivisions.Requery
Me.cboDivisions.Value = 1
Me.Requery

But unfortunately it's still now working. It opens with all the records
and
cboMonthSelect box has "August" in it and the cboDivisons has "1" in
it.
But
it's still showing all the records.

:

You must be doing something incorrectly. It not only works I just
tested
it
with a double list box and it works just fine. To prove it download my
test
file at:

http://www.accessmvp.com/Arvin/Combo.zip

Then run the Products form and select the first list box, then the
second.
After you're satisfied, paste the following code into the form's Open
event,
save it, close and reopen it:

Private Sub Form_Open(Cancel As Integer)
Me.lstCategories.Value = 5
Me.lstProducts.Requery
Me.lstProducts.Value = 52
Me.Requery
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

message Still nothing. When I open my form it has the current month (August)
selected
since I have it as the default value. But it's still not filtering
the
records. But once I select it from the list it will filter.

:

Try :

Sub Form_Open(Cancel As Integer)
Me.cboMyCombo.Value = Format(Date, "mmmm")
Me.Requery
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

in
message I already have a select query for my records on this form. I'm
using
the
combo box to filter my records right now. Everything filters
properly
but
I
just want to have it filter to the current month when the form is
opened.
Will the after update event requery the records even if I don't
select
anything from the combo box since it is already defaulted to the
current
month?
Do I just put this on the after update event? Me.Requery

:


I would suggest you create a select query and use it as the
source
for
the
records, format the appropriate field to "mmmm", create a
criteria
in
the
field to reference the combo box and put in an after update
event
(in
the
combo) to requery the records.

I hope that makes sense.

let me know.


--
www.ae911truth.org



:

I have a combo box on my form header that I'm using to filter
my
data
by
month. The combo box has a list of the months and I have the
default
value
set to:

Format(Now(),"mmmm")

How do I get it to automatically filter my data to my default
value
when I
open the form? Right now it just shows the current month in my
combo
box but
it doesn't filter the records.

 

[Arvin Meyer [MVP]]

The filter property relies on built in code which sometimes doesn't work as
expected because it is in conflict with security schemas or other factors.
It is often easier to trace your own code because you can set breakpoints
and watches and determine what the values are at any given time.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

It's working now. I modified the recordsource query so it will select the
records based on the forms combo boxes. I didn't realize I had to do that
since the filters were working without that before I wanted it to default
to
specific selections.

Why is it that it would filter when I manually selected values from the
combo boxes before I modified the query? How come it just wouldn't filter
based on the defaulted values in those combo boxes? I'd like to understand
the difference so I know the next time.

The only possible explanation that I can easily see is that the
recordsource
for the form itself is not something like:

SELECT *
FROM MyTable
WHERE DivisionID=[Forms]![frmMyForm]![cboDivisions];

You must have code in the AfterUpdate event of cboDivisions which is
changing the recordsource. If that's the case, the AfterUpdate event
won't
fire unless you tell it to like:

Sub Form_Open(Cancel As Integer)
Me.cboMonthSelect.Value = Format(Date, "mmmm")
Me.cboDivisions.Requery
Me.cboDivisions.Value = 1
cboDivisions_AfterUpdate
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

That's exactly what I'm looking to do. Here's what I put into my Open
event
of my form:

Me.cboMonthSelect.Value = Format(Date, "mmmm")
Me.cboDivisions.Requery
Me.cboDivisions.Value = 1
Me.Requery

But unfortunately it's still now working. It opens with all the records
and
cboMonthSelect box has "August" in it and the cboDivisons has "1" in
it.
But
it's still showing all the records.

:

You must be doing something incorrectly. It not only works I just
tested
it
with a double list box and it works just fine. To prove it download my
test
file at:

http://www.accessmvp.com/Arvin/Combo.zip

Then run the Products form and select the first list box, then the
second.
After you're satisfied, paste the following code into the form's Open
event,
save it, close and reopen it:

Private Sub Form_Open(Cancel As Integer)
Me.lstCategories.Value = 5
Me.lstProducts.Requery
Me.lstProducts.Value = 52
Me.Requery
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

message Still nothing. When I open my form it has the current month (August)
selected
since I have it as the default value. But it's still not filtering
the
records. But once I select it from the list it will filter.

:

Try :

Sub Form_Open(Cancel As Integer)
Me.cboMyCombo.Value = Format(Date, "mmmm")
Me.Requery
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

in
message I already have a select query for my records on this form. I'm
using
the
combo box to filter my records right now. Everything filters
properly
but
I
just want to have it filter to the current month when the form is
opened.
Will the after update event requery the records even if I don't
select
anything from the combo box since it is already defaulted to the
current
month?
Do I just put this on the after update event? Me.Requery

:


I would suggest you create a select query and use it as the
source
for
the
records, format the appropriate field to "mmmm", create a
criteria
in
the
field to reference the combo box and put in an after update
event
(in
the
combo) to requery the records.

I hope that makes sense.

let me know.


--
www.ae911truth.org



:

I have a combo box on my form header that I'm using to filter
my
data
by
month. The combo box has a list of the months and I have the
default
value
set to:

Format(Now(),"mmmm")

How do I get it to automatically filter my data to my default
value
when I
open the form? Right now it just shows the current month in my
combo
box but
it doesn't filter the records.

 

[Secret Squirrel]

Makes sense. Thanks for your help.

One follow up question. Say I want to have this combo box on my main form
and have the detail on a subform. How would I point that combo box to the
subform to filter the records there? Would the recordsource of the subform
still be the select query?

I'm looking to have 3 subforms, 1 for each of my divisions. But on the main
form I want the cboMonthSelect so I can select a month and have it filter the
records on each of the 3 subforms. Would I just put the cboMonthSelect on the
main form and link it to the subforms?

The filter property relies on built in code which sometimes doesn't work as
expected because it is in conflict with security schemas or other factors.
It is often easier to trace your own code because you can set breakpoints
and watches and determine what the values are at any given time.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

It's working now. I modified the recordsource query so it will select the
records based on the forms combo boxes. I didn't realize I had to do that
since the filters were working without that before I wanted it to default
to
specific selections.

Why is it that it would filter when I manually selected values from the
combo boxes before I modified the query? How come it just wouldn't filter
based on the defaulted values in those combo boxes? I'd like to understand
the difference so I know the next time.

The only possible explanation that I can easily see is that the
recordsource
for the form itself is not something like:

SELECT *
FROM MyTable
WHERE DivisionID=[Forms]![frmMyForm]![cboDivisions];

You must have code in the AfterUpdate event of cboDivisions which is
changing the recordsource. If that's the case, the AfterUpdate event
won't
fire unless you tell it to like:

Sub Form_Open(Cancel As Integer)
Me.cboMonthSelect.Value = Format(Date, "mmmm")
Me.cboDivisions.Requery
Me.cboDivisions.Value = 1
cboDivisions_AfterUpdate
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

message That's exactly what I'm looking to do. Here's what I put into my Open
event
of my form:

Me.cboMonthSelect.Value = Format(Date, "mmmm")
Me.cboDivisions.Requery
Me.cboDivisions.Value = 1
Me.Requery

But unfortunately it's still now working. It opens with all the records
and
cboMonthSelect box has "August" in it and the cboDivisons has "1" in
it.
But
it's still showing all the records.

:

You must be doing something incorrectly. It not only works I just
tested
it
with a double list box and it works just fine. To prove it download my
test
file at:

http://www.accessmvp.com/Arvin/Combo.zip

Then run the Products form and select the first list box, then the
second.
After you're satisfied, paste the following code into the form's Open
event,
save it, close and reopen it:

Private Sub Form_Open(Cancel As Integer)
Me.lstCategories.Value = 5
Me.lstProducts.Requery
Me.lstProducts.Value = 52
Me.Requery
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

message Still nothing. When I open my form it has the current month (August)
selected
since I have it as the default value. But it's still not filtering
the
records. But once I select it from the list it will filter.

:

Try :

Sub Form_Open(Cancel As Integer)
Me.cboMyCombo.Value = Format(Date, "mmmm")
Me.Requery
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

in
message I already have a select query for my records on this form. I'm
using
the
combo box to filter my records right now. Everything filters
properly
but
I
just want to have it filter to the current month when the form is
opened.
Will the after update event requery the records even if I don't
select
anything from the combo box since it is already defaulted to the
current
month?
Do I just put this on the after update event? Me.Requery

:


I would suggest you create a select query and use it as the
source
for
the
records, format the appropriate field to "mmmm", create a
criteria
in
the
field to reference the combo box and put in an after update
event
(in
the
combo) to requery the records.

I hope that makes sense.

let me know.


--
www.ae911truth.org



:

I have a combo box on my form header that I'm using to filter
my
data
by
month. The combo box has a list of the months and I have the
default
value
set to:

Format(Now(),"mmmm")

How do I get it to automatically filter my data to my default
value
when I
open the form? Right now it just shows the current month in my
combo
box but
it doesn't filter the records.

 

[Arvin Meyer [MVP]]

Since it doesn't make sense to create and set the same value 3 times, the
main form is the place to do it. Just refer to the main form properly. In
code, you can do it from the subform simply by using Me.Parent, but in a
query you need to use the full syntax:

Forms!MainFormName!SubformControlName.Form!cboMonthSelect
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Makes sense. Thanks for your help.

One follow up question. Say I want to have this combo box on my main form
and have the detail on a subform. How would I point that combo box to the
subform to filter the records there? Would the recordsource of the subform
still be the select query?

I'm looking to have 3 subforms, 1 for each of my divisions. But on the
main
form I want the cboMonthSelect so I can select a month and have it filter
the
records on each of the 3 subforms. Would I just put the cboMonthSelect on
the
main form and link it to the subforms?

The filter property relies on built in code which sometimes doesn't work
as
expected because it is in conflict with security schemas or other
factors.
It is often easier to trace your own code because you can set breakpoints
and watches and determine what the values are at any given time.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

It's working now. I modified the recordsource query so it will select
the
records based on the forms combo boxes. I didn't realize I had to do
that
since the filters were working without that before I wanted it to
default
to
specific selections.

Why is it that it would filter when I manually selected values from the
combo boxes before I modified the query? How come it just wouldn't
filter
based on the defaulted values in those combo boxes? I'd like to
understand
the difference so I know the next time.

:

The only possible explanation that I can easily see is that the
recordsource
for the form itself is not something like:

SELECT *
FROM MyTable
WHERE DivisionID=[Forms]![frmMyForm]![cboDivisions];

You must have code in the AfterUpdate event of cboDivisions which is
changing the recordsource. If that's the case, the AfterUpdate event
won't
fire unless you tell it to like:

Sub Form_Open(Cancel As Integer)
Me.cboMonthSelect.Value = Format(Date, "mmmm")
Me.cboDivisions.Requery
Me.cboDivisions.Value = 1
cboDivisions_AfterUpdate
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

message That's exactly what I'm looking to do. Here's what I put into my
Open
event
of my form:

Me.cboMonthSelect.Value = Format(Date, "mmmm")
Me.cboDivisions.Requery
Me.cboDivisions.Value = 1
Me.Requery

But unfortunately it's still now working. It opens with all the
records
and
cboMonthSelect box has "August" in it and the cboDivisons has "1" in
it.
But
it's still showing all the records.

:

You must be doing something incorrectly. It not only works I just
tested
it
with a double list box and it works just fine. To prove it download
my
test
file at:

http://www.accessmvp.com/Arvin/Combo.zip

Then run the Products form and select the first list box, then the
second.
After you're satisfied, paste the following code into the form's
Open
event,
save it, close and reopen it:

Private Sub Form_Open(Cancel As Integer)
Me.lstCategories.Value = 5
Me.lstProducts.Requery
Me.lstProducts.Value = 52
Me.Requery
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

in
message Still nothing. When I open my form it has the current month
(August)
selected
since I have it as the default value. But it's still not
filtering
the
records. But once I select it from the list it will filter.

:

Try :

Sub Form_Open(Cancel As Integer)
Me.cboMyCombo.Value = Format(Date, "mmmm")
Me.Requery
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Secret Squirrel" <[email protected]>
wrote
in
message
I already have a select query for my records on this form. I'm
using
the
combo box to filter my records right now. Everything filters
properly
but
I
just want to have it filter to the current month when the form
is
opened.
Will the after update event requery the records even if I
don't
select
anything from the combo box since it is already defaulted to
the
current
month?
Do I just put this on the after update event? Me.Requery

:


I would suggest you create a select query and use it as the
source
for
the
records, format the appropriate field to "mmmm", create a
criteria
in
the
field to reference the combo box and put in an after update
event
(in
the
combo) to requery the records.

I hope that makes sense.

let me know.


--
www.ae911truth.org



:

I have a combo box on my form header that I'm using to
filter
my
data
by
month. The combo box has a list of the months and I have
the
default
value
set to:

Format(Now(),"mmmm")

How do I get it to automatically filter my data to my
default
value
when I
open the form? Right now it just shows the current month in
my
combo
box but
it doesn't filter the records.

 

[Secret Squirrel]

So I would add Me.Parent to reference my combo boxes on the main form instead
of the subform? I'll basically take the two combo boxes and put them on the
main form and have 3 subforms that will be triggered by the combo boxes on
the main form.

Wouldn't the code for the query just be:

Forms!MainFormName!cboMonthSelect since that's where the combo box will be?

Since it doesn't make sense to create and set the same value 3 times, the
main form is the place to do it. Just refer to the main form properly. In
code, you can do it from the subform simply by using Me.Parent, but in a
query you need to use the full syntax:

Forms!MainFormName!SubformControlName.Form!cboMonthSelect
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Makes sense. Thanks for your help.

One follow up question. Say I want to have this combo box on my main form
and have the detail on a subform. How would I point that combo box to the
subform to filter the records there? Would the recordsource of the subform
still be the select query?

I'm looking to have 3 subforms, 1 for each of my divisions. But on the
main
form I want the cboMonthSelect so I can select a month and have it filter
the
records on each of the 3 subforms. Would I just put the cboMonthSelect on
the
main form and link it to the subforms?

The filter property relies on built in code which sometimes doesn't work
as
expected because it is in conflict with security schemas or other
factors.
It is often easier to trace your own code because you can set breakpoints
and watches and determine what the values are at any given time.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

message It's working now. I modified the recordsource query so it will select
the
records based on the forms combo boxes. I didn't realize I had to do
that
since the filters were working without that before I wanted it to
default
to
specific selections.

Why is it that it would filter when I manually selected values from the
combo boxes before I modified the query? How come it just wouldn't
filter
based on the defaulted values in those combo boxes? I'd like to
understand
the difference so I know the next time.

:

The only possible explanation that I can easily see is that the
recordsource
for the form itself is not something like:

SELECT *
FROM MyTable
WHERE DivisionID=[Forms]![frmMyForm]![cboDivisions];

You must have code in the AfterUpdate event of cboDivisions which is
changing the recordsource. If that's the case, the AfterUpdate event
won't
fire unless you tell it to like:

Sub Form_Open(Cancel As Integer)
Me.cboMonthSelect.Value = Format(Date, "mmmm")
Me.cboDivisions.Requery
Me.cboDivisions.Value = 1
cboDivisions_AfterUpdate
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

message That's exactly what I'm looking to do. Here's what I put into my
Open
event
of my form:

Me.cboMonthSelect.Value = Format(Date, "mmmm")
Me.cboDivisions.Requery
Me.cboDivisions.Value = 1
Me.Requery

But unfortunately it's still now working. It opens with all the
records
and
cboMonthSelect box has "August" in it and the cboDivisons has "1" in
it.
But
it's still showing all the records.

:

You must be doing something incorrectly. It not only works I just
tested
it
with a double list box and it works just fine. To prove it download
my
test
file at:

http://www.accessmvp.com/Arvin/Combo.zip

Then run the Products form and select the first list box, then the
second.
After you're satisfied, paste the following code into the form's
Open
event,
save it, close and reopen it:

Private Sub Form_Open(Cancel As Integer)
Me.lstCategories.Value = 5
Me.lstProducts.Requery
Me.lstProducts.Value = 52
Me.Requery
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

in
message Still nothing. When I open my form it has the current month
(August)
selected
since I have it as the default value. But it's still not
filtering
the
records. But once I select it from the list it will filter.

:

Try :

Sub Form_Open(Cancel As Integer)
Me.cboMyCombo.Value = Format(Date, "mmmm")
Me.Requery
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Secret Squirrel" <[email protected]>
wrote
in
message
I already have a select query for my records on this form. I'm
using
the
combo box to filter my records right now. Everything filters
properly
but
I
just want to have it filter to the current month when the form
is
opened.
Will the after update event requery the records even if I
don't
select
anything from the combo box since it is already defaulted to
the
current
month?
Do I just put this on the after update event? Me.Requery

:


I would suggest you create a select query and use it as the
source
for
the
records, format the appropriate field to "mmmm", create a
criteria
in
the
field to reference the combo box and put in an after update
event
(in
the
combo) to requery the records.

I hope that makes sense.

let me know.


--
www.ae911truth.org



:

I have a combo box on my form header that I'm using to
filter
my
data
by
month. The combo box has a list of the months and I have
the
default
value
set to:

Format(Now(),"mmmm")

How do I get it to automatically filter my data to my
default
value
when I
open the form? Right now it just shows the current month in
my
combo
box but
it doesn't filter the records.

 

[Secret Squirrel]

I changed my query to point to my main form:

Forms!frmMonthlyMain!cboMonthSelect

Then I added this code to the main form:

Private Sub cboMonthSelect_AfterUpdate()

Me.Filter = "[Forms]![frmMonthlyDetail1]![MONTHCOUNT] = '" &
Me!cboMonthSelect & "'"
Me.FilterOn = True

End Sub

Private Sub Form_Open(Cancel As Integer)
Me.cboMonthSelect.Value = Format(Date, "mmmm")
Me.Requery
End Sub

I removed the cboDivisions since I can filter that from the query.
Everything seems to be working fine.

I didn't have to reference the main form using the Me.Parent. I also didn't
use the query sample code you wrote to show the full syntax.

Forms!MainFormName!SubformControlName.Form!cboMonthSelect

It's working so I'm not gonna touch it but how come I didn't have to use any
of those references you spoke of in your last post? I have no code on my
subform since the filter is on the main form. I guess I'm doing it right?

Since it doesn't make sense to create and set the same value 3 times, the
main form is the place to do it. Just refer to the main form properly. In
code, you can do it from the subform simply by using Me.Parent, but in a
query you need to use the full syntax:

Forms!MainFormName!SubformControlName.Form!cboMonthSelect
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Makes sense. Thanks for your help.

One follow up question. Say I want to have this combo box on my main form
and have the detail on a subform. How would I point that combo box to the
subform to filter the records there? Would the recordsource of the subform
still be the select query?

I'm looking to have 3 subforms, 1 for each of my divisions. But on the
main
form I want the cboMonthSelect so I can select a month and have it filter
the
records on each of the 3 subforms. Would I just put the cboMonthSelect on
the
main form and link it to the subforms?

The filter property relies on built in code which sometimes doesn't work
as
expected because it is in conflict with security schemas or other
factors.
It is often easier to trace your own code because you can set breakpoints
and watches and determine what the values are at any given time.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

message It's working now. I modified the recordsource query so it will select
the
records based on the forms combo boxes. I didn't realize I had to do
that
since the filters were working without that before I wanted it to
default
to
specific selections.

Why is it that it would filter when I manually selected values from the
combo boxes before I modified the query? How come it just wouldn't
filter
based on the defaulted values in those combo boxes? I'd like to
understand
the difference so I know the next time.

:

The only possible explanation that I can easily see is that the
recordsource
for the form itself is not something like:

SELECT *
FROM MyTable
WHERE DivisionID=[Forms]![frmMyForm]![cboDivisions];

You must have code in the AfterUpdate event of cboDivisions which is
changing the recordsource. If that's the case, the AfterUpdate event
won't
fire unless you tell it to like:

Sub Form_Open(Cancel As Integer)
Me.cboMonthSelect.Value = Format(Date, "mmmm")
Me.cboDivisions.Requery
Me.cboDivisions.Value = 1
cboDivisions_AfterUpdate
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

message That's exactly what I'm looking to do. Here's what I put into my
Open
event
of my form:

Me.cboMonthSelect.Value = Format(Date, "mmmm")
Me.cboDivisions.Requery
Me.cboDivisions.Value = 1
Me.Requery

But unfortunately it's still now working. It opens with all the
records
and
cboMonthSelect box has "August" in it and the cboDivisons has "1" in
it.
But
it's still showing all the records.

:

You must be doing something incorrectly. It not only works I just
tested
it
with a double list box and it works just fine. To prove it download
my
test
file at:

http://www.accessmvp.com/Arvin/Combo.zip

Then run the Products form and select the first list box, then the
second.
After you're satisfied, paste the following code into the form's
Open
event,
save it, close and reopen it:

Private Sub Form_Open(Cancel As Integer)
Me.lstCategories.Value = 5
Me.lstProducts.Requery
Me.lstProducts.Value = 52
Me.Requery
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

in
message Still nothing. When I open my form it has the current month
(August)
selected
since I have it as the default value. But it's still not
filtering
the
records. But once I select it from the list it will filter.

:

Try :

Sub Form_Open(Cancel As Integer)
Me.cboMyCombo.Value = Format(Date, "mmmm")
Me.Requery
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Secret Squirrel" <[email protected]>
wrote
in
message
I already have a select query for my records on this form. I'm
using
the
combo box to filter my records right now. Everything filters
properly
but
I
just want to have it filter to the current month when the form
is
opened.
Will the after update event requery the records even if I
don't
select
anything from the combo box since it is already defaulted to
the
current
month?
Do I just put this on the after update event? Me.Requery

:


I would suggest you create a select query and use it as the
source
for
the
records, format the appropriate field to "mmmm", create a
criteria
in
the
field to reference the combo box and put in an after update
event
(in
the
combo) to requery the records.

I hope that makes sense.

let me know.


--
www.ae911truth.org



:

I have a combo box on my form header that I'm using to
filter
my
data
by
month. The combo box has a list of the months and I have
the
default
value
set to:

Format(Now(),"mmmm")

How do I get it to automatically filter my data to my
default
value
when I
open the form? Right now it just shows the current month in
my
combo
box but
it doesn't filter the records.

 

[Arvin Meyer [MVP]]

Wouldn't the code for the query just be:

Forms!MainFormName!cboMonthSelect since that's where the combo box will
be?

Correct. If the code is running on the main form, use the main form syntax,
if running from the subform use Me.Parent or the full subform syntax. In
either case if you are calling the combo from a query, you cannot use
Me.Parent since Me refers to the currently open, and in focus, form or
report, and Parent refers to a Parent object (in this case main form) which
doesn't exist inside of the query. I hope that's clear <g>

 

[Arvin Meyer [MVP]]

You only use:

Forms!MainFormName!SubformControlName.Form!cboMonthSelect

if cboMonthSelect is in the subform.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I changed my query to point to my main form:

Forms!frmMonthlyMain!cboMonthSelect

Then I added this code to the main form:

Private Sub cboMonthSelect_AfterUpdate()

Me.Filter = "[Forms]![frmMonthlyDetail1]![MONTHCOUNT] = '" &
Me!cboMonthSelect & "'"
Me.FilterOn = True

End Sub

Private Sub Form_Open(Cancel As Integer)
Me.cboMonthSelect.Value = Format(Date, "mmmm")
Me.Requery
End Sub

I removed the cboDivisions since I can filter that from the query.
Everything seems to be working fine.

I didn't have to reference the main form using the Me.Parent. I also
didn't
use the query sample code you wrote to show the full syntax.

Forms!MainFormName!SubformControlName.Form!cboMonthSelect

It's working so I'm not gonna touch it but how come I didn't have to use
any
of those references you spoke of in your last post? I have no code on my
subform since the filter is on the main form. I guess I'm doing it right?

Since it doesn't make sense to create and set the same value 3 times, the
main form is the place to do it. Just refer to the main form properly. In
code, you can do it from the subform simply by using Me.Parent, but in a
query you need to use the full syntax:

Forms!MainFormName!SubformControlName.Form!cboMonthSelect
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Makes sense. Thanks for your help.

One follow up question. Say I want to have this combo box on my main
form
and have the detail on a subform. How would I point that combo box to
the
subform to filter the records there? Would the recordsource of the
subform
still be the select query?

I'm looking to have 3 subforms, 1 for each of my divisions. But on the
main
form I want the cboMonthSelect so I can select a month and have it
filter
the
records on each of the 3 subforms. Would I just put the cboMonthSelect
on
the
main form and link it to the subforms?

:

The filter property relies on built in code which sometimes doesn't
work
as
expected because it is in conflict with security schemas or other
factors.
It is often easier to trace your own code because you can set
breakpoints
and watches and determine what the values are at any given time.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

message It's working now. I modified the recordsource query so it will
select
the
records based on the forms combo boxes. I didn't realize I had to do
that
since the filters were working without that before I wanted it to
default
to
specific selections.

Why is it that it would filter when I manually selected values from
the
combo boxes before I modified the query? How come it just wouldn't
filter
based on the defaulted values in those combo boxes? I'd like to
understand
the difference so I know the next time.

:

The only possible explanation that I can easily see is that the
recordsource
for the form itself is not something like:

SELECT *
FROM MyTable
WHERE DivisionID=[Forms]![frmMyForm]![cboDivisions];

You must have code in the AfterUpdate event of cboDivisions which
is
changing the recordsource. If that's the case, the AfterUpdate
event
won't
fire unless you tell it to like:

Sub Form_Open(Cancel As Integer)
Me.cboMonthSelect.Value = Format(Date, "mmmm")
Me.cboDivisions.Requery
Me.cboDivisions.Value = 1
cboDivisions_AfterUpdate
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

in
message That's exactly what I'm looking to do. Here's what I put into my
Open
event
of my form:

Me.cboMonthSelect.Value = Format(Date, "mmmm")
Me.cboDivisions.Requery
Me.cboDivisions.Value = 1
Me.Requery

But unfortunately it's still now working. It opens with all the
records
and
cboMonthSelect box has "August" in it and the cboDivisons has "1"
in
it.
But
it's still showing all the records.

:

You must be doing something incorrectly. It not only works I
just
tested
it
with a double list box and it works just fine. To prove it
download
my
test
file at:

http://www.accessmvp.com/Arvin/Combo.zip

Then run the Products form and select the first list box, then
the
second.
After you're satisfied, paste the following code into the form's
Open
event,
save it, close and reopen it:

Private Sub Form_Open(Cancel As Integer)
Me.lstCategories.Value = 5
Me.lstProducts.Requery
Me.lstProducts.Value = 52
Me.Requery
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Secret Squirrel" <[email protected]>
wrote
in
message
Still nothing. When I open my form it has the current month
(August)
selected
since I have it as the default value. But it's still not
filtering
the
records. But once I select it from the list it will filter.

:

Try :

Sub Form_Open(Cancel As Integer)
Me.cboMyCombo.Value = Format(Date, "mmmm")
Me.Requery
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Secret Squirrel" <[email protected]>
wrote
in
message
I already have a select query for my records on this form.
I'm
using
the
combo box to filter my records right now. Everything
filters
properly
but
I
just want to have it filter to the current month when the
form
is
opened.
Will the after update event requery the records even if I
don't
select
anything from the combo box since it is already defaulted
to
the
current
month?
Do I just put this on the after update event? Me.Requery

:


I would suggest you create a select query and use it as
the
source
for
the
records, format the appropriate field to "mmmm", create a
criteria
in
the
field to reference the combo box and put in an after
update
event
(in
the
combo) to requery the records.

I hope that makes sense.

let me know.


--
www.ae911truth.org



:

I have a combo box on my form header that I'm using to
filter
my
data
by
month. The combo box has a list of the months and I have
the
default
value
set to:

Format(Now(),"mmmm")

How do I get it to automatically filter my data to my
default
value
when I
open the form? Right now it just shows the current month
in
my
combo
box but
it doesn't filter the records.

 

[Secret Squirrel]

Yes everything is clear now. It's all working fine. Thank you very much for
all your help!