displaying result from VBA into a control of form

M

mk sabeel

hello sir

i am performing some operations in a VBA and say i get a
result of an operation like-result=740x48i in VBA

i want to display this evaluated result obtained in VBA on
to a control of a form, how do i do acieve this

what command(if it is there) or what method i have to
give/use in the VBA that displays the result of an
operation/calculation/expression in VBA in a control of a
form

plz help me

waiting for a stimulating response

thank you
mk sabeel
[email protected]
 
J

John Vinson

i am performing some operations in a VBA and say i get a
result of an operation like-result=740x48i in VBA

i want to display this evaluated result obtained in VBA on
to a control of a form, how do i do acieve this
Click to expand...

If you have a VBA function (it must be a Function, not a Sub) simply
set the Control Source property of the form control to

=MyFunction(<parameters>)

In the Function you would have code like

Public Function MyFunction(thisparameter, thatparameter) As String
<your code here>
MyFunction = <some value>
End Function
 
M

mk sabeel

hello sir,
thanks for your feedback in advance again

i tried the method of assigning the function name to the
controlsource property but it is not working

i have written a function to do the evaluation but i dont
know how to go abt where to write it,displaying it or
should i write in the modules-type of the database window.
My code is ready but i just dont know where can i write
the code and how to display it.

also sir i want to display the result of an operation in a
way wherein i dont have to use the controlsource property
as that feild`s controlsource in form is assigned to a
feild in a table so that when i get a evaluated value in
feild of the form it gets saved in the table whose feild
(table`s) is assigned to the controlsource property of
that feild in the form.
Ideally i would like it to be that when i open the form
the value should have been evaluated and placed in that
particular feild of the form (without using controlsource
property) and the result of that feild gets saved in the
respective feild in the table

plz help me

thank you for being so patient
waiting for a stimulating response
mk sabeel
[email protected]
 
J

John Vinson

hello sir,
thanks for your feedback in advance again

i tried the method of assigning the function name to the
controlsource property but it is not working
Click to expand...

What SPECIFICALLY did you do? What is the actual text in the control
source? In what way is it "not working"?
i have written a function to do the evaluation but i dont
know how to go abt where to write it,displaying it or
should i write in the modules-type of the database window.
My code is ready but i just dont know where can i write
the code and how to display it.
Click to expand...

I do not understand. You have written a function but you don't know
where to write it!?

It should be in a Module; the name of the module must be different
than the name of the function. For instance you might have a Module
named basUtilities containing

Public Function MyFunc(parm1 As Integer, Parm2 As String) As String
<various code>
MyFunc = <the desired output>
End Function

and then either set the Control Source of a form or report control to

=MyFunc(SomeIntegerField, SomeStringField)

or include as a calculated field in a Query:

SomeName: MyFunc(SomeIntegerField, SomeStringField)

Or, you can call the function from VBA code in your Form (see below).
 
J

John Vinson

On Wed, 23 Jul 2003 00:00:56 -0700, "mk sabeel"

Sorry! Previous message was sent incomplete.
also sir i want to display the result of an operation in a
way wherein i dont have to use the controlsource property
as that feild`s controlsource in form is assigned to a
feild in a table so that when i get a evaluated value in
feild of the form it gets saved in the table whose feild
(table`s) is assigned to the controlsource property of
that feild in the form.
Click to expand...

This might not be a very good idea.

Storing data which can be derived accomplishes three things:
it wastes disk space (cheap, I know); wastes time (a
multiplication is MUCH faster than a disk read); and most
important, risks data corruption. If one of the fields which
makes up your expression changes, your calculated value
stored on disk is now WRONG.

Rather than storing it, just store the base data and do the
calculation in a Query or in the Control Source of a textbox
on a Form or Report. That way the correct calculated value
will always be regenerated afresh, correctly.
Ideally i would like it to be that when i open the form
the value should have been evaluated and placed in that
particular feild of the form (without using controlsource
property) and the result of that feild gets saved in the
respective feild in the table
Click to expand...

Does the function depend on values that exist in a record on the Form?
If so, when you open the Form, do you want the function evaluated for
the current record? or as you move from record to record, do you want
to reevaluate it?
 
M

mk sabeel

hello sir
thank you for ur response

i have achieved what i wanted to;how i have done is that i
have writtenthe function in the module ,the function
doesnt take any parameters , and then the function is run
from a macro the result of the function is stored in the
control of the form by assigning the gotfocus property of
the control to the control,thereby getting the result from
the function on to the control of the form.
the reason why i am storing the data in the table is that,
i have been assigned the job of running a tool/program
which gives out features of video streams in a text file
and i am reading the text file and running a query to
extract features,and the in the module i am opening the
query as recordset and performing certain calculations on
the query records and then storing the result of the
operations in the table of the database to keep track of
different features of the video streams.

and also their is no much worry of data corruption in the
table i am storing the result as from macro i run a
command i.e"RunCommand" and it that i use "recordsGotoNew"

but i thing i will tell u sir that their is abt 10 times i
have to do such operations by reading the text file,as
their are 10 feilds in form representing corresponding
unique feature of the video streams, and it takes lot of
time to get the complete result in all the 10 feilds of
the form.
so it is slow, is their any way that i can reduce the
speed of the above operation,u know sir what the operation
is which is 1st linking a text file,then running a
query,then running a function,all this achieved from a
macro which is assigned to the activate prperty of the
form,so that when the form opens everything is
automatically calculated and put in as a new record.

sir also one thing is that i use in the macro
the "transfertext" method and when ever i repeat the
transfering text procedure,by running the macro again
access2000 makes an extra copy of the table(with a
different file name),the table containing the text file
contents. how do i prevent the making of a new table when
i run the macro.

thank you being patient by reading such a long mail.
waiting for your feedback
thank you
sabeel
[email protected]
 
J

John Vinson

how do i prevent the making of a new table when
i run the macro.
Click to expand...

Well... if you're reading the same file ten times, just stop doing
that!!!

If you have the data in the table already, you do not need to run
TransferText again. Simply use the table. You may need one macro for
the initial reading of the file, and a different macro (run ten times)
to do the calculations.

I'm afraid this is getting deeper into the project than I can support
as an unpaid volunteer... what I see as the next step is rewriting the
Macro as a VBA Module (which will allow much more flexibility), but
that's more than I can take on in this newsgroup, I fear.
 
Top