Refresh Filter After ComboBox Selection

[ellidavd]

I have an unbound combobox where a user can select three colors. When
the form first loads, the navigation buttons at the bottom show the
number of all the records in the table. How would I get the form to
filter and display the number of records on the navigation buttons
based on the user selections? I'll try to make my question clearer. I
have 10 records with different color combinations. Three of those
records might have the combination blue, red, green. When the form
first loads, the navigation buttons will show a total of 10 records.
That is ok. Then, can I get it to display only the three records with
the blue, red, green combination when a user selects those choices?

I am basically trying to do something similar to calling another form
filtered on the selections, but I would like to have it filter the
current form instead of calling another form.

I'll actually be using the previous and next record buttons rather than
the navigation buttons, but I think the solution will be the same.

I might use a button to refresh the form based on the filters, or I
might have it refresh as the user makes selections. An answer to either
technique would be appreciated.

Thanks.

 

[strive4peace]

although you have just one combo, this example is written so you can
have more...

put comboboxes, textboxes, listboxes, etc on the form (i put then in the
header). Assign this to the AfterUpdate event of each one...

=SetFormFilter()

then put this code behind the form

'~~~~~~~~~~~~~~~
Private Function SetFormFilter()

dim mFilter as string
mFilter = ""

If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" _
& me.controlname_for_text & "'"
end if

