By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,501 Members | 2,850 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,501 IT Pros & Developers. It's quick & easy.

Multiple Filtering on a subform

P: n/a
I have your typically form/subform. You enter the account number in a
textbox and select whether you want to see the detail or summary
information on the main form. Both fields I want to filter on are
text. Here is a sample of the code I am using.

The filtering works fine when I do them independently (see below)
Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.Filter = "[EKCNBR]
=" & Me![bxAcctNbr]

Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.Filter =
"[BREAKLVL] =" & "'" & 2 & "'"

When I combine them, I receive the error message "You canceled the
previous operation" when it hits the filterOn = true line.
strfilter = "[EKCNBR] = """ & Me![bxAcctNbr] & """" & " And " &
"[BREAKLVL] = "" & 2 & """
Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.Filter = strfilter
Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.FilterOn = True

Can anyone figure this out? Thanks for any help you can provide
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
strfilter = "([EKCNBR] = """ & Me![bxAcctNbr] & """) And ([BREAKLVL] =
""2"")"
Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.Filter = strfilter
Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.FilterOn = True

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Doug" <do********@cox.com> wrote in message
news:1e**************************@posting.google.c om...
I have your typically form/subform. You enter the account number in a
textbox and select whether you want to see the detail or summary
information on the main form. Both fields I want to filter on are
text. Here is a sample of the code I am using.

The filtering works fine when I do them independently (see below)
Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.Filter = "[EKCNBR]
=" & Me![bxAcctNbr]

Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.Filter =
"[BREAKLVL] =" & "'" & 2 & "'"

When I combine them, I receive the error message "You canceled the
previous operation" when it hits the filterOn = true line.
strfilter = "[EKCNBR] = """ & Me![bxAcctNbr] & """" & " And " &
"[BREAKLVL] = "" & 2 & """
Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.Filter = strfilter
Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.FilterOn = True

Can anyone figure this out? Thanks for any help you can provide

Nov 12 '05 #2

P: n/a
I am still getting the error message "You canceled the previous
operation". Is there another way to process this request?

Thanks for your help

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@freenews.iinet.net .au>...
strfilter = "([EKCNBR] = """ & Me![bxAcctNbr] & """) And ([BREAKLVL] =
""2"")"
Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.Filter = strfilter
Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.FilterOn = True

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Doug" <do********@cox.com> wrote in message
news:1e**************************@posting.google.c om...
I have your typically form/subform. You enter the account number in a
textbox and select whether you want to see the detail or summary
information on the main form. Both fields I want to filter on are
text. Here is a sample of the code I am using.

The filtering works fine when I do them independently (see below)
Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.Filter = "[EKCNBR]
=" & Me![bxAcctNbr]

Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.Filter =
"[BREAKLVL] =" & "'" & 2 & "'"

When I combine them, I receive the error message "You canceled the
previous operation" when it hits the filterOn = true line.
strfilter = "[EKCNBR] = """ & Me![bxAcctNbr] & """" & " And " &
"[BREAKLVL] = "" & 2 & """
Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.Filter = strfilter
Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.FilterOn = True

Can anyone figure this out? Thanks for any help you can provide

Nov 12 '05 #3

P: n/a
The message indicates that there is something wrong with the filter string.
Perhaps there is a spelling mistake, or perhaps the delimiters are wrong.

For example, if CKCNBR is a Number type field (not a Text type field), drop
the extra quotes:
strfilter = "([EKCNBR] = " & Me![bxAcctNbr] & ") And ...

One way to see how this should look would be to make a query into this
table. Put any old number as an example under the 2 fields. Then switch to
SQL view (View menu), and see what the WHERE clause looks like. At least you
know what strfilter should end up like.

Now add this to your code:
Debug.Print strFilter
After it messes up, open the Immediate Window (Ctl+G) and compare what it
wrote to what you saw in the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Doug" <do********@cox.com> wrote in message
news:1e**************************@posting.google.c om...
I am still getting the error message "You canceled the previous
operation". Is there another way to process this request?

Thanks for your help

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<40***********************@freenews.iinet.net .au>...
strfilter = "([EKCNBR] = """ & Me![bxAcctNbr] & """) And ([BREAKLVL] =
""2"")"
Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.Filter = strfilter
Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.FilterOn = True

"Doug" <do********@cox.com> wrote in message
news:1e**************************@posting.google.c om...
I have your typically form/subform. You enter the account number in a
textbox and select whether you want to see the detail or summary
information on the main form. Both fields I want to filter on are
text. Here is a sample of the code I am using.

The filtering works fine when I do them independently (see below)
Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.Filter = "[EKCNBR]
=" & Me![bxAcctNbr]

Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.Filter =
"[BREAKLVL] =" & "'" & 2 & "'"

When I combine them, I receive the error message "You canceled the
previous operation" when it hits the filterOn = true line.
strfilter = "[EKCNBR] = """ & Me![bxAcctNbr] & """" & " And " &
"[BREAKLVL] = "" & 2 & """
Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.Filter = strfilter
Forms![frmPaymentSearch].[sbfrmPaymentSearch].Form.FilterOn = True

Can anyone figure this out? Thanks for any help you can provide

Nov 12 '05 #4

P: n/a
The did the trick!!!
strfilter = "([EKCNBR] = " & Me![bxAcctNbr] & ") And ([BREAKLVL]
=""2"")". The field EKCNBR contains numbers but based on the field data
type it shows it as a text. I only assumed I should be looking for text
rather than a number. The table is a linked table from the AS400 that I
did not create. Thank you so very much!!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.