Pause Code Timer

J

Jessie

Hello.

I have an automated process thatexports three tables as
text then Calls a batch file that merges 3 files and names
it Sales.txt. I then have to pull a control off of a field
in my form and rename that file accordingly. It works
great except sometimes the batch file isn't fast enough
for the code and I will get an error saying the Sales.txt
does not exist. Here's what I have:

With DoCmd

..TransferText
acExportFixed, "EOFExp", "tblEOF", "C:\Alltel\EOF.txt"
..TransferText
acExportFixed, "HeaderExp", "tblHeader", "C:\Alltel\Header.
txt"
..TransferText
acExportFixed, "TransExp", "tblTransfer", "C:\Alltel\Transf
er.txt"
Dim stName As String

stName = "C:\Alltel\Merge.bat"
Call Shell(stName, 1)


Dim txtcontact As Control
Set txtcontact = Forms!CSR_Input![SalesLeadID]
Name "C:\Alltel\sales.txt" As txtcontact & ".TFR"

End With

Where I have the Name command is where it will sometimes
error out saying that Sales.txt does not exist. I am
pretty sure it is a timing thing.

Is there a way to pause the code to make access wait
before trying to rename the file?

Thanks,
Jessie
 
D

Dirk Goldgar

Jessie said:
Hello.

I have an automated process thatexports three tables as
text then Calls a batch file that merges 3 files and names
it Sales.txt. I then have to pull a control off of a field
in my form and rename that file accordingly. It works
great except sometimes the batch file isn't fast enough
for the code and I will get an error saying the Sales.txt
does not exist. Here's what I have:

With DoCmd

.TransferText
acExportFixed, "EOFExp", "tblEOF", "C:\Alltel\EOF.txt"
.TransferText
acExportFixed, "HeaderExp", "tblHeader", "C:\Alltel\Header.
txt"
.TransferText
acExportFixed, "TransExp", "tblTransfer", "C:\Alltel\Transf
er.txt"
Dim stName As String

stName = "C:\Alltel\Merge.bat"
Call Shell(stName, 1)


Dim txtcontact As Control
Set txtcontact = Forms!CSR_Input![SalesLeadID]
Name "C:\Alltel\sales.txt" As txtcontact & ".TFR"

End With

Where I have the Name command is where it will sometimes
error out saying that Sales.txt does not exist. I am
pretty sure it is a timing thing.

Is there a way to pause the code to make access wait
before trying to rename the file?
Click to expand...

Here's a quickie function I just wrote to wait for a file to be created:

'----- start of code -----
Function fncWaitForFile( _
FilePath As String, _
MaxSeconds As Long, _
Optional NonZeroLength As Boolean = True) _
As Boolean

' This function tests for the existence of file <FilePath>,
' waiting up to <MaxSeconds> seconds for the file to be
' created. By default, the function waits until the file
' not only exists, but has a length > 0; however, this
' behavior may be overridden by specifying False for the
' optional argument, <NonZeroLength>.
'
' The function returns True if the file was found within
' the specified time limit; False if not.

Dim dtStarted As Date
Dim blnDone As Boolean

dtStarted = Now()

Do
If Len(Dir(FilePath)) > 0 Then
If NonZeroLength Then
If FileLen(FilePath) > 0 Then
fncWaitForFile = True
blnDone = True
End If
Else
fncWaitForFile = True
blnDone = True
End If
End If

If Not blnDone Then
If DateDiff("s", dtStarted, Now()) >= MaxSeconds Then
blnDone = True
Else
DoEvents
End If
End If

Loop Until blnDone

End Function
'----- end of code -----

So if you paste the function into a standard module, you could modify
your code like this:

' ... preliminay code ...

stName = "C:\Alltel\Merge.bat"
Call Shell(stName, 1)

If Not fncWaitForFile("C:\Alltel\sales.txt", 30) Then
MsgBox "Something went wrong! The file wasn't created."
Else

' ... remainder of code ...

End If
 
J

Jessie

Wow. Thanks a lot.
-----Original Message-----
Hello.

I have an automated process thatexports three tables as
text then Calls a batch file that merges 3 files and names
it Sales.txt. I then have to pull a control off of a field
in my form and rename that file accordingly. It works
great except sometimes the batch file isn't fast enough
for the code and I will get an error saying the Sales.txt
does not exist. Here's what I have:

With DoCmd

.TransferText
acExportFixed, "EOFExp", "tblEOF", "C:\Alltel\EOF.txt"
.TransferText
acExportFixed, "HeaderExp", "tblHeader", "C:\Alltel\Header.
txt"
.TransferText
acExportFixed, "TransExp", "tblTransfer", "C:\Alltel\Transf
er.txt"
Dim stName As String

stName = "C:\Alltel\Merge.bat"
Call Shell(stName, 1)


Dim txtcontact As Control
Set txtcontact = Forms!CSR_Input![SalesLeadID]
Name "C:\Alltel\sales.txt" As txtcontact & ".TFR"

End With

Where I have the Name command is where it will sometimes
error out saying that Sales.txt does not exist. I am
pretty sure it is a timing thing.

Is there a way to pause the code to make access wait
before trying to rename the file?
Click to expand...

Here's a quickie function I just wrote to wait for a file to be created:

'----- start of code -----
Function fncWaitForFile( _
FilePath As String, _
MaxSeconds As Long, _
Optional NonZeroLength As Boolean = True) _
As Boolean

' This function tests for the existence of file
Click to expand...
 
Top