Solved

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

Posted on 2012-03-14
11
496 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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…
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…

707 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

16 Experts available now in Live!

Get 1:1 Help Now