Table Level Validation

[Stretch2312]

Hi,
I have a table with multiple fields, for entering machine defects. I want to prevent duplicate data from being entered. There are 3 fields that need to be validated to prevent this



1 - "Tag Number" (this is a physical tag placed on the machine, once the repair has been completed the tag can be reused)
2 - "Red/Blue" (there are 2 types of tags we use)
3 - "Status" (This is the status of the tag ie Completed, Active, Pending Approval)

I want to be able to prevent duplicate "Active" tags being entered. For Example;

If there is a record with a "Red" tag, Number "652" that is "Active", I want to prevent users creating another "Red" "652", until it has been "Completed"


Please help

[June7]

These tag numbers are reused - why? Is tag assigned to specific machine and used only on that machine? Same tag number will have blue and red versions?

Try setting compound primary key. Do you want only one record in table for each TagNumber/Color pair and the Status value will be changed?

This means cannot have historical data in this table.

Otherwise use a related child table of records that document use of these tags. Consider:

tblMachines
MachineID (PK)

tblTags
TagNumber (PK)
MachineID (FK)

tblTagsHistory
TagNumber (FK)
Color
DateAction
Action (Pending, Active, Completed)

[Stretch2312]

a tag can be used on any machine on any line. The tags are red or blue matal disks which and attched to the relevant peices of machinery.

I need a way to validate that a tag has been "Completed" before it can be reused online.

So Tag number and colour can be used multiple time (and reported on historically) i only want to restrict "Active" tags

[June7]

Okay, then:

tblMacines
MachineID (PK)

tblTags
TagNumber (PK)

tblTagsHistory
TagNumber (FK)
Color
MachineNumber (FK)
DateAction
Action

Don't see any way to set up table to manage the limitation on 'Active' status. This will require form. Can probably set up combobox on data entry form so that the 'Active' action would not be available if 'Active' is the last action associated with selected tag. I say 'probably' because never tried to set up combobox RowSource with criteria like that. I might use VBA code to build the combobox RowSource based on tag number and color.

[ssanfu]

PMFJI,

I would agree with June. The validation probably couldn't be done at the table level (maybe with a UDF?). Using VBA, you have more control over the validation process.
I would think about using the form before update event. Open a recordset and check the status. But I gets trickier... Is there one record where the status gets updated or one record per status, per color, per machine?

If someone tried to add a new active record for a "Red" tag, Number "652", you would cancel the update and give a message that Red - 652 is currently active.
Also have to check if there is an active record for "Red" tag, Number "652" and someone was trying to add a record "Pending",,,, shouldn't be able to add it?

Can you add a record for "Red, 652, Active" if there is not first a record for "Red, 652, Pending"? Must one follow the other?

Lots of validating to do.... Not so bad if one record and the status gets updated. Lots of checking, if one record per status, color, number.