Preventing code from running more than once per year

  • Thread starter Thread starter google3luo359
  • Start date Start date
G

google3luo359

Would someone be able to tell me what the simplest way to achieve the
following?

Some VB code needs to be run just once a year (the user clicks a
command button).
I have this code working properly now.
I'd like to have the db prevent this code from running more than once
per year, should the user inadvertantly click on the command button
again.

TIA Ric
 
Would someone be able to tell me what the simplest way to achieve the
following?

Some VB code needs to be run just once a year (the user clicks a
command button).
I have this code working properly now.
I'd like to have the db prevent this code from running more than once
per year, should the user inadvertantly click on the command button
again.

TIA Ric
Click to expand...

Hi

I have a running database that needs to be updated with certain stuff once
(and only once) a year.
In a table I have a value called CurrentYear. Each time the database is
opened (AutoExec) the CurrentYear value is compared to Year(Now()). If they
match, do nothing. If Year(Now())>CurrentYear then prompt user that "this is
first launch this year, do you want to do stuff?" If the user selects "Yes,
go on" then CurrentYear is updated to Year(Now()).

Hth

Rgds
Gunnar Nygaard
 
Gunnar said:
I have a running database that needs to be updated with certain stuff once
(and only once) a year.
In a table I have a value called CurrentYear. Each time the database is
opened (AutoExec) the CurrentYear value is compared to Year(Now()). If they
match, do nothing. If Year(Now())>CurrentYear then prompt user that "this is
first launch this year, do you want to do stuff?" If the user selects "Yes,
go on" then CurrentYear is updated to Year(Now()).
Click to expand...


Thanks Gunnar, that is a workable solution.
I was wondering if it was possible to come up with something that
didn't involve creating a new table. Probably not, as the current
condition needs to be stored somewhere.

Ric
 
Food for thought on future development.

I have gotten to the point where I have created a control table in all
my applications. It is defined

tblControl
ID
tblControlID
tblControlValue

In that table I store such things as the output directory path that is
used for ALL exports
maybe ControlYear
maybe that special path or email address or whatever that is used for
that ONE special report to that one special person.
the directory path for spcial input/imports

That way if I suddenly need just something new that could be used
anywhere within the system I have one place to go and a dlookup can
find it quickly.

If application is moved, I can change the report directory and
everything runs find without changing any code in any module.
It has simplified moving the location significantly and I can add
another one at the drop of a hat.

Something to think about for future development.

Ron
 
Ron2006 said:
Food for thought on future development.
....
tblControl
ID
tblControlID
tblControlValue

In that table I store such things as the output directory path that is
used for ALL exports
maybe ControlYear
Click to expand...


Ron thanks very much, that is an excellent suggestion. I may even use
it for this db.
I just posted a question about avoiding disaster with my app, and this
could play a part in that prevention.

You mentioned having all paths included in the table and not needing to
change any other code. How about the paths that are included in VB code
that refer to Import/Export Specs. I have one such mention in my db.
Would that be changed to a lookup in the Control table?

Also could you give some examples of your VB code that uses the Lookup
function
to pick out say, an import directory path?

TIA Ric
 
I have the following query defined

SELECT TimeAnalysisControlTable.ControlInfo,
TimeAnalysisControlTable.ControlID
FROM TimeAnalysisControlTable
WHERE (((TimeAnalysisControlTable.ControlID)="ReportDirectory"));

and it is called "Query - Get Report Directory"
And one that is similar that gets the master directory.
========================================

In my vba module I have a createanalysis function: it deletes the
output report if it happens to have already been run so that it can be
replaced by a more recent version. I then copy a master that has some
special formating etc. and then export into that copy.

function CreateAnalysis()

MasterDir = DLookup("[ControlInfo]", "Query - Get Masters
Directory")
ReportDir = DLookup("[ControlInfo]", "Query - Get Report
Directory")

ReportFileName = "Time Analysis Report " & Year(EndDate) &
Format(Month(EndDate), "00") & Format(Day(EndDate), "00") & ".xls"


With Application.FileSearch
.NewSearch
.LookIn = ReportDir
.SearchSubFolders = False
.Filename = ReportFileName

If .Execute() = 1 Then
Let match = ""
VBA.FileSystem.Kill ReportDir & ReportFileName
End If
End With

VBA.FileCopy MasterDir & "Time Analysis Ding Master.xls", ReportDir
& ReportFileName

ReportFileName = ReportDir & ReportFileName

DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel9, _
TableName:="Query - All Employee Ding Only", _
Filename:=ReportFileName, _
HasFieldNames:=True, _
Range:="ExportData"

function end
===========================================
That is the general gist of the function. I does other things but that
is how I use the dlookup and the control file.

I started out a long time ago having a single record file with many
fields, but whenever I had to add another field it was a bear. Then I
saw a reference to this type of structure in in one of the groups and
have converted over to using it.

Hope this gives you some ideas.

The same approach could be used almost anywhere. I would want to write
out the vba code for an importtxt so I would have better control and
instead of using the macro to do the import/export, have it run the
code that does the import/export. Just like I did with the
transferspreadsheet above.

Ron
 
Hi Ron,

Thanks very much! These are excellent examples.
I'll be studying them closely in the next couple of days.
I really appreciate it.

Ric
 
Back
Top