Can anyone help with data conversions?

[malibu65k]

I am trying to convert text to number using the following code and Insert the data into my table. All my data gets inserted until I get to this point. The computer locks up. I go to Task Manager and it shows that Access is Not Responding. My Table's datatype for this field is Number-Double. When I view the data after it's converted to the number, (using a MSGBOX) it converts it but the problem starts when it goes to update it to the table.

rst("PRDMET") = CDbl(Val(Mid(strLineData, 155, 5)) / 100)

Here is the full code:

Sub UpdateCA(FileName As String)

Dim db1 As DAO.Database
Dim rst As DAO.Recordset
Set db1 = CurrentDb()
Dim vConvert As Double
Dim strLineData As String
Dim strFilePath As String

strFilePath = "C:\Documents and Settings\MORRISONKM\My Documents\Kathy\LSA Data\ICRL\" & FileName & ".txt"
Open strFilePath For Input As #1
Do While Not EOF(1)
Line Input #1, strLineData

Set rst = db1.OpenRecordset(FileName)

With rst
rst.AddNew
rst("LSACON") = Mid(strLineData, 15, 18)
rst("Taskcd") = Mid(strLineData, 36, 7)
rst("Refeia") = Mid(strLineData, 43, 10)
rst("RefLCN") = Mid(strLineData, 53, 18)
rst("RefAlc") = Mid(strLineData, 71, 2)
rst("RefTyp") = Mid(strLineData, 73, 1)
rst("RefTsk") = Mid(strLineData, 74, 7)
rst("TaskId") = Mid(strLineData, 103, 36)
rst("TSKFRQ") = CDbl(Val(Mid(strLineData, 139, 7)) / 10000)
rst("MSDMET") = CDbl(Val(Mid(strLineData, 150, 5)) / 100)
rst("PRDMET") = CDbl(Val(Mid(strLineData, 155, 5)) / 100)
rst("MSDMMH") = CDbl(Val(Mid(strLineData, 160, 5)) / 100)
rst("PRDMMH") = CDbl(Val(Mid(strLineData, 165, 5)) / 100)

rst.Update
End With
Loop
Close #1

End Sub

 

[MoLaker]

Quick take...
Try placing the line "Set rst = db1.OpenRecordset(FileName)" just before the Do While line. You should only have to open the recordset once.

Then be sure to close it and destroy objects after you're finished

rst.close
set rst = nothing
set db1 = nothing

 

[malibu65k]

Thank you, MoLaker. That did the trick.