Setting the Priority Number unique

S

skk

Could you give me a suggestion on coding? I have this field called
"Priority" user enters the priority number for each Project. Once the
Project has the Priority number assinged other Projects cannot use this
number. There is no range on Priority number. Thank you in advance.
 
J

John Vinson

Could you give me a suggestion on coding? I have this field called
"Priority" user enters the priority number for each Project. Once the
Project has the Priority number assinged other Projects cannot use this
number. There is no range on Priority number. Thank you in advance.
Click to expand...

Open the Table in design view; select the Priority field; on the
Indexes tab (in the lower left corner) select Yes... Unique.

John W. Vinson[MVP]
 
S

skk

Thanks John. how easy that was. The only thing is now the system generated
msg pops up. How can I do away with system msg and have a custom msg of my
own? Because system msg is general and user will not no what it is since
it's not even setting the focus to that field. Thank you in advance.
 
J

John Vinson

Thanks John. how easy that was. The only thing is now the system generated
msg pops up. How can I do away with system msg and have a custom msg of my
own? Because system msg is general and user will not no what it is since
it's not even setting the focus to that field. Thank you in advance.
Click to expand...

Probably the best way (in addition to, rather than instead of, the
unique index) is to use the BeforeUpdate event of the control in which
the priority number is entered. Use DLookUp to find the entered
number; if one is found, issue a warning with Msgbox and set Cancel to
true.

E.g.

Private Sub txtPriority_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[Priority]", "[yourtablename]", _
"[Priority] = " & Me.txtPriority) Then
MsgBox "This priority has already been assigned", vbOKOnly
Cancel = True
Me.txtPriority.Undo
End If
End Sub

John W. Vinson[MVP]
 
S

skk

Great. Thank you so much.

John Vinson said:
Thanks John. how easy that was. The only thing is now the system generated
msg pops up. How can I do away with system msg and have a custom msg of my
own? Because system msg is general and user will not no what it is since
it's not even setting the focus to that field. Thank you in advance.
Click to expand...

Probably the best way (in addition to, rather than instead of, the
unique index) is to use the BeforeUpdate event of the control in which
the priority number is entered. Use DLookUp to find the entered
number; if one is found, issue a warning with Msgbox and set Cancel to
true.

E.g.

Private Sub txtPriority_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[Priority]", "[yourtablename]", _
"[Priority] = " & Me.txtPriority) Then
MsgBox "This priority has already been assigned", vbOKOnly
Cancel = True
Me.txtPriority.Undo
End If
End Sub

John W. Vinson[MVP]
Click to expand...
 
Top