Hi, guys I am working on vb project, which will save client database.

I've done the coding of ADD, NEW, SAVE, EDIT button. Now, I am stuck at DELETE button. When I delete any particular client then I want my client_id (it's a column in my access database) should get updated (like if I have five clients in database and if I delete 3rd client, then the client_id should start from 1,2,3,4; not like 1,2,4,5)

I am using ADODB and Ms-access at backend, please help!!!!!

for DELETE, I've done following coding:

Private Sub cmdDelete_Click()

If rs.State = 1 Then rs.Close
admincon.Execute "delete from clientdetails where client_id=" & (lblId.Caption) & ""
MsgBox ("Record is Deleted successfully")
Clear
generateid ' It's a function i am using for generating ids. It's coding is given follow
Unload Me
frmAddClient.Show

End Sub

And for function generateid, I've done following coding:

Public Sub generateid()
Dim i As Integer
i = 1
If rs.State = 1 Then rs.Close
rs.Open "clientdetails", admincon, adOpenDynamic, adLockOptimistic, adCmdTable
Do
admincon.Execute "update clientdetails set client_id=" & i & ""

i = i + 1
rs.movenext

Loop While rs.EOF = True

End Sub

Hi, guys I am working on vb project, which will save client database.

I've done the coding of ADD, NEW, SAVE, EDIT button. Now, I am stuck at DELETE button. When I delete any particular client then I want my client_id (it's a column in my access database) should get updated (like if I have five clients in database and if I delete 3rd client, then the client_id should start from 1,2,3,4; not like 1,2,4,5)

No it shouldn't. Once assigned, a client ID should never be changed. It is their unique value used throughout the system. All records pertaining to that person should be using this number and not their name, so the change would be monumental in a 'real' database.

What do you think would happen in the real world if, when someone stops driving, all the driver's license numbers changed?

commented: lol +6

WaltP is very right. Please all IDs should remain unique

Hi,

Yes, Client_ID's should be Unique.
But still if, you want to renumber them, Change your code to :

Public Sub generateid()
    Dim i As Integer
    i = 1
    If rs.State = 1 Then rs.Close
    rs.Open "clientdetails", admincon, adOpenDynamic, adLockOptimistic
    Do While Not rs.EOF
       rs("Client_ID") = i
       rs.Update
       i = i + 1
       rs.MoveNext
    Loop 
End Sub

Regards
Veena

Hi,

Yes, Client_ID's should be Unique.
But still if, you want to renumber them, Change your code to :

Public Sub generateid()
    Dim i As Integer
    i = 1
    If rs.State = 1 Then rs.Close
    rs.Open "clientdetails", admincon, adOpenDynamic, adLockOptimistic
    Do While Not rs.EOF
       rs("Client_ID") = i
       rs.Update
       i = i + 1
       rs.MoveNext
    Loop 
End Sub

Regards
Veena

But what about all the other database records in the other tables that have the ID in them?

WaltP, actually in my case client ID is not a primary key. It's actually requirement of my project that when i delete the customer client id should get updated.

let me first try this code

Hi,

Yes, Client_ID's should be Unique.
But still if, you want to renumber them, Change your code to :

Public Sub generateid()
    Dim i As Integer
    i = 1
    If rs.State = 1 Then rs.Close
    rs.Open "clientdetails", admincon, adOpenDynamic, adLockOptimistic
    Do While Not rs.EOF
       rs("Client_ID") = i
       rs.Update
       i = i + 1
       rs.MoveNext
    Loop 
End Sub

Regards
Veena

You are awesome man, that code worked well for me. Thanks a lot!!!

But what about all the other database records in the other tables that have the ID in them?

Thanks for your help

if any one hv code in working form so kindly please
share vb6.0 & db file that generate auto ID number....I face error
& I REALLY WANT AUTO NUMBER IN MY PROJECT
where such codeshould be implimented
please help me

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.