Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-03-14
11
Medium Priority
?
605 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 31

Assisted Solution

by:hnasr
hnasr earned 1000 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 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 31

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

715 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