Cannot Add Then Cancel Record In VB6 & Access 2003


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Cannot Add Then Cancel Record In VB6 & Access 2003

Hybrid View

  1. #1
    Join Date
    May 2007
    Posts
    4

    Cannot Add Then Cancel Record In VB6 & Access 2003

    I am using VB6 with Access 2003. I must of done something because all of a sudden I cannot add a record and cancel it without causing an error. Here is my code:

    strCnn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=myMDB.mdb"


    cnn1.Open strCnn

    Set adoPrimaryRS = New Recordset

    adoPrimaryRS.CursorLocation = adUseServer

    strPrimarySQL = "select casename,renldate,product,fundingcode, * from Client " & " order by casename, renldate, product, fundingcode"
    adoPrimaryRS.Open strPrimarySQL, cnn1, adOpenStatic, adLockOptimistic


    When I want to add a record:

    With adoPrimaryRS
    If Not (.BOF Or .EOF Or .RecordCount = 0) Then
    mvBookMark = .Bookmark
    Else
    mvBookMark = 0
    End If
    .AddNew
    End With

    I cancel a record by:


    adoPrimaryRS.CancelUpdate
    If adoPrimaryRS.RecordCount = 0 Then
    AddFirstRecord
    Else
    If mvBookMark > 0 Then
    adoPrimaryRS.Bookmark = mvBookMark
    Else
    adoPrimaryRS.MoveFirst
    End If
    End If

    If I then go to a previous record I get an "Operation Canceled" error message on the moveprevious statement.

    If Not adoPrimaryRS.BOF Then
    adoPrimaryRS.MovePrevious
    End If

    If adoPrimaryRS.BOF And adoPrimaryRS.RecordCount > 0 Then
    Beep
    'moved off the end so go back
    adoPrimaryRS.MoveFirst
    end if

    This makes no sense to me because it was working fine 2 weeks ago. I reinstalled MDAC_TYP,EXE. What would cause this to happen. I also repaired the database several times without any success. It seems like a corrupted file someplace but for the life of me I can't figure out which one.

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    Are there any other errors in the Errors collection of the Connection object?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    May 2007
    Posts
    4
    When I do the addnew, in cnn1.error(0) I get "No such interface supported". Then when I get to the CancelUpdate command I get "Operation was canceled"


    Mark
    Last edited by mark1110; 05-07-2007 at 02:37 PM.

  4. #4
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    You should use the .RollbackTrans after creating a transaction off of the connection object.

    Code:
    Option Explicit
    
    Private Sub Form_Load()
        On Error GoTo MyError
    
        Dim oCnn As ADODB.Connection
        Set oCnn = New ADODB.Connection
        'open code
        '...
        '...
        oCnn.BeginTrans
    
        'Do insert or update record stuf
        '...
        '...
        Exit Sub
    
    MyError:
        oCnn.RollbackTrans
    End Sub
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  5. #5
    Join Date
    May 2007
    Posts
    4
    Unfortunately rewriting the code is not an option. What would cause something like this to happen.

    Mark

  6. #6
    Join Date
    May 2007
    Posts
    4
    Here is some more code that first gives me an error "No such interface supported"


    Dim oText As TextBox, oCheckbox As CheckBox
    Dim oCombo As ComboBox, oList As ListBox, oMaskEDBox As MaskEdBox
    Dim ctl As Control

    'Bind the text boxes to the data provider

    For Each oText In Me.txtFields
    Set oText.DataSource = adoPrimaryRS
    nDataType = adoPrimaryRS.Fields(oText.DataField).Type
    oText.Tag = WhatIsDataType(adoPrimaryRS.Fields(oText.DataField).Type)
    Next

    txtFields is a control array of textboxes from 0 to 150. I first get the error message on the first textbox. It does populate the text box with the correct information from the access database and it does seem to work when I do a next and previous. But when I add then cancel I get the operation has been canceled error message. .

    Mark

Similar Threads

  1. using VB6 code in Access 97
    By kctse1 in forum VB Classic
    Replies: 0
    Last Post: 06-13-2005, 11:59 PM
  2. How does the navigation buttons works? (Access 2003)
    By me_code2004 in forum Database
    Replies: 0
    Last Post: 06-04-2005, 07:06 AM
  3. VB6 and Access 2K
    By Bob in forum VB Classic
    Replies: 2
    Last Post: 05-09-2001, 10:46 AM
  4. setting up multiuser env. using vb6 (DAO) & Access 97
    By Jonathan Sullivan in forum VB Classic
    Replies: 0
    Last Post: 04-12-2001, 06:39 AM
  5. Access 2000 automation from VB6
    By Mak Foka in forum authorevents.kurata
    Replies: 3
    Last Post: 04-18-2000, 09:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center