Focus Returned To Form Before On_Click Code Completes

[chudlychudson]

I created a new database in Access 2003. I have a form that imports data from a legacy database. This form has a delete button which runs:

 
DoCmd.RunSQL ("DELETE * From VHEADER")

This button prompts to confirm.

There's an import button which runs my import VBA code in the OnClick event. Usually when you run code in OnClick, it runs the code and waits for it to complete before returning focus to the form. But in this case, it's immediately returning focus to the form and is allowing the user to click the buttons again.

I know it's running because I have a

 
Temp = SysCmd(acSysCmdSetStatus, "Bytes To Process: " & myBodyTextFile.BytesLeft)

Which displays the bytes left to process in the status bar. It counts down to zero. While it's running I can press the delete button, and it prompts confirm the delete query while the bytes are counting.

I google searched for "OnClick event returns focus before completing execution" and several variations thereof but did not find this issue.

Is there a setting I might have accidentally changed to cause this behavior. EVERY other form button code I've ever written returns focus AFTER the code has completed when I place it in OnClick.

Thanks,
Richard

[HiTechCoach]

It will help to see all the VBA code in the On Click event. Will you please post the VBA code.

[chudlychudson]

Hi Boyd, Thanks.

The data is in a 1980's era database called "O'Hanlon Database Solution". The data is stored in a 512 byte record fixed length file with no crlf delimiters. I'm using Chuck Grimsby's ReadTextFile class: http://www.mvps.org/access/modules/mdl0057.htm

Here's my OnClick code:

Private Sub Command0_Click()
Dim myBodyTextFile As New clsReadTextFile
Dim myString As String
Dim intError As Integer
Dim curDatabase As DAO.Database
Dim rstHeader As DAO.Recordset
Dim intCtr As Integer
intCtr = 0
If Me.CheckImportLive = True Then
    myBodyTextFile.FileName = "T:\NJ\VHEADER.MS"
Else
    myBodyTextFile.FileName = "T:\COPYOF~1\VHEADER.MS"
End If
myBodyTextFile.FixedWidthLineLength = 512
intError = myBodyTextFile.cfOpenFile
If intError = 0 Then
 
    Set curDatabase = CurrentDb
    Set rstHeader = curDatabase.OpenRecordset("VHEADER")
 
    myBodyTextFile.csGetALine
    Do While Not myBodyTextFile.EndOfFile
        Temp = SysCmd(acSysCmdSetStatus, "Bytes To Process: " & myBodyTextFile.BytesLeft)
 
        intCtr = intCtr + 1
 
        If Left(myBodyTextFile.Text, 1) <> "" Then
            rstHeader.AddNew
            rstHeader("APTS").Value = Mid(myBodyTextFile.Text, 1, 4)
            rstHeader("BLDGAKA").Value = Mid(myBodyTextFile.Text, 5, 30)
            rstHeader("BLDGCITY").Value = Mid(myBodyTextFile.Text, 35, 20)
            rstHeader("BLDGNAME").Value = Mid(myBodyTextFile.Text, 55, 30)
            rstHeader("BLDGNO").Value = Mid(myBodyTextFile.Text, 85, 3)
            rstHeader("BLDGSTRNAM").Value = Mid(myBodyTextFile.Text, 88, 25)
            rstHeader("BLDGSTRNUM").Value = Mid(myBodyTextFile.Text, 113, 10)
            rstHeader("BLDGUSE").Value = Mid(myBodyTextFile.Text, 123, 1)
            rstHeader("BLDGZIP").Value = Mid(myBodyTextFile.Text, 124, 9)
            rstHeader("BLOCK").Value = Mid(myBodyTextFile.Text, 133, 6)
            rstHeader("CARDCODE").Value = Mid(myBodyTextFile.Text, 139, 1)
            rstHeader("COMUCODE").Value = Mid(myBodyTextFile.Text, 140, 4)
            rstHeader("CONDITION").Value = Mid(myBodyTextFile.Text, 144, 1)
            rstHeader("CONTPHONE").Value = Mid(myBodyTextFile.Text, 145, 12)
            rstHeader("ENDDATE").Value = Mid(myBodyTextFile.Text, 157, 8)
            rstHeader("ENDTIME").Value = Mid(myBodyTextFile.Text, 165, 6)
            rstHeader("INSPCODE").Value = Mid(myBodyTextFile.Text, 171, 2)
            rstHeader("INSPCOMP").Value = Mid(myBodyTextFile.Text, 173, 1)
            rstHeader("INSPNAME").Value = Mid(myBodyTextFile.Text, 174, 30)
            rstHeader("KY1INSPINT").Value = Mid(myBodyTextFile.Text, 204, 3)
            rstHeader("KY2BEGDATE").Value = Mid(myBodyTextFile.Text, 207, 8)
            rstHeader("KY3BEGTIME").Value = Mid(myBodyTextFile.Text, 215, 8)
            rstHeader("LOT").Value = Mid(myBodyTextFile.Text, 223, 6)
            rstHeader("NEWREGNO").Value = Mid(myBodyTextFile.Text, 229, 14)
            rstHeader("OLDREGNO").Value = Mid(myBodyTextFile.Text, 243, 6)
            rstHeader("OWNERADD").Value = Mid(myBodyTextFile.Text, 249, 35)
            rstHeader("OWNERCITY").Value = Mid(myBodyTextFile.Text, 284, 20)
            rstHeader("OWNERNAME").Value = Mid(myBodyTextFile.Text, 304, 60)
            rstHeader("OWNERPHONE").Value = Mid(myBodyTextFile.Text, 364, 12)
            rstHeader("OWNERSTATE").Value = Mid(myBodyTextFile.Text, 376, 2)
            rstHeader("OWNERZIP").Value = Mid(myBodyTextFile.Text, 378, 9)
            rstHeader("PROJCODE").Value = Mid(myBodyTextFile.Text, 387, 6)
            rstHeader("PROJCOMP").Value = Mid(myBodyTextFile.Text, 393, 1)
            rstHeader("PROJLEADER").Value = Mid(myBodyTextFile.Text, 394, 1)
            rstHeader("PROMPTQUES").Value = Mid(myBodyTextFile.Text, 395, 10)
            rstHeader("RECNUM") = intCtr
            rstHeader("STORIES").Value = Mid(myBodyTextFile.Text, 405, 2)
            rstHeader("SUPERINIT").Value = Mid(myBodyTextFile.Text, 407, 3)
            rstHeader("TOTALBLDG").Value = Mid(myBodyTextFile.Text, 410, 3)
            rstHeader("TOTALUNITS").Value = Mid(myBodyTextFile.Text, 413, 4)
            rstHeader("TYPECONST").Value = Mid(myBodyTextFile.Text, 417, 1)
            rstHeader("UNITCOUNT").Value = Mid(myBodyTextFile.Text, 418, 4)
            rstHeader("UNITS").Value = Mid(myBodyTextFile.Text, 422, 4)
            rstHeader.Update
        End If
 
        myBodyTextFile.csGetALine
    Loop
 
    myBodyTextFile.cfCloseFile
    rstHeader.Close
    Set rstHeader = Nothing
    Set curDatabase = Nothing
 
    MsgBox "DONE"
    Temp = SysCmd(acSysCmdClearStatus)
 
