Solved

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

Posted on 2012-03-14
11
505 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 119

Expert Comment

by:Rey Obrero
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
 
LVL 119

Accepted Solution

by:
Rey Obrero 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 119

Expert Comment

by:Rey Obrero
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now