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

x
?
Solved

Screee.ActiveForm in Query Criteria

Posted on 2007-09-29
28
Medium Priority
?
857 Views
Last Modified: 2008-01-09
Dear Access Expert

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)
End Function

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.
0
Comment
Question by:cations
  • 15
  • 7
  • 4
  • +1
28 Comments
 
LVL 75
ID: 19985903
" I find it very strange that it works when combined with LIKE "

I'm shocked!!  But, if that's really true, then why not:

Like Screen.ActiveForm![myControlName]

?

mx

0
 
LVL 75
ID: 19985904
Why not just reference the Form and Control Name anyway?

Forms.YourFormName.YourControlName

?

mx
0
 

Author Comment

by:cations
ID: 19985924
Because I want this query to work with many Forms.   I don't want to write the same query for 4 forms. I want to have one generic query.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75
ID: 19985931
ok I see what is going on - I just tried this.  Wherein you SHOULD get an error using Screen.ActiveForm in a query, it's doesn't error out.  Then *only* reason Like "*" & Screen.ActiveForm![myControlName] returns anything is because of the Like "*" ....

So, this Like Screen.ActiveForm![myControlName] or this Screen.ActiveForm![myControlName]  won't work.

mx
0
 

Author Comment

by:cations
ID: 19985947
Hi guys

I have been experimenting with this.  DatabaseMX.. you are right in query view the Like "*" Screen.ActiveForm![myControlName]  actually acts as Like "*".  

However, when used as a recordsource in the Form the Screen.ActiveForm![myControlName]  actually WORKS.  There is a bug with MS Access.  It gets confused when the query grid is open.  Maybe it thinks the query grid is a form?  So the conclusion is Screen.ActiveForm.mycontrolname works but the query cannot be tested using the querygrid because it gives you the incorrect results.

Interesting... Microsoft should fix this.  But I am happy.  Now I can make my queries using Screen.ActiveForm and don' have to write long SQL statements.

thanks for the prompt responses.

0
 
LVL 75
ID: 19985950
You could do this - example using and employee table - just an example:

SELECT tblEmp.EmpID, tblEmp.EmpName
FROM tblEmp
WHERE (((tblEmp.EmpID)=GetScreenActiveFormControValue()));

Public Function GetScreenActiveFormControValue ()

    GetScreenActiveFormControValue = Screen.ActiveForm.YourControl

End Function
0
 
LVL 75
ID: 19985953
"Maybe it thinks the query grid is a form? "

Don't think so.  Screen.ActiveForm or Screen.ActiveControl are only me to work in vba code:

*****

ActiveForm Property
See AlsoApplies ToExampleSpecificsYou can use the ActiveForm property together with the Screen object to identify or refer to the form that has the focus. Read-only Form object.

expression.ActiveForm
expression    Required. An expression that returns one of the objects in the Applies To list.

Setting
This property setting contains a reference to the Form object that has the focus at run time.

This property is available by using a macro or Visual Basic and is read-only in all views.

Remarks
You can use the ActiveForm property to refer to an active form together with one of its properties or methods. The following example displays the Name property setting of the active form.

Dim frmCurrentForm As Form
Set frmCurrentForm = Screen.ActiveForm
MsgBox "Current form is " & frmCurrentForm.Name
            
If a subform has the focus, ActiveForm refers to the main form. If no form or subform has the focus when you use the ActiveForm property, an error occurs.
0
 

Author Comment

by:cations
ID: 19985958
HI DATABASE MX

The Screen.ActiveForm![myControlName]  works just can't be tested using the Query grid.  Must be tested using Forms.

I would like to write SQL statements but how would you easly convert this SQL.  This an average SQL statement in my database.

