- 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..
6 1263
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
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
-
Thanx for your kind responses ... i am attaching the actual database this will clears my idea..and requirement....
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.
i think now u ve understand my problem , actually that is a requirement...i ve to provide that facility to my client...
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. Sign in to post your reply or Sign up for a free account.
Similar topics
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;...
|
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...
|
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,...
|
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;
}
|
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)...
|
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) ...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
| |