Increment each order line number by 1 for the same OrderID only

[antonycat]

I have the following set of example data:

OrderID LineNo OrderQty
102 1 10
102 2 5
103 1 2
103 2 3
103 3 20

If I then add another OrderLine for example OrderID = 102, I would like to increment the LineNo by 1 to in this case 3. How would I go about doing this in VBA ? ie If OrderID = 102, increment LineNo by 1 and by the same principle if I add an OrderLine for an OrderID that does not exist I need to number the LineNo from 1 to whatever the last line for that particular Order is.

Thanking you in advance.

Regards
Antony

 

[snyperx3]

make sure you have the reference to Microsoft DAO object library set.

Dim dbs as DAO.Database
Dim rst as DAO.Recordset
Dim NewLineNum as Integer

Set dbs = CurrentDB()
Set rst = dbs.OpenRecordset("Select * FROM tblYourTableName WHERE OrderID = " & yourvalue & ";"
If Not rst.EOF Then
    rst.MoveLast
    newlinenum = rst.RecordCount + 1
Else
    newlinenum = 1
End If

-Pete

 

[snyperx3]

You know what?...DCount would be better.

Dim NewLineNum as integer
NewLineNum = DCount("OrderID","tblYourTable","OrderID = " & yourvalue) + 1

-Pete

 

[HandsOnAccess]

You can try like this...

If Not IsNull(DMax("LineNo","<<Table Name>>","OrderId = " & Me.OrderID)) = True Then
     me.LineNo = DMax("LineNo","<<Table Name>>","OrderId = " & Me.OrderID) + 1
ELSE
     me.LineNo = 1
END If

Let me know how it goes...

Regards,

 

[antonycat]

Thanks for the help so far.

However I get the same LineNumber generated, which is not what I want. We are using Access 2002 frontend and Sql 2000 Server backend.

Let me rephrase what I am trying to do. I have a temporary form for entering the OrderLines. When I press the save button, I need to append all the OrderLines to the Transactions table and increment each linenumber by 1 for the relevant OrderID. I presume some kind of loop.

Not sure how to write the relevant code. This is the code that I have so far attached to the Save button:

Private Sub cmdSave_Click()

On Error Resume Next

    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenQuery "qappOrderLines"
    DoCmd.Close acForm, "frmOrdersAdd"
    DoCmd.SetWarnings True
    
End Sub


Thanking you in advance.

Regards
Antony

 

[HandsOnAccess]

Please Post "qappOrderLines" Query SQL Statement.

Regards,

 

[antonycat]

Herewith the qappOrderLines SQL:

INSERT INTO Transactions ( OrderID, JobOrderReference, StopID, SourceID, RoutingDate, OrderType, CommodityType, OrderSize, TrolleyShelves, LoadReference, Comments, BookingInReference )
SELECT [Forms]![frmOrdersAdd]![txtOrderID] AS tmpOrderID, OrderLinesAdd.JobOrderNumber, OrderLinesAdd.StopID, OrderLinesAdd.SourceID, OrderLinesAdd.RoutingDate, OrderLinesAdd.OrderType, OrderLinesAdd.CommodityType, OrderLinesAdd.OrderSize, OrderLinesAdd.TrolleyShelves, OrderLinesAdd.LoadReference, OrderLinesAdd.Comments, IIf([BookingInReference] Is Null,[BookInRef],[BookingInReference]) AS BookInReference
FROM OrderLinesAdd LEFT JOIN Customer ON OrderLinesAdd.BookingInReference = Customer.BookInRef
WHERE (((OrderLinesAdd.CommodityType) Is Not Null) AND ((OrderLinesAdd.OrderSize)<>0 Or (OrderLinesAdd.OrderSize) Is Not Null));

Thanking you in advance.

Regards
Antony

 

[HandsOnAccess]

In your query, can you please point out which field refers to LineNumber.

Moreover Where actually have you written the code to increment the LineNumber by 1, I dont see it anywhere.

I see these fields:
tmpOrderID,
JobOrderNumber,
StopID,
SourceID,
RoutingDate,
OrderType,
CommodityType,
OrderSize,
TrolleyShelves,
LoadReference,
Comments,
BookInReference

Which among these refers to LineNumber.

 

[antonycat]

INSERT INTO Transactions ( OrderID, JobOrderReference, StopID, SourceID, RoutingDate, OrderType, CommodityType, OrderSize, TrolleyShelves, LoadReference, Comments, BookingInReference )
SELECT [Forms]![frmOrdersAdd]![txtOrderID] AS tmpOrderID, OrderLinesAdd.JobOrderNumber, OrderLinesAdd.StopID, OrderLinesAdd.SourceID, OrderLinesAdd.RoutingDate, OrderLinesAdd.OrderType, OrderLinesAdd.CommodityType, OrderLinesAdd.OrderSize, OrderLinesAdd.TrolleyShelves, OrderLinesAdd.LoadReference, OrderLinesAdd.Comments, IIf([BookingInReference] Is Null,[BookInRef],[BookingInReference]) AS BookInReference,IIf((DMax("LineNumber","Transactions","OrderID=[Forms]![frmOrdersAdd]![txtOrderID]")) Is Not Null,(DMax("LineNumber","Transactions","OrderID=[Forms]![frmOrdersAdd]![txtOrderID]")+1),1) AS LineNo
FROM OrderLinesAdd LEFT JOIN Customer ON OrderLinesAdd.BookingInReference = Customer.BookInRef
WHERE (((OrderLinesAdd.CommodityType) Is Not Null) AND ((OrderLinesAdd.OrderSize)<>0 Or (OrderLinesAdd.OrderSize) Is Not Null));

Sorry about excluding the LineNumber field. I deleted it from the query when I could not get it to work.

Regards
Antony

 

[PHV]

Why not using a trigger in the Sql 2000 Server backend ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[antonycat]

I have just started using SQL so am not sure how I would write the code for the trigger but I assume Dmax would be used but am not sure how best to proceed ?

Regards
Antony

 

[HandsOnAccess]

Modified Code to generate LineNumbers

Private Sub cmdSave_Click()
On Error Resume Next
    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdSaveRecord
    
    Dim Rs as DAO.RecordSet
    'you need to add referenct to DAO Object Library

    Set Rs = me.RecordSetClone
    Rs.MoveLast
    Rs.MoveFirst

    Dim xLineNo as Long
    If Not IsNull(DMax("LineNumber","Transactions","OrderId = " & Me.OrderID)) = True Then
         xLineNo = DMax("LineNumber","Transactions","OrderId = " & Me.OrderID) + 1
    ELSE
         xLineNo = 1
    END If

    While Rs.EOF = False
       Rs.Edit
       Rs.Fields("LineNo") = xLineNo
       xLineNo = xLineNo + 1
       Rs.Update
       Rs.MoveNext
    WEnd
    Rs.Close
    Set Rs = Nothing

    DoCmd.OpenQuery "qappOrderLines"
    DoCmd.Close acForm, "frmOrdersAdd"
    DoCmd.SetWarnings True
End Sub

QUERY DEFINITION For qappOrderLines

INSERT INTO Transactions ( OrderID, JobOrderReference, LineNumber, StopID, SourceID, RoutingDate, OrderType, CommodityType, OrderSize, TrolleyShelves, LoadReference, Comments, BookingInReference )
SELECT [Forms]![frmOrdersAdd]![txtOrderID] AS tmpOrderID, OrderLinesAdd.JobOrderNumber, LineNo, OrderLinesAdd.StopID, OrderLinesAdd.SourceID, OrderLinesAdd.RoutingDate, OrderLinesAdd.OrderType, OrderLinesAdd.CommodityType, OrderLinesAdd.OrderSize, OrderLinesAdd.TrolleyShelves, OrderLinesAdd.LoadReference, OrderLinesAdd.Comments, IIf([BookingInReference] Is Null,[BookInRef],[BookingInReference]) AS BookInReference
FROM OrderLinesAdd LEFT JOIN Customer ON OrderLinesAdd.BookingInReference = Customer.BookInRef
WHERE (((OrderLinesAdd.CommodityType) Is Not Null) AND ((OrderLinesAdd.OrderSize)<>0 Or (OrderLinesAdd.OrderSize) Is Not Null));

Hope this helps...
Regards

 

[antonycat]

Thanks HandsOnAccess

I still don't seem to be able to get it to work.

Maybe I need to give a trigger a go as PHV suggested but I am not sure how I would write the code for that ?

Regards
Antony