calling a module function

[Guest]

Please forgive me if this is just an absurd question. I have a module that I
am using called "Date Difference". This module has the following arguments
"Date2Diff(interval, date1, date2, zeros)". This works great in a query. I
want to use it in a text box on a form. I started creating the following
code to call up the module. I can't get it to work. Please help.

Private Sub Text116_AfterUpdate()
Dim strDate1 As String
Dim strDate2 As String
Dim mdl As Module

strDate1 = [Received Date]
strDate2 = [Response Date]
Set mdl = Modules![Date Difference]

End Sub

 

[John Vinson]

Please forgive me if this is just an absurd question. I have a module that I
am using called "Date Difference". This module has the following arguments
"Date2Diff(interval, date1, date2, zeros)". This works great in a query. I
want to use it in a text box on a form. I started creating the following
code to call up the module. I can't get it to work. Please help.

Private Sub Text116_AfterUpdate()
Dim strDate1 As String
Dim strDate2 As String
Dim mdl As Module

strDate1 = [Received Date]
strDate2 = [Response Date]
Set mdl = Modules![Date Difference]

End Sub

No code is needed.

The name of the Module is irrelevant and not used - you can't "call a
module". A Module contains procedures, either Sub or Function
procedures. If your module contains a line like

Public Function Date2Diff(...

you can simply set the Control Source of your textbox to

=Date2Diff("d", [Received Date], [Response Date], <whatever is
appropriate for the zeros argument>)

John W. Vinson[MVP]

 

[Guest]

Thanks John. I need the value that is created to be saved to a field on a
table. If I use the module as my control source, will I still be able to
save this calculated vaule?
--
Hlewis

Please forgive me if this is just an absurd question. I have a module that I
am using called "Date Difference". This module has the following arguments
"Date2Diff(interval, date1, date2, zeros)". This works great in a query. I
want to use it in a text box on a form. I started creating the following
code to call up the module. I can't get it to work. Please help.

Private Sub Text116_AfterUpdate()
Dim strDate1 As String
Dim strDate2 As String
Dim mdl As Module

strDate1 = [Received Date]
strDate2 = [Response Date]
Set mdl = Modules![Date Difference]

End Sub

No code is needed.

The name of the Module is irrelevant and not used - you can't "call a
module". A Module contains procedures, either Sub or Function
procedures. If your module contains a line like

Public Function Date2Diff(...

you can simply set the Control Source of your textbox to

=Date2Diff("d", [Received Date], [Response Date], <whatever is
appropriate for the zeros argument>)

John W. Vinson[MVP]

 

[John Vinson]

Thanks John. I need the value that is created to be saved to a field on a
table. If I use the module as my control source, will I still be able to
save this calculated vaule?

A textbox on a form can have only one control source: either a table
field (a bound control) or an expression (a calculated field). It
can't be both.

What you can do is to "push" the value into the bound control using
some suitable form event. I don't know what that might be in your
case.

HOWEVER...
Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

Is it REALLY necessary to store this value?

John W. Vinson[MVP]

 

[Guest]

John,

Yes, I need to store the value. I am using this db to log emails coming
into a shared mailbox. Once the messages are received, the person
responsible for resolving issues must log the time the problem was fixed. On
the form in question, the person will enter the date and time they resolved
the issue. All information entered on this form is saved to a table. I need
to be able to capture the elasped time from when the email came into the
mailbox and the time it was resolved. If you have any other suggestions on
how to record this elaspsed time, I'm all ears.

 

[John Vinson]

If you have any other suggestions on
how to record this elaspsed time, I'm all ears.

If you store the time received, and the time resolved - which you are
apparently doing - you can calculate the elapsed time whenever you
wish. It is NOT necessary, nor is it good design, to store it in the
table. You can calculate it in a Query, if you wish, and use that
calculated field for searching, sorting, exports, reporting, whatever
you like - storing it redundantly in your table provides no benefit.


John W. Vinson[MVP]