Module update from droplist? (1 Viewer)

[hammerite]

Hi,
I have created a partner database for project claims use, and I am having a little difficulty with one element of it.

Basically, I have a module which has a global constant value which needs to change on a monthly basis.

My question is "Is there any way I can design a form where a drop list amends this global value?"

I wanted to lock the database, which I have managed to do, but am stumpted on an ideal way in which I can protect the module.

The module is designed to export data into a text file and by giving accesss to users it is prone to parts of the script being deleted.

At the moment, I have a macro which opens the module from the switchboard and partners can amend the value manually, but this doesn't seem ideal.

Thanks in advance,

 

[DCrake]

You can protect you code by placing a password on it by the way.

Back to your issue.

Try this

1. Create a text file in you application path or some other known location that is visible to the application. In this file enter the variable you want to revise/update.

2.On the load of the application get it to read the contents of the file and pass this value to your global variable.

So if you want to change the variable you simply open up the txt file in worpad or notepad change the value and save the file. Thi smeans there is no need to let anyone near your code.

Public Function GetGlobalVariable()

Dim ff as Long
Dim strVariable As String
ff = FreeFile

If Dir("Z:\Path\FileName.Txt") <> "" Then
   Open "Z:\Path\FileName.Txt" For Input As #ff
       Line Input #ff, strVariable
   Close #ff

   strGlobalVariableName = strVariable
End If

End Function

Place this function in a stand alone module. Not named the same as the function.

Then on the OnLoad of the startup screen of your app

Call GetGlobalVariable

This will got to the designated file - if it exists - and reads the contents of the first line and pass the contetns to a variable (strVariable) which inturn will pass it to your global variable. It then closes the file, and that's it.

For Example
If your global variable was a month, say "March" and you now wanted to change it to "April" simply edit the file and change the month name, save the file, and open the app.

 

[hammerite]

Hi David,
Thanks for your speedy reply.

I tried your suggestion and cannot seem to get it to work.
I am not sure I did was correct so I list below:

1. Created text file called Global_Const.txt and saved text of variable as:
Global Const RETURN_TYPE_ESF = "ER04"

2. Saved your public function in module called modGetGlobalVariable
[I changed the pathnames to match that of the text file].

3. Saved OnLoad of frmExport call getGlobalVariable.

4. Ran the form and checked the module for changes.

Would the above identify Global Const RETURN_TYPE_ESF = "ER02" which is saved in my module called OutputILR?

Thanks once again,
Steve

 

[boblarson]

or just use a table for storing the value. Then you can use a form to change the value. That's what we do here with a "preferences table." We have several rows which have the preference name and the value and then we can just change the value in the table and it is done.

 

[hammerite]

Hi Bob,
Thanks for your suggestion, excuse my ignorance but my vba coding isn't the best.
How do I go about linking the variable field to the module?
Thanks,
Steve

 

[hammerite]

Hi guys,
I managed to get a work around for this where rather than setting a global constant for RETURN_TYPE_ESF I set it to lookup a field from the export form, which is a drop list.
Regards,
Steve