473,320 Members | 1,868 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

What is wrong with this code..

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
  9.  
  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
  20.  
  21.         End If
  22.         rs.MoveNext
  23.     Loop
  24.     If rs.EOF = False Then
  25.  
  26.     ElseIf rs.EOF = False Then
  27.  
  28.  
  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
6 1263
CJ_London
27 16bit
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
topher23
234 Expert 100+
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
  5.  
  6. DoCmd.Execute strSQL
  7.  
Nov 12 '13 #3
begnar
3
Thanx for your kind responses ... i am attaching the actual database this will clears my idea..and requirement....
Nov 13 '13 #4
NeoPa
32,556 Expert Mod 16PB
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
begnar
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
zmbd
5,501 Expert Mod 4TB
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.
Nov 19 '13 #7

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

Similar topics

3
by: RobertTG | last post by:
Someone please translate the code below into English... Particularly the indicated line Thanks function attachComment() { var aForms = document.getElementsByTagName("FORM"); for (var i = 0;...
13
by: dbuchanan | last post by:
This code resets a form with two cbo's (comboBoxes) and one datagrid. The first cbo (cboSelection) selects a main table and filters the second cbo. The second cbo (cboView) selects the secondary...
9
by: oddvark | last post by:
Hello, under vc7.1 this code compiles: if (!parent.fillTool) parent.fillTool.dispose; where dispose is a method of fillTool. Notice that dispose does not have ( ) behind it. Under vc8,...
1
by: locy | last post by:
can someone explain to me "what does this code do" class baseclass { public: virtual void runme() { std::cout<<"are you"<<std::endl; }
5
by: lucas | last post by:
is a javascript file; is ajax? or javascript but in hidden code? eval(function(p,a,c,k,e,d) {e=function(c)...
3
by: qianz99 | last post by:
Hi I am not sure what this code does. I have the following questions 1. where is the case? 2. #define TLV_INTEGER(name, octets) p->name = -1; Is it define a function TLV_INTEGER(name, octets) ...
6
by: sbcs | last post by:
I'm a website developer. Recently I've found variations of this code on the home pages of several of my sites. It triggers warnings in some anti-virus/malware programs but not in others. The pages...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.