473,326 Members | 2,588 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Multiple Filtering on a subform

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
4 8872
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jason | last post by:
I am trying to filter records in a primary form based on records in related tables. The data in the related tables is being displayed in the primary form through subforms. To be more specific, I...
3
by: arthur-e | last post by:
I can filter a query for a report on a field of a subform BUT now.... I'd like to be able to select more than one item in a multi-list box to select all the records. ALSO to use two fields (or...
19
by: William Wisnieski | last post by:
Hello Everyone, I have a main form with a datasheet subform that I use to query by form. After the user selects two criteria on the main form and clicks the cmdShowResults button on the main...
4
by: Doug | last post by:
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...
3
by: Todd | last post by:
Hi, I've been told in another forum that I can apply a filter to a subform (from a control on the main form) by using the following code: Me.sfrMySubform.Filter = " = 7" I've tried this...
2
by: Katie | last post by:
I need to store a history of two fields in two seperate tables: Event - where the key is Event_ID Stage - where the key is Stage_ID The joins are: Main Table Event Table Stage Table ...
3
by: paquer | last post by:
On my Main form I have a Command Button that opens a Subform in order to create a new Subform record. At this point I want the subform to show only the new record being created. Not all the...
1
by: woodey2002 | last post by:
Hi Everyone and many thanks for your time.. I am trying to begin access and a bit of VBA i am enjoying it but I have a annoying problem I just can’t get any where on. My databse mostly includes...
64
by: lovelydan | last post by:
hi all i need someone to help me with a code that will enable me filter records in a subform form through a combo box attached to a mainform-that has a main menu that controls all the sub form...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.