Tabular Form and VBA coding

  • Thread starter Thread starter Kany
  • Start date Start date
K

Kany

Hi,
I created a form with Master and Child relationship. My child (subform) is a
tabular form that displays data based on the selection for student number
from master form. So as soon as user selects student number from combo box
all the following fileds displays in a subform:
Student name, Role Assigned, Status, Severity_code, Relation, date open and
class id, A1, A2.
Everything works fine. But now I would like to add another field (which is
not in the database) Days aging to this subform and would like to calculate
the dates student is behind in his/her payment. I have a custom made function
that calcuates the days agining minus weekend and holidays. I created a
unbound text box and put a function =checkdays(A1,A2) in control source of
unbound text box.
The performance has been decreased quite alot that each row is taking some
time to display on the subform.
My questions would be:
Can I write a vba code something like this:
me.unboundtext = checkdays(a1,a2)
Behind the form to calculate the aging? I tried doing this on several events
such as "Before update, After update, on Current, data change etc" but
nothing worked. I also tried to create a command button for each row and
wrote the same VBA code for Onlick event of this command button and when I
clicked the commandbutton it generated value for all rows under that unbound
column with the same value as row 1.

I need help on this to how to calcualte the days againg for each row without
sacrificing the perfomrnance.
 
Kany said:
I created a form with Master and Child relationship. My child (subform) is a
tabular form that displays data based on the selection for student number
from master form. So as soon as user selects student number from combo box
all the following fileds displays in a subform:
Student name, Role Assigned, Status, Severity_code, Relation, date open and
class id, A1, A2.
Everything works fine. But now I would like to add another field (which is
not in the database) Days aging to this subform and would like to calculate
the dates student is behind in his/her payment. I have a custom made function
that calcuates the days agining minus weekend and holidays. I created a
unbound text box and put a function =checkdays(A1,A2) in control source of
unbound text box.
The performance has been decreased quite alot that each row is taking some
time to display on the subform.
My questions would be:
Can I write a vba code something like this:
me.unboundtext = checkdays(a1,a2)
Behind the form to calculate the aging? I tried doing this on several events
such as "Before update, After update, on Current, data change etc" but
nothing worked. I also tried to create a command button for each row and
wrote the same VBA code for Onlick event of this command button and when I
clicked the commandbutton it generated value for all rows under that unbound
column with the same value as row 1.

I need help on this to how to calcualte the days againg for each row without
sacrificing the perfomrnance.
Click to expand...


The biggest payoff will come from making your function run
faster.

There are only two choices of where you can put the function
call, in the text box expression or in the form's record
source so the text box is bound. The latter might(?) be
faster. You might even be able to force the calculations to
be done during the initial form load if you can find a way
to hide the calculation in a query behind the form record
source query or (yuck) in a make table query that you run
when you start your application.
 
Back
Top