Filter Current Month

[Secret Squirrel]

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.

 

[scubadiver]

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.

 

[Secret Squirrel]

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

 

[scubadiver]

Apologies,

How about inserting

=date()

into the criteria row for the month.

That may work.

 

[Secret Squirrel]

I have the default value of the combo box set to the current month. When the
form opens it shows the current month in that combo box. Now I just want to
requery my records to only show the records for that month. If I was to
select the current month from the combo box it will then filter the records
for that month. But if it has that default in the box when the form is opened
it doesn't filter. Why is it that it will filter after I select something
from the combo box but it won't when it has a value in it when opening the
form?

 

[Arvin Meyer [MVP]]

Try :

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

 

[Arvin Meyer [MVP]]

It will work, but it will also limit all the records to that specific month
and not allow him to change it.

 

[Secret Squirrel]

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.

 

[scubadiver]

I have tried something similar to yours with some info I have. If I change
the option in the combo it will list the records in the query but not the
form.

I am doing something similar but I am using a form and subform with a list
box and it works fine. The list box has a default value and the records are
listed.

Try using a form and subform instead (if that is possible which sounds
likely).

 

[Ken Sheridan]

You can call the AfterUpdate event procedure of the combo box in the Form's
load event procedure e.g.

cboMonths_AfterUpdate

Normally the event won't be executed when the form loads as you are not
actually selecting an item from the combo box, but calling the event
procedure causes it to execute as though you had.

Or, if your code in the combo box's AfterUpdate procedure is filtering the
form by means of its Filter and FilterOn properties, you can explicitly
filter the form in the same way in its Load event procedure:

Me.Filter = "Month([YourDateField]) = " & Month(VBA.Date)
Me.FilterOn = True

Or, if the data is restricted by a reference to the combo box as a parameter
in the form's underlying query requerying the form in its Load event
procedure should do it:

Me.Requery

I assume that the form's underlying recordset covers only one year's data.
Otherwise filtering solely on the month, but not the year also, will return
records for that month from each year not just the current year.

Ken Sheridan
Stafford, England

 

[Secret Squirrel]

Hi Ken,
I am using a filter in the AfterUpdate event of my combo box. I just tried
using the filter code you wrote but now it's asking me to enter a parameter
value. Should I remove the default value from my combo box or is there
something more to the code you wrote?

Here's what I put in the Load event:

Me.Filter = "Month([cboMonthSelect]) = " & Month(VBA.Date)
Me.FilterOn = True

Here is the code I'm using to filter my records in the AfterUpdate event of
my combo box if this helps.

' Find the record that matches the control.
Dim strFilter As String

If IsNull(Me!cboDivisions) = False Then
strFilter = "[FPRODCL] = " & Me!cboDivisions & " AND "
End If

strFilter = strFilter & "[MONTHCOUNT] = '" & Me!cboMonthSelect & "'"
Debug.Print strFilter

Me.Filter = strFilter
Me.FilterOn = True

I also have this as the default value of my combo box:

Format(Now(),"mmmm")

You can call the AfterUpdate event procedure of the combo box in the Form's
load event procedure e.g.

cboMonths_AfterUpdate

Normally the event won't be executed when the form loads as you are not
actually selecting an item from the combo box, but calling the event
procedure causes it to execute as though you had.

Or, if your code in the combo box's AfterUpdate procedure is filtering the
form by means of its Filter and FilterOn properties, you can explicitly
filter the form in the same way in its Load event procedure:

Me.Filter = "Month([YourDateField]) = " & Month(VBA.Date)
Me.FilterOn = True

Or, if the data is restricted by a reference to the combo box as a parameter
in the form's underlying query requerying the form in its Load event
procedure should do it:

Me.Requery

I assume that the form's underlying recordset covers only one year's data.
Otherwise filtering solely on the month, but not the year also, will return
records for that month from each year not just the current year.

Ken Sheridan
Stafford, England

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

 

[Secret Squirrel]

Why would it work using a form/subform instead of just one form? I may take
that course but before I do I want to understand why there is a difference.

 

[Arvin Meyer [MVP]]

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

 

[Arvin Meyer [MVP]]

I haven't tested it, but a default value doesn't force usually data. It
won't equal the default value until the first character is typed. That is
why I explicitly set the value for the combo in my opening code.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

You can call the AfterUpdate event procedure of the combo box in the
Form's
load event procedure e.g.

cboMonths_AfterUpdate

Normally the event won't be executed when the form loads as you are not
actually selecting an item from the combo box, but calling the event
procedure causes it to execute as though you had.

Or, if your code in the combo box's AfterUpdate procedure is filtering the
form by means of its Filter and FilterOn properties, you can explicitly
filter the form in the same way in its Load event procedure:

Me.Filter = "Month([YourDateField]) = " & Month(VBA.Date)
Me.FilterOn = True

