Option Group not requerying after remove filter

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!
 
J

Jeanette Cunningham

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
 
L

Lisa M. Fida

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...
 
J

Jeanette Cunningham

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...
 
L

Lisa M. Fida

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...
 
S

Sean King

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter a subform from main form 2
Runtime Error 2001,when setting filter. 7
Urgent - filter subform by form - option group 2
Using a combo box to display info in a subform 5
Option group to filter a form 5
Option group & paramater error 8
"Enter parameter value" with multiple form instances 1
Filter a Sub-Form Using Option Buttons 2

Top