Using A Form's Filter String As A SQL Where Clause
Hello Experts,
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.Ca
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!
Thanks,
Correa
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.Ca
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!
Thanks,
Correa
ASKER
Yeah, I was afraid that was going to be the case. I'm not the world's most gifted coder, and I don't like writing complex procedures (head... hurts...), so I was hoping there might be an easy way to handle this. The other option I'm thinking about is coding the form so that it takes the default values from the header and cycles through all of the records in the subform and set the values that way. I've done something similar to that before, so it shouldn't be too much of a stretch.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think this is easiest, yes. Good luck with your project!
(°v°)
(°v°)
You can either use VB and the form's Recordset (or RecordsetClone) to parse the records you need or, indeed, use the form's filter.
Converting the "Lookup_cboNewCarrierID.Ca
Technically, you could automate the process, by inserting the entire RowSource string of the combo as subquery, but I think I would hard-code and fine-tune it instead. Mainly because the combo's query might very well not be updatable...
Does this make sense?
(°v°)