Alert to notify if duplicate Active measures

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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
 
Graham - Worked excellent - thanks.
--
QWERTY


Graham Mandeno said:
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

John said:
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
Click to expand...
Click to expand...
 
Back
Top