Using A Form's Filter String As A SQL Where Clause

Posted on 2006-04-14
Last Modified: 2006-11-18
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!


Question by:CCorrea01
    LVL 58

    Expert Comment


    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?

    Author Comment

    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.
    LVL 58

    Accepted Solution

    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!
    LVL 58

    Expert Comment

    I think this is easiest, yes. Good luck with your project!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now