Retiring one field when another is filled

[Jan Il]

Hi all - Access 2002 XP - WinME

I have a data entry form based on a table. The fields are as follows:

PurchaseID (PK)
PRDate (Purchase Requisition Date)
PRNo (Purchase Requisition Number)
PODate (Purchase Order Date)
PONo (Purchase Order Number)
Status (Open or Closed) *
Vendor
Dept

* There may be standing/open PO's that orders will be charged against,
usually for a one year period. These will remain 'open' until the full
amount of the PO is used, at which time it will be 'Closed' Then a new PO
will be opened if necessary.

My question is: Is it possible to retire/inactivate the PR number at the
time the PO becomes active? So that when the record is pulled up at a later
date for the purchase, only the PO Number and a date will be displayed.

In other words, when the PONo and PODate is entered, the PRDate and PRNo
will be deactivated from the list, but, remain in the database as a backup
record to the PO. I have used a similar method for retiring Employees and
vehicles, but, the processes have been done manually.

So, let's say we have an entry in the fields for PRDate of 01/03/04 and PRNo
of 00789. When the new PODate is entered to that respective field,
01/05/04, and the PONo R000514 is entered into that respective field, it
will automatically deactivate/retire the related PRDate and PRNo. You would
call up the existing record by the PR number and enter the PODate and PONo
that will replace that PRNo and date.

The PR date and number will be entered first at it is created by our dept.
At the time the PO number is created by the Purchasing Agent, the PR number
is deactivated and officially removed from record within the existing data
processing system, but, there are 'numerous' glitches. Thus a PR can be
created, and then the PO, at which time the PR is deleted from the record.
One of the glitches is... that it loses PO's. Yeppa...they just go
''poof'', no record of them at all. So, when this happens..if you don't
have any backup information, you don't know what is and isn't out there.

I'm just trying to cover the our back door by creating our own tracking
system at the source of the purchasing process.

If anyone has any suggestions or ideas, I would truly appreciate hearing
them. If we could handle this replacement process automatically it would be
a very big help, and better insure that a deactivation is not overlooked.

Jan

 

[Michel Walsh]

Hi,


In the same spirit, but may be not "exactly" what you are looking
for. It is a little bit long, in two steps.

It is first possible to remove the possibility to delete a record
from table tA having a primary key field tpk by building a table, tB, with a
field tpk, and an enforced relation with tA.tpk BUT WITH NO CASCADE delete,
no cascade update. With that design, if the value tpk0 is present in tB.tpk
(and in tA.tpk) and someone try to remove the record in tA where its tpk =
tpk0, an error will occur and the record won't be deleted in tA. The only
way to remove (or modify) tA.tkp=tpk0 is to remove it from tB first.

We have so a system that insure us that the primary key can be
"locked" (not updated, not delete... cannot append another pk with the same
value, it is already required to be unique since it is a primary key). We
must now consider a way to "lock" the other fields of the same record. Note
that only records having they tpk value in tB will have to be locked, the
others records won't be "locked".

We can also protect the other fields of the same record if we have
Jet 4.0 (won't work with the All Mighty SQL Server, it works only with Jet,
the Toy). The idea is to build a CHECK( ) constraint that evaluates to FALSE
is the pk value is in tB. Doing so, no update and no append would be
possible (if the pk is in tB):


CurrentProject.Connection.Execute "ALTER TABLE tA ADD CONSTRAINT
DataInTableAIsLocked CHECK( 0 = (SELECT COUNT(*) FROM tB WHERE
tB.tkp=tpk ) ) ; "

( cannot do the statement in the query designer, that won't work there, have
to do it in the Immediate Debug window, as example).


A CHECK constraint works like this: if data is append or modified, the
statement inside the CHECK is evaluated. If it returns true, or Null, the
append or the modification is accepted. If the result is false, the
operation is cancelled (rollback).

So, if a pk value is in tB and

if someone try to modify the pk value, the DRI fires and avoid the
modif.
if someone try to modify another field, the CHECK statement
evaluates to false (there is 1, not 0, record in tB with the actual tpk
value) and the modif is rejected.

To "unlock" back the record, remove its pk value from tB. To re-lock it,
re-append its pk value in tB. Table tB acts like a "software lock", or as a
"partial lock", allowing you to lock some records in tA, leaving some
records of tA "unlock" too.


In the end, since the records in tA are locked, you can "display"
the data even so, because the end user won't be able to modify it (through
your form).


If you ever change your mind and wish to remove the check
constraint, the syntax is:

CurrentProject.Connection.Execute "ALTER TABLE tA DROP CONSTRAINT
DataInTableAIsLocked "


The CHECK( ) constraint in MS SQL Server won't work, since MS SQL
Server does not allow full fledge SQL statements in it, so only the field
constraint, CHECK( fieldname > 0), and the so call "table" constraint, which
in fact is a record constraint, CHECK( FieldEndingTime >=
FieldStartingTime ), are allowed in the All Mighty. Jet, the Toy, allows
inter-table constraint, as we saw it, in the CHECK constraint. There is a
limitation with the CHECK constraint, in Jet, and it is that you cannot use
VBA functions in it.



Hoping it may help,
Vanderghast, Access MVP