SELECT Flights.FltDate, Weekday([FltDate]) AS [Day], Flights.FltID, Flights.FltNum, Flights.TailNum, Flights.CargoWeight, Flights.DepartureSched, Flights.DepartureActual, Flights.ArrivalSched, Flights.ArrivalActual, Flights.Origin, Flights.Destination
FROM Flights
WHERE (((Flights.FltDate) Between [Forms]![DrillDown]![From] And [Forms]![DrillDown]![To]) AND ((Weekday([FltDate]))=IIf([Forms]![DrillDown]![Sunday],1,"") Or (Weekday([FltDate]))=IIf([Forms]![DrillDown]![Monday],2,"") Or (Weekday([FltDate]))=IIf([Forms]![DrillDown]![Tuesday],3,"") Or (Weekday([FltDate]))=IIf([Forms]![DrillDown]![Wednesday],4,"") Or (Weekday([FltDate]))=IIf([Forms]![DrillDown]![Thursday],5,"") Or (Weekday([FltDate]))=IIf([Forms]![DrillDown]![Friday],6,"") Or (Weekday([FltDate]))=IIf([Forms]![DrillDown]![Saturday],7,"")));

All the [Forms]![DrillDown]! would have to be replaced with a variable and the whole thing would have to be concatenated using the variable it's crazy.... easier to use Screen.ActiveForm in the query grid NO?

Although I believe most good developers don't use Screen.ActiveForm but build complex SQL statements using variables.
0
 
LVL 75
ID: 19985996
"The Screen.ActiveForm![myControlName]  works"

"Although I believe most good developers don't use Screen.ActiveForm but build complex SQL statements using variables."

I use Screen.ActiveForm and/or Screen.ActiveControl all the time for different things.  I *rarely* use SQL in code because it s bitch to maintain, messy to get the syntax right and hard to read.  I can do anything with a saved query.

MX
0
 
LVL 75
ID: 19986003
"All the [Forms]![DrillDown]! would have to be replaced with a variable "

Variable ?

You cannot use variables in a stored query - if that's what you mean ?

But ... you can call Functions - per the example I gave above.

mx
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 19986176
Hello all,
If it's just that field...and not like 20 fields. Trick your application. Assuming you have a "splash" form that stays open all the time when your DB is open, put a textbox on it and when the activeform you want is up on the screen, use the form_current's event to set the value in the textbox on splash form = to the value in that form's field. Now your queries can all reference the splash form's textbox.
Enjoy!
J
0
 
LVL 75
ID: 19986187
JT ... I don't see how Screen.ActiveForm can work in a query, well, actually it doesn't ... but, it also does not throw an error either. Weird.  

mx
0
 

Author Comment

by:cations
ID: 19986406
DatabaseMX

Screen.ActiveForm does work in a query just like your function.  Both methods though have the problem that they don't work when used in the query grid.  when you use your function in the query grid you will get an error.  But if you use the query in  a form  and view the results it will work correctly

Public Function GetScreenActiveFormControValue ()

    GetScreenActiveFormControValue = Screen.ActiveForm.YourControl

End Function
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 19986432
Mx, and cations,
I was suggesting a method outside of using active.form. Maybe it got lost in translation?
J
0
 
LVL 75
ID: 19986473
"when you use your function in the query grid you will get an error"

I've been using function calls or multiple function calls in Access queries for 15 years .... since Access 1.0.

mx
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 19986536
I'm surprised this is such an issue ;-)

cations - IMO you just really shouldn't be trying to make direct use of this.
Use MX's suggestion of a wrapping function - or construct the SQL in code.
Do one or the other - but please... just abandon trying to call Screen.ActiveForm directly from a query - there's no value in doing so when better, more robust, alternatives exist.

From the query grid - well naturally it would fail.
There *is* no Active Form.  By very definition.  The query grid is *not* a form - but it's the active window.
Therefore - there's no active form.

Take the offered function call.  Flesh it out by all means.
Check for forms you consider viable - and put in some error handling.  Use it.  
e.g.

Public Function GetScreenActiveFormControValue()
On Error GoTo HandleErr
   
    Dim frm As Form
   
    Set frm = Screen.ActiveForm
   
    Select Case frm.Name
        Case "FormA", "FormB", "FormX", "FormY", "FormZ"
            'Forms that are OK - use it
            GetScreenActiveFormControValue = frm.ControlName
        Case "FormBad", "FormNo", "FormKak", "FormPants"
            'Alternatively only list forms you *don't* want - though seems unlikely
    End Select

