473,320 Members | 1,990 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.

Assigning Sequence Numbers to Multiple Rows of Data Problem

129 100+
Hi i have a table called preordlin where a csv file imports there data into it. I have an attribute called 'ItemNo' which i need to input sequential numbers for like an AutoNumber but one that resets itself everytime a csv file has been inputted. The code i have so far is simple but incorrect:

Expand|Select|Wrap|Line Numbers
  1.     Set db = CurrentDb()
  2.     Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  3.  
  4.     Do
  5.         rst.Edit
  6.         rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
  7.         rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
  8.         rst!Cost = DLookup("[CostPrice1]", "primas", "[StkID] =" & rst!StkID)
  9.         rst!ItemNo = [ItemNo] + 1
  10.         rst.update
  11.         rst.MoveNext
  12.     Loop Until rst.EOF
  13.     rst.Close
  14.     Me.Requery
  15.     Set rst = Nothing
The rst!ItemNo = [ItemNo] + 1 works and inputs the numbers 1 and 2 correctly in the rows of data, however when it comes to the 3rd and 4th row of data it still states that it is ItemNo 2. I know the reason for this which is because the code is looking at row 1 each time and then + 1 to the result.

I know i could use an autonumber but i require a sequence number filler code instead as the number have to reset themselves each time an order is being placed.

Is there a way i can tell the code to look at the previous row (ItemNo) and then + 1 to it? I have tried but unfortunately i have failed each time.

Any help would be much appreciated!
Sep 4 '08 #1
7 2871
NeoPa
32,556 Expert Mod 16PB
It seems to me that this question (although not quite a duplicate possibly) has been answered adequately in Generating Automatic PO Numbers via SQL within Loop Procedures.
Sep 5 '08 #2
Constantine AI
129 100+
Not anymore sorry i am just about to add to this question, which is relevant and different from the one you answered for me, thanks by the way.
Sep 5 '08 #3
Constantine AI
129 100+
I have a table called 'preordlin' and one called 'ordlin', each contains the same fields. The preordlin table is used for csv importing, sorting and cleaning the data before it gets imported into the ordlin table. When an item of stock appears in the preordlin table it creates an 'ItemNo' (Sequence coding). So the more stock items the more ItemNos get assigned to these stock items.

I have structured the code below to look at an existing order number and find the last inputted 'ItemNo' so i can carryon from the last entry if a late stock item needs inputting onto an order. At present the numbers starts from scratch which is no good to me. So i created the code below;

Expand|Select|Wrap|Line Numbers
  1.     Dim db As Database
  2.     Dim rst As Recordset
  3.     Dim rst2 As Recordset
  4.     Dim SeqItemNo As Integer
  5.  
  6.     DoCmd.SetWarnings False
  7.  
  8.     Set db = CurrentDb()
  9.     Set rst2 = db.OpenRecordset("SELECT * FROM ordlin WHERE (((ordlin.OrderNo)=[Forms]![frmPreSOLineAdd]![OrderNo]) AND ((ordlin.ItemNo)>0)) ORDER BY ordlin.ItemNo")
  10.  
  11.         rst2.MoveLast
  12.         SeqItemNo = rst2!ItemNo
  13.         rst.Close
  14.  
  15.     Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  16.  
  17.     Do
  18.         rst.Edit
  19.         rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
  20.         rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
  21.         rst!Cost = DLookup("[CostPrice1]", "primas", "[StkID] =" & rst!StkID)
  22.         rst!ItemNo = rst!ItemNo + SeqItemNo
  23.         rst.update
  24.         SeqItemNo = SeqItemNo + 1
  25.         rst.MoveNext
  26.     Loop Until rst.EOF
  27.     rst.Close
  28.     Me.Requery
  29.     Set rst = Nothing
  30.  
  31.     DoCmd.SetWarnings True
I am certain this code would work providing i didn't receive an error which says Too Few Parameters: Expected 1. I have checked with the help option and checked that what i have done is correct but i can't see where i am going wrong!

Could anyone please suggest something? The code errors on line 9
Sep 5 '08 #4
NeoPa
32,556 Expert Mod 16PB
Pleased to help.

Adding is fine, but consider the delay. It may be that posting a new one might give it more attention than adding to this. Only you know the details so do as you think is most appropriate.
Sep 5 '08 #5
Stewart Ross
2,545 Expert Mod 2GB
Hi. In line 9 you have a reference in the SQL to the control OrderNo on your form. Although such queries do work from the Query Editor, in opening a recordset from code the JET database engine will not recognise this as a valid field name, and will give you the rather confusing parameter error message as a result.

You need to supply the value of the control within the SQL statement, not the full reference to the name of the control, as follows. Use the first version if OrderNo is a string, the second if it is a number:
Expand|Select|Wrap|Line Numbers
  1. WHERE (((ordlin.OrderNo)= '" & [Forms]![frmPreSOLineAdd]![OrderNo] & "') AND ...
  2. WHERE (((ordlin.OrderNo)= " & [Forms]![frmPreSOLineAdd]![OrderNo] & ") AND ...
-Stewart
Sep 5 '08 #6
Constantine AI
129 100+
Thanks for that, it worked a treat. Cheers
Sep 5 '08 #7
NeoPa
32,556 Expert Mod 16PB
Well spotted Stewart.

I didn't even know that was a restriction in DAO recordsets. I would never recommend using that in a SQL string created in VBA anyway, so I suppose I've just never come across the problem.

I would certainly recommend doing it the way you suggest :)
Sep 5 '08 #8

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

Similar topics

6
by: cjm | last post by:
I need to group records and assign a setid to the group. I have a table with data that looks like this ColA ColB 94015 01065 94016 01065 94015 01085 94015 01086 33383 00912 32601 00912
6
by: python1 | last post by:
I apologize if this is redundant. How would one fill an empty column with a sequence of numbers? The column exists in a table with aproximately 1000000 rows of data. I believe in oracle the...
6
by: James Liu | last post by:
I did some search and didn't find anything about whether Yokon will support sequence object. Appreciate it if you can reply with some resources. Thanks, James
4
by: Kamran K | last post by:
Hello I have created a client server application using C#. Existing application is using random number on client side to generate sequence numbers that are then assigned to transactions. This...
4
by: Eric E | last post by:
Hi, I have a question about sequences. I need a field to have values with no holes in the sequence. However, the values do not need to be in order. My users will draw a number or numbers from...
5
by: jacob.dba | last post by:
I have a table with first name, last name, SSN(social security number) and other columns. I want to assign group number according to this business logic. 1. Records with equal SSN and (similar...
12
by: Jim Michaels | last post by:
I need to generate 2 random numbers in rapid sequence from either PHP or mysql. I have not been able to do either. I get the same number back several times from PHP's mt_rand() and from mysql's...
17
by: I.M. !Knuth | last post by:
Hi. I'm more-or-less a C newbie. I thought I had pointers under control until I started goofing around with this: ...
482
by: bonneylake | last post by:
Hey Everyone, Well i am not sure if this is more of a coldfusion problem or a javscript problem. So if i asked my question in the wrong section let me know an all move it to the correct place. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
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...
1
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: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.