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.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!


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


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.CarrierCode" requires a little work. You would have to add the underlying table of the combo to your update query, and do a Replace() of that string to the reference <table>.CarrierCode.

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?
CCorrea01Author Commented:
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.
Yes, that would be the first suggestion above. Basically:

    With Me.RecordsetClone   ' or: Me.subFormControl.Form.RecordsetClone
        If .RecordCount Then .MoveFirst
        Do Until .EOF
            ' so stuff here, using any field(s), e.g.
            Debug.Print !strTransportationMode
    End With

If the number of records is reasonable, it will be fast enough that way.

Good luck!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I think this is easiest, yes. Good luck with your project!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.