Filter & Requery Subform

[Bill]

I get an error to the effect that Access can't find the
field referred to in your expression. I just want to set
the filter expression in the RecordSource of the
subform "TransactionsSubform". I have to assume
I don't have the syntax correct?????

Form("TransactionsSubform")!Filter = "FolioID = " & strFolioID
Form("TransactionsSubform")!FilterOn = True
Form("TransactionsSubform").Requery

Thanks,
Bill

 

[Bill]

I get the same error with this syntax:

Forms!TransAccounting!TransAcctSubform.Form.Filter = "FolioID = " &
strFolioID
Forms!TransAccounting!TransAcctSubform.Form.FilterOn = True
Forms!TransAccounting!TransAcctSubform.Form.Requery

What am I missing here?
Thanks,
Bill

 

[Bill]

Here's where I am now:

Error: 2448
"You can't assign a value to this object"

SubFrmFilter = "FolioID = " & strFolioID

Me.TransAcctSubform.Form.Filter = SubFrmFilter <<< Err 2448
Me.TransAcctSubform.Form.FilterOn = True
Me.TransAcctSubform.Form.Requery

What gives??

Bill

 

[Bill]

Good Grief!!! Here's what the filter expression required:

SubFrmFilter = "FolioID = """ & strFolioID & """"

 

[John W. Vinson]

Good Grief!!! Here's what the filter expression required:

SubFrmFilter = "FolioID = """ & strFolioID & """"

Right. The Filter expression needs to be a valid SQL WHERE clause (without the
word WHERE); and if the field being searched is of Text type, the search term
must be delimited by quotemarks, either ' or ". If strFolioID is A123, the
filter string needs to be either

FolioID = 'A123'

or

FolioID = "A123"

So you could use either

SubFrmFilter = "FolioID = '" & strFolioID & "'"

which, "blown up" for readability, is

SubFrmFilter = "FolioID = ' " & strFolioID & " ' "

putting a literal ' mark within the string; or you can use two consecutive
doublequote characters within the string constant to introduce a ":

SubFrmFilter = "FolioID = """ & strFolioID & """"

which blows up to

SubFrmFilter = "FolioID = "" " & strFolioID & " "" "

Using ' is simpler if the string being searched will never contain an
apostrophe; if you're searching free text (people's names for instance) you
can't rely on that to be true (e.g. LastName = "O'Neal").
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bill]

Thanks John. Now that you mention it, I seem to
recall something from the past about the filter
expression having to satisfy the rules for strings
in SQL "WHERE" clauses. Sorry to say that I had
to fumble around with this one for quite awhile
this afternoon.

While you seem to be around, would you do me
a favor and look at my post in the Forms NG
from last night, "Forms - Conditional Formatting".
I've scrubbed my brain and Googled the issue
trying to solve, but so far I"ve not had any
success.

Thanks,
Bill