Solved

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

Posted on 2012-03-14
11
543 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

752 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