Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How Do I Dynamically Filter the ADP Combo Box Rowsource?

Posted on 2006-03-23
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?
Question by:jm-johnmeyer-us
  • 2
LVL 28

Accepted Solution

omgang earned 1000 total points
ID: 16276201
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

ID: 16276218
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 Comment

ID: 16276287

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

575 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