update text box during code execution

[James Gaylord via AccessMonster.com]

I need to have a label/text box that updates as my code loops through the
parsing/impoting of a text files. I want the label/text box to show which
record number I am currently on. I can't seem to get the label/text to show
during the execution of the code. Here is the code I am currently using


Dim iFile As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim nLine, st2nd, st3rd, stUOM, stDesc1, stDesc2 As String
Dim lngItem As Long
Dim tStart As Single
Dim x As Long


' Deletes the current table before running the update
On Error GoTo ImportParseJDEItems_Error
tStart = Timer
DoCmd.OpenQuery "qryelJDEItems", acViewNormal, acEdit

Set db = CurrentDb()
Set rs = db.OpenRecordset("JDEItemNumbers")

iFile = FreeFile

Open "Z:\VMITMMST.csv" For Input Access Read As iFile
x = 1
Do Until EOF(iFile)

Forms![frm:Maintanence].[lblRecCount].Caption = x & " records processed"

Line Input #iFile, nLine

st2nd = Split(nLine, ",")(0)

If Left(st2nd, 1) = """" Then
st2nd = Mid(st2nd, 2, Len(st2nd) - 2)
End If

st3rd = Split(nLine, ",")(1)
If Left(st3rd, 1) = """" Then
st3rd = Mid(st3rd, 2, Len(st3rd) - 2)
End If

lngItem = Split(nLine, ",")(2)

stUOM = Split(nLine, ",")(3)
If Left(stUOM, 1) = """" Then
stUOM = Mid(stUOM, 2, Len(stUOM) - 2)
End If

stDesc1 = Split(nLine, ",")(4)
If Left(stDesc1, 1) = """" Then
stDesc1 = Mid(stDesc1, 2, Len(stDesc1) - 2)
End If
stDesc2 = Split(nLine, ",")(5)
If Len(stDesc2) > 2 And Left(stDesc2, 1) = """" Then
stDesc2 = Mid(stDesc2, 2, Len(stDesc2) - 2)
Else
stDesc2 = ""
End If
rs.AddNew
rs![2NDItemNumber] = st2nd
rs![3RDIteNumber] = st3rd
rs![ItemNumber(Short)] = lngItem
rs!UOM = stUOM
rs!Desc1 = stDesc1
rs!Desc2 = stDesc2

rs.Update
x = x + 1
Loop

rs.Close

db.Close

MsgBox "Processed " & x & " records in " & (Timer - tStart) & " seconds!",
vbExclamation, "Process complete!"

On Error GoTo 0
Exit Sub

ImportParseJDEItems_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
ImportParseJDEItems of Module mod:JDEImport"

End Sub


I am getting an error on the "Forms![frm:Maintanence].[lblRecCount].Caption =
x & " records processed" " line stating the form can't be found.

TIA

 

[Jeanette Cunningham]

James,
I spotted a typo in this line:

Forms![frm:Maintanence].[lblRecCount].Caption = x & " records processed"

There is a colon between frm and Maintenence - frm:Maintanence
Don't know if it is a copy and paste from your code or not?

Jeanette Cunningham

 

[Paolo]

Hi James,

You have to repaint the screen to echo the update to the label/text box so
do that:
after the line
Forms![frm:Maintanence].[lblRecCount].Caption = x & " records processed"
add
me.repaint

HTH Paolo

 

[James Gaylord via AccessMonster.com]

No, This is the way I named my form. "frm:Maintanence"

James,
I spotted a typo in this line:

Forms![frm:Maintanence].[lblRecCount].Caption = x & " records processed"

There is a colon between frm and Maintenence - frm:Maintanence
Don't know if it is a copy and paste from your code or not?

Jeanette Cunningham

I need to have a label/text box that updates as my code loops through the
parsing/impoting of a text files. I want the label/text box to show which

[quoted text clipped - 90 lines]

TIA

--
James B Gaylord
From the Wolf Comes the Strength of the Pack
From the Pack Comes the Strength of the Wolf
- R Kipling

Message posted via AccessMonster.com

 

[James Gaylord via AccessMonster.com]

This worked great.

Thanks,

Hi James,

You have to repaint the screen to echo the update to the label/text box so
do that:
after the line
Forms![frm:Maintanence].[lblRecCount].Caption = x & " records processed"
add
me.repaint

HTH Paolo

I need to have a label/text box that updates as my code loops through the
parsing/impoting of a text files. I want the label/text box to show which

[quoted text clipped - 88 lines]

TIA

--
James B Gaylord
From the Wolf Comes the Strength of the Pack
From the Pack Comes the Strength of the Wolf
- R Kipling

Message posted via AccessMonster.com