Setting textbox value to the filename of last imported txt file 1

[PROXI]

Hello All,

I have code that I use to import a group of text files into my database. What I am coming across is that I forget what the last file was that I imported since they are moved off to a different server after I have copied them. I would like to set a textbox or label on my main form to display the file name of the last file that I imported. I have been able to pass this value to both of them, but have run into problems. I don't really want to have a table just for this info. I want to just use the lable and set the caption property of it to the file name. The problem is that the label doesn't hold the value I pass it after I close the form. What can I do to fix this.
This is what I have so far:

Private Sub bImportFiles_Click()
On Error GoTo bImportFiles_Click_Err

Call ChangeFilename

Dim strImportFile As String, strTableName As String
Dim strFile As String
Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String
Dim strnew As String

strFileExt = ".txt"
strTableName = "test"
strFolderPath = "C:\Documents and Settings\parkes23\My Documents\Billing Recon\"
strImportFile = Dir(strFolderPath & "\*" & strFileExt)
strFile = strFolderPath & strImportFile
strnew = "\\Ohlewnas0240\psrp\zoo0212\FACT\Accrual-Billing\Completed Daily Files\"

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
    If Right(objF1.Name, 3) = "txt" Then
        strFile = objF1
        DoCmd.TransferText acImportFixed, "TxtImportSpec", strTableName, strFile
        Name strFile As strnew & objF1.Name
    End If
Next

[COLOR=red][b]Me.Label46.Caption = strImportFile[/b][/color red]


Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing

bImportFiles_Click_Exit:
Exit Sub

bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit
End Sub

Thanks,

PROXI

 

[Darrylles]

Proxi,

If this is only required for the life of a session (e.g. it'll be lost when the application is closed), then define a global variable in a module.

This global will hold the value until the application closes.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[PROXI]

That is the thing. I need it to be there the next time that I log into the database. That is why I am trying to set the label caption to the variable. I want to avoid losing it when I log out.

Thanks,

PROXI

 

[Darrylles]

Proxi,

The you MUST store it in a table.

Why worry about creating a table to store a single value? This is done frequently by many programmers.


ATB
Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[PROXI]

Alright... What is the best way to go about doing that. I would need it to just overwrite the data that is in the table each time since I just need the last file name.

Thanks,

PROXI

 

[PROXI]

Alright... I got it to work with a bit of trial and error. The problem that I am running into is that it is pulling the file name of the first file imported and not the last file imported. Here is what I have now:
[cod]
Private Sub bImportFiles_Click()
On Error GoTo bImportFiles_Click_Err

Call ChangeFilename

Dim strImportFile As String, strTableName As String
Dim strFile As String
Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String
Dim strnew As String

strFileExt = ".txt"
strTableName = "test"
strFolderPath = "C:\Documents and Settings\parkes23\My Documents\Billing Recon\"
strImportFile = Dir(strFolderPath & "\*" & strFileExt)
strFile = strFolderPath & strImportFile
strnew = "\\Ohlewnas0240\psrp\zoo0212\FACT\Accrual-Billing\Completed Daily Files\"

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
If Right(objF1.Name, 3) = "txt" Then
strFile = objF1
DoCmd.TransferText acImportFixed, "TxtImportSpec", strTableName, strFile
Name strFile As strnew & objF1.Name
End If
Next

Me![Tblfile_sub].Form![file] = strImportFile
Me.Tblfile_sub.SetFocus
Me.Tblfile_sub.Form!file.SetFocus

Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing

bImportFiles_Click_Exit:
Exit Sub

bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit
End Sub
[/code]

How do I get it to pull in the last file that is looped through?

Thanks,

PROXI

 

[Darrylles]

Proxi,

Copy the following code to a module.
N.B. I've used tblLast_File as the table name and Last_File_Imported as the text field name within that table.

This function either:
1) Stores the filename in the tblLast_File table (if a filename is passed to it) or:
2) if strFileName is NULL - returns the tblLast_File.Last_Imported_File field content back to the caller:
[tt]
Private Function Last_File(strFileName as String) as String
Dim dbs as database
Dim rstLast_File as recordset

