How To Validate Duplicate Id Number In Client Server?

[OBA]

Hi To All.... This Is My First Try To Create A Program Which Compose A Server And 2 Client...i Use Access Database As My Back End And Vb6 As My Front End...

Problem
1. I Create A Code In Which A User Can Generate Autonumber
But The Problem Is If Two Clients Input Together It Would
Both Save The Data To The Server With A Duplicate And
Same Id Number...

This Is My Code Which I Generate Auto Number.....

Dim Rs As New Adodb.recordset
Set Rs=cn.execute("select Max(fieldname) From Tblname)
If Isnull(rs(0))=true Then
Idnum=1
Else
Idnum=rs(0)+1
Endif

Please Help Me..... How Can I Validate Idnum Before It Save To Server?....
tnx...

[werks]

HI this is my code in Validating the username of the Librarian

Dim ExistUserName AS Boolean
Private Sub cmdSave_Click()
...
    Call VerifyUserName
    If ExistUserName = True Then
        MsgBox "Username exist please choose aanother", 48, "Username Existing"
        Exit Sub
    End If
...
End Sub
 
 
Private Sub VerifyUserName()
    Dim rsUserList As New ADODB.Recordset
    'ASSUMING THAT THE USERNAME IS NOT EXISTING
    ExistUserName = False
    'OPEN THE STUDENT TABLE
    rsUserList.Open "SELECT * FROM tblUser WHERE Username='" & txtName(7).Text & "'", CoNN, 3, 3
    'CHECK IF THE ID NUMBER IS ALREADY EXIST
    If rsUserList.RecordCount > 0 Then
        ExistUserName = True
    End If
 
    rsUserList.Close
    Set rsUserList = Nothing
 
End Sub
 
 

Hope this help..

[mafaisal]

Hello Werks

I think this is not good
if we edit some thing then save then duplication error is coming using this code
Better way is use Primary key and Unique key constarint for duplication
Also this code
use verifyusename as function return type boolean, then we can avoid extra var for

Faisal

HI this is my code in Validating the username of the Librarian

Dim ExistUserName AS Boolean
Private Sub cmdSave_Click()
...
    Call VerifyUserName
    If ExistUserName = True Then
        MsgBox "Username exist please choose aanother", 48, "Username Existing"
        Exit Sub
    End If
...
End Sub
 
 
Private Sub VerifyUserName()
    Dim rsUserList As New ADODB.Recordset
    'ASSUMING THAT THE USERNAME IS NOT EXISTING
    ExistUserName = False
    'OPEN THE STUDENT TABLE
    rsUserList.Open "SELECT * FROM tblUser WHERE Username='" & txtName(7).Text & "'", CoNN, 3, 3
    'CHECK IF THE ID NUMBER IS ALREADY EXIST
    If rsUserList.RecordCount > 0 Then
        ExistUserName = True
    End If
 
    rsUserList.Close
    Set rsUserList = Nothing
 
End Sub
 
 

Hope this help..

[debasisdas]

1.Make the auto number field your primary key. It won't accept duplicate value.
or
2.You can check for existence of the record using count and then insert the record if that does not exist already in database.