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

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.
I think this is easiest, yes. Good luck with your project!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.