ExitHere:
    Exit Function

HandleErr:
    If Err <> 2475 Then
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in GetScreenActiveFormControValue", vbExclamation
    End If
    Resume ExitHere

End Function
0
 
LVL 75
ID: 19986545
:-)

0
 

Author Comment

by:cations
ID: 19996312
When I use your expanded function or the DatabaseMX minimal function  I get the same behavior as Screen.ActiveForm.Mycontrol.

It works correctly when running from a form but doesn't give you the correct result when the form is open and you run the query from the database window or the query grid.  It's the exact same thing!

Hence the conclusion is to Create dynamic SQL statements and assign them to the Querdef ... which is really cumbersome in my opinion but the only viable option if I want ot check my results from the query grid / database window.

Jeff Willey your method is good but I have about 10 parameters in the query and I don't think it would be too effecient to keep updating the background / switchboard form upon every user Change.  

My impression from this discussion is that I have to contruct SQL statements using variables for the formName and then assign them to the querydef if I want to be able to test my queries from the database window or the query grid.



0
 
LVL 75
ID: 19996350
"It works correctly when running from a form but doesn't give you the correct result when the form is open and you run the query from the database window or the query grid."

Right ... because at that moment ... the 'form' is *not* the active object ... thus failure.  Those functions are only going to work if ... you run the query from the Form - while it is active.

Please ... take Leigh's advice and/or use Jeff's approach.  Basically, you are trying to use Screen.ActiveForm and/or Control for something it was not intended.  When used as intended ... it's very powerful and can save a LOT of coding.

mx
0
 

Author Comment

by:cations
ID: 19996399
HI DatabaseMx

Thanks for all your quick responses... It almost appears as if we are having a conversation.  You are on fire!  How can you type and think so fast???

I am not sure who you are refering to when you say Leigh....

DatabaseMX  I feel like you do in regards to the below statement ....

  I *rarely* use SQL in code because it s bitch to maintain, messy to get the syntax right and hard to read.  I can do anything with a saved query.

so how do you avoid using SQL if you have the above problems? (I have the same issues and I love using the query grid because it's so easy ot change things.  That's why I was pushing Screen.ActiveForm but I can see it wasn't meant to be used this way)  Do you use JeffWiley's method?

0
 
LVL 75
ID: 19996430
"I am not sure who you are refering to when you say Leigh.... "  =  LPurvis

"so how do you avoid using SQL if you have the above problems?"

I have stored queries (ie, saved queried in the database window (aka container) ... which call functions ... to which I can pass anything I need.

" Do you use JeffWiley's method"

Indirectly ... sure.  Sometimes (many times I suppose) I reference a control(s) on a form ... instead of calling a function ... it just ... 'depends' ...

"How can you type and think so fast?"

I wish I knew ... maybe I could write a book :-)

mx

0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 19996459
Question:
*Why* would you ever want a query to operate from a query window in an application that's been distributed to users?
If you're using a form as a basis for running a query - then run it from a form.
Query windows in general, let alone the QBE, shouldn't be offered to users as a rule.

Your problem seems to be that you're not wanting the *current* form - but the most recent?
The concept alone of that is very vague.
Define "most recent".
The last opened form?  Nice, easier - but unlikely (the user perspective would be unlikely to grasp that as they navigate around).  So it's going to be the last form to have had the focus.
So your UDF solution remains much as it is - but you need your forms to leave a trail for you.

If each form you "care" about writes to a public variable (I wouldn't use a puplic variable most likely - but the concept's there) as the "last" used form - then it's that variable you'll examine for the form to use as criteria.
Hence, checking if Screen.ActiveForm returns a valid object - if not examine the variable and return instead
Forms(pvarLastUsedForm)
as the form object (where pvarLastUsedForm is that so named variable).

The decision of how and when to set that variable from forms is an issue though. (i.e. from what event).
OnGotFocus *doesn't* work as you'll expect I'll wager, OnActivate won't fire for any forms you have set as popups.
So you'll need to think about how you want to handle this.
0
 

Author Comment

by:cations
ID: 19996487
DatabaseMX

How do you achieve the Screen.ActiveForm functionality without using SQL statements or JeffWiley's method of having temporary controls on a splash screen that hold the activeForm's parameters?

How do you get the BELOW functionality without using SQL and being able to test the result in the query grid view?  

SELECT Flights.FltNum, False AS Include
FROM Flights
WHERE (((Weekday([FltDate]))=IIf([Screen].[ActiveForm]![Sunday],1,"") Or (Weekday([FltDate]))=IIf([Screen].[ActiveForm]![Monday],2,"") Or (Weekday([FltDate]))=IIf([Screen].[ActiveForm]![Tuesday],3,"") Or (Weekday([FltDate]))=IIf([Screen].[ActiveForm]![Wednesday],4,"") Or (Weekday([FltDate]))=IIf([Screen].[ActiveForm]![Thursday],5,"") Or (Weekday([FltDate]))=IIf([Screen].[ActiveForm]![Friday],6,"") Or (Weekday([FltDate]))=IIf([Screen].[ActiveForm]![Saturday],7,"")) AND ((Flights.FltDate) Between [Screen].[ActiveForm]![From] And [Screen].[ActiveForm]![To]) AND ((Flights.Origin)=[Screen].[ActiveForm]![Origin]) AND ((Flights.Destination)=[Screen].[ActiveForm]![Destination]));

0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 19996539
Cations, I honestly don't think you're seeing the problem here.

The semantics of using a query (in QBE or SQL view) or SQL generated in VBA aren't relevant.
The entire thing falls down at your use of Screen.ActiveForm.
From *anywhere* it is called, that will not return a valid object unless there is... *an active form*.

Jeff's suggestion is a variation on the public variable method.
And no - it needn't be a SQL generated in VBA method at all (not that it's anything to vitally stay clear of anyway).

