Reference the current form in SQL select query

Question

I have a query that I use on many forms to set the row source of a cascading combo box. I would ideally be able to copy/paste this set of combo boxes for use on multiple forms without having to edit the query. Is there a simple way to reference controls on the current form in a SQL select query?

My current query looks like this:

    SELECT tblExample.ChildID
    FROM tblExample
    WHERE ((tblExample.ParentID)=[Forms]![frmExample]![CboParentID]);

I want to create something more general, like:

    SELECT tblExample.ChildID
    FROM tblExample
    WHERE ((tblExample.ParentID)=Me![CboParentID]);

That way I don't have to edit [frmExample] every time I reuse this query. Is it possible to reference controls on the current form in a general way?

Not that I know of. But you could create a function to be called when opening the form that sets the rowsource of the combobox. — Gustav
Access SQL queries lack the necessary context of where they are being called from to support such a reference. — C Perkins
@June7 Because there are a lot of one-to-many or many-to-many relationships in my data. The input is not the same, but the link to related tables is often the same. — Elizabeth Ham
If combobox is on same form as CboParentID, don't need full form reference prefix. SQL statement in RowSource can simply be: SELECT ChildID FROM tblExample WHERE ParentID=[CboParentID]; Now copy/paste combobox. — June7

Elizabeth Ham Elizabeth Ham · Accepted Answer · 2018-10-15T18:42:11

As a commenter suggested, because my parent combo box and child combo box appear together on forms, I can simplify my where condition to WHERE ParentID=[CboParentID];

Reference the current form in SQL select query

1 Answers