vba and variable lifetime(sorry, it is posted twice) 1

[stroll]

Hi,

I am a new user of vba.
Is there a way of permanently storing varibles in vba?(without using a table)

I would like to create a variable which is updated once a year. Throughout the year, this number would be used in a subtraction process to create another number. At the end of the year, this number would then be updated and used accordingly.

The application using this number would be closed when not in use.

Thanks
Ryan [sig][/sig]

 

[Ruairi]

you could use a constant and change it at the end of the year, or write the number to a text file and load it into a variable each time you start the program. [sig][/sig]

 

[stroll]

Thanks,

I am worried that I will forget to change the constant. I would like the program to automatically update it at the end of the year. So, are there permanent variables?

Stroll

[sig][/sig]

 

[Ruairi]

anytime i want a permanent variable i write it to a text file and load it into a variable when my project opens, you could also use a registry key for the same effect but it requires api programming which gets a little complicated. You said you are worried that you will forget to change the constant, if you could use a permanent variable how would it be getting changed at the end of the year? [sig][/sig]

 

[stroll]

Hi,

I was wondering if there was a way to state the current directory on the machine for file access, instead of c:\foldername\txtfile.txt. The program that I am developing will be used on different machines and will have different directory structures.

Ryan [sig][/sig]

 

[Ruairi]

To use the current directory you just use filenames with no paths, as the current directory is always changing(it is systemwide, not specific to your program) i'm guessing you want to know about the path of your program. in vb it is App.Path (so to use it you would say "Open App.Path & "\test.txt" for output as #1&quot i dont know if that carries over to vba, but try it. [sig][/sig]

 

[stroll]

Hi,

Thanks for all your help so far. It has worked, but we have now decided to use a table to store the constant. How do we access this table so we can assign the value of the constant to a variable in the form?
Thanks again
Ryan [sig][/sig]

 

[MoGryph]

Using a table to store a single string (ie., the Path location) uses a lot of resources.

Another method, which has not been stated in previous responses, is to set a Database Property. I've actually used this with many of my databases, for storing the same thing- the pathname of a specific file.
I created 2 functions for this, the CreatePrp and the UpdatePrp functions.
The CreatePrp will Create a new Custom database property. If the property exists, it will only update the property. Use this if you're not sure if the property exists.
The UpdatePrp function will only update properties that already exist. If you know you've already create the property, then use this one. It's slightly more efficient, since it does not require you to create extra references.

Function CreatePrp(strPrpName As String, strPrpVal As String) As Boolean
CreatePrp = False ' Set initially to false, in case any error happens, _
and the function does not complete
Dim dbs As Database
Dim prp As Property
Set dbs = CurrentDb
Dim doc As Document

On Error GoTo Err_CreatePrp
Const PrpExists = 3367
Set doc = dbs.Containers!Databases.Documents!UserDefined
Set prp = doc.CreateProperty(strPrpName, dbText, strPrpVal)
doc.Properties.Append prp
CreatePrp = True
GoTo Exit_CreatePrp

Err_CreatePrp:
Select Case Err.Number
Case PrpExists
Set prp = dbs.Containers!Databases.Documents!UserDefined.Properties(strPrpName)
prp.Value = strPrpVal
CreatePrp = True
Case Else
MsgBox "Error #: " & Err.Number & " Function: CreatePrp" _
& "@An unknown error has occured!" _
& "@Please take note of the Error #, Function Name, and what you were doing when" _
& vbCrLf & "this error occurred, and contact the program's author.", _
vbOKOnly + vbExclamation, "Error..."
End Select

Exit_CreatePrp:
Set doc = Nothing
Set prp = Nothing
Set dbs = Nothing
End Function

Function UpdatePrp(strPrpName As String, strPrpVal As String) As Boolean
UpdatePrp = False ' Set initially to false, in case any error happens, _
and the function does not complete
Dim dbs As Database
Dim prp As Property
Set dbs = CurrentDb

On Error GoTo Err_UpdatePrp
Const prpNotExist = 3270

Set prp = dbs.Containers!Databases.Documents!UserDefined.Properties(strPrpName)
prp.Value = strPrpVal
UpdatePrp = True
GoTo Exit_UpdatePrp

Err_UpdatePrp:
Select Case Err.Number
Case prpNotExist
MsgBox "Property Does Not Exist" _
& "@Code has been run, that attempts to update a property that does not exist." _
& "@Try replacing the call for ""UpdatePrp"" with a call to ""CreatePrp""." _
& vbCrLf & "If you need assistance, please contact the program's author.", _
vbOKOnly + vbExclamation, "Error..."
Case Else
MsgBox "Error #: " & Err.Number & " Function: UpdatePrp" _
& "@An unknown error has occured!" _
& "@Please take note of the Error #, Function Name, and what you were doing when" _
& vbCrLf & "this error occurred, and contact the program's author.", _
vbOKOnly + vbExclamation, "Error..."
End Select

Exit_UpdatePrp:
Set prp = Nothing
Set dbs = Nothing

End Function

Sub TestCreatePrp()
' Test creating (or updating) a new database property
If CreatePrp("NewPrp", "Test String Value&quot Then
MsgBox "Congratulations! You created/Updated a new Custom Database Property!", vbOKOnly + vbInformation _
, "Congratulations!!!"

End If
End Sub

Sub TestUpdatePrp()
' Test Updating (only) a custom database property
If UpdatePrp("NewPrp", "Test String Value2&quot Then
MsgBox "Congratulations! You have Updated a Custom Database Property!", vbOKOnly + vbInformation _
, "Congratulations!!!"
Else
MsgBox "You have failed. Don't give up- you'll get it!", vbOKOnly + vbExclamation, "Sorry, you failed..."
End If
End Sub