If not IsNull(me.date_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[DateFieldname]= #" _
& me.controlname_for_date & "#"
end if

If not IsNull(me.numeric_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " _
& me.controlname_for_number
end if

if len(mfilter) > 0 then
me.filter = mfilter
me.FilterOn = true
else
me.FilterOn = false
end if

me.requery

End Function
'~~~~~~~~~~~~~~~



me.controlname_for_number refers to the NAME property of a control on
the form you are behind (Me. represents the form -- kinda like "me" for
me is not "me" for you )

delimiters are used for data that is not a number
quote marks ' or " for text
number signs # for dates

mfilter is a string that is being built for each condition -- but if
nothing is specified in the filter control (IsNull), then that addition
to the filter string is skipped.

finally, when the filter string is done, it is applied to your form.

That means that as you flip through records, ONLY records matching that
filter will show

Then, put another command button on the form

Name --> btnShowAll
OnClick --> [Event Procedure]

'~~~~~~~~~~~~~~~
me.filteron = false
me.requery
'~~~~~~~~~~~~~~~

a requery of the form will also reset the record pointer back to the
first record.

If you want to capture the record you are on and set it back, you can do
this:

'~~~~~~~~~~~~~~~
'save value of primary key
dim mPrimaryKey as long
mPrimaryKey = 0
if not me.newrecord then
mPrimaryKey = nz(me.mPrimaryKey_controlname)
end if

me.filteron = false
me.requery

'go back to record you were on
if mPrimaryKey <> 0 then
me.mPrimaryKey_controlname.setfocus
docmd.findrecord mPrimaryKey
end if

'~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[ellidavd]

Thank you, Crystal. You saved me a lot of time. I was able to use this
code for several forms and will probably use it again.

although you have just one combo, this example is written so you can
have more...

put comboboxes, textboxes, listboxes, etc on the form (i put then in the
header). Assign this to the AfterUpdate event of each one...

=SetFormFilter()

then put this code behind the form

'~~~~~~~~~~~~~~~
Private Function SetFormFilter()

dim mFilter as string
mFilter = ""

If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" _
& me.controlname_for_text & "'"
end if

If not IsNull(me.date_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[DateFieldname]= #" _
& me.controlname_for_date & "#"
end if

If not IsNull(me.numeric_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " _
& me.controlname_for_number
end if

if len(mfilter) > 0 then
me.filter = mfilter
me.FilterOn = true
else
me.FilterOn = false
end if

me.requery

End Function
'~~~~~~~~~~~~~~~



me.controlname_for_number refers to the NAME property of a control on
the form you are behind (Me. represents the form -- kinda like "me" for
me is not "me" for you )

delimiters are used for data that is not a number
quote marks ' or " for text
number signs # for dates

mfilter is a string that is being built for each condition -- but if
nothing is specified in the filter control (IsNull), then that addition
to the filter string is skipped.

finally, when the filter string is done, it is applied to your form.

That means that as you flip through records, ONLY records matching that
filter will show

Then, put another command button on the form

Name --> btnShowAll
OnClick --> [Event Procedure]

'~~~~~~~~~~~~~~~
me.filteron = false
me.requery
'~~~~~~~~~~~~~~~

a requery of the form will also reset the record pointer back to the
first record.

If you want to capture the record you are on and set it back, you can do
this:

'~~~~~~~~~~~~~~~
'save value of primary key
dim mPrimaryKey as long
mPrimaryKey = 0
if not me.newrecord then
mPrimaryKey = nz(me.mPrimaryKey_controlname)
end if

me.filteron = false
me.requery

'go back to record you were on
if mPrimaryKey <> 0 then
me.mPrimaryKey_controlname.setfocus
docmd.findrecord mPrimaryKey
end if

'~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

I have an unbound combobox where a user can select three colors. When
the form first loads, the navigation buttons at the bottom show the
number of all the records in the table. How would I get the form to
filter and display the number of records on the navigation buttons
based on the user selections? I'll try to make my question clearer. I
have 10 records with different color combinations. Three of those
records might have the combination blue, red, green. When the form
first loads, the navigation buttons will show a total of 10 records.
That is ok. Then, can I get it to display only the three records with
the blue, red, green combination when a user selects those choices?

I am basically trying to do something similar to calling another form
filtered on the selections, but I would like to have it filter the
current form instead of calling another form.

I'll actually be using the previous and next record buttons rather than
the navigation buttons, but I think the solution will be the same.

I might use a button to refresh the form based on the filters, or I
might have it refresh as the user makes selections. An answer to either
technique would be appreciated.

Thanks.

 

[strive4peace]

you're welcome happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Thank you, Crystal. You saved me a lot of time. I was able to use this
code for several forms and will probably use it again.

although you have just one combo, this example is written so you can
have more...

put comboboxes, textboxes, listboxes, etc on the form (i put then in the
header). Assign this to the AfterUpdate event of each one...

=SetFormFilter()

then put this code behind the form

'~~~~~~~~~~~~~~~
Private Function SetFormFilter()

dim mFilter as string
mFilter = ""

If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" _
& me.controlname_for_text & "'"
end if

If not IsNull(me.date_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[DateFieldname]= #" _
& me.controlname_for_date & "#"
end if

If not IsNull(me.numeric_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " _
& me.controlname_for_number
end if

if len(mfilter) > 0 then
me.filter = mfilter
me.FilterOn = true
else
me.FilterOn = false
end if

me.requery

End Function
'~~~~~~~~~~~~~~~



me.controlname_for_number refers to the NAME property of a control on
the form you are behind (Me. represents the form -- kinda like "me" for
me is not "me" for you )

delimiters are used for data that is not a number
quote marks ' or " for text
number signs # for dates

mfilter is a string that is being built for each condition -- but if
nothing is specified in the filter control (IsNull), then that addition
to the filter string is skipped.

finally, when the filter string is done, it is applied to your form.

That means that as you flip through records, ONLY records matching that
filter will show

Then, put another command button on the form

Name --> btnShowAll
OnClick --> [Event Procedure]

'~~~~~~~~~~~~~~~
me.filteron = false
me.requery
'~~~~~~~~~~~~~~~

a requery of the form will also reset the record pointer back to the
first record.

If you want to capture the record you are on and set it back, you can do
this:

'~~~~~~~~~~~~~~~
'save value of primary key
dim mPrimaryKey as long
mPrimaryKey = 0
if not me.newrecord then
mPrimaryKey = nz(me.mPrimaryKey_controlname)
end if

me.filteron = false
me.requery

'go back to record you were on
if mPrimaryKey <> 0 then
me.mPrimaryKey_controlname.setfocus
docmd.findrecord mPrimaryKey
end if

'~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

I have an unbound combobox where a user can select three colors. When
the form first loads, the navigation buttons at the bottom show the
number of all the records in the table. How would I get the form to
filter and display the number of records on the navigation buttons
based on the user selections? I'll try to make my question clearer. I
have 10 records with different color combinations. Three of those
records might have the combination blue, red, green. When the form
first loads, the navigation buttons will show a total of 10 records.
That is ok. Then, can I get it to display only the three records with
the blue, red, green combination when a user selects those choices?

I am basically trying to do something similar to calling another form
filtered on the selections, but I would like to have it filter the
current form instead of calling another form.

I'll actually be using the previous and next record buttons rather than
the navigation buttons, but I think the solution will be the same.

I might use a button to refresh the form based on the filters, or I
might have it refresh as the user makes selections. An answer to either
technique would be appreciated.

Thanks.