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

Assigning Sequence Numbers to Multiple Rows of Data Problem

P: 129
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")
  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
Share this Question
Share on Google+
7 Replies

Expert Mod 15k+
P: 31,761
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

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

P: 129
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
  6.     DoCmd.SetWarnings False
  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")
  11.         rst2.MoveLast
  12.         SeqItemNo = rst2!ItemNo
  13.         rst.Close
  15.     Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  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
  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

Expert Mod 15k+
P: 31,761
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

Expert Mod 2.5K+
P: 2,545
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 ...
Sep 5 '08 #6

P: 129
Thanks for that, it worked a treat. Cheers
Sep 5 '08 #7

Expert Mod 15k+
P: 31,761
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

Post your reply

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