By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,098 Members | 1,893 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,098 IT Pros & Developers. It's quick & easy.

What is wrong with this code..

P: 3
Expand|Select|Wrap|Line Numbers
  1. Private Sub Sno_AfterUpdate()
  2. Dim dbs As DAO.Database
  3. Dim rs As DAO.Recordset
  4. Dim sql As String
  5. Dim tmp As Variant
  6. Dim bno As String
  7. bno = Me.BookNo.Value
  8. area = Me.area.Value
  10. Set dbs = CurrentDb
  11.     'sql = "select s_no from customer_info where [book_no]='bno';"
  12.     Set rs = dbs.OpenRecordset("select s_no from customer_info where bookno='" & bno & "' and C_Area='" & area & "'")
  13.     Do While rs.EOF = False
  14.         If rs.Fields(0) = Me.S_No.Value Then
  15.             Do While rs.EOF = False
  16.                  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))
  17.                  rs.MoveNext
  18.             Loop
  19.         Else
  21.         End If
  22.         rs.MoveNext
  23.     Loop
  24.     If rs.EOF = False Then
  26.     ElseIf rs.EOF = False Then
  29.     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..
Nov 12 '13 #1
Share this Question
Share on Google+
6 Replies

P: 16
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:

Expand|Select|Wrap|Line Numbers
  1. sqlstr="UPDATE Customer_Info SET Customer_Info.s_no = [s_no]+1
  2. 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
Nov 12 '13 #2

Expert 100+
P: 234
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:

Expand|Select|Wrap|Line Numbers
  1. strSQL="UPDATE customer_info SET s_no = [s_no] + 1 " & _
  2. "WHERE Not s_no < " & Me.S_No & " AND " & _ 
  3. "C_Area='" & area & "' AND " & _ 
  4. "bookno='" & bno & "';" 'remember to check that bookno is correct
  6. DoCmd.Execute strSQL
Nov 12 '13 #3

P: 3
Thanx for your kind responses ... i am attaching the actual database this will clears my idea..and requirement....
Nov 13 '13 #4

Expert Mod 15k+
P: 31,494
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.
Nov 14 '13 #5

P: 3
i think now u ve understand my problem , actually that is a requirement...i ve to provide that facility to my client...
Nov 16 '13 #6

Expert Mod 5K+
P: 5,397
i think now u ve understand my problem , actually that is a requirement...i ve to provide that facility to my client...
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.
Nov 19 '13 #7

Post your reply

Sign in to post your reply or Sign up for a free account.