Using A Form's Filter String As A SQL Where Clause
Posted on 2006-04-14
I'm working on a form to facilitate the service area transitions for various groups of stores throughout the US. For each phase of the transition plan, there will be a group of stores that will be serviced from a different DC. Part of the schedule tracks specific transition dates, new carriers and new delivery terminals per store. The users will use Access form filter functionality to limit the store list in the details section until they have the set they want to apply new values to. In the header section they will set the new values and then click a button to apply them.
What I am trying to do is take the filter criteria from the details section and use that as a where clause for the update SQL. I'm running into problems with the combo boxes, where the filter text is like "Lookup_cboNewCarrierID.CarrierCode='DAWI'". My first approach was to extract the entire lookup section of the string, then the code it's looking for and finally opening a recordset to get the ID behind the code to use in the update SQL. This approach is turning out to be very difficult to code, and very time consuming.
My questions are:
1. Is there a better method for applying default values to filtered records only?
2. If not, is there a better method for using a form's filter string as a SQL where clause?
Any help or suggestions would be greatly appreciated!