Option Group not requerying after remove filter

  • Thread starter Thread starter Lisa M. Fida
  • Start date Start date
L

Lisa M. Fida

Windows XP, Access 2003

I have an unbound main form with a bound (to an SQL statement) subform. The
subform's SQL statement has a field called 'Posted' which uses the value of
an unbound option group on the main form to select the appropriate records
(1=No, 2=Yes). The OptionGroup_AfterUpdate event requeries the subform and
works perfectly. The problem occurs when I apply a filter to the form and
then unapply the filter. When I apply the filter (filtering on a field
called 'Vendor') it still gives the desired results; i.e. I can click on the
option group and get the Posted or Unposted records for the selected Vendor.
When I remove the filter the subform displays all the records for Posted (if
that was originally selected when the filter was applied) or Unposted (if
that was originally selected). But if I click the other option nothing
happens. Very frustrating. Here is my code:

My subform's SQL statement:
SELECT APHeader.APHdrRID, APHeader.Vendor, APHeader.Name, APHeader.Invoice,
APHeader.InvoiceDescription, Choose(APHeader!InvoiceType,"Invoice","Credit
Memo") AS InvoiceType, APHeader.InvoiceDate, APHeader.DueDate,
APHeader.DiscountDate, APHeader.InvoiceAmt, APHeader.Posted
FROM APHeader
WHERE (((APHeader.Posted)=CStr([Forms]![APInvoiceMain]![optPostingStatus])));

My option group code:
Private Sub optPostingStatus_AfterUpdate()
On Error GoTo ErroptPostingStatus_AfterUpdate
Dim frm As Form
Set frm = Forms!APInvoiceMain!subfrmAPInvoiceMainSub.Form
frm.Requery
ExitoptPostingStatus_AfterUpdate:
Exit Sub
ErroptPostingStatus_AfterUpdate:
MsgBox Err.Description
Resume ExitoptPostingStatus_AfterUpdate
End Sub

My filter code:
Private Sub tglApplyFilter_Click()
On Error GoTo ErrtglApplyFilter_Click
Dim frm As Form
Dim strFilterField As String
Dim strFilterValue As String
Dim strFormFilter As String
Set frm = Forms!APInvoiceMain!subfrmAPInvoiceMainSub.Form
If Me!tglApplyFilter.Value Then
strFilterField = Me!cboFilterField
strFilterValue = Me!cboFilterValue
strFormFilter = "[" & strFilterField & "] = '" & strFilterValue & "'"
frm.Filter = strFormFilter
frm.FilterOn = True
Else
frm.Filter = ""
frm.FilterOn = False
End If
ExittglApplyFilter_Click:
Exit Sub
ErrtglApplyFilter_Click:
MsgBox Err.Description
Resume ExittglApplyFilter_Click
End Sub

Any suggestions would be greatly appreciated!
 
Hi Lisa,
Instead of using the filter property, just requery the subform after the
option group is updated.
You will probably need a 3rd option in your option group called show all.
For show all, the subform's sql statement would need to have the where
clause removed.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Hello Jeanette,
Thank you for your response. My goal is to have the filter button and the
option group work independently. And I don't necessarily what to show all
records (Posted and Unposted) at the same time. What I don't understand is
why the requery of the subform is not working after the filter is removed?
Once the filter is removed I still want to see just Posted or Unposted
records. Is there another way of going about this?

Thank you!
--
Lisa M. Fida


Jeanette Cunningham said:
Hi Lisa,
Instead of using the filter property, just requery the subform after the
option group is updated.
You will probably need a 3rd option in your option group called show all.
For show all, the subform's sql statement would need to have the where
clause removed.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Lisa M. Fida said:
Windows XP, Access 2003

I have an unbound main form with a bound (to an SQL statement) subform.
The
subform's SQL statement has a field called 'Posted' which uses the value
of
an unbound option group on the main form to select the appropriate records
(1=No, 2=Yes). The OptionGroup_AfterUpdate event requeries the subform
and
works perfectly. The problem occurs when I apply a filter to the form and
then unapply the filter. When I apply the filter (filtering on a field
called 'Vendor') it still gives the desired results; i.e. I can click on
the
option group and get the Posted or Unposted records for the selected
Vendor.
When I remove the filter the subform displays all the records for Posted
(if
that was originally selected when the filter was applied) or Unposted (if
that was originally selected). But if I click the other option nothing
happens. Very frustrating. Here is my code:

My subform's SQL statement:
SELECT APHeader.APHdrRID, APHeader.Vendor, APHeader.Name,
APHeader.Invoice,
APHeader.InvoiceDescription, Choose(APHeader!InvoiceType,"Invoice","Credit
Memo") AS InvoiceType, APHeader.InvoiceDate, APHeader.DueDate,
APHeader.DiscountDate, APHeader.InvoiceAmt, APHeader.Posted
FROM APHeader
WHERE
(((APHeader.Posted)=CStr([Forms]![APInvoiceMain]![optPostingStatus])));

My option group code:
Private Sub optPostingStatus_AfterUpdate()
On Error GoTo ErroptPostingStatus_AfterUpdate
Dim frm As Form
Set frm = Forms!APInvoiceMain!subfrmAPInvoiceMainSub.Form
frm.Requery
ExitoptPostingStatus_AfterUpdate:
Exit Sub
ErroptPostingStatus_AfterUpdate:
MsgBox Err.Description
Resume ExitoptPostingStatus_AfterUpdate
End Sub

My filter code:
Private Sub tglApplyFilter_Click()
On Error GoTo ErrtglApplyFilter_Click
Dim frm As Form
Dim strFilterField As String
Dim strFilterValue As String
Dim strFormFilter As String
Set frm = Forms!APInvoiceMain!subfrmAPInvoiceMainSub.Form
If Me!tglApplyFilter.Value Then
strFilterField = Me!cboFilterField
strFilterValue = Me!cboFilterValue
strFormFilter = "[" & strFilterField & "] = '" & strFilterValue &
"'"
frm.Filter = strFormFilter
frm.FilterOn = True
Else
frm.Filter = ""
frm.FilterOn = False
End If
ExittglApplyFilter_Click:
Exit Sub
ErrtglApplyFilter_Click:
MsgBox Err.Description
Resume ExittglApplyFilter_Click
End Sub

Any suggestions would be greatly appreciated!
Click to expand...
Click to expand...
 
There are some bugs with filters in access. One bug affects filters applied
to subforms when you try to remove the filter.
Allen Browne has a list of them here
http://allenbrowne.com/bug-02.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Lisa M. Fida said:
Hello Jeanette,
Thank you for your response. My goal is to have the filter button and the
option group work independently. And I don't necessarily what to show all
records (Posted and Unposted) at the same time. What I don't understand
is
why the requery of the subform is not working after the filter is removed?
Once the filter is removed I still want to see just Posted or Unposted
records. Is there another way of going about this?

Thank you!
--
Lisa M. Fida


Jeanette Cunningham said:
Hi Lisa,
Instead of using the filter property, just requery the subform after the
option group is updated.
You will probably need a 3rd option in your option group called show all.
For show all, the subform's sql statement would need to have the where
clause removed.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Lisa M. Fida said:
Windows XP, Access 2003

I have an unbound main form with a bound (to an SQL statement) subform.
The
subform's SQL statement has a field called 'Posted' which uses the
value
of
an unbound option group on the main form to select the appropriate
records
(1=No, 2=Yes). The OptionGroup_AfterUpdate event requeries the subform
and
works perfectly. The problem occurs when I apply a filter to the form
and
then unapply the filter. When I apply the filter (filtering on a field
called 'Vendor') it still gives the desired results; i.e. I can click
on
the
option group and get the Posted or Unposted records for the selected
Vendor.
When I remove the filter the subform displays all the records for
Posted
(if
that was originally selected when the filter was applied) or Unposted
(if
that was originally selected). But if I click the other option nothing
happens. Very frustrating. Here is my code:

My subform's SQL statement:
SELECT APHeader.APHdrRID, APHeader.Vendor, APHeader.Name,
APHeader.Invoice,
APHeader.InvoiceDescription,
Choose(APHeader!InvoiceType,"Invoice","Credit
Memo") AS InvoiceType, APHeader.InvoiceDate, APHeader.DueDate,
APHeader.DiscountDate, APHeader.InvoiceAmt, APHeader.Posted
FROM APHeader
WHERE
(((APHeader.Posted)=CStr([Forms]![APInvoiceMain]![optPostingStatus])));

My option group code:
Private Sub optPostingStatus_AfterUpdate()
On Error GoTo ErroptPostingStatus_AfterUpdate
Dim frm As Form
Set frm = Forms!APInvoiceMain!subfrmAPInvoiceMainSub.Form
frm.Requery
ExitoptPostingStatus_AfterUpdate:
Exit Sub
ErroptPostingStatus_AfterUpdate:
MsgBox Err.Description
Resume ExitoptPostingStatus_AfterUpdate
End Sub

My filter code:
Private Sub tglApplyFilter_Click()
On Error GoTo ErrtglApplyFilter_Click
Dim frm As Form
Dim strFilterField As String
Dim strFilterValue As String
Dim strFormFilter As String
Set frm = Forms!APInvoiceMain!subfrmAPInvoiceMainSub.Form
If Me!tglApplyFilter.Value Then
strFilterField = Me!cboFilterField
strFilterValue = Me!cboFilterValue
strFormFilter = "[" & strFilterField & "] = '" & strFilterValue
&
"'"
frm.Filter = strFormFilter
frm.FilterOn = True
Else
frm.Filter = ""
frm.FilterOn = False
End If
ExittglApplyFilter_Click:
Exit Sub
ErrtglApplyFilter_Click:
MsgBox Err.Description
Resume ExittglApplyFilter_Click
End Sub

Any suggestions would be greatly appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Jeannette:

Thank you again! :)
--
Lisa M. Fida


Jeanette Cunningham said:
There are some bugs with filters in access. One bug affects filters applied
to subforms when you try to remove the filter.
Allen Browne has a list of them here
http://allenbrowne.com/bug-02.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Lisa M. Fida said:
Hello Jeanette,
Thank you for your response. My goal is to have the filter button and the
option group work independently. And I don't necessarily what to show all
records (Posted and Unposted) at the same time. What I don't understand
is
why the requery of the subform is not working after the filter is removed?
Once the filter is removed I still want to see just Posted or Unposted
records. Is there another way of going about this?

Thank you!
--
Lisa M. Fida


Jeanette Cunningham said:
Hi Lisa,
Instead of using the filter property, just requery the subform after the
option group is updated.
You will probably need a 3rd option in your option group called show all.
For show all, the subform's sql statement would need to have the where
clause removed.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Windows XP, Access 2003

I have an unbound main form with a bound (to an SQL statement) subform.
The
subform's SQL statement has a field called 'Posted' which uses the
value
of
an unbound option group on the main form to select the appropriate
records
(1=No, 2=Yes). The OptionGroup_AfterUpdate event requeries the subform
and
works perfectly. The problem occurs when I apply a filter to the form
and
then unapply the filter. When I apply the filter (filtering on a field
called 'Vendor') it still gives the desired results; i.e. I can click
on
the
option group and get the Posted or Unposted records for the selected
Vendor.
When I remove the filter the subform displays all the records for
Posted
(if
that was originally selected when the filter was applied) or Unposted
(if
that was originally selected). But if I click the other option nothing
happens. Very frustrating. Here is my code:

My subform's SQL statement:
SELECT APHeader.APHdrRID, APHeader.Vendor, APHeader.Name,
APHeader.Invoice,
APHeader.InvoiceDescription,
Choose(APHeader!InvoiceType,"Invoice","Credit
Memo") AS InvoiceType, APHeader.InvoiceDate, APHeader.DueDate,
APHeader.DiscountDate, APHeader.InvoiceAmt, APHeader.Posted
FROM APHeader
WHERE
(((APHeader.Posted)=CStr([Forms]![APInvoiceMain]![optPostingStatus])));

My option group code:
Private Sub optPostingStatus_AfterUpdate()
On Error GoTo ErroptPostingStatus_AfterUpdate
Dim frm As Form
Set frm = Forms!APInvoiceMain!subfrmAPInvoiceMainSub.Form
frm.Requery
ExitoptPostingStatus_AfterUpdate:
Exit Sub
ErroptPostingStatus_AfterUpdate:
MsgBox Err.Description
Resume ExitoptPostingStatus_AfterUpdate
End Sub

My filter code:
Private Sub tglApplyFilter_Click()
On Error GoTo ErrtglApplyFilter_Click
Dim frm As Form
Dim strFilterField As String
Dim strFilterValue As String
Dim strFormFilter As String
Set frm = Forms!APInvoiceMain!subfrmAPInvoiceMainSub.Form
If Me!tglApplyFilter.Value Then
strFilterField = Me!cboFilterField
strFilterValue = Me!cboFilterValue
strFormFilter = "[" & strFilterField & "] = '" & strFilterValue
&
"'"
frm.Filter = strFormFilter
frm.FilterOn = True
Else
frm.Filter = ""
frm.FilterOn = False
End If
ExittglApplyFilter_Click:
Exit Sub
ErrtglApplyFilter_Click:
MsgBox Err.Description
Resume ExittglApplyFilter_Click
End Sub

Any suggestions would be greatly appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Lisa,

Did you ever figure out why it did not update and find a solution around this?

I am having teh same problem where as if i select a radio button from my options group and then decided i want to select the other one it still gives me results for previous one i had selected!

i know im suppose to do a requery or referesh or something like that but how and where i have no idea ive tried nearly everything.

any guidance or thoughts would be greatly appreciated!



LisaMFid wrote:

Option Group not requerying after remove filter
22-Apr-09

Windows XP, Access 200

I have an unbound main form with a bound (to an SQL statement) subform. The
subform's SQL statement has a field called 'Posted' which uses the value of
an unbound option group on the main form to select the appropriate records
(1=No, 2=Yes). The OptionGroup_AfterUpdate event requeries the subform and
works perfectly. The problem occurs when I apply a filter to the form and
then unapply the filter. When I apply the filter (filtering on a field
called 'Vendor') it still gives the desired results; i.e. I can click on the
option group and get the Posted or Unposted records for the selected Vendor.
When I remove the filter the subform displays all the records for Posted (if
that was originally selected when the filter was applied) or Unposted (if
that was originally selected). But if I click the other option nothing
happens. Very frustrating. Here is my code

My subform's SQL statement:
SELECT APHeader.APHdrRID, APHeader.Vendor, APHeader.Name, APHeader.Invoice,
APHeader.InvoiceDescription, Choose(APHeader!InvoiceType,"Invoice","Credit
Memo") AS InvoiceType, APHeader.InvoiceDate, APHeader.DueDate,
APHeader.DiscountDate, APHeader.InvoiceAmt, APHeader.Poste
FROM APHeade
WHERE (((APHeader.Posted)=CStr([Forms]![APInvoiceMain]![optPostingStatus])))

My option group code
Private Sub optPostingStatus_AfterUpdate(
On Error GoTo ErroptPostingStatus_AfterUpdat
Dim frm As For
Set frm = Forms!APInvoiceMain!subfrmAPInvoiceMainSub.For
frm.Requer
ExitoptPostingStatus_AfterUpdate
Exit Su
ErroptPostingStatus_AfterUpdate
MsgBox Err.Descriptio
Resume ExitoptPostingStatus_AfterUpdat
End Su

My filter code
Private Sub tglApplyFilter_Click(
On Error GoTo ErrtglApplyFilter_Clic
Dim frm As For
Dim strFilterField As Strin
Dim strFilterValue As Strin
Dim strFormFilter As Strin
Set frm = Forms!APInvoiceMain!subfrmAPInvoiceMainSub.For
If Me!tglApplyFilter.Value The
strFilterField = Me!cboFilterFiel
strFilterValue = Me!cboFilterValu
strFormFilter = "[" & strFilterField & "] = '" & strFilterValue & "'
frm.Filter = strFormFilte
frm.FilterOn = Tru
Els
frm.Filter = "
frm.FilterOn = Fals
End I
ExittglApplyFilter_Click
Exit Su
ErrtglApplyFilter_Click
MsgBox Err.Descriptio
Resume ExittglApplyFilter_Clic
End Su

Any suggestions would be greatly appreciated
--
Lisa M. Fida

EggHeadCafe - Software Developer Portal of Choice
XAML "Windows Send Error Report"
http://www.eggheadcafe.com/tutorial...9c6-0e6c5954f2af/xaml-windows-send-error.aspx
 
Back
Top