Form Closed

[Steve]

If the form for my criteria is closed how do I write the criteria to look for
the same field name in another form that is open? I placed the 1st option in
the Criteria box and the 2nd option in the box marked "or"(under the criteria
label).
Hope some one can help.

Thanks from Down Under

 

[John Spencer]

One method is to use a VBA function to return the value based on which
form is open. In the simplest example that I can think of, that might
consist of a function that looks something like:

Public Function vGetValue()

If CurrentProject.AllForms("YourFormName").IsLoaded Then
vGetValue=Forms![YourFormName]![YourControlName]

ElseIf CurrentProject.AllForms("SomeOtherFormName").IsLoaded Then
vGetValue=Forms![SomeOtherFormName]![SomeControlName]
else

vGetValue=Null
End If

End Function

In your query you would use
vGetValue()
instead of the reference to the form control.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Steve]

Thanks John, I have writen the code in visual basic, and typed vGetValue()
in the "criteria " in the query, but when it runs It runs I receive the
following message "Undefind function 'vGetValue' in expression. I am a novice
at this, and I thank you for your help.
I keep geting this message that this reply has not been sent (happened with
original question thats why I suppose it was posted 3 times.
--
Thanks from Down Under

One method is to use a VBA function to return the value based on which
form is open. In the simplest example that I can think of, that might
consist of a function that looks something like:

Public Function vGetValue()

If CurrentProject.AllForms("YourFormName").IsLoaded Then
vGetValue=Forms![YourFormName]![YourControlName]

ElseIf CurrentProject.AllForms("SomeOtherFormName").IsLoaded Then
vGetValue=Forms![SomeOtherFormName]![SomeControlName]
else

vGetValue=Null
End If

End Function

In your query you would use
vGetValue()
instead of the reference to the form control.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

If the form for my criteria is closed how do I write the criteria to look for
the same field name in another form that is open? I placed the 1st option in
the Criteria box and the 2nd option in the box marked "or"(under the criteria
label).
Hope some one can help.

Thanks from Down Under

 

[John Spencer MVP]

Or third option (may be a different error message) is that you named the
module vGetValue as well as the function. Functions, subs, and modules cannot
have the same name. I use the naming convention of "mod_" at the beginning of
all my module names to preclude that from happening.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Steve,

I would guess there are two possible reasons for this.

1. You may have put the function in a forms code module. Code in a forms
code module is not generally available for use throughout the rest of the
application. If this is the case, go to the VB Editor. Right click in the
Project Explorer window (mine is in the upper left corner), and select the
Insert -> Module option from the popup menu. Then paste your code in that
window.

2. The second possibility is that you declared the function as private, not
public.

HTH
Dale

Thanks John, I have writen the code in visual basic, and typed vGetValue()
in the "criteria " in the query, but when it runs It runs I receive the
following message "Undefind function 'vGetValue' in expression. I am a novice
at this, and I thank you for your help.
I keep geting this message that this reply has not been sent (happened with
original question thats why I suppose it was posted 3 times.

One method is to use a VBA function to return the value based on which
form is open. In the simplest example that I can think of, that might

[quoted text clipped - 32 lines]

Thanks from Down Under

 

[Steve]

John And Dale thanks for your help. All is well, till the next time.
Kind regards
Steve
--
Thanks from Down Under

Steve,

I would guess there are two possible reasons for this.

1. You may have put the function in a forms code module. Code in a forms
code module is not generally available for use throughout the rest of the
application. If this is the case, go to the VB Editor. Right click in the
Project Explorer window (mine is in the upper left corner), and select the
Insert -> Module option from the popup menu. Then paste your code in that
window.

2. The second possibility is that you declared the function as private, not
public.

HTH
Dale

Thanks John, I have writen the code in visual basic, and typed vGetValue()
in the "criteria " in the query, but when it runs It runs I receive the
following message "Undefind function 'vGetValue' in expression. I am a novice
at this, and I thank you for your help.
I keep geting this message that this reply has not been sent (happened with
original question thats why I suppose it was posted 3 times.

One method is to use a VBA function to return the value based on which
form is open. In the simplest example that I can think of, that might

[quoted text clipped - 32 lines]

Thanks from Down Under

 

[Steve]

John And Dale thanks for your help. All is well, till the next time.
Kind regards
Steve
--
Thanks from Down Under

Steve,

I would guess there are two possible reasons for this.

1. You may have put the function in a forms code module. Code in a forms
code module is not generally available for use throughout the rest of the
application. If this is the case, go to the VB Editor. Right click in the
Project Explorer window (mine is in the upper left corner), and select the
Insert -> Module option from the popup menu. Then paste your code in that
window.

2. The second possibility is that you declared the function as private, not
public.

HTH
Dale

Thanks John, I have writen the code in visual basic, and typed vGetValue()
in the "criteria " in the query, but when it runs It runs I receive the
following message "Undefind function 'vGetValue' in expression. I am a novice
at this, and I thank you for your help.
I keep geting this message that this reply has not been sent (happened with
original question thats why I suppose it was posted 3 times.

One method is to use a VBA function to return the value based on which
form is open. In the simplest example that I can think of, that might

[quoted text clipped - 32 lines]

Thanks from Down Under

 

[Steve]

G'Day Dale,
the problem was as you expected I had put the code in to a form instead of
a new Module, thanks again all's well that ends well
Steve
--
Thanks from Down Under

Steve,

What did you finally figure out was the problem?

Dale

John And Dale thanks for your help. All is well, till the next time.
Kind regards
Steve

Steve,

[quoted text clipped - 24 lines]

Thanks from Down Under

--
HTH

Dale Fye

Message posted via AccessMonster.com