Set dbs = CurrentDb
Set rsLast_File = dbs.OpenRecordset("tblLast_File")

If ( Not IsNull(strFileName)) then
rsLast_File.Edit
rsLast_File!Last_File_Imported = strFileName
rsLast_File.Update
Last_File = strFileName
Else
Last_File = rstLast_File!Last_File_Imported
EndIf

End Sub
[/tt]
Call this function immediatly BEFORE you want to store the last filename or immediately BEFORE you want retrieve the last filename.

Store:

strFile_To_Store = Last_File("c:\myfolder\myfile.txt")

Will store "c:\myfolder\myfile.txt" in tblLast_File.Last_Imported_File field.

Retrieve:

strFile_To_Store = Last_File()

will retrieve the content of the tblLast_File.Last_Imported_File field into the strFile_To_Store variable.

(Error checking of course is still required in this).

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[TheAceMan1]

How are ya PROXI . . .

You can set a [blue]custom db property[/blue] for this. These properties are part of the db and are [blue]nonvolatile[/blue]. Your filename will be there [blue]even if you close the db[/blue]. You only have to create the property once, from there you simply set/get the value.

Have a look here thread702-1236022 about 6 posts down . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PROXI]

Alright... I ended up finding a simple way to retrieve the value of the for my textbox using Dlookup() to pull the value into the texbox. I am trying to use an SQL update statement to put the value of the import file into the table I am using. What am I doing wrong here?!?!?!?!?!?

Private Sub bImportFiles_Click()
On Error GoTo bImportFiles_Click_Err

Call ChangeFilename

Dim strImportFile As String, strTableName As String
Dim strFile As String
Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String
Dim strnew As String
Dim strSql As String


strFileExt = ".txt"
strTableName = "test"
strFolderPath = "C:\Documents and Settings\parkes23\My Documents\Billing Recon\"
strImportFile = Dir(strFolderPath & "\*" & strFileExt)
strFile = strFolderPath & strImportFile
strnew = "\\Ohlewnas0240\psrp\zoo0212\FACT\Accrual-Billing\Completed Daily Files\"

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
    If Right(objF1.Name, 3) = "txt" Then
        strFile = objF1
        DoCmd.TransferText acImportFixed, "TxtImportSpec", strTableName, strFile
        Name strFile As strnew & objF1.Name
    End If
Next

[COLOR=red]DoCmd.RunSQL "UPDATE Tblfile SET File = " & strFile & ";"[/color]
'Me.Text52.Value = Right(strFile, 17)


Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing

bImportFiles_Click_Exit:
Exit Sub

bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit
End Sub

Thanks,

PROXI

 

[PROXI]

I figured it out. I always hate when people solve the problem and don't put the code that finally did it for later people to use. Here is how I have completed my problem. I set the control source of my unbound textbox to be:

=DLookUp("File","Tblfile")

And here is my code behind on the form to complete everything else:

Private Sub bImportFiles_Click()
On Error GoTo bImportFiles_Click_Err

Call ChangeFilename

Dim strImportFile As String, strTableName As String
Dim strFile As String
Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String
Dim strnew As String
Dim strSql As String


strFileExt = ".txt"
strTableName = "TblDailyfiles"
strFolderPath = "C:\Documents and Settings\parkes23\My Documents\Billing Recon\"
strImportFile = Dir(strFolderPath & "\*" & strFileExt)
strFile = strFolderPath & strImportFile
strnew = "\\Ohlewnas0240\psrp\zoo0212\FACT\Accrual-Billing\Completed Daily Files\"

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
    If Right(objF1.Name, 3) = "txt" Then
        strFile = objF1
        DoCmd.TransferText acImportFixed, "TxtImportSpec", strTableName, strFile
        Name strFile As strnew & objF1.Name
    End If
Next

DoCmd.RunSQL "UPDATE Tblfile SET File = '" & Right(strFile, 17) & "';"
Me.Text52.Requery


Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing

bImportFiles_Click_Exit:
Exit Sub

bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit
End Sub

Thanks,

PROXI