Error Trapping with VB and Access 2

[Caustic]

I'm new to VB and was hoping for some kernels of wisdom. Any help is appreciated:

I recently created a program in VB6 that pulls information from an Access database via an ADO connection. The database stores all of our PC assets and uses the unique serial number as its primary key.

The VB program allows someone to browse, create, modify or delete records from the database through text boxes and command buttons.

I want to set up error handling so when someone adds a new record with a serial number that already exists, it brings up a message box telling them that a record already exists with that serial number. As is, the program generates a runtime error and produces this predictable error:

Run-time error '-2147467259 (80004005)'

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. etc etc.

I figure since an error is generated, I must be able to trap it and give the user some more productive feedback and another chance to redeem themselves.

 

[csniffer]

Could you not just disable the textbox with textboxname.Enabled = FALSE ?

 

[Caustic]

Thanks for the response.

I could, but then they would not be able to enter a serial number.

I want them to enter a serial number. I just don't want them to enter a duplicate. Access also does not want them to enter a duplicate and so it generates the above error.

I know for many common errors you can trap them, like "file not found" or "file open". I guess I'm wondering how to trap for this particular error or if it is even possible.

Thanks.

 

[robctech]

You could use a filter string on the entered serial number..as they enter or after they have entered the serial number apply a filter...if recordcount is 0 add the record otherwise go to and display the error in the format you want.

RecordSet.Filter = "SerialID = " & textbox
if RecordCount = 0 then
   Msgbox "We can add the record"
Else
   MsgBox "That serial number already exists"
End if

 

[CasperTFG]

Well first of doing validation by way of waiting for an error is not the prefered way to go.

What you should isntead do is find out if the record is in use...

rs = cn.Execute "SELECT * FROM t_Table WHERE ID='" & Text1 & "';"
If rs.RecordCount > 0 Then
MsgBox "Blah..."
Exit Function
End If

But as for error handling, you can start here.
faq222-1694

Craig

"I feel sorry for people who don't drink. When they wake up in the morning, that's as good as they're going to feel all day."
~Frank Sinatra

 

[Caustic]

Thanks, I had thought about validation before the error is generated but wasn't sure how to implement it. Actually, I had thought I would have to run some complicated loop to check against every character of each serial number.

Thanks for the help, I'll try those suggestions.