He suggests the Current event - it's one choice though will fire more often than you need it to (and won't fire when simply setting a form as active having been to another - you'd need to change records... so again - user perception plays a part here).
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1500 total points
ID: 19996558
"How do you achieve the Screen.ActiveForm functionality without using SQL statements or JeffWiley's method of having temporary controls on a splash screen that hold the activeForm's parameters?

How do you get the BELOW functionality without using SQL and being able to test the result in the query grid view?  "

Whew!!  I have no clue how to answer that in a few words ... other than, I would never use more that a max of one nested IIF in a query ... it's just way too complex and messy to try an troubleshoot ... the is prone to logic mistakes.  

In most every case I can think of, I'm only going to reference a specific form, either directly (in name) or indirectly via a function call wherein some form has passed one or more parameters to the function.

Not sure what else I can add at this point ...

mx
0
 
LVL 75
ID: 19996575
Please ignore the typos and syntax errors in the last post ... it's getting late ... time to zzzz

Leigh is 12 hrs later ... so, it's his shift now :-)

mx
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 19996597
12hrs?  Are you currently in New Zealand? :-)
At a mere 8 hrs it's still a bit early for me.  None the less...

I'll just re-iterate at this point...
A valid reason for using a UDF to wrap the Screen.ActiveForm call - is so that if there is *no* active form - then your function call can return no valid value and hence the query returns no results (or all results - however you want to play it - though I'd lean towards the former).
It doesn't generate an error - it behaves in a controlled manner.

It doesn't seem a conceptual leap - that when there is no Active Form - ActiveForm doesn't return an object.
If you want to shift your functionality and aim to that as mentioned previously (initially be Jeff - but with the caveats I've detailed) then so be it.
But there's work and thought ahead - including thinking about what you want, and why?  
Why is function from a query window of importance to you?
If that's your only requirement - then I think there's a different issue to tackle before worrying about public variables etc.
0
 
LVL 75
ID: 19996612
oh ... I'm in Rancho Cucamonga CA (east of Los Angeles about 50 miles).  I was think AU instead of UK ... but still ... it's 'Day Shift' time, lol.

later ...

mx
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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 a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

571 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