Hi All,
Fairly complicated problem that I have been stuck on for a while. I have the following procedure that imports an XML file from a PDF form to a new record in table "ApplicationInbox" and then moves the same XML file to an archive folder along with any other file located in the same temp folder. The folder is created with the format "dmmyy-hhmmss-AM/PM".
When the new record is created it also has a unique ref of dmmyy-hhmmss-AM/PM in the field ArchiveID. I need this because I want the user to be able to click a button to load the archive folder for a particular record.
The problem is that the two ref formatted 'dmmyy-hhmmss-AM/PM' don't always match and can sometimes be up to 2-3 seconds out.
Is there any way of transferring the 'fnow' to the ArchiveID field or can anyone think of a better way of achieving the same outcome.
The database is Access 2007 with a back and front end seperate.
Code:
Private Sub ImportApp_Click()
On Error GoTo ErrorHandle
Dim fso
Dim fco
Dim sfol As String, dfol As String, fnow As String
Set fco = CreateObject("Scripting.FileSystemObject")
fnow = (Format(Now(), "ddmmyy-hhmmss-AM/PM"))
Application.ImportXML "C:\Sample\Sample.xml", acAppendData
sfol = "C:\TempFolder"
dfol = "C:\Archive\" & fnow & ""
If Not fco.FolderExists(dfol) Then
fco.CreateFolder (dfol)
Set fso = CreateObject("Scripting.FileSystemObject")
Name "C:\TempFolder\Sample.xml" As (dfol)
If Not fso.FolderExists(sfol) Then
MsgBox sfol & " is not a valid folder/path.", vbInformation, "Invalid Source"
ElseIf Not fso.FolderExists(dfol) Then
MsgBox dfol & " is not a valid folder/path.", vbInformation, "Invalid Destination"
Else
On Error Resume Next
fso.MoveFile (sfol & "\*.pdf"), dfol
fso.MoveFile (sfol & "\*.mht"), dfol
fso.MoveFile (sfol & "\*.doc"), dfol
fso.MoveFile (sfol & "\*.jpg"), dfol
fso.MoveFile (sfol & "\*.tiff"), dfol
End If
Exit_ImportApp_Click:
Exit Sub
ErrorHandle:
Select Case Err.Number
Case 31527
MsgBox "No Application Found"
Case Else
Err.Clear
End Select
End If
End Sub