Solved

MS-Access query to only run once a day when first user opens.

Posted on 2012-03-14
11
518 Views
Last Modified: 2012-06-27
I have an MS- Access database that needs to run a query whenever the user opens it for the first time in the day and only once a day. Is there code that I can run that will do this?
Thanks.
0
Comment
Question by:donnie91910
  • 6
  • 3
  • 2
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37722468
one way to do this, which i have done in one of my application is to create a table with a Date/time field.

when the db is open, check if there is value in the Date field equal to the current date,
if no date found, run the query, then insert or update the date field with the current date.

got the idea?
0
 

Author Comment

by:donnie91910
ID: 37723493
I created a table called DateCheck with a field called ForDateCheck as a DateTime datatype.

So here is the code that I have written.  Please feel free to make any changes that you feel are syntacticly incorrect. Thanks.

Public Sub Check_Date()
If [DateCheck]![ForDateCheck] <> Now() Then
DoCmd.RunQuery "Qry_RunTestData"
Else
DoCmd.RunQuery "Qry_UpdateCheck_DateWithTodaysDate"
End If
End Sub
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 250 total points
ID: 37723564
Private Sub Form_Open(Cancel As Integer)
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("DateCheck ")
    If rs("theDate") = Date Then
        Exit Sub
    Else
        rs.Edit
        rs("theDate") = Date   'theDate format is short Date such as dd/mm/yyyy
        ' now changes evey tick., you need to check per day
        rs.Update
        DoCmd.OpenQuery ("Qry_UpdateCheck_DateWithTodaysDate")
    End If
End Sub
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 37724379
test this codes


Public Sub Check_Date()
If Nz(DLookup("ForDateCheck", "DateCheck")) <> Date Then
DoCmd.RunQuery "Qry_RunTestData"
    If DCount("*", "DateCheck") = 0 Then  'check if there is a date entry in the table
    ' add an entry if table is empty
    CurrentDb.Execute "Insert into DateCheck(ForDateCheck) values (#" & Date & "#)"
    Else
    'update the value of the date field to the current date
    CurrentDb.Execute "Update DateCheck set ForDateCheck=#" & Date & "#"
    End If
Else
DoCmd.RunQuery "Qry_UpdateCheck_DateWithTodaysDate"
   
    'update the value of the date field to the current date
    CurrentDb.Execute "Update DateCheck set ForDateCheck=#" & Date & "#"

End If
End Sub
0
 

Author Comment

by:donnie91910
ID: 37727316
Can I run this code from a Macro in Access or do I need to change the code to a function.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37727350
* you can call the sub  in the open event of the form
* you can place the codes in the open event of the form
* you can change the Sub to a Function and run the Function from a macro

take your pick
0
 

Author Comment

by:donnie91910
ID: 37727747
I changed the code to a Function.  When I run the function from a Macro  I get the following Error:
RunTime error '2001':
You Cancelled the Previous Operation
    End         Debug

I choose Debug
and the first line of code is highlighted in yellow:

If Nz(DLookup("ForDateCheck", "DateCheck")) <> Date Then


By the way what is "Nz"?  Thanks.
0
 

Author Comment

by:donnie91910
ID: 37731859
Any ideas why this is happening?
0
 

Author Comment

by:donnie91910
ID: 37735572
Waiting for reply.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 37738669
Ceck this database implementing my comment.
Code can be used in form open event or in a butto click event.
runQueryOncePerDay.accdb
0
 

Author Closing Comment

by:donnie91910
ID: 37863055
Thanks.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question