Customised Find Button on Form

[QuickLearner]

Hi
I have table with 3 fields and a Form with 12 buttons "Jan, Feb,
March...Dec" Caption

tblClients
fdFirstName
fdLastName
fdQtrEnd // row source is Jan, Feb, March....Dec

frmMenu


I would to make a Button on the Form Say for May month to show me a Preview
of only those Clients who has Quarter end "May" selected in fdQtrEnd..

how can I do that.
Thanks

 

[Graham Mandeno]

Hi QuickLearner

First, I recommend that you make fdQtrEnd a numeric field (byte) with values
1 (Jan) ... 12 (Dec). The reason is that there are many different text
representations for month names and abbreviation - Sep, Sept, September -
nevermind all the variations in other languages.

Make your combo box to select the month with two columns:
ControlSource: fdQtrEnd
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0 (this hides the first column)
RowSourceType: Value List
RowSource: 1;January;2;February ... 12;December

Now, instead of 12 buttons, have a single unbound combo box with a label
"Select by Quarter End:". Make its properties the same as the combo box
above, except for the following:
Name: cboFilterQtrEnd
ControlSource: <blank>
RowSource: 0;<show all>;1;January;2;February ... 12;December
DefaultValue: 0

Finally, use this combo's AfterUpdate event to set the appropriate filter on
the form:

Private Sub cboFilterQtrEnd_AfterUpdate()
If IsNull(cboFilterQtrEnd) then cboFilterQtrEnd = 0
If cboFilterQtrEnd = 0 Then
Me.FilterOn = False
Else
Me.Filter = "fdQtrEnd=" & cboFilterQtrEnd
If Not Me.FilterOn then Me.FilterOn = True
End If
End Sub

 

[QuickLearner]

thanks Graham Mandeno
I have followed your instruction and everything goods so far..
I can see the combo box on the form but when I select Say May nothing
happens (even though there are 4 clients with Qtr End May in the table)
Where I am going wrong? "something to do with After update code? (I have
copied your code and paste in the form code. Names are OK too)
I would really want to know your way..


I did mange to do it myself but a long way..
First I have created 12 queries with criteria "January",
"February"....."December" and them created 12 Reports based on those 12
queries and then made 12 buttons on a form and assigned macros for each
reports.
It does work..

But I still like your idea of NOT HAVING 12 buttons but only ONE dropdown
menu.

Waiting for more instruction from you..
Thanks

Hi QuickLearner

First, I recommend that you make fdQtrEnd a numeric field (byte) with
values 1 (Jan) ... 12 (Dec). The reason is that there are many different
text representations for month names and abbreviation - Sep, Sept,
September - nevermind all the variations in other languages.

Make your combo box to select the month with two columns:
ControlSource: fdQtrEnd
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0 (this hides the first column)
RowSourceType: Value List
RowSource: 1;January;2;February ... 12;December

Now, instead of 12 buttons, have a single unbound combo box with a label
"Select by Quarter End:". Make its properties the same as the combo box
above, except for the following:
Name: cboFilterQtrEnd
ControlSource: <blank>
RowSource: 0;<show all>;1;January;2;February ... 12;December
DefaultValue: 0

Finally, use this combo's AfterUpdate event to set the appropriate filter
on the form:

Private Sub cboFilterQtrEnd_AfterUpdate()
If IsNull(cboFilterQtrEnd) then cboFilterQtrEnd = 0
If cboFilterQtrEnd = 0 Then
Me.FilterOn = False
Else
Me.Filter = "fdQtrEnd=" & cboFilterQtrEnd
If Not Me.FilterOn then Me.FilterOn = True
End If
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi
I have table with 3 fields and a Form with 12 buttons "Jan, Feb,
March...Dec" Caption

tblClients
fdFirstName
fdLastName
fdQtrEnd // row source is Jan, Feb, March....Dec

frmMenu


I would to make a Button on the Form Say for May month to show me a
Preview of only those Clients who has Quarter end "May" selected in
fdQtrEnd..

how can I do that.
Thanks

 

[Graham Mandeno]

Hi QL

It's possible that your AfterUpdate code is not even running.

Try inserting MsgBox "Hello" as the first line of the procedure (before If
IsNull...)

