Run query on specific date (1 Viewer)

[expublish]

Hi,

I have two queries:

1) called 'Update Holiday'
2) called 'Delete Holiday'

One is an append query and the other is a delete query. I need my Database to run these two queries on the last day of each financial year - so on 31st March 2002 (31/03/02).

I do not want them run on any other dates, just once when the database is opened on 31/03/02 (the DB is used daily).

Does anyone know how to make it do this?

Oh - they need to run when I open a specific form. I don't mind using VB but will be just as happy using a macro that runs the two queries and simply runnign that on Ope Form.

TIA

Scott.

 

[Graham T]

Scott

This is a basic example of something that would work although there are other things that would need to be considered. The basic code is as follows:

Private Sub Form_Open(Cancel As Integer)

'Check to see if today's date is 31 March
    If Date = #31/03/2002# Then
'Turn off system warnings
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "Update Holiday"
        DoCmd.OpenQuery "Delete Holiday"
'Turn system warnings back on
        DoCmd.SetWarnings True
    End If
End Sub

You would need to consider:

1. What happens if the date falls on a weekend and the form is not opened?

2. You will need to update the code yearly as the year is hardcoded in this example (thus defeating the object of automatically running this). There are ways to pull out the year only and this can be found here: http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=31503 However I could not get this option working the way I was doing it.

3. If the form is opened more than once on the specified date the queries will run every time, this may obviously cause problems. You would need to buil something in to check if these actions had previously been carried out.

I hope this gives you some ideas.

Graham

 

[expublish]

Thank you for getting me started.

At the moment I only have a basic grasp on VBA so I am not sure where to go from here.

It is obvious the code will need to be more complex.

Another idea is to set the form so a pop box reminds the user that the queries need to be run and then have a button somehwhere that runs the queries.

But I still need to make the popup run on the 31/03 every year. Any ideas?

Scott.

 

[expublish]

Just had a think and was wondering if it would be possible to put this behind a command button - would it work?

Private Sub Command1_Click(Cancel As Integer)

'Check to see if today's date is 31 March
    If Date = #31/03/2002# Then
'Turn off system warnings
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "Update Holiday"
        DoCmd.OpenQuery "Delete Holiday"
'Turn system warnings back on
        DoCmd.SetWarnings True
    End If
End Sub

Private Sub Command1_Click(Cancel As Integer)

'Check to see if today's date is 31 March
    If Date = #31/03/2003# Then
'Turn off system warnings
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "Update Holiday"
        DoCmd.OpenQuery "Delete Holiday"
'Turn system warnings back on
        DoCmd.SetWarnings True
    End If
End Sub

Private Sub Command1_Click(Cancel As Integer)

'Check to see if today's date is 31 March
    If Date = #31/03/2004# Then
'Turn off system warnings
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "Update Holiday"
        DoCmd.OpenQuery "Delete Holiday"
'Turn system warnings back on
        DoCmd.SetWarnings True
    End If
End Sub

Private Sub Command1_Click(Cancel As Integer)

'Check to see if today's date is 31 March
    If Date = #31/03/2005# Then
'Turn off system warnings
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "Update Holiday"
        DoCmd.OpenQuery "Delete Holiday"
'Turn system warnings back on
        DoCmd.SetWarnings True
    End If
End Sub

Basically do it for about 10 years (by which time they would have made a new DB program - more than likely).

I understand the code may have to be compacted quite a bit but don't know how.

What d'ya think?

Scott.

 

[Graham T]

Scott

Revised code that pulls out the 31 March of any year:

Private Sub Form_Open(Cancel As Integer)
Dim dte As Date

'Check to see if today's date is 31 March of any year
    dte = Format(Date, "DD/MM/YYYY")
    If Left(dte, 5) = "31/03" Then
'Turn off system warnings
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "Update Holiday"
        DoCmd.OpenQuery "Delete Holiday"
'Turn system warnings back on
        DoCmd.SetWarnings True
    End If
End Sub

Again you will still need to consider points 1 & 3 of my previous posting.

HTH

Graham

 

[expublish]

Great, thanks.

I have decided to put it behind a command button that is on a menu form.

Now all I need is for message box to pop up on the 31/03 of each year, or the next time the DB is opened after that date.

Any ideas?

Scott.

 

[expublish]

Also ...

What snippet of code can I add to make the button produce a msg box before the update. It can say 'Are you sure?' and have Yes or No options (or OK/Cancel). If yes is clicked it goes ahead if not then it cancels and returns to the form.

Thanks,

Scott

 

[The_Doc_Man]

A different approach, still requiring that a machine is up and running on a particular date, involves use of the Windows scheduler facility. As clunky as it is, you can put a task to be run only on a certain date of the year. The task can be an icon based on Access. You would build a shortcut to Access, rename it, and in the Properties of the shortcut you could include command-line parameters.

If the machine is up on the critical date, you could use your queries to do your updates by putting them behind a MACRO. The macro has to end in such a way as to close Access.

Then (you can search this forum for it) there is a way, using the Access command line with option /X macro-name to open Access, run the macro, and (guided by the last command in the macro) close Access. So obviously, it would be a variant of this command that got loaded to the shortcut's command line.

This would work OK as long as only one machine is the one on which it should be done, and the scheduler / shortcut preparations are on that one machine.

Hope that helps you to know that.