How Do I Dynamically Filter the ADP Combo Box Rowsource?

Posted on 2006-03-23
Last Modified: 2013-12-05
Hi, I have a combo box that works great in my MDB using linked tables, and I am trying to migrate it to an ADP.  The rowsource property in the MDB is as follows:

SELECT SubPeriods.SubStart, SubPeriods.ID_Subperiod, SubPeriods.ID_Plan FROM SubPeriods WHERE ((SubPeriods.ID_Plan)=Forms!AllocationPlanForm!ID_Plan);

ID_Plan is a control on the form that contains a value that must be used to filter the result set.

 In the ADP, I can't get it to work with the WHERE clause.   I get the following error message: "Incorrect Syntax Near !".  I know that ADP is different but I wonder what is the fastest approach to restricting a combo box based on field value.  Is the best approach to build a rowsource string and set the properties on the combo box when the form sends the OnCurrent event?
Question by:jm-johnmeyer-us
    LVL 28

    Accepted Solution

    You can set, and reset, the RowSource property of the combo box from a number of different events, i.e. Form, Open or Control, AfterUpdate or Button, Click

    You'd use something like

    Private Sub MyField-AfterUpdate()

       Dim strSQL As String

       strSQL = "SELECT SubPeriods.SubStart, SubPeriods.ID_Subperiod, SubPeriods.ID_Plan " _
             & "FROM SubPeriods " _
             & "WHERE ((SubPeriods.ID_Plan)=Forms!AllocationPlanForm!ID_Plan);"

          Me.cboComboBox.RowSource = strSQL

    End Sub

    OM Gang
    LVL 28

    Expert Comment

    If the current form is AllocationPlanForm then I'd change the WHERE clause to
    WHERE ((SubPeriods.ID_Plan)=Me.ID_Plan);"

    just typing that may have exposed your error.  I think it needs to be

    WHERE ((SubPeriods.ID_Plan)='" & Me.ID_Plan &"');"

    note the inclusion of single quotation marks

    OM Gang
    LVL 3

    Author Comment


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    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…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    732 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

    18 Experts available now in Live!

    Get 1:1 Help Now