Using function as criteria? 1

[HenryAnthony]

The following code is returning the values that I want on a form but how can I use this function in a query as criteria? specID is an autonumber field and I want ActFrm() to be the criteria. Thanks in advance.

Public Function ActFrm() As Long
Dim ActFrmX As Form
Set ActFrmX = Screen.ActiveForm
If ActFrmX.Name = "frmPrintSpec_adprint" Then
ActFrm = Forms!frmPrintSpec_adprint!specID.Value
ElseIf ActFrmX.Name = "frmPrintSpec_Flyer" Then
ActFrm = Forms!frmPrintSpec_Flyer!revhistID.Value
End If
End Function

 

[oharab]

You can just use it in your query:

SELECT .... FROM .... WHERE [SomeField]=ActFrm()

be sure to include the brackets after the function name, or the query designer will try and convert it to a field or parameter.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------
Want to get great answers to your Tek-Tips questions? Have a look at F

 

[HenryAnthony]

Ben,

That is exactly what I am doing. I've tested with other functions and the criteria passes just fine. I think it has something to do with this "Screen.ActiveForm" thingy. Somewhere between pushing a button to print a report, the function is losing its value or some such. Could the form be losing the focus momentarily?

 

[PHV]

You may try something like this:
Public Function ActFrm() As Long
If CurrentProject.AllForms("frmPrintSpec_adprint").IsLoaded Then
ActFrm = Forms!frmPrintSpec_adprint!specID.Value
ElseIf CurrentProject.AllForms("frmPrintSpec_Flyer").IsLoaded Then
ActFrm = Forms!frmPrintSpec_Flyer!revhistID.Value
End If
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HenryAnthony]

Yo PH!

So far this works. Let's see if I can break it

Many thanks - you too Ben. Where would I be without all the help from this forum. I shudder to think.

But why does Screen.Active form not work?

Henry