Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trouble with Editing a Recordset (97 SR-2)

    I've written some VBA to import 2 fixed width files into Access tables. Some of the fields are dollar amounts or interest rates with missing decimal points so I need to adjust the values that are in some of the fields. Here's the portion of my code that tries to do this:

    <pre>Function ReportSPE()
    Dim ZipFilePath As String, OutputPath As String, mmddyy As String, FilesToUnzip As String
    Dim RC As Double
    Dim StartTime As Single
    Dim Companies(4) As String
    Dim Company As Variant
    Dim ThisDB As Database
    Dim RecID As Field
    Dim ThisTable As TableDef
    Dim PauseToUnzip As Boolean
    Dim FileLen As Long
    Dim DataFile As String
    Dim N As Integer
    Dim TableField As Field
    Dim SPERecords As Recordset

    Set ThisDB = CurrentDb

    <irrelevant code deleted>


    ThisDB.TableDefs.Refresh

    Set SPERecords = ThisDB.OpenRecordset("SPEMBR Data")
    Do While Not SPERecords.EOF
    SPERecords.Edit
    For Each TableField In SPERecords.Fields
    With TableField
    If .Type = dbCurrency And InStr(1, .Name, "DeathBen") = 0 _
    Then .Value = .Value / 100
    End With
    Next TableField
    SPERecords.Update
    SPERecords.MoveNext
    Loop

    Set SPERecords = ThisDB.OpenRecordset("All SPE")
    Do While Not SPERecords.EOF
    SPERecords.Edit '*********************Error Occurs Here***********************
    For Each TableField In SPERecords.Fields
    With TableField
    If .Type = dbCurrency And InStr(1, .Name, "FaceAmt") = 0 _
    Then .Value = .Value / 100
    If .Type = dbDouble And InStr(1, .Name, "rate") > 0 _
    Then .Value = .Value / 1000000
    End With
    Next TableField
    SPERecords.Update
    SPERecords.MoveNext
    Loop
    </pre>

    Everything seems to work fine on the first table. On the second table, I'm getting the following error:

    Run time error '3218': Couldn't update; currently locked.

    When I click on Debug, I'm on the second SPERECORDS.EDIT line as indicated above. If I then hit F5, everything runs fine to completion.

    What is my problem? I've tried switching the order of the table and the error always occurs on the second table. I've tried using a different variable name besides SPERECORDS for the second recordset.

    More generally, is there a better way to do the adjusting?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Trouble with Editing a Recordset (97 SR-2)

    Try closing the recordset after each loop:

    SPERecords.Close

    But why don't you create some update queries and run those? They'll be much faster than looping through a recordset and looping through its fields.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •