What is wrong with this code..

[begnar]

Private Sub Sno_AfterUpdate()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String
Dim tmp As Variant
Dim bno As String
bno = Me.BookNo.Value
area = Me.area.Value
 
Set dbs = CurrentDb
    'sql = "select s_no from customer_info where [book_no]='bno';"
    Set rs = dbs.OpenRecordset("select s_no from customer_info where bookno='" & bno & "' and C_Area='" & area & "'")
    Do While rs.EOF = False
        If rs.Fields(0) = Me.S_No.Value Then
            Do While rs.EOF = False
                 dbs.Execute ("update customer_info set s_no=" & rs.Fields(0) + 1 & " where C_Area='" & area & "'and bookno='" & bno & "' and s_no=" & rs.Fields(0))
                 rs.MoveNext
            Loop
        Else
 
        End If
        rs.MoveNext
    Loop
    If rs.EOF = False Then
 
    ElseIf rs.EOF = False Then
 
 
    End If

actual i ve a table named customer_info, which has some fields like aera, BookNo, S_No,....and so on, the problem is the updating the existing data of field S_No , this contains data in the form of 1,2,3,4,5,6,7,8,9.....this is the serial number of customers. now if i assigned 7 to a new customer then the existing 7 should changed to 8 and 8 to 9 and so on....and 7 to the new customer..

[CJ_London]

Why do you want to that - to insert a new customer 'in front' of an old one? Just curious, I presume that serial number is not a unique id/auto number in which case you cannot do this.

But you haven't explained what is wrong, you told us what you want to do. So why doesn't your code work? do you get an error message? If so, what error number and message? Does it appear to run but does not change anything? etc...

If I have understood you correctly then all you need is a query along the following lines:

sqlstr="UPDATE Customer_Info SET Customer_Info.s_no = [s_no]+1
WHERE Customer_Info.s_no>=" & s_no AND C_Area='" & area & "'and bookno='" & bno & "'"

Other things to check

s_no datatype is not an autonumber
s_no is not indexed, no duplicates

[topher23]

FYI, serial number implies series, as in "each instance comes after the last." An autonumber is a true serial number generator; it provides unique identifiers in series. If what you are doing is try to provide an identifier for something like a unique sort, this makes better sense.

As to why this doesn't work, I'm concerned about bookno. In your commented sql string, you call it out as [book_no], but in the SQL that is executing you simply call it bookno. Further, you've got it wrapped in string qualifiers: if it's actually a number, it doesn't need those. Otherwise, CA has the right idea, there are just a few issues in the SQL string. Use instead:

strSQL="UPDATE customer_info SET s_no = [s_no] + 1 " & _
"WHERE Not s_no < " & Me.S_No & " AND " & _ 
"C_Area='" & area & "' AND " & _ 
"bookno='" & bno & "';" 'remember to check that bookno is correct
 
DoCmd.Execute strSQL
 

[begnar]

Thanx for your kind responses ... i am attaching the actual database this will clears my idea..and requirement....

[NeoPa]

Please don't.

You were asked questions because the information you're responsible for including in you question was incomplete.

Please respond with the answers in place of providing an attachment where experts can, if they want to take the risk, download it and find the answers for themselves.

Remember, it's not their responsibility to provide a sensible question.

[begnar]

i think now u ve understand my problem , actually that is a requirement...i ve to provide that facility to my client...

[zmbd]

begnar
i think now u ve understand my problem , actually that is a requirement...i ve to provide that facility to my client...

Begnar:
You have not answered CAintheUK's question (Post#2) without this requested information it makes it fairly difficult for even the "experts" to provide an answer.

I've already given you a chance to clear up the thread when I merged your first and second posts and we're still no closer to understanding your goal. So please, take a deep breath, organize your thoughts, and try to provide use with some detail about what your goal is, why what you have isn't working, which version of Access you are working with, and any other details.

Also, I re-itterate Neopa's statement - please do not attach your database.