Else
    MsgBox "ERROR"
 
End If
End Sub

I tried this on a Windows 7/Access 2010 machine and it exhibits the same behavior.

Thanks for looking,
Richard

[HiTechCoach]

I have been reading and writing to text files with VBA starting back with Access 2.0. I have not ever had the issue you are describing when processing text files. I also do not use a Class to handle the file processing. IMHO. the code to process a text file is just to simple to not include in-line.

My suspicion is that it has something to do with the Class you are using to read the file. The never versions of Access might handle the Class and threading different than when that code was written and tested.

Have you previously used this Class successfully?

Have you tried it with Access 2000 or 97?

[chudlychudson]

Thanks for your input.

All of the text files I've worked with before, I used an import specification as linked tables. That didn't work with this data since it doesn't have crlf delimiters. The class looked promising since it has a fixed length record option.

I rewrote the code using:

Open "T:\NJ\VHEADER.MS" For Binary As intFile Len=512

and

myString = Input(512, intFile)

and it now behaves as expected, hourglassing on the form while the counter counts down.

[vitols]

I created a new database in Access 2003. I have a form that imports data from a legacy database. This form has a delete button which runs:

 
DoCmd.RunSQL ("DELETE * From VHEADER")

This button prompts to confirm.

There's an import button which runs my import VBA code in the OnClick event. Usually when you run code in OnClick, it runs the code and waits for it to complete before returning focus to the form. But in this case, it's immediately returning focus to the form and is allowing the user to click the buttons again.

I know it's running because I have a

 
Temp = SysCmd(acSysCmdSetStatus, "Bytes To Process: " & myBodyTextFile.BytesLeft)

Which displays the bytes left to process in the status bar. It counts down to zero. While it's running I can press the delete button, and it prompts confirm the delete query while the bytes are counting.

I google searched for "OnClick event returns focus before completing execution" and several variations thereof but did not find this issue.

Is there a setting I might have accidentally changed to cause this behavior. EVERY other form button code I've ever written returns focus AFTER the code has completed when I place it in OnClick.

Thanks,
Richard

Hi

What about disabling the button untill completion of the delete ?

How you got a DoEvents anywhere that might get executed ?

Bye
Vitols

[HiTechCoach]

Thanks for your input.

All of the text files I've worked with before, I used an import specification as linked tables. That didn't work with this data since it doesn't have crlf delimiters. The class looked promising since it has a fixed length record option.

I rewrote the code using:

Open "T:\NJ\VHEADER.MS" For Binary As intFile Len=512

and

myString = Input(512, intFile)

and it now behaves as expected, hourglassing on the form while the counter counts down.

Thanks for the udpate.

I was thinking it had to do with the Class.

Glad to hear that you got it working.