Filtration of a form by data in the subform?

[Matthew DeAngelis]

Hi everyone,

I was hoping I could get some help with a filtration problem.

I am building a search function for my database. There are two main
tables that are being searched: one that contains information specific
to companies (such as address) and one that contains information
specific to deals (such as the status of a particular interaction).
Right now, I have it set up so that users can search either company
information or deal information, and the results of a concatenated
filter string are displayed in either the main company form (with deals
listed in a continuous subform) or the deal form. This mostly works
fine, since the data is usually handled separately. I just have one
problem: a majority of the deals in the database are old, and there
will be situations where my users will want to search only the
companies that have at least one deal that is not classified as 'dead'.

The [Status] field does show up on the deals subform on the companies
form, so I added a checkbox on the search form that, when checked, adds
the line " And Forms![frmSearchByCompanyDealSubform]![Status] Not Like
'6 - Dead/No Action'" to the Filter statement. According to my VBA
book, this is the valid syntax for referencing a field on a subform,
and at least it does not spit out an error. However, checking the
checkbox, rather than returning only active deals, returns no records
at all, even if I restrict the search to a company that has only active
deals.

Does anyone know what I am doing wrong? Is there another way of doing
this?


Thanks,
Matt

 

[Tim Ferguson]

The [Status] field does show up on the deals subform on the companies
form, so I added a checkbox on the search form that, when checked, adds
the line " And Forms![frmSearchByCompanyDealSubform]![Status] Not Like
'6 - Dead/No Action'" to the Filter statement.

I think you need to start the chain from the open form:

Forms("MainForm").Controls("sfmDeals").Form.Controls("Status")


... you can use the bang notation if you find it more legible! Note that you
refer to the subform control that holds the actual subform, then get its
Form property, and then access the controls on that.

Hope that helps


Tim F

 

[Ernie]

I also think you need an * in your 'like' statement,
unless of cource the '6 - Dead/No Action' fills the entire
field, in which case you don't need 'like' at all, just =

HTH

-----Original Message-----

The [Status] field does show up on the deals subform on the companies
form, so I added a checkbox on the search form that, when checked, adds
the line " And Forms![frmSearchByCompanyDealSubform]! [Status] Not Like
'6 - Dead/No Action'" to the Filter statement.

I think you need to start the chain from the open form:

Forms("MainForm").Controls("sfmDeals").Form.Controls ("Status")


... you can use the bang notation if you find it more legible! Note that you
refer to the subform control that holds the actual subform, then get its
Form property, and then access the controls on that.

Hope that helps


Tim F

.

 

[Matthew DeAngelis]

The [Status] field does show up on the deals subform on the
companies form, so I added a checkbox on the search form that, when
checked, adds the line " And
Forms![frmSearchByCompanyDealSubform]![Status] Not Like '6 -
Dead/No Action'" to the Filter statement.

I think you need to start the chain from the open form:

Forms("MainForm").Controls("sfmDeals").Form.Controls("Status")


.. you can use the bang notation if you find it more legible! Note
that you refer to the subform control that holds the actual subform,
then get its Form property, and then access the controls on that.

Hope that helps


Tim F

Hello Tim, and thanks for your help.

Here is my modified statement (I decided to use string variables to
make the thing a little more readable). It is not the only thing I
have tried, but I figured that it would be easier if I put it in your
preferred form:

stSearchCriteria = stSearchCriteria & " And
Forms(stDocName).Controls(stSubName).Form.Controls('Status') Not Like
'6 - Dead/No Action'"

I am now getting the following syntax error
Invalid use of '.', '!', or '()'. in query expression '[Company] Like
'*Saegis Pharmaceuticals, Inc.*' And
Forms(stDocName).Controls(stSubName).Form.Controls('[Status]') Not Like
'6 - Dead/No Action''.

I guess I can narrow it down to an invalid use of '.' or '()', but I
have been unable to feed it something valid. Can you help?


Thanks,
Matt

 

[Tim Ferguson]

stSearchCriteria = stSearchCriteria & " And
Forms(stDocName).Controls(stSubName).Form.Controls('Status') Not Like
'6 - Dead/No Action'"

Even the all-knowing Access expression evaluator does not know anything
about the contents of variables like stDocName. You have to supply the
actual values:

stSearch = stSearch & "AND " _
"Forms(" & stDocName & ").Controls(" & stSubName & ").Form..."


I have to say, though, that trying to write programs that deal with unknown
controls on unknown forms in the wild hope that it will all match up nine
months down the line when you are adding and updating stuff and forgotten
what you did back now, is like eating spaghetti with boxing gloves.

Still, best of luck


Tim F

 

[Matthew DeAngelis]

Even the all-knowing Access expression evaluator does not know
anything about the contents of variables like stDocName. You have to
supply the actual values:

stSearch = stSearch & "AND " _
"Forms(" & stDocName & ").Controls(" & stSubName & ").Form..."


I have to say, though, that trying to write programs that deal with
unknown controls on unknown forms in the wild hope that it will all
match up nine months down the line when you are adding and updating
stuff and forgotten what you did back now, is like eating spaghetti
with boxing gloves.

Still, best of luck


Tim F

The form names are showing up fine now, so thank you for pointing out
my problem. I am still getting the familiar syntax error, which
probably means I will need to play with quotes a little bit before I
get it right.

I am not sure what you mean by your comment regarding unknown controls
and forms, as I am clearly defining which form and subform objects and
fields I am using in the function. Still, I will re-evaluate my design
and see if I can find a better way to do what I am after.

Thank you for your help,
Matt

 

[Tim Ferguson]

I am not sure what you mean by your comment regarding unknown controls
and forms, as I am clearly defining which form and subform objects and
fields I am using in the function.

There probably are legitimate reasons for doing stuff like this, but in
general this smacks of 60-s style self-modifying code. For me, command
button that updates the Tweaking value of a record in the Twoozles table
can only ever refer to Forms!frmEditTwoozle.Controls!txtTweaking and there
does not seem to me to be much point in parameterising code just for the
sake of it: it just double or trebles the error-checking load.

This is probably more about style preferences, however, than any
objectively good or bad programing practice, so don't take too much notice!

All the best


Tim F