Where/how to put VBA code?

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

Guest

I have a table with fields A,B,C,,, and IndexZ which is calculated by an
algorithm e.g. if A>10 and B<5 then set IndexZ=A. There are approximatley 800
records in the table. At data entry time the Form_AfterUpdate event calls a
function passing a record number and after a .FindFirst to the record number
the calculation of IndexZ is done and .Update saves the record. Now, I also
want to be able to call the function to read thru all 800 records
recalulating IndexZ, but I only want to have the algorithm defined ONCE in my
application as it's long and complex. This is also good practise.

Thoughts? Thanks.
 
Doug said:
I have a table with fields A,B,C,,, and IndexZ which is calculated by an
algorithm e.g. if A>10 and B<5 then set IndexZ=A. There are approximatley 800
records in the table. At data entry time the Form_AfterUpdate event calls a
function passing a record number and after a .FindFirst to the record number
the calculation of IndexZ is done and .Update saves the record. Now, I also
want to be able to call the function to read thru all 800 records
recalulating IndexZ, but I only want to have the algorithm defined ONCE in my
application as it's long and complex. This is also good practise.
Click to expand...


Modify the code so you can put it in a standard module. The
changes should be any more that passing arguments for what
ever form items the procedure depends on (e.g. Me).
 
If I understand correctly then you're suggesting pass all the arguments, well
there are A,B,C,,, up to 70 possible fields/arguments. Unwieldly?
 
Doug said:
If I understand correctly then you're suggesting pass all the arguments, well
there are A,B,C,,, up to 70 possible fields/arguments. Unwieldly?
Click to expand...


Your calculation depends on all 70 fields? That must be
some function ;-)

How about passing the form objec? The function can then get
to all the controls on the form. This way you probably can
get away with just changing the function's control
references from Me.controlname to frm.controlname where frm
is the argument.

If you prefer, you could pass the form's Recordset. The
function could then directly reference the fields in the
current record (nite the difference between control and
field).

Or, if it's so complex that you need data from multiple
records, you can pass the form's RecordsetClone and get to
all the fields in all the records in the form's dataset.
 
Thanks for your noodling, I now have some direction.
What I have here is a healthcare comorbidity index which looks at many
diseases and states of each disease.
 
Back
Top