Saving a variable from one time to the next 4

[cssilver]

Thanks to everyone for all your help so far!
Another question:
How do I save a value even if the user exits the application & have it available the next time he runs the application? In the old dBase languages, there was something called a "memory variable". Is there something similar in Access VBA? If not, how do I do it?

 

[christheprogrammer]

Just create a table (maybe hiddeen), with a field for each variable you want to save, update it often and things should be fine. I am not sure how to save it when the user clicks the X on the top right or Exit from the file menu... Let me know if you find out I'd be very interested!
Hope I lead you in the right direction
Cheers
Chris

 

[jimmythegeek]

Use .ini Files. This will be lengthy, but works great, I do it all the time:

In my example I will retreive a tax rate for sake of simplicity, but you can use any value:

First, I open notepad, and add my Section, Key and value:

Example:
[Section]
Key=Value

So for me, it would be:

[Data]
Taxrate=.082
Markuprate=.55

Note: You can have as many sections, keys and values in an .ini as you like. Also, I named my first section "Data", you could name it anything you want

Now I am going to save the file as dbData.ini (make sure you add the .ini when saving)

Now Copy everything between the lines below into a new module: Trust me, it will work.

==============================
Public Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long
Public Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpString As Any, ByVal lpFileName As String) As Long

Public Function GetINI(sINIFile As String, sSection As String, sKey As String, sDefault As String) As String
'Purpose: Returns a value FROM an INI File
'GetINI(Path of INI File, Name of section, Name of Key, Default value if not found)
'Example: GetINI("C:\WINNT\ACROREAD.ini", "AdobeViewer", "MaxApp", "0&quot

Dim sTemp As String * 256
Dim nLength As Integer

sTemp = Space$(256)
nLength = GetPrivateProfileString(sSection, sKey, sDefault, sTemp, 255, sINIFile)
GetINI = Left$(sTemp, nLength)
End Function
Public Sub WriteINI(sINIFile As String, sSection As String, sKey As String, sValue As String)
'Purpose: Writes value TO an INI File
'GetINI(Path of INI File, Name of Section, Name of Key, Value)
'Example: WriteINI("C:\WINNT\ACROREAD.ini", "AdobeViewer", "AntialiasThreshold", "25&quot

Dim iCounter As Integer
Dim sTemp As String

sTemp = sValue

'Replace any CR/LF characters with spaces
For iCounter = 1 To Len(sValue)
If Mid$(sValue, iCounter, 1) = vbCr Or Mid$(sValue, iCounter, 1) = vbLf Then Mid$(sValue, iCounter) = " "
Next iCounter

iCounter = WritePrivateProfileString(sSection, sKey, sTemp, sINIFile)
End Sub
==============================

OK, now to retrieve the value you use the GetINI function. You can just retrieve the value, or set a variable equal to it, or even set a public variable equal to it on open of your application, then use it throughout, it's up to you.

Dim myVar
myvar = GetINI("C:\My Documents\dbData.ini", "Data", "Taxrate", "0&quot
The "0" is the value if not found

To write a value out, us the WriteINI function:
WriteINI("C:\My Documents\dbData.ini", "Data", "Taxrate", "YourValue&quot

This is assuming your dbData.ini file is located in your "My Documents" folder. You could use constants here for paths, as well as the db.Name function stripping off the database name to retrieve the path of the database.

One advantage to this, is that if values change, you can go edit the .ini file, and you NEVER have to touch the database.

 

[cssilver]

Thanks - I think in my case creating a table with the variable would be most appropriate.

But how do I open that table (let's say it's called "StoredVariables&quot & then refer to a field in that table? I used the OpenTable method of DoCmd, but when I tried to refer to a field in a Select Case:

Select Case StoredVariables!fieldname

Case 0 ...

I get an error that it can't find StoredVariables.

Once I fix that problem, I also need to know:

1)How do I set the field in the table to a value? Do I just say:
StoredVariables!fieldname = value

2) Do I specifically need to close the table?

3) I don't want to open the table in any particular mode (datasheet, etc) How do I do this?

4)(this one is just theoretical) If I have a form that is based on a table & I open another table, will it close the first one? Do I need to "focus" on the table that I'd like to refer to?

Thanks!

 

[RoyVidar]

Hi!

You'll need to open it another way, more like this:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("StoredVariables&quot
Select Case rst!fieldname
Case 0 ...

In addition you should probably add some error trapping, like "If not rst.BOF then..." to ensure that it acutally are some values in the recordset.

To add a new value, this would work

rst!fieldname = "NewValue"
rst.update

It is a good practice to close the tables, and in addition, setting the object-variable to point to nothing. This will reduce the amount of memory used by your app;

rst.close
set rst = Nothing

3 - this way of opening the table, does it all behind the screen, so table-mode does not apply, only your programming.

4 - if you by this mean changing the recordsource of the form, you might do this "on the fly" by the code;

Me.recordsource = "newQuery"
I'm not sure wether this closes the original recordsource.

Good luck, Roy-Vidar

 

[jimmythegeek]

Using DAO as Roy has pointed out above, will allow you to open, retrieve, and write data to and from tables, completely behind the scenes. It will not affect any form you have open (unless you want it to), and will happen extremely fast.

Let's say your table is called StoredVariables, and the field in the table is call VarValue.
Let's say you have a form called frmTest, with a field on it called txtTest
Now, on Open of the form, the following code will do various things with the data, and fields:

==========
Private Sub Form_Open()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String

strSQL = "Select * From StoredVariables"

Set db = Currentdb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

With rs
If .RecordCount > 0 Then
Me.txtTest = !VarValue ''' Sets the field on the form = the field in table
!VarValue = Me.txtTest ''' Sets the field in the table = to the field on the form
End If
.Close
End With

db.Close
End Sub

 

[christheprogrammer]

I Concur. Jim's code will allow you to open a recordset on any table, local or linked. If you only care about local tables, the (easier) way to open a recordset is:

set rst = currentdb.openrecordset("Table_Name&quot
rst.movefirst
while not rst.eof
'use rst!Variable_Name
'and rst!Variable_Value here, or whatever the field names are.
rst.movenext
loop

hope this helps
Cheers
Chris [sig][/sig]

 

[Mikeauz]

jimmythegeek,

Love the .ini file code, I can retrieve values from the .ini file but I get syntax error when I use:

WriteINI("C:\MyDocuments\dbData.ini", "Data", "Taxrate", "YourValue")

is there any extra code I need to add or should this line simply insert the value?

Thanks for any Help