We help IT Professionals succeed at work.

How Do I Dynamically Filter the ADP Combo Box Rowsource?

jm-johnmeyer-us
on
Medium Priority
953 Views
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?
Comment
Watch Question

IT Manager
CERTIFIED EXPERT
Commented:
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
      Me.cboComboBox.Requery

End Sub

OM Gang

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

Ask the Experts
omgangIT Manager
CERTIFIED EXPERT

Commented:
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

Author

Commented:
Yes!
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.

OR

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.