ADO Based Forms - Filters

[gray]

Hi

I'm just about to begin adding some more complex filters into an ADO RS based form (subform actually) and I have an architecture question please.

In the main-form open event, I build an SQL String, apply it to an ADO RS.Open, open the subform with the RS. I then use filters in the main-form Current event (using the new current main-form ID) in order to replicate the Master/Child links functionality in DAO. This works hunky dorey for simple filters.

I started this weeks ago, got distracted and I'm now coming back to it. At that time I'm positive I read somewhere that ADO filters do not have an Operator Preference for ANDs & ORs?? I've looked around but can't find that info again.

Before I start unpicking reams of my code and wandering into the Access maze again I wonder, could I ask please; Firstly, did I imagine this? Secondly, if it is true, how does one achieve the equivalent of the below.

strFilter= "Surname='Smith' AND (FirstName='John' OR FirstName='Fred')
myRS.Filter = adFilterNone
myRS.Filter = strFilter
Set myForm.Recordset = MyForm.Recordset

Thanks

Edit-

Ran a few tests with mixed ANDs & ORs and they seem to produce the correct results... I never trust things I've even seen with my own eyes nowadays.... so any affirmation or contradiction to my results would be much appreciated

 

[gray]

Hi

Thanks for the reply. Firstly, I have to apologise for raising this thread. I looked back and I'd raised a very similar one about ADO filters a few months back... I got so distracted with other problems recently that I completely forgot I'd gone through this already.

If you are reading this for the first time there is further explanation at:-
http://support.microsoft.com/kb/235892

As to ADO. I was persuaded to ADO because I'd read it was a requirement if SQL server or other Db back-ends might be needed in the future. As you say, Pat, this is a not a trivial conversion and is indeed fraught with problems. Here's a couple of observations.

a) Searching ("Find") is only allowed on one criteria so if you want to RS.Find First_name='Fred', Second_name = 'Bloggs' you have to use Rs.Filter instead. Oh the joy when this penny drops... after hours of trying to figure out why searches were not returning the correct results.
b) RS.Filters using AND & ORs can catch you out. They do not map directly with use in SQL ... see the link above
c) If you want to use the same functionality as DAO Master/Child links this can be done either by modifying the RS.Source (SELECT * FROM xxx) or updating an RS.Filter. These would be modified in the main form current event.
Now the former necessitates a reload of the entire subform each time ... click to another record in the main form where there are multiple subforms and you will have time to catch up on your filing.
If you use the latter (RS.Filter), the RS.Source obviously must contain at least the records you are looking for... so you have to load the whole or a subset of the table in the Rs.Source. Add in large tables and network traffic.. well you get the picture.
d) There are a bedazzling array of options for cursors, locks and connections dependent upon the back-end and provider capabilities and if BeginTrans, CommitTrans is used.. deletes, adds or edits are needed.
e) I deduce that these are effectively disconnected RS's. With the options I have set, to see other peoples' new records the form needs to be reloaded, edited data can appear just by setting the RS to itself .. Set myForm.RS=myForm.RS.

Apologies again for raising my query twice.

 

[gray]

Hi Pat

I am testing SQL TXs in DAO Bound Forms even as I write! thanks and rgds