How to initialize a variable with the value used in last exit of the VBA application

[subedimite]

I have googled the functionality to use a Browse function to Look- in folders and then select required file from the folder.

However when I close the data base and open again, I would like VBA to remember the path name, so that I don’t have to Browse the folder and look for the same files again.

I suppose, some how I need to initialize a global variable with the value how it exited the database previously. Does this sound right?

What is the best way to do it?

[Jim Doherty]

@subedimite
There are various options open to you Global variables that are not constant is not one of them because they lose their scope once the database is closed or if you encounter an untrapped error during program flow.

The Easiest technique here, is this to create a one row table and call it UsystblConfigLocal (table names prefixed with 'Usys' automatically inherit the hidden atribute) that you can use to populate with all sorts of information and simply look your specific item up using the DLookup function at an appropriate point in your program ie: shutdown, when database first opens and so on.

Then there are more advanced methods ie database 'properties' that you can consider where you might create a property and append it to the properties collection at runtime.

Another simple little option you might want to consider is creating an 'external' file that sits in the same folder as your database something that the database can refer to on open or on close or at whatever other point in time you wish. This type of external file creation technique, is often used as a yardstick to measure against, if you understand me ie: if the file exists on the hard drive, do this, that or the other so to speak.

The following two separate functions will create and read the contents from an external file and return the value of that content to your calling function. Like I say.....there are many ways to do this... this is but one technique.

The PutFilePath function creates the dat file in the same folder as your database and relies on a full filepath and filename being passed to it (which of course is what you would be doing with the File dialog functionality when you 'click the button' so to speak.

The GetFileContents Function simply reads this small file (which gets replaced incidentally) each time you create and returns the content value to the calling procedure

Function PutFilePath(FilePath As String)
    datpath = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
    Open datpath & "filepath.dat" For Output As #1
        Print #1, FilePath & ",";
    Close #1
End Function
Function GetFileContents() As String
    Dim f As Integer
    datpath = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
    datpath = datpath & "filepath.dat"
    If Dir(datpath) <> "" Then
        f = FreeFile
        Open datpath For Binary Access Read As #f
        mycontents = Input(LOF(f), f)
        Close #f
    End If
End Function

Nothing I have said is intended to show you the best way, as that is always a subjective test, it is merely intended as a guider to certain options available to you. Ultimately you decide what best suits your purpose :)

Regards

[patjones]

Hi,

I did something very similar to Jim, except with a text file. I don't know that there is necessarily any advantage to doing it my way, but here it is.

The write part looks like:

Private Sub cmdWrite_Click()
 
Dim fs As FileSystemObject
Dim ts As TextStream
Dim strTextToWrite As String
 
strTextToWrite = Me.txtTextToWrite
 
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("f:\data\Hold Variable.txt", True)
ts.WriteLine (strTextToWrite)
ts.Close
 
End Sub

I just wrote some text from a text box on a form to the file, but you would be assigning whatever you need to the string variable, and of course replacing my path with wherever you want to put the file.

To read it:

Private Sub cmdReadText_Click()
 
Dim fs As FileSystemObject
Dim ts As TextStream
Dim strTextToRead As String
 
Set fs = CreateObject("Scripting.FileSystemObject")
Set ts = fs.OpenTextFile("f:\data\Hold Variable.txt", ForReading)
strTextToRead = ts.ReadLine
ts.Close
 
Debug.Print strTextToRead
 
End Sub

The nice thing is, a text file with a short string in it takes up hardly any space. Hopefully one of our suggestions will work for you.

Pat

[NeoPa]

Store the value in a table in your database.

** Edit **
As these two new posts have appeared since I first loaded the page I'll elaborate.

From what you say, I see no reason for anything more complicated than this. It was Jim's first suggestion. Other suggestions are always useful to see of course, and can prove useful in various situations. From what you tell us of yours though, this would appear to be the simplest, both conceptually and implementationally, and is fully adequate to the task.

Ultimately of course, what you decide is your own choice.

[subedimite]

Thanks All.

Great
Hmmnnn... Let me read all this out and find out the best way. I do appriciate your Help.

Cheers

[subedimite]

For What I am doing. I found it easier to create table( may be call it tblConfigurations, that will be written at exit of the data base and then read out next time when opening) as Jim suggested.



I need to take the data base in differnt Machines and if I created a new text file, I need to remember more set up files I need for the migration of the database.

Creating text files is also helpful for me for other applications as I am a Newbie, any new code is something valuable:)

Cheers All

[NeoPa]

That describes clearly why the table option is probably the most apt for you, but as you say, other techniques are always good to add to your repertoire.