If the message does not pop up when you change the combo box selection,
check:

1. That the name of the combo matches the name of the procedure
(cboFilterQtrEnd)
2. That the AfterUpdate property of the combo is [Event Procedure]

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

thanks Graham Mandeno
I have followed your instruction and everything goods so far..
I can see the combo box on the form but when I select Say May nothing
happens (even though there are 4 clients with Qtr End May in the table)
Where I am going wrong? "something to do with After update code? (I have
copied your code and paste in the form code. Names are OK too)
I would really want to know your way..


I did mange to do it myself but a long way..
First I have created 12 queries with criteria "January",
"February"....."December" and them created 12 Reports based on those 12
queries and then made 12 buttons on a form and assigned macros for each
reports.
It does work..

But I still like your idea of NOT HAVING 12 buttons but only ONE dropdown
menu.

Waiting for more instruction from you..
Thanks

Hi QuickLearner

First, I recommend that you make fdQtrEnd a numeric field (byte) with
values 1 (Jan) ... 12 (Dec). The reason is that there are many different
text representations for month names and abbreviation - Sep, Sept,
September - nevermind all the variations in other languages.

Make your combo box to select the month with two columns:
ControlSource: fdQtrEnd
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0 (this hides the first column)
RowSourceType: Value List
RowSource: 1;January;2;February ... 12;December

Now, instead of 12 buttons, have a single unbound combo box with a label
"Select by Quarter End:". Make its properties the same as the combo box
above, except for the following:
Name: cboFilterQtrEnd
ControlSource: <blank>
RowSource: 0;<show all>;1;January;2;February ... 12;December
DefaultValue: 0

Finally, use this combo's AfterUpdate event to set the appropriate filter
on the form:

Private Sub cboFilterQtrEnd_AfterUpdate()
If IsNull(cboFilterQtrEnd) then cboFilterQtrEnd = 0
If cboFilterQtrEnd = 0 Then
Me.FilterOn = False
Else
Me.Filter = "fdQtrEnd=" & cboFilterQtrEnd
If Not Me.FilterOn then Me.FilterOn = True
End If
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi
I have table with 3 fields and a Form with 12 buttons "Jan, Feb,
March...Dec" Caption

tblClients
fdFirstName
fdLastName
fdQtrEnd // row source is Jan, Feb, March....Dec

frmMenu


I would to make a Button on the Form Say for May month to show me a
Preview of only those Clients who has Quarter end "May" selected in
fdQtrEnd..

how can I do that.
Thanks

 

[QuickLearner]

Sorry for late response, I was away.
Thanks you were right It was mistake on my part.
I am using this one and it is working fine.


Sub HideRows()
Dim MyRange As Range, cl As Range

Set MyRange = Sheet3.Range("A23:IV25")
Application.ScreenUpdating = False
For Each cl In MyRange
If cl.Value <> "Minimum" Then cl.EntireRow.Hidden = True
Next cl
Application.ScreenUpdating = True
End Sub


Now another question is
How can I hide the Entire Range("A23:IV25") if the Value is "Minimum"?

Thanks

Hi QL

It's possible that your AfterUpdate code is not even running.

Try inserting MsgBox "Hello" as the first line of the procedure (before If
IsNull...)

If the message does not pop up when you change the combo box selection,
check:

1. That the name of the combo matches the name of the procedure
(cboFilterQtrEnd)
2. That the AfterUpdate property of the combo is [Event Procedure]

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

thanks Graham Mandeno
I have followed your instruction and everything goods so far..
I can see the combo box on the form but when I select Say May nothing
happens (even though there are 4 clients with Qtr End May in the
table)
Where I am going wrong? "something to do with After update code? (I have
copied your code and paste in the form code. Names are OK too)
I would really want to know your way..


I did mange to do it myself but a long way..
First I have created 12 queries with criteria "January",
"February"....."December" and them created 12 Reports based on those 12
queries and then made 12 buttons on a form and assigned macros for each
reports.
It does work..

But I still like your idea of NOT HAVING 12 buttons but only ONE dropdown
menu.

Waiting for more instruction from you..
Thanks

Hi QuickLearner

