own message error

[mmiraj]

I use this expression to prevent duplication on my data (GuestID is unique).it was successfuly creating my own message. The problem is it comes up another message
" The value in the field or records violates the validation rules for the records or field"

Is that "Variant" affect to my GuestID value ?

thank you for your attention


Private Sub GuestID_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
If DCount("[GuestID]", "Guest Application record", "[GuestID]= " & Me![GuestID]) = 1 Then
Answer = MsgBox("Guest ID sudah ada, Coba Yg Lain", vbOKOnly, "Hey You!")
If Answer = vbOK Then Cancel = True
End If
End Sub

 

[David R]

Is GuestID a text field or numeric? Is the field required? Is it the first field on your form to be filled out?

You could use a combo box to avoid duplicate entries, perhaps. Also I would not even check the value of Answer, just post the MsgBox and then set Cancel = True.

 

[mmiraj]

Thanks for the response, i can't use combo box for some reason

GuestID is numeric
Yes this field is required
no, this is second field to be filled out

 

[Geoff Codd]

here's some code I use to validate an order no using my own error message

Private Sub Order_Survey_No_AfterUpdate()
DoCmd.SetWarnings False
On Error Resume Next
On Error GoTo Error_Message

DoCmd.RunCommand acCmdSaveRecord
Me!Order_Survey_No.Locked = True
Me!TCard_No.Enabled = True
' T-Card Number
If IsNull(DMax("[TCard_No]", "Order_Details")) Then
Me!TCard_No = "T-00001"
Else
Me!TCard_No = "T-" & Format(CLng(Right(DMax("[TCard_No]", "Order_Details"), 5)) + 1, "00000")
End If
Me!TCard_No.Locked = True
DoCmd.RunCommand acCmdSaveRecord
Call Form_Current
Me!Work_Progress_subform!Date_Received.Enabled = True
Me!Work_Progress_subform!TglDateReceived.Enabled = True
Me!Work_Progress_subform!Date_Received.SetFocus

DoCmd.SetWarnings True
Exit Sub

Error_Message:
MsgBox " The Order / Survey Number You Have Entered Belongs to Another Order."
Me!Order_Survey_No.Locked = False
Me!TCard_No.Enabled = False
Me!Order_Survey_No.SetFocus

End Sub