In the procedure below I want to select a group of DeliveryNumbers and used codes to number them starting from one. I am not successful because I cannot get around it if DeliveryNumber is null and I am not getting the delivery numbers to increment by one.

Thanks of the help

Dim rst as ADODB.Recordset
dim cnn as ADODB.Connection
Dim strSQL as String

strSQL = "SELECT tblDelivery.DeliveryNumber " & _
    "FROM tblDelivery INNER JOIN tblDeliveryDetails ON tblDelivery.DeliveryID = tblDeliveryDetails.DeliveryID " & _
    "WHERE tblDeliveryDetails.MonthOf = '" & [Forms]![frmDelivery]![frmDeliverySub]![MonthOf] & "'" & _
    " AND tblDeliveryDetails.DeliveryYear = " & [Forms]![frmDelivery]![frmDeliverySub]![DeliveryYear]


    Set rst = New ADODB.Recordset
    Set cnn = CurrentProject.Connection

    rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic, adCmdText

    Do Until rst.EOF

        rst!DeliveryNumber = rst.DeliveryNumber + 1
        rst.Update
        rst.MoveNext

    Loop

Hi,

   First, I would create the query in Access and get it to perform the way that you want. Then instead of opening this recordset with a SQL statement, I would open it this way:

'----------
Dim strSQL as String

strSQL = "SELECT * FROM qryDeliveryQuery"

'----------

If you can get the query to perform the way you want, then you should be able to do the rest.

However, if you are running an Update Query on the DeliveryNumber field...

First, it won't work if the DeliveryNumber field is the PK or autonumber.

Second, what you need to do is an UPDATE query in Access, and then ALL of your code would look like this:

'----------

DoCmd.OpenQuery "qryUPDATEDeliveryNumber"

'----------

Type "Update Query" in the Help box in the upper right of your Access window.

HTH

mmcdonal

Sorry, that was a SQL statement. What I meant was by using this complicated SQL statement.

mmcdonal