Repost: My Filtering Question

[John R. Schmidt]

A portion of my application implements a process derived from the Access
Cookbook (O'Reilly) entitled "Print Only Records Matching A Form's Filter."
To do this, I have a switchboard command button with the caption "Filter
Transactions." The On Click event for this command button calls the
following elementary code segment:



Private Sub cmdFilter_Click()

DoCmd.OpenForm "frmJRS"

End Sub



As in the O'Reilly book, frmJRS is an unbound main form with an embedded
subform bound to a query (qryJRS), which in turn displays its fields as a
datasheet. I have this working. However, there's a problem. Two of the
latter fields are text fields with combo boxes to act as lookups, and they
have the LimitToList property set to True. When I define the filter
conditions for the datasheet using Filter by Form and include one of these
text fields, the scheme fails due to the LimitToList test . If, on the
other hand, I turn off the LimitToList test ahead of time, I can
successfully implement such filtering expressions as In, Between, Like, etc.



Now, at long last, to my question. Can I temporarily disable the
LimitToList test by inserting statements ahead and directly behind the
OpenForm command in the calling subroutine above. Those ahead would set the
LimitToList property to False, while the subsequent statements would
restore this property to True. Is this a sound approach?

And if so, HOW do you reference the fields involved?



Thanks loads for your help. JRS

 

[Albert D. Kallal]

Two of the

latter fields are text fields with combo boxes to act as lookups

did you build those combo boxes, or are you using the built in lookup
feature at the table level?

I suggest you read the following short list..and take CAREFUL NOTE of the
2th suggestion:

http://www.mvps.org/access/tencommandments.htm

So, to solve this problem, simply build a nice continues form, but don't use
the datasheet view..but use the continues form view. That way, you can use
the wizard ON THE FORM to build a combo box. You are then free to set those
combo boxes limits to list to yes, or no..but not effect anything to do with
your table designs.

At this point you might be able to continue to use the horrible lookup
field, but at any rate..simply built your own continues sub form..and use
the combo box wizard on the form to create the combo. That way, after your
done..you can change (and save) the combo box setting with the form..and no
code will be needed to do this.

I also often use prompt forms for reports. For some screen shots of what I
mean..check out:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

And, to see some screen shots of what continues forms look like..and NOT use
a datasheet..check out:
http://www.attcanada.net/~kallal.msn/Articles/Grid.htm

You will notice that there is little, if any difference between using a
datasheet..and the preferred continues form as in the grids examples above.

 

[John R. Schmidt]

Thanks for responding. I built the combo boxes for the two text fields,
without the lookup wizard. These are important to me. (I have long since
read The Ten Commandments.) Access Help for the Limit To List property says
one can use VBA to set this property to True or False. I merely want to
know how to do it!!
Do you need to call a special Sub, or what? If a standard code line will do
it, how should that line be written, given the following:
The table: tblDemoTrans
The query: qryJRS
The field: ClientShortName (in position 0)

What in the world is a "continues form" Is this a Kallal invention or are
you talking about continuous forms?
John

 

[Albert D. Kallal]

Thanks for responding. I built the combo boxes for the two text fields,
without the lookup wizard.

Access Help for the Limit To List property says

one can use VBA to set this property to True or False. I merely want to
know how to do it!!

Hum..ok...it just seemed strange why you don't just build the combo boxes
with the limit to list turned off in the first place?
conditions for the datasheet using Filter by Form and include one of these
text fields, the scheme fails due to the LimitToList test . If, on the
other hand, I turn off the LimitToList test ahead of time, I can
successfully implement such filtering expressions as In, Between, Like, etc.

Hum, I was not aware of the above being the problem. Something here is not
quite right?? You can certainly change the limit to list property to yes, or
no..but that does seem to follow some logic to fix your problem

me.ClientShortName.LimitToList = False

It just seems to me very dangerous to allow, or change a limit to list to
"no/false" if the limit does not need to in fact need to be?

You are going to be faced with the problem of control when, and when not to
have this limit set. Users will likely break this feature often,a nd type in
things that don't belong....

What in the world is a "continues form" Is this a Kallal invention or are
you talking about continuous forms?

yes..my spell checker ate that one!! Thanks!!