Expiration Date Automatic Update

[Beakie]

I have been tasked with building a database that keeps track of all the
different permits and licenses for our organization along with when they will
expire (dates and renewal frequencies vary), and am not sure if what I want
to it to accomplish can be achieved. I have the main license table that will
include all the licenses by site, with one record for each different license
(fields include site, license type, license number, expiration date, renewal
frequency in days, vendor name), and then another table that will be updated
with the payment information for each license (fields include site, license
number, vendor name, payment date, payment amount). What I want is for the
expiration date in the license table to automatically update to the next
expiration date when payment information for the license is entered into the
payment table. The payment table will be an ongoing history, meaning that it
will end up having multiple records for a license each time it is renewed, so
I also need to know if it's possible for each specific license expiration
date to update each time a new payment record is entered for it, despite
having other payment records in the table. I would like to know if this is
possible and if so, within my capabilites and knowledge of Access (advanced
beginner to intermediate knowledge). Any suggestions, ideas and tips on how
to accomplish this are appreciated...

 

[Mrs. Ugh]

Ken has a great procedure, but you probably need to keep the expiration date
stored because renewal is normally based on a fixed renewal date, not the
payment date. If I pay early, the remaining license period should not be
shortened because of it. (i.e. If my expiration date is 31 Dec and my renewal
period is 30 days, my new expiration date should be 30 Jan, even if I pay on
15 Dec.)

It seems you have a lot of duplicate data in your two tables (site, vendor
name, etc.) You really only need it in one, just keep them linked vis the
primary keys. That way if something changes it is only in one place.
Jill

From your description it appears to me that you probably don't need to store
the expiration date in a table at all, and moreover probably should not do so
as it would introduce redundancy, which would leave the table open to
inconsistent data. The expiration date can be computed from the other data
which guarantees that it is up to date at any time.

As far as I can see the expiration date is determined by adding the number of
renewal frequency days to the latest (Max) payment date per licence per site
(for a variation see below). You could do this with an expression in a
computed column in a query or computed control in a form or report, but I'd
suggest wrapping it in a little function, which can be called anywhere in the
database. As you have both site and license number columns in both tables I
assume that they are related on the two columns, so you'd pass both of these
into the function, along with the number of days, as its arguments, in which
case it would be along these lines:

Function NextExpiration(strSite As String, _
lngLicenceNumber As Long, _
intRenewalInterval As Integer) As Variant

Dim strCriteria As String
Dim dtmLatestPaymentDate As Date

strCriteria = "Site = """ & strSite & _
""" And [License Number] = " & lngLicenceNumber

dtmLatestPaymentDate = _
DMax("[Payment Date]", "[LicencePayments]", strCriteria)

NextExpiration = DateAdd("d", intRenewalInterval, dtmLatestPaymentDate)

End Function

I've assumed that the payments table is called LicencePayments, Site is a
text data type, and Licence Number a long integer number data type, and that
the initial expiration date is determined by the first payment date for the
site/licence number, and thereafter by the subsequent payment dates for the
same site/licence number.

Put the function in a standard module in the database. It could then be
called as the ControlSource of a form or report based on the licences table
like so:

=NextExpiration([Site], [Licence Number], [Renewal Frequency])

or as a computed column in a query based on the licences table in the same
way.

Another possibility would be for the expiration date to be computed on the
basis of the renewal interval number of days, independently of the actual
payment dates following the first. The computation for this would be the
initial payment date + the renewal interval number of days multiplied by the
number of payments less 1, in which case the function would be along these
lines:

Function NextExpiration(strSite As String, _
lngLicenceNumber As Long, _
intRenewalInterval As Integer) As Variant

Dim strCriteria As String
Dim dtmFirstPaymentDate As Date
Dim intNumberOfPayments As Integer

strCriteria = "Site = """ & strSite & _
""" And [License Number] = " & lngLicenceNumber

dtmFirstPaymentDate = _
DMin("[Payment Date]", "[LicencePayments]", strCriteria)

intNumberOfPayments = _
DCount("*", "[LicencePayments]", strCriteria)

NextExpiration = _
DateAdd("d", (intNumberOfPayments -1) * _
intRenewalInterval, dtmFirstPaymentDate )

End Function

The exception would be if it were permissible to manually vary the expiration
date value from the computed value, in which the date would be stored in a
column and one of the above functions used, but in this case to open a
licences for at the relevant record and update a control bound to the
expiration date column when a payment record is entered, enabling the user to
then vary the date from the computed one if necessary. The form would be
opened, and the date updated by code in the AfterUpdate event procedure of a
payments form.

As the above functions rely on at least one row existing in the payments
table for the licence/site in question any control in a licences form which
calls the function will of course produce an error until such a row has been
inserted in the payments table. Ideally the error should be handled by
amending the functions accordingly.

Ken Sheridan
Stafford, England

I have been tasked with building a database that keeps track of all the
different permits and licenses for our organization along with when they will
expire (dates and renewal frequencies vary), and am not sure if what I want
to it to accomplish can be achieved. I have the main license table that will
include all the licenses by site, with one record for each different license
(fields include site, license type, license number, expiration date, renewal
frequency in days, vendor name), and then another table that will be updated
with the payment information for each license (fields include site, license
number, vendor name, payment date, payment amount). What I want is for the
expiration date in the license table to automatically update to the next
expiration date when payment information for the license is entered into the
payment table. The payment table will be an ongoing history, meaning that it
will end up having multiple records for a license each time it is renewed, so
I also need to know if it's possible for each specific license expiration
date to update each time a new payment record is entered for it, despite
having other payment records in the table. I would like to know if this is
possible and if so, within my capabilites and knowledge of Access (advanced
beginner to intermediate knowledge). Any suggestions, ideas and tips on how
to accomplish this are appreciated...

--



.