Alert to notify if duplicate Active measures

[John]

I have a table "t_Measures" with fields; "MeasureID" (pkey), "MeasureNumber"
(text), and "MeasureActive" (yes/no). I have a form "f_Measures" where users
can update current measures or create new ones. I do not want them to be
able to create duplicate Active MeasureNumbers. It is OK (and expected) that
there will be duplicate Inactive Measures within the database over time.
This is because measures are numbered based on a system and measure numbers
will be reused (hence the MeasureID acting as the primary key). Only a
single instance of a measure number should be active at any time.

How can I have the form "f_Measures" fire a warning (msgbox) after the user
updates the "MeasureNumber" field if there is another identical measure
number in the t_Measures table that is active (-1)?

I've tried some DMIN arguments but cannot get it to work

 

[Graham Mandeno]

Hi John

Use the BeforeUpdate event of the textbox:

Private Sub MeasureNumber_BeforeUpdate( Cancel as Integer)
If Not IsNull( DLookup( "MeasureID", "t_Measures", _
"MeasureActive<>0 and MeasureNumber='" & Me!MeasureNumber _
& "' and MeasureID<>" & Me!MeasureID) ) Then
MsgBox "Another active measure has that number"
Cancel = True
End If
End Sub

 

[John]

Graham - Worked excellent - thanks.
--
QWERTY

Hi John

Use the BeforeUpdate event of the textbox:

Private Sub MeasureNumber_BeforeUpdate( Cancel as Integer)
If Not IsNull( DLookup( "MeasureID", "t_Measures", _
"MeasureActive<>0 and MeasureNumber='" & Me!MeasureNumber _
& "' and MeasureID<>" & Me!MeasureID) ) Then
MsgBox "Another active measure has that number"
Cancel = True
End If
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a table "t_Measures" with fields; "MeasureID" (pkey),
"MeasureNumber"
(text), and "MeasureActive" (yes/no). I have a form "f_Measures" where
users
can update current measures or create new ones. I do not want them to be
able to create duplicate Active MeasureNumbers. It is OK (and expected)
that
there will be duplicate Inactive Measures within the database over time.
This is because measures are numbered based on a system and measure
numbers
will be reused (hence the MeasureID acting as the primary key). Only a
single instance of a measure number should be active at any time.

How can I have the form "f_Measures" fire a warning (msgbox) after the
user
updates the "MeasureNumber" field if there is another identical measure
number in the t_Measures table that is active (-1)?

I've tried some DMIN arguments but cannot get it to work