Selecting Multiple Files

[Sash]

I'm being a pain today, but trying to address all the issues with my
database.

I use the following to allow my customer to select and load multiple files.
My issue is that if they click yes and then get to the selection window and
click cancel, the program gives me an error message and stops. How could I
put something in that indicates if cancel is hit, leave the loop.



'***** ABC Company *****
If Me!Frame50.Value = 9 Then
strClient = "ABCCo"
strClientNum = "170"
'Drop All Records from Current Table
strSQLDrop = "DELETE * from ABCCo"
DoCmd.RunSQL strSQLDrop

'GetFile
Dim vbMsgBoxResultSA As Long
vbMsgBoxResultSA = vbYes

Do While vbMsgBoxResultSA = vbYes
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select Import File"
.Filters.Clear
.Filters.Add "Text", "*.txt"
.Filters.Add "All Files", "*.*"
.FilterIndex = 1
.AllowMultiSelect = False
.InitialFileName = "\u:interfaces/import/"
result = .Show
If (result <> 0) Then
fileName = Trim(.SelectedItems.Item(1))
End If
End With
strOrigFile = Mid(fileName, 22, 30)
'Trim File Header
Call TrimFileHeaderX(fileName, 1)

'Load with Specs
stSpecs = "ABCCo Import"
sttable = "ABCCo"
stfilename = fileName
stQuery1 = "ABCCo_UpdateIN1Name"
stquery2 = "ABCCo_UpdateIN2Name"
stquery3 = "ABCCo_UpdateIN3Name"
DoCmd.TransferText acImportDelim, stSpecs, sttable, stfilename
DoCmd.OpenQuery stQuery1
DoCmd.Close acQuery, stQuery1
DoCmd.OpenQuery stquery2
DoCmd.Close acQuery, stquery2
DoCmd.OpenQuery stquery3
DoCmd.Close acQuery, stquery3


'Ask Another Import if so, GOTO GetFile and loop until user says NO
vbMsgBoxResultSA = MsgBox("Load Another File?", vbYesNo)
'Move File to Archive
FileCopy stfilename, "u:\interfaces\import\archive\ABCCo\" & "_" &
Format(Time, "HHMMSS") & strOrigFile
Kill stfilename
Loop

*****rest of program ******

 

[Klatuu]

What cancel button?
Is is at command button on your form?
If so, what is the code in the Click event?
If it is not a command button on your form? what is it?

 

[Sash]

Sorry. The code below opens a Windows Explorer menu where the user can
select a file. When they click "Okay" they're prompted if they want to open
another file. If they click "Yes" and get to the Windows Explorer menu and
decide there's not another file and click "Cancel" my program doesn't know
what to do. So I wasn't sure if there was a way to say no file was selected,
so exit the loop. Does this make more sense?

 

[Klatuu]

If (result <> 0) Then
fileName = Trim(.SelectedItems.Item(1))
End If
End WithAt this point, if no files were selected or the cancel button was clicked
fileName will be a Null string. In other words, it will = vbNullstring.
Since I only have a snippet of your code, I can't tell you exactly what you
will need to do, but assuming this code is in a Sub and you don't want to do
anything else in the sub, you can use and Exit Sub at this point. If that
is not the case, you will need to construct the logic to do whatever is
necessary. So, it could be as simple as

If strfileName = vbNullString Then
Exit Sub 'or Exit Function if it is a function
End If
strOrigFile = Mid(fileName, 22, 30)
'Trim File Header
Call TrimFileHeaderX(fileName, 1)