Return calculated value to form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to build a form that will run some vba code and then put a value
calculated in a text box. What do I put in the Control Source of the text
box to get it to display the value of a variable defined in the vba code? I
know the vba is good as far as calculating the value. Does something have to
be put in the code to make it available to the form? I will be using the
value for other calculations later. I would like the value calculated when
the form is opened and have have some question about where to place the vba
code. Is the OnEnter event the proper place?
 
Either set the control property of the box to
=YourVBAFunctionName()
or in your function itself use the code
[Forms]![YourFormName]![YourBoxName] = YourVariable
 
Works Great!! THANK YOU

Dennis said:
Either set the control property of the box to
=YourVBAFunctionName()
or in your function itself use the code
[Forms]![YourFormName]![YourBoxName] = YourVariable

vtj said:
I am trying to build a form that will run some vba code and then put a value
calculated in a text box. What do I put in the Control Source of the text
box to get it to display the value of a variable defined in the vba code? I
know the vba is good as far as calculating the value. Does something have to
be put in the code to make it available to the form? I will be using the
value for other calculations later. I would like the value calculated when
the form is opened and have have some question about where to place the vba
code. Is the OnEnter event the proper place?
Click to expand...
Click to expand...
 
In the controlSource property for the field use =myFunction()

Function myFunction()

myFunction = 25507

end function

Doing this will automatically display the return value of the function.
If information is added to the fields on the form which will effect the
return value, say quantity of items purchased you'll need to requery the
field using

fieldName.Requery

after the quantity of items purchased field is updated. Otherwise, if
you want the calculation to run on demand, as opposed to being
automatically updated, in the onClick event of a button use the code

fieldName = myFunction()

This will set the field named fieldName to the value of myFunction()
 
Thanks for the suggestion. I have tried this and it doesn't work. In
building the function, vba will not let me use the same name for a variable
as the name of the function. Also if I put the =myFunction() in the control
source I always get a #Name error. I suspect that may be because the text
box that I am trying to put the result into does not know the variable name
to take from the function. I have not found a way to tell it what variable
it should use. I have had to put the vba code in each form where it is used
instead of being able to use a public function which would be easier and save
time.
 
The function as-is does work, be certain NOT to try to add a DIM
statement for myFunction as the function header handles declaring it.
The statement myFunction = 25507 sets the value that should be returned
by the function. To try and debug the problem make the control an
unbound control and then add a button with the following code in the
button's onClick event.

Me!formFieldName = myFunction()

When the button is clicked 25507 should appear in formfield.
 
It still doesn't work. I removed the control source statement to make the
box unbounded, put a button in, and put the Me!... statement in the on click
event. It returns a message that it can't find the Macro named 'myFunction'.
It is a Function, not a macro so it won't find a macro. How do I get it to
look for a function?
 
So you put the

Me!formFieldName = myFunction()

into the button's onClick event? Please post the code.
 
Here is the code:
On Click Property on button:
Me!Text0=JulianCalc3()

Function:
Public Function JulianCalc3()
JulianCalc3 = 180
End Function

As you can tell I'm trying to write to text box 'Text0' which is an unbound
general number format box. It want to find a macro when the button is
clicked. Does a macro of the same name with a call to the function have to
exist for this to work? Thanks for your help!
 
Double check the OnClick PROPERTY in the PROPERTIES window for the
button to ensure that [Event Procedure] is listed, this points the event
to the procedure as opposed to a macro.
 
Thanks for your help. Putting [Event Procedure] in the on click property
opens a private sub command. When it tries to run, the error message
‘expected variable or procedure, not module’ is returned as a compile error.
The private sub follows:
Private Sub Command6_Click()
Me!Text0 = JulianCalc3()
End Sub
I have tried removing the ‘()’ at the end but the result is the same. I
also tried putting a ‘call’ or ‘run’ statement with the JulianCalc3() and it
still gives the same error message.
This is running on Access 2000 if that makes a difference. Is it possible
that there is no way to use a public function on a form?


David C. Holley said:
Double check the OnClick PROPERTY in the PROPERTIES window for the
button to ensure that [Event Procedure] is listed, this points the event
to the procedure as opposed to a macro.
Here is the code:
On Click Property on button:
Me!Text0=JulianCalc3()

Function:
Public Function JulianCalc3()
JulianCalc3 = 180
End Function

As you can tell I'm trying to write to text box 'Text0' which is an unbound
general number format box. It want to find a macro when the button is
clicked. Does a macro of the same name with a call to the function have to
exist for this to work? Thanks for your help!


:
Click to expand...
Click to expand...
 
The fix to get the form to work is to include the "= myfunction" on the
Before Update control. So it appears two places - the control source and
before update lines in the properties of the form text box.

David C. Holley said:
Double check the OnClick PROPERTY in the PROPERTIES window for the
button to ensure that [Event Procedure] is listed, this points the event
to the procedure as opposed to a macro.
Here is the code:
On Click Property on button:
Me!Text0=JulianCalc3()

Function:
Public Function JulianCalc3()
JulianCalc3 = 180
End Function

As you can tell I'm trying to write to text box 'Text0' which is an unbound
general number format box. It want to find a macro when the button is
clicked. Does a macro of the same name with a call to the function have to
exist for this to work? Thanks for your help!


:
Click to expand...
Click to expand...
 
Back
Top