I have been trying to get the Screen.ActiveForm![myControlName] to work in my query criteria unfortunately I have not been successful. If I use Screen.ActiveForm![myControlName] in my query criteria the resulting recordset is empty. If I use Like "*" & Screen.ActiveForm![myControlName] I get the correct results but the problem is that I want an Exact Match without using Like.
I used this function to pass into the criteria which only works when I run the query as a recordsource in a form. (But gives me an error when I try to view the results from the query design view. Error NO Active Form. Access probably assumes The Query Grid is the Active Form)
Public Function ActFormCnt(strCntName As String) As Variant
ActFormCnt = Screen.ActiveForm(strCntName)
How can I get Screen.ActiveForm![myCntName] to work as my query Criteria without the LIKE? I find it very strange that it works when combined with LIKE but not on it's own.
As an aside I talked to another developer about this problem and he suggested I just build the SQL statement using variables. The problem I find with this is that it is very cumbersome to create the SQL statement and easy to make a mistake. Also, If I need to make major changes to the query it takes alot of work to recrate the SQL statement but changing the query in the Query grid view is very easy.
What is the current standard ? Do developers just build their SQL statements dynamically using variables and don't rely on the Screen.activeForm method?
Thank you so much for your help and I hope there is a solution for the Screen.ActiveForm in the query grid.
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page.
The problem with sub-reports and headings:
Add a dummy group to the sub report using the expression =1:
Set the “Repeat Section” property of the dummy…