We help IT Professionals succeed at work.

How Do I Dynamically Filter the ADP Combo Box Rowsource?

Medium Priority
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?
Watch Question

IT Manager
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
omgangIT Manager

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


Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.