Filtering a subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a subform, which is based on a query, which is based on a table. I'd
like to filter the year column for different year, i.e. 2005, 2006 etc...,
but I'm having trouble figuring out exactly how to do this. I'd also need to
be able to unfilter or to show "all" records. I would prefer to do this with
command buttons on the form, so the user does not have to use the tool bar.

I appreciate your assistance...

Manuel
 
Let's assume you put a combo on the form so the user can select a year.
Set the combo's properties like this:
Name cboYear
Row Source Type value list
Row Source 2002;2003;2004;2005;2006;2007;2008;2009
Format General Number
After Update [Event Procedure]

Now click the Build button (...) beside the combo's AfterUpdate property.
Access opens the code window. Between the "Private Sub..." and "End Sub"
lines, enter this kind of thing:
Dim strWhere As String
With Me.cboYear
If .Value > 1900 And .Value < 2999 Then
If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = strWhere
Me.FilterOn = True
Else
MsgBox "Enter a year in 4-digit format."
End If
End With

Replace MyField with the name of your year field.
If your year is a Text field (not a Number field), you need extra quotes:
strWhere = "[MyField] = """ & .Value & """"


The code for to show all records would be:
If Me.Dirty Then Me.Dirty = False
Me.FilterOn = False
 
Thanks for the info but it doesn't seem to be working. The combo box is
sitting on the parent form not the subform, could this be part of the
problem? The only reason I put the combo box on the parent form is because I
have the subform open in datasheet view, and can't seem to be able to place
the combo box on the subform when it's in datasheet view. Any other
suggestion given the new information.

Thanks for all your help...

Manuel

Allen Browne said:
Let's assume you put a combo on the form so the user can select a year.
Set the combo's properties like this:
Name cboYear
Row Source Type value list
Row Source 2002;2003;2004;2005;2006;2007;2008;2009
Format General Number
After Update [Event Procedure]

Now click the Build button (...) beside the combo's AfterUpdate property.
Access opens the code window. Between the "Private Sub..." and "End Sub"
lines, enter this kind of thing:
Dim strWhere As String
With Me.cboYear
If .Value > 1900 And .Value < 2999 Then
If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = strWhere
Me.FilterOn = True
Else
MsgBox "Enter a year in 4-digit format."
End If
End With

Replace MyField with the name of your year field.
If your year is a Text field (not a Number field), you need extra quotes:
strWhere = "[MyField] = """ & .Value & """"


The code for to show all records would be:
If Me.Dirty Then Me.Dirty = False
Me.FilterOn = False

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Manuel said:
I have a subform, which is based on a query, which is based on a table.
I'd
like to filter the year column for different year, i.e. 2005, 2006 etc...,
but I'm having trouble figuring out exactly how to do this. I'd also need
to
be able to unfilter or to show "all" records. I would prefer to do this
with
command buttons on the form, so the user does not have to use the tool
bar.

I appreciate your assistance...

Manuel
Click to expand...
Click to expand...
 
Yes, the code would need adapting if the combo is on a parent form.

Could you show your form in Continuous view instead of Datasheet view? This
lets you put a filter combo in the Form Header section, like the screenshot
in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Alternatively, replace "Sub1" below with the name of your subform control:

Dim strWhere As String
With Me.cboYear
If .Value > 1900 And .Value < 2999 Then
With Me[Sub1].Form
.Filter = strWhere
.FilterOn = True
End With
Else
MsgBox "Enter a year in 4-digit format."
End If
End With

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Manuel said:
Thanks for the info but it doesn't seem to be working. The combo box is
sitting on the parent form not the subform, could this be part of the
problem? The only reason I put the combo box on the parent form is
because I
have the subform open in datasheet view, and can't seem to be able to
place
the combo box on the subform when it's in datasheet view. Any other
suggestion given the new information.

Thanks for all your help...

Manuel

Allen Browne said:
Let's assume you put a combo on the form so the user can select a year.
Set the combo's properties like this:
Name cboYear
Row Source Type value list
Row Source 2002;2003;2004;2005;2006;2007;2008;2009
Format General Number
After Update [Event Procedure]

Now click the Build button (...) beside the combo's AfterUpdate property.
Access opens the code window. Between the "Private Sub..." and "End Sub"
lines, enter this kind of thing:
Dim strWhere As String
With Me.cboYear
If .Value > 1900 And .Value < 2999 Then
If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = strWhere
Me.FilterOn = True
Else
MsgBox "Enter a year in 4-digit format."
End If
End With

Replace MyField with the name of your year field.
If your year is a Text field (not a Number field), you need extra quotes:
strWhere = "[MyField] = """ & .Value & """"


The code for to show all records would be:
If Me.Dirty Then Me.Dirty = False
Me.FilterOn = False

Manuel said:
I have a subform, which is based on a query, which is based on a table.
I'd
like to filter the year column for different year, i.e. 2005, 2006
etc...,
but I'm having trouble figuring out exactly how to do this. I'd also
need
to
be able to unfilter or to show "all" records. I would prefer to do
this
with
command buttons on the form, so the user does not have to use the tool
bar.

I appreciate your assistance...

Manuel
Click to expand...
Click to expand...
Click to expand...
 
Still not working, but I don't think it's the code you're giving me, I think
it's a refresh issue. I tried passing an SQL statement using VBA (utilizing
QryDef property) and could actually verify that the query was affected.
However, the subform for the query was not affected with the change.

Any suggestions for issues pertaining to refreshing subforms?


Allen Browne said:
Yes, the code would need adapting if the combo is on a parent form.

Could you show your form in Continuous view instead of Datasheet view? This
lets you put a filter combo in the Form Header section, like the screenshot
in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Alternatively, replace "Sub1" below with the name of your subform control:

Dim strWhere As String
With Me.cboYear
If .Value > 1900 And .Value < 2999 Then
With Me[Sub1].Form
.Filter = strWhere
.FilterOn = True
End With
Else
MsgBox "Enter a year in 4-digit format."
End If
End With

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Manuel said:
Thanks for the info but it doesn't seem to be working. The combo box is
sitting on the parent form not the subform, could this be part of the
problem? The only reason I put the combo box on the parent form is
because I
have the subform open in datasheet view, and can't seem to be able to
place
the combo box on the subform when it's in datasheet view. Any other
suggestion given the new information.

Thanks for all your help...

Manuel

Allen Browne said:
Let's assume you put a combo on the form so the user can select a year.
Set the combo's properties like this:
Name cboYear
Row Source Type value list
Row Source 2002;2003;2004;2005;2006;2007;2008;2009
Format General Number
After Update [Event Procedure]

Now click the Build button (...) beside the combo's AfterUpdate property.
Access opens the code window. Between the "Private Sub..." and "End Sub"
lines, enter this kind of thing:
Dim strWhere As String
With Me.cboYear
If .Value > 1900 And .Value < 2999 Then
If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = strWhere
Me.FilterOn = True
Else
MsgBox "Enter a year in 4-digit format."
End If
End With

Replace MyField with the name of your year field.
If your year is a Text field (not a Number field), you need extra quotes:
strWhere = "[MyField] = """ & .Value & """"


The code for to show all records would be:
If Me.Dirty Then Me.Dirty = False
Me.FilterOn = False

I have a subform, which is based on a query, which is based on a table.
I'd
like to filter the year column for different year, i.e. 2005, 2006
etc...,
but I'm having trouble figuring out exactly how to do this. I'd also
need
to
be able to unfilter or to show "all" records. I would prefer to do
this
with
command buttons on the form, so the user does not have to use the tool
bar.

I appreciate your assistance...

Manuel
Click to expand...
Click to expand...
Click to expand...
 
Try:
Me.[Sub1].Form.Requery

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Manuel said:
Still not working, but I don't think it's the code you're giving me, I
think
it's a refresh issue. I tried passing an SQL statement using VBA
(utilizing
QryDef property) and could actually verify that the query was affected.
However, the subform for the query was not affected with the change.

Any suggestions for issues pertaining to refreshing subforms?


Allen Browne said:
Yes, the code would need adapting if the combo is on a parent form.

Could you show your form in Continuous view instead of Datasheet view?
This
lets you put a filter combo in the Form Header section, like the
screenshot
in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Alternatively, replace "Sub1" below with the name of your subform
control:

Dim strWhere As String
With Me.cboYear
If .Value > 1900 And .Value < 2999 Then
With Me[Sub1].Form
.Filter = strWhere
.FilterOn = True
End With
Else
MsgBox "Enter a year in 4-digit format."
End If
End With

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Manuel said:
Thanks for the info but it doesn't seem to be working. The combo box
is
sitting on the parent form not the subform, could this be part of the
problem? The only reason I put the combo box on the parent form is
because I
have the subform open in datasheet view, and can't seem to be able to
place
the combo box on the subform when it's in datasheet view. Any other
suggestion given the new information.

Thanks for all your help...

Manuel

:

Let's assume you put a combo on the form so the user can select a
year.
Set the combo's properties like this:
Name cboYear
Row Source Type value list
Row Source 2002;2003;2004;2005;2006;2007;2008;2009
Format General Number
After Update [Event Procedure]

Now click the Build button (...) beside the combo's AfterUpdate
property.
Access opens the code window. Between the "Private Sub..." and "End
Sub"
lines, enter this kind of thing:
Dim strWhere As String
With Me.cboYear
If .Value > 1900 And .Value < 2999 Then
If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = strWhere
Me.FilterOn = True
Else
MsgBox "Enter a year in 4-digit format."
End If
End With

Replace MyField with the name of your year field.
If your year is a Text field (not a Number field), you need extra
quotes:
strWhere = "[MyField] = """ & .Value & """"


The code for to show all records would be:
If Me.Dirty Then Me.Dirty = False
Me.FilterOn = False

I have a subform, which is based on a query, which is based on a
table.
I'd
like to filter the year column for different year, i.e. 2005, 2006
etc...,
but I'm having trouble figuring out exactly how to do this. I'd
also
need
to
be able to unfilter or to show "all" records. I would prefer to do
this
with
command buttons on the form, so the user does not have to use the
tool
bar.

I appreciate your assistance...

Manuel
Click to expand...
Click to expand...
Click to expand...
 
Allen,

I switched the Default View on the subform to Continuous Forms and placed
the combo box on the subform itself. I then incorporated the code you gave
me in your first replay and it worked!!!

Thank you so much for your assistance!

Allen Browne said:
Try:
Me.[Sub1].Form.Requery

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Manuel said:
Still not working, but I don't think it's the code you're giving me, I
think
it's a refresh issue. I tried passing an SQL statement using VBA
(utilizing
QryDef property) and could actually verify that the query was affected.
However, the subform for the query was not affected with the change.

Any suggestions for issues pertaining to refreshing subforms?


Allen Browne said:
Yes, the code would need adapting if the combo is on a parent form.

Could you show your form in Continuous view instead of Datasheet view?
This
lets you put a filter combo in the Form Header section, like the
screenshot
in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Alternatively, replace "Sub1" below with the name of your subform
control:

Dim strWhere As String
With Me.cboYear
If .Value > 1900 And .Value < 2999 Then
With Me[Sub1].Form
.Filter = strWhere
.FilterOn = True
End With
Else
MsgBox "Enter a year in 4-digit format."
End If
End With

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the info but it doesn't seem to be working. The combo box
is
sitting on the parent form not the subform, could this be part of the
problem? The only reason I put the combo box on the parent form is
because I
have the subform open in datasheet view, and can't seem to be able to
place
the combo box on the subform when it's in datasheet view. Any other
suggestion given the new information.

Thanks for all your help...

Manuel

:

Let's assume you put a combo on the form so the user can select a
year.
Set the combo's properties like this:
Name cboYear
Row Source Type value list
Row Source 2002;2003;2004;2005;2006;2007;2008;2009
Format General Number
After Update [Event Procedure]

Now click the Build button (...) beside the combo's AfterUpdate
property.
Access opens the code window. Between the "Private Sub..." and "End
Sub"
lines, enter this kind of thing:
Dim strWhere As String
With Me.cboYear
If .Value > 1900 And .Value < 2999 Then
If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = strWhere
Me.FilterOn = True
Else
MsgBox "Enter a year in 4-digit format."
End If
End With

Replace MyField with the name of your year field.
If your year is a Text field (not a Number field), you need extra
quotes:
strWhere = "[MyField] = """ & .Value & """"


The code for to show all records would be:
If Me.Dirty Then Me.Dirty = False
Me.FilterOn = False

I have a subform, which is based on a query, which is based on a
table.
I'd
like to filter the year column for different year, i.e. 2005, 2006
etc...,
but I'm having trouble figuring out exactly how to do this. I'd
also
need
to
be able to unfilter or to show "all" records. I would prefer to do
this
with
command buttons on the form, so the user does not have to use the
tool
bar.

I appreciate your assistance...

Manuel
Click to expand...
Click to expand...
Click to expand...
 
Back
Top