(--) need help how to trap duplicate id_no in the database

[jemz]

hello please help me on this i don't know how to trap the duplicate id_no..example if i have already added this id_no H-1010..then if i input again it should prompt that id_no is already exist on your database..please help me on this..hoping for your positive responds ..thanks in advance

[sidnei]

What exactly you want?

1. You want user don't be able to input a existing number again?
2. You want to show a message that says ID already exists?
3. You want the user can put many ID same as existing?...

First case, you'll need to make a SELECT instruction with the given ID number, to see if it exists. If exists, abort the operation and inform the user.

Second case (and I probably use this), put an "on error" statement in your function, redirecting to a message that explains what hapenned.

Third one (and I totally disagree with this), you can disable PRIMARY KEY index in your database.

For the second case, a bit of code to help you:

Private Sub Rotina()

'PUT THIS IN THE BEGINNING OF YOUR CODE
On Error GoTo errMensagem

'THEN WRITE YOUR CODE NORMALLY...
Line 1
Line 2
...
'THIS LINE GENERATES AN ERROR
MsgBox 10 / 0

'YOU MUST USE EXIT SUB / FUNCTION BEFORE TRAP ERROR
Exit Sub

errMensagem:
  MsgBox "Error ocurred: " & Err.Number & " - " & Err.Description, vbExclamation

End Sub

If you want to show a detailled message for each type of error, you can test the Err.Number variable, like this:

errMensagem:

If Err.Number = 7 then
  MsgBox "The amount of memory in your computer is not enough to run this application", vbCritical
ElseIf Err.Number = 11 then
  MsgBox "You cannot divide any number by zero, it's impossible!", vbExclamation
ElseIf Err.Number = 53 then
  MsgBox "The file you are looking for is either removed or deleted, or your access to this file is denied", vbExclamation
'AND MANY OTHER SITUATION YOU WANT TO TEST...

Above, user reads "The file you are looking for is either removed or deleted, or your access to this file is denied" instead of "File not found" generic message.

Good luck!

[vb5prgrmr]

sidnei, good post but if you reread jemz post you will see that they are entering H-1010 for the primary key, meaning that the primary key is a text field. So jemz I would suggest that you use sidnei's 1st suggestion...

strSQL = "SELECT COUNT(fieldname) AS THECOUNT FROM tablename WHERE fieldname = 'H-1010'" 'or conversly you could do...
'strSQL = SELECT * FROM tablename WHERE fieldname = 'H-1010'"
'set Rs = ...
If Rs.Fields("THECOUNT") = 0 Then
  strSQL = "INSERT INTO tablename(fieldname1, fieldname2, fieldname3) VALUES('H-1010', someothernumericvalue, 'someothertextvalue')"
  Cn.Execute strSQL
Else
  MsgBox "Key Exists! Please enter another primary key", vbOkayOnly, "Primary Key Exists!"
End If

Good Luck

[abu taher]

You can try this.

[jemz]

oki thanks i will try this thanls for the reply and helping me ...i will write again if i have problem..more power to you...

[abu taher]

if you have no question about this topic then please mark it solve.

[jemz]

sidnei i try you code...my length of my idno is 5..when i input 123456 then it get an error it says control etc.....i forgot sorry for this .....then when i input another with the right 12345 it get an error it says object is open...please help me on this hoping for your positive responds...

[jemz]

how to attach files?please help me on this hoping for your positive responds...

[kinwang2009]

Hi jemz,

Use abu_taher's sample code instead of sidnei's code. abu_taher's sample works fine.

Thankx

[sidnei]

vb5prgrmr,
sorry by the confusion on data types...
but the explanation can be the same, only assumes that have no number, have to work with string...

Jemz,
please get more specific message, or I can't help you better, ok?

You cannot input more than 5 letters if your database field has set up to five characters. Try putting a value in MaxLength property of your textbox, this way user cannot be able to type more than MaxLength value defined - in this case, MaxLength property of your textbox should be 5.

The "object is open" error refers to a dataset or connection you don't close after use.
Please verify your code again. Possibly you're opening a recordset variable (.Open) and don't closing it after use (.Close).

If you made a SELECT instruction, take care to use CLOSE method in your recordset object.

If you're having any trouble, write again...

Cheers,
Sidnei

sidnei i try you code...my length of my idno is 5..when i input 123456 then it get an error it says control etc.....i forgot sorry for this .....then when i input another with the right 12345 it get an error it says object is open...please help me on this hoping for your positive responds...

[sidnei]

Ahn,
take a look on abu taher's code...

It made a simple insertion in database. The difference is between data types - his code uses a number for the primary key, while you're using text. But, anyway, his codes treats any error generated by the application.

Closer look his code. This may helpfull.

Sidnei

[vb5prgrmr]

So then use the count code to see if it exists... If Rs.Fields("THECOUNT").Value = 0 Then it does not exists and so you can add it...

Good Luck

[phpbeginners]

then mark every id with true.
if not true available.

[jemz]

hi sidnei thanks for the reply i will write again if i have problem

[zigpy_siva]

hi Jemz

simple way is to add id_no as the primary key or unique key constraint in the database.

[jemz]

sir can i ask,..how can i delete my attachment?..hoping for your positive responds