Assigning Sequence Numbers to Multiple Rows of Data Problem

[Constantine AI]

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:

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM preordlin")
 
    Do
        rst.Edit
        rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
        rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
        rst!Cost = DLookup("[CostPrice1]", "primas", "[StkID] =" & rst!StkID)
        rst!ItemNo = [ItemNo] + 1
        rst.update
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Me.Requery
    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!

[NeoPa]

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.

[Constantine AI]

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.

[Constantine AI]

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;

    Dim db As Database
    Dim rst As Recordset
    Dim rst2 As Recordset
    Dim SeqItemNo As Integer
 
    DoCmd.SetWarnings False
 
    Set db = CurrentDb()
    Set rst2 = db.OpenRecordset("SELECT * FROM ordlin WHERE (((ordlin.OrderNo)=[Forms]![frmPreSOLineAdd]![OrderNo]) AND ((ordlin.ItemNo)>0)) ORDER BY ordlin.ItemNo")
 
        rst2.MoveLast
        SeqItemNo = rst2!ItemNo
        rst.Close
 
    Set rst = db.OpenRecordset("SELECT * FROM preordlin")
 
    Do
        rst.Edit
        rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
        rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
        rst!Cost = DLookup("[CostPrice1]", "primas", "[StkID] =" & rst!StkID)
        rst!ItemNo = rst!ItemNo + SeqItemNo
        rst.update
        SeqItemNo = SeqItemNo + 1
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Me.Requery
    Set rst = Nothing
 
    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

[NeoPa]

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.

[Stewart Ross]

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:

WHERE (((ordlin.OrderNo)= '" & [Forms]![frmPreSOLineAdd]![OrderNo] & "') AND ...
WHERE (((ordlin.OrderNo)= " & [Forms]![frmPreSOLineAdd]![OrderNo] & ") AND ...

-Stewart

[Constantine AI]

Thanks for that, it worked a treat. Cheers

[NeoPa]

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 :)