Updating Fields in a Sequence 1

[dumass]

I have no clue how to do this and was wondering if someone had an idea. I've got a table which has three fields--cart_id, order, and orderid. The cart_id field has the number of the cart (1, 2, 3, etc.), and the order field has a random order number which is on that cart. So it looks something like:

CART_ID ORDER ORDERID
1 123456
1 123457
1 123458
2 123459
2 123460
2 123461
3 123462
3 156123
3 159987

I want to update the ORDERID field with the sequential number of the order on that particular cart. So, the above example would look like 1,2,3,1,2,3,1,2,3,1,2,3 (order number 123461's orderid would be 3 since it's the 3rd order in the 2nd cart). The are always only three orders on a cart.

Any ideas?

 

[DanJR]

Hi there,

Here are two ways to do it. I would use method 1, since method two assumes that the first Cart_ID is not the same as the one stored in memmory. Also, you will need to create a query that orders the Cart_ID, then make the update query based on this query.

--------
Method 1
--------

A function update the OrderID field using a recordset:

Public Function UpdateOrderID()
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim lngOrderID As Long
Dim lngCartID As Long

strSQL = "SELECT [CART_ID], [ORDERID] FROM tblCarts ORDER BY [Cart_ID]"

Set rs = New ADODB.Recordset
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

lngOrderID = 0 'just stating the obvious

Do Until rs.EOF

If lngCartID = rs.Fields("Cart_ID&quot.Value Then
lngOrderID = lngOrderID + 1
Else
lngOrderID = 1
End If

rs.Fields("OrderID&quot.Value = lngOrderID
rs.Update

lngCartID = rs.Fields("Cart_ID&quot.Value

rs.MoveNext
Loop

Set rs = Nothing

End Function

---------
Method 2:
---------

Use an update query which relies on a public function containing static variables.

1. Create this function in a module:

Function fSeqOrderID(CartID As Long) As Long
Static lngOrderID As Long
Static lngCartID As Long

If CartID <> lngCartID Then
lngOrderID = 1
Else
lngOrderID = lngOrderID + 1
End If

lngCartID = CartID

fSeqOrderID = lngOrderID


End Function

2. create this update query, which calls the function

UPDATE tblCarts SET tblCarts.ORDERID = fseqorderid([cart_id])


I hope this helps.
Cheers,
Dan

P.S. oh, I assume the order of field &quot;Order&quot; was not important. If it is, then just update the strSQL string in Method 1.

 

[dumass]

Thanks DanJR! Both of those worked like a charm. And saved me several hours of bashing my skull against the wall.

One other question--if I have a control on a report that I want to find the order number of a given record by using multiple criteria (cart_id and orderid), what's the best way to do it?

I've been toying with a DLookup function like:

DLookUp(&quot;[order]&quot;,&quot;tblCarts&quot;,&quot;[cart_id]=&quot; And [orderid]=2 )

but I can't get the second part of the criteria (the orderid) to work. Any ideas?

 

[JeremyNYC]

Look at your criteria again. There's nothing for cart_id to be equal to. You'll want to do something like this...
DLookUp(&quot;[order]&quot;,&quot;tblCarts&quot;,&quot;[cart_id]=&quot; & me!NameOfYourControl & &quot; And [orderid]=2 ) =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.