Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
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
  #1 (permalink)  
Old January 20th, 2006, 02:35 PM
Registered User
 
Join Date: Jan 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default add new record to ADO recordset via stored proc

Hi all,

I am converting a split DAO .mdb to SQL/ADO/.adp.
In the original .mdb, there is a main form with 4 subforms. each of these subforms contain records that correlate to the main form. I had to simplify the original form recordsources to make them writable (they used multiple joins before) and transform those recordsources to stored proc's. I am having trouble writing to one of these recordsets still however, particularily in the re-sequencing if the records after .addnew method. Thanks in advance for any insight. see below code :

(original DAO .mdb code)
Private Sub AddRecordButton_Click()
Dim rst As Recordset, CurrentRecord As Long, varBookmark As Variant

If (IsNull(Me.Parent.CNPickCombo)) Then
    MsgBox ("Cannot insert a record into a use case without selecting a change.")
Else
    CurrentRecord = Me!SequenceNumber
    Set rst = Me.RecordsetClone
    If (rst.BOF = False And rst.EOF = False) Then
        rst.MoveFirst
        rst.Move CurrentRecord - 1
        Do Until rst!SequenceNumber = rst.RecordCount
            rst.Edit
            rst!SequenceNumber = rst!SequenceNumber + 1
            rst.Update
            rst.MoveNext
        Loop
        rst.Edit
        rst!SequenceNumber = rst!SequenceNumber + 1
        rst.Update
        rst.AddNew
            rst!OwnerUseCaseID = Forms!UCStepsMainForm!UseCaseID
            rst!SequenceNumber = CurrentRecord
            If (Me.Parent.Baselined) Then
                rst!Modified = True
                rst!CurrentCR = Me.Parent.CNPickCombo
            End If
        rst.Update
        rst.Close
        Me.Requery
        Set rst = Me.RecordsetClone
        rst.FindFirst "SequenceNumber = " & CurrentRecord
        Me.Bookmark = rst.Bookmark
        rst.Close
    Else
        rst.Close
    End If
End If

End Sub

(Current ADO .adp code)

Private Sub AddRecordButton_Click()
Dim rst As New ADODB.Recordset
Dim CurrentRecord As Long


If (IsNull(Me.Parent.CNPickCombo)) Then
    MsgBox ("Cannot insert a record into a use case without selecting a change.")
Else
    CurrentRecord = Me!SequenceNumber
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic
    Set rst = Me.Recordset

    If (rst.BOF = False And rst.EOF = False) Then <---PROBLEM HERE?
        rst.MoveFirst
        rst.Move CurrentRecord - 1
            Do Until rst!SequenceNumber = rst.RecordCount + 1

                rst!SequenceNumber = rst!SequenceNumber + 1
                rst.Update
                rst.MoveNext
            Loop

        rst!SequenceNumber = rst!SequenceNumber + 1
        rst.Update
        rst.AddNew
            rst!OwnerUseCaseID = Forms!UCStepsMainForm!UseCaseID
            rst!SequenceNumber = CurrentRecord - 1
            If (Me.Parent.Baselined) Then
                rst!Modified = True
                rst!CurrentCR = Me.Parent.CNPickCombo
            End If
        rst.Update
        rst.Close
        Me.Requery

        rst.CursorLocation = adUseClient
        rst.CursorType = adOpenDynamic
        rst.LockType = adLockOptimistic
        Set rst = Me.Recordset

        rst.Find "SequenceNumber = " & CurrentRecord
        'Me.Bookmark = rst.Bookmark

    Else

    End If
End If

End Sub




Similar Threads
Thread Thread Starter Forum Replies Last Post
ADO record copy and add to table Freddyfred Access 2 February 16th, 2005 10:36 PM
Poor performance with ADO stored proc. call aldovalerio VBScript 0 August 23rd, 2004 08:21 AM
Stored Proc returns closed recordset ? xgbnow Pro VB Databases 10 March 26th, 2004 06:00 AM
efficiency qn:ado .AddNew VS INSERT in stored proc ak Classic ASP Databases 1 February 25th, 2004 10:08 AM
effieciency: ado .AddNew vs Insert in Stored proc ak SQL Server ASP 2 February 25th, 2004 09:20 AM





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