First, I recommend that you make fdQtrEnd a numeric field (byte) with
values 1 (Jan) ... 12 (Dec). The reason is that there are many
different text representations for month names and abbreviation - Sep,
Sept, September - nevermind all the variations in other languages.

Make your combo box to select the month with two columns:
ControlSource: fdQtrEnd
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0 (this hides the first column)
RowSourceType: Value List
RowSource: 1;January;2;February ... 12;December

Now, instead of 12 buttons, have a single unbound combo box with a label
"Select by Quarter End:". Make its properties the same as the combo box
above, except for the following:
Name: cboFilterQtrEnd
ControlSource: <blank>
RowSource: 0;<show all>;1;January;2;February ... 12;December
DefaultValue: 0

Finally, use this combo's AfterUpdate event to set the appropriate
filter on the form:

Private Sub cboFilterQtrEnd_AfterUpdate()
If IsNull(cboFilterQtrEnd) then cboFilterQtrEnd = 0
If cboFilterQtrEnd = 0 Then
Me.FilterOn = False
Else
Me.Filter = "fdQtrEnd=" & cboFilterQtrEnd
If Not Me.FilterOn then Me.FilterOn = True
End If
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi
I have table with 3 fields and a Form with 12 buttons "Jan, Feb,
March...Dec" Caption

tblClients
fdFirstName
fdLastName
fdQtrEnd // row source is Jan, Feb, March....Dec

frmMenu


I would to make a Button on the Form Say for May month to show me a
Preview of only those Clients who has Quarter end "May" selected in
fdQtrEnd..

how can I do that.
Thanks

 

[QuickLearner]

thanks Graham

Hi QL

It's possible that your AfterUpdate code is not even running.

Try inserting MsgBox "Hello" as the first line of the procedure (before If
IsNull...)

If the message does not pop up when you change the combo box selection,
check:

1. That the name of the combo matches the name of the procedure
(cboFilterQtrEnd)
2. That the AfterUpdate property of the combo is [Event Procedure]

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

thanks Graham Mandeno
I have followed your instruction and everything goods so far..
I can see the combo box on the form but when I select Say May nothing
happens (even though there are 4 clients with Qtr End May in the
table)
Where I am going wrong? "something to do with After update code? (I have
copied your code and paste in the form code. Names are OK too)
I would really want to know your way..


I did mange to do it myself but a long way..
First I have created 12 queries with criteria "January",
"February"....."December" and them created 12 Reports based on those 12
queries and then made 12 buttons on a form and assigned macros for each
reports.
It does work..

But I still like your idea of NOT HAVING 12 buttons but only ONE dropdown
menu.

Waiting for more instruction from you..
Thanks

Hi QuickLearner

First, I recommend that you make fdQtrEnd a numeric field (byte) with
values 1 (Jan) ... 12 (Dec). The reason is that there are many
different text representations for month names and abbreviation - Sep,
Sept, September - nevermind all the variations in other languages.

Make your combo box to select the month with two columns:
ControlSource: fdQtrEnd
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0 (this hides the first column)
RowSourceType: Value List
RowSource: 1;January;2;February ... 12;December

Now, instead of 12 buttons, have a single unbound combo box with a label
"Select by Quarter End:". Make its properties the same as the combo box
above, except for the following:
Name: cboFilterQtrEnd
ControlSource: <blank>
RowSource: 0;<show all>;1;January;2;February ... 12;December
DefaultValue: 0

Finally, use this combo's AfterUpdate event to set the appropriate
filter on the form:

Private Sub cboFilterQtrEnd_AfterUpdate()
If IsNull(cboFilterQtrEnd) then cboFilterQtrEnd = 0
If cboFilterQtrEnd = 0 Then
Me.FilterOn = False
Else
Me.Filter = "fdQtrEnd=" & cboFilterQtrEnd
If Not Me.FilterOn then Me.FilterOn = True
End If
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi
I have table with 3 fields and a Form with 12 buttons "Jan, Feb,
March...Dec" Caption

tblClients
fdFirstName
fdLastName
fdQtrEnd // row source is Jan, Feb, March....Dec

frmMenu


I would to make a Button on the Form Say for May month to show me a
Preview of only those Clients who has Quarter end "May" selected in
fdQtrEnd..

how can I do that.
Thanks