Help using Dmax as an OrderID counter...?

[acenumber5]

Hi!

I am trying to use the DMax function to create an incremental Order ID number in my Orders table.

VPD_Order_IDmax("VPD_Order_ID","OrdersMaster") +1

It works fine if I am importing one order at a time, but if I import multiple orders it tried to assign them all the same number. Any suggestions?

More info: I'm doing this in an Append Query in Access 2010. I have a table "Amazon_orders" that I append the new orders to an "Ordersmaster" table. I'm trying to create the incremental Order_ID in the Orders Master table. I tried using an Autonumber, but that....didn't work. I also sometimes have more than one item on an order, so I could have 2 rows with the same Order number.



Any suggestions would be greatly appreciated!
Thanks!
Andrew

[June7]

You said you are importing orders? Importing from where? Might have to use VBA to save records and assign the Order number.

[acenumber5]

Importing from another table..
Csv file -> amazonimporttable -> Ordersmaster table.

I'm using an append query to copy data from the amazonimporttable to the OrdersMaster table.

Do you know any examples of how to do this in VBA?

Thanks!

[June7]

So the import table does not have an order number? What distinguishes the orders in the imported data? If the import can include multiple items for each order, need another table 'OrderDetails'.

Code would be a VBA procedure that opens recordset of the import table, reads each record, writes data to tables Orders and OrderDetails, incrementing the OrderID. How much programming skill do you have?

[acenumber5]

Hi!
Yes the imported table does have an Order-ID and an Order-Item-ID, but they are 20+ characters and contain "-"''s them. The same Order-ID can have multiple Order-Item-ID's if one order has more than one item on it.
I need to create one that's 8 characters or less, which I use to send the orders to the distributor. 8 characters is the distributors data field requirement.

I have solid below average programming skills. I can usually modify VBA programs, but would be challenged to come up with it from scratch.
Thank you!

[June7]

Here is some very rough 'air code' (typing on-the-fly, not tested). See what you can do with it.

Sub AppendOrders()
Dim strOldOrderID
Dim intNewOrderID
Dim strOrderItemID
Dim booFirst As Boolean
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Amazon_orders ORDER BY OrderID, OrderItemID;"
strOldOrderID = rs!OrderID
booFirst = True
While Not rs.EOF
    If strOldOrderID = rs!OrderID Then
        If booFirst = True Then
            intNewOrderID = DMax("OrderID", "Orders") + 1
            CurrentDb.Execute "INSERT INTO Orders(OrderID, CustomerID) VALUES(" & intNewOrderID & ", " & rs!CustomerID & ")"
            booFirst = False
        End If
        CurrentDb.Execute "INSERT INTO OrderDetails(OrderID, OrderItemID) VALUES(" & intNewOrderID & ", " & rs!OrderItemID & ")"
        rs.MoveNext
    Else
        booFirst = True
        If Not rs.EOF Then strOldOrderID = rs!OrderID
    End If
Wend
End Sub

If your OrderID in new table is not an integer but an alphanumeric structure, incrementing it will be a little more complicated. However, I just pictured a way this might be possible with queries. Want to provide a sample of the imported data? Follow instructions at bottom of my post.

[acenumber5]

Awesome! I will try it tonight and report back.

Thank you!

[June7]

I might have edited my last post after you first read it. See the comments after the code sample.

Be sure to do testing with copy of data.

[acenumber5]

Hey June7 -I'm in the middle of acquiring a small company, so I haven't had much time to work on this, but as soon as I do I'll report back. Thanks!