Leave current record event? (or conditional relationship?)

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

Guest

Hi,
I currently have 2 related tables as follows:

InspectedSerialNumbers
=================
SerialNumber (Primary Key)
Inspector
Status {Pass, Fail}

DefectDetails
=================
SerialNumber (Primary Key)
Location (Primary Key)
ErrorCode (Primary Key)

For every record in the InspectedSerialNumbers table that has its status as
"Fail" MUST have at least one related record in the DefectDetails table.
"Pass" serial numbers do not require a related record in DefectDetails.
I don't know how to set a conditional constraint that would help me achieve
the above objective, so I'm thinking of controlling it via forms. So on my
form, I want to be able to programmatically trigger an event that checks to
see if a related record in DefectDetails exists every time the user LEAVES a
current InspectedSerialNumbers record. I've tried using the BeforeUpdate
event, but it only works when an InspectedSerialNumbers record is changed
(for EX, its status changes etc.), and that doesn't suffice. I want the
event to run every time the record loses focus whether or not it was modified.

Therefore, is there an event that will be triggered every time a record
loses focus (or the user navigates to another record)? Or should I
reconstruct my tables to better enforce this requirement?

Thanks much in advance for any ideas!
 
Hi,


Sounds like Status is a computed field. If your table is let without it, a
query could compute it:


SELECT SerialNumber,
Inspector,
NOT EXISTS( SELECT *
FROM DefectDetails AS dd
WHERE dd.SerialNumber = isn.SerialNumber) AS
Status
FROM InspectedSerialNumbers As isn





Hoping it may help,
Vanderghast, Access MVP
 
Back
Top