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:
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
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
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?
