Error check when adding a value to recordset

This is the problem I am having.

Before the my prog throws the error saying user is trying to enter that same data as in the database. The Errhandler throws an error saying

"Unique Constrain Vilolation."

So what i want is to throw my message saying there is similar entry please enter a different one.

I don't want the Error Handler to throw it because Errorhadler is there to throw an error when other errors such as database failur or something.

any help regarding this.





Private Sub cmdSave_Click()

On Error GoTo Errhandler
intTmpMsg = MsgBox("Do you want to Save the Changes? ", vbYesNo, "Save")
If intTmpMsg = 6 Then

Do While Not rs.EOF
If Trim(txtMLI.text) = Trim(rs("mli").Value & "") Then
MsgBox ("The Entered value Exists. Please Enter a New value")
Exit Sub
End If
rs.MoveNext
Loop

rs.AddNew
rs!mli = txtMLI.text
rs!MLIDESC = txtDiscrip.text
rs.Update

If Not rs.EOF Then rs.MoveFirst
Do While Not rs.EOF
frmMLI.cmbMLI.AddItem rs!mli
rs.MoveNext
Loop
frmMLI.cmbMLI.ListIndex = 0
frmMLI.cmbMLI.SetFocus
'txtDiscrip.text = ""
'txtMLI.text = ""
Unload Me
End If

Exit Sub

Errhandler: MsgBox Err.Description
Exit Sub
End Sub

I would suggest finding the error number that is being thrown and from that you can bypass that particular error and go onto yours

Private Sub cmdSave_Click()

On Error GoTo Errhandler
.
.
.


Errhandler:
if Err.Number = ## then ' enter exact # here
resume next
else
MsgBox Err.Description, Err.Number
end if

End Sub

Just Select Case on the Err.Number. Say that error you have is number 30001. in Case 30001, then make Err.Description = "There is similar entry please enter a different one."

Just Select Case on the Err.Number. Say that error you have is number 30001. in Case 30001, then make Err.Description = "There is similar entry please enter a different one."


error number i get is from Oracle database. To be exact
" ORA-00001: Unique constrain(TABLE NAME) violated"

So how can i handle this using Err.number

I can't give this
Err.Number = ORA-00001

So any ideas ?

Thanks for the help

A better idea is to do a SELECT COUNT(*) or some sort of record validation before attempting to insert the record. If the count is > 0 this record already exists, and prompt the user. If this is based off a primary key or foreign key, you can just scan the table index and look for the value trying to be inserted (if not an autonumber), then again, if found, don't even attempt the insert routine and prompt the user.

Now i am lost. I haven't mentioned the exact forms. I strongly believe Err.Number is a good way to go because it helps the situation.

This is the big picture. 2 forms. One form shows the discription of an item when user select the item from the combo drop down and the other form allows user to enter new item with a discription. So in the second form when the user enter value it shouldn't enter it if it is in the recordset already has it. I won't anyways because Oracle throws an error i mentioned. But i want user to know its because its because he is trying to eneter a value already there. Err.discription is used to throw an error when there is any other errors in Database.

Hope this helps. Please help me

So letting the error occur and then trapping it is better than preventing the error completely? Sorry, have to disagree with you there.

Thanks for the help. I just need this make it work in a simple way. I am not a VB expert just a newbie. So I would prefer if you code show me how to do it if you can. I did get the idea of using Err.number.

So thats why i thought its better to go that way. Please let me know