Or, if the data is restricted by a reference to the combo box as a
parameter
in the form's underlying query requerying the form in its Load event
procedure should do it:

Me.Requery

I assume that the form's underlying recordset covers only one year's data.
Otherwise filtering solely on the month, but not the year also, will
return
records for that month from each year not just the current year.

Ken Sheridan
Stafford, England

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

 

[Secret Squirrel]

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.

 

[Arvin Meyer [MVP]]

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

 

[Secret Squirrel]

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.

 

[Secret Squirrel]

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.

 

[Ken Sheridan]

cboMonthSelect is an unbound control, right? It’s the underlying table's
original date/time column's name you'd need to refer to, not the combo box's,
assuming the column is returned by the form's underlying query. However, it
sounds like your (computed?) MONTHCOUNT column contains the month values in
format "mmmm" already, so you should be able to use:

Me.Filter = "MONTHCOUNT = """ & Format(VBA.Date,"mmmm") & """"
Me.FilterOn = True

Have you tried simply calling the AfterUpdate event procedure in the form's
Load event with:

cboMonthSelect_AfterUpdate

That's probably the simplest solution as it just executes the code you
already know is working.

Ken Sheridan
Stafford, England

Hi Ken,
I am using a filter in the AfterUpdate event of my combo box. I just tried
using the filter code you wrote but now it's asking me to enter a parameter
value. Should I remove the default value from my combo box or is there
something more to the code you wrote?

Here's what I put in the Load event:

Me.Filter = "Month([cboMonthSelect]) = " & Month(VBA.Date)
Me.FilterOn = True

Here is the code I'm using to filter my records in the AfterUpdate event of
my combo box if this helps.

' Find the record that matches the control.
Dim strFilter As String

If IsNull(Me!cboDivisions) = False Then
strFilter = "[FPRODCL] = " & Me!cboDivisions & " AND "
End If

strFilter = strFilter & "[MONTHCOUNT] = '" & Me!cboMonthSelect & "'"
Debug.Print strFilter

Me.Filter = strFilter
Me.FilterOn = True

I also have this as the default value of my combo box:

Format(Now(),"mmmm")

You can call the AfterUpdate event procedure of the combo box in the Form's
load event procedure e.g.

cboMonths_AfterUpdate

Normally the event won't be executed when the form loads as you are not
actually selecting an item from the combo box, but calling the event
procedure causes it to execute as though you had.

Or, if your code in the combo box's AfterUpdate procedure is filtering the
form by means of its Filter and FilterOn properties, you can explicitly
filter the form in the same way in its Load event procedure:

Me.Filter = "Month([YourDateField]) = " & Month(VBA.Date)
Me.FilterOn = True

Or, if the data is restricted by a reference to the combo box as a parameter
in the form's underlying query requerying the form in its Load event
procedure should do it:

Me.Requery

I assume that the form's underlying recordset covers only one year's data.
Otherwise filtering solely on the month, but not the year also, will return
records for that month from each year not just the current year.

Ken Sheridan
Stafford, England

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.

 

[Ken Sheridan]

Setting the DefaultValue property of a bound control doesn't initiate an edit
of the current row in the underlying recordset, but in the case of an unbound
'navigational' control like this that's not a pertinent issue. The key
factor is that it does not cause the AfterUpdate event procedure to execute,
but nor does assigning a value in code, so whether the DefaultValue property
is set or a value assigned to the Control something more needs to be done.
What was unclear from the OP was whether the form was being 'filtered' by
means of its Filter property or by restricting the underlying recordset by
means of a parameter referencing the control. Its now clear that it’s the
former, so the filter needs to be implemented on load rather than requerying.

Ken Sheridan
Stafford, England

I haven't tested it, but a default value doesn't force usually data. It
won't equal the default value until the first character is typed. That is
why I explicitly set the value for the combo in my opening code.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

You can call the AfterUpdate event procedure of the combo box in the
Form's
load event procedure e.g.

cboMonths_AfterUpdate

Normally the event won't be executed when the form loads as you are not
actually selecting an item from the combo box, but calling the event
procedure causes it to execute as though you had.

Or, if your code in the combo box's AfterUpdate procedure is filtering the
form by means of its Filter and FilterOn properties, you can explicitly
filter the form in the same way in its Load event procedure:

Me.Filter = "Month([YourDateField]) = " & Month(VBA.Date)
Me.FilterOn = True

Or, if the data is restricted by a reference to the combo box as a
parameter
in the form's underlying query requerying the form in its Load event
procedure should do it:

Me.Requery

I assume that the form's underlying recordset covers only one year's data.
Otherwise filtering solely on the month, but not the year also, will
return
records for that month from each year not just the current year.

Ken Sheridan
Stafford, England

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.