Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 29th, 2005, 09:08 AM
Authorized User
 
Join Date: Jun 2003
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Default increment gropup of records by one

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
 
Old July 1st, 2005, 06:39 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old July 1st, 2005, 06:40 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
How to auto increment ? Shawn Mohan SQL Server 2000 2 June 22nd, 2006 03:00 AM
How to auto increment? Shawn Mohan ASP.NET 2.0 Basics 6 June 20th, 2006 10:36 PM
Is it possible to increment in crystal cmdolcet Crystal Reports 2 August 14th, 2005 06:55 PM
Increment date akibaMaila VB.NET 2002/2003 Basics 1 August 11th, 2005 11:04 PM
auto increment? hosefo81 PHP Databases 1 February 2nd, 2004 03:56 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.