?
Solved

Time Diff

Posted on 2011-10-30
7
Medium Priority
?
231 Views
Last Modified: 2012-05-12
I have a button with the following code attached,
 
Private Sub btnAdd_Click()
On Error GoTo Err_btnAdd_Click
    txtTimeNow = Now
If txtLastPall < ((DateAdd("n", 0, txtTimeNow)) - 30) Then
    MsgBox "Too soon to have made another Pallet", vbInformation, "Not Possible"
    Exit Sub
End If
    txtLastPall = Time
    txtPallDate = Date
    txtPallTime = Time
    Me.Dirty = False
    DoCmd.GoToRecord , , acNewRec
    PallGraph.Requery
Exit_btnAdd_Click:
    Exit Sub
Err_btnAdd_Click:
    MsgBox Err.Description
    Resume Exit_btnAdd_Click
End Sub

Open in new window

all the text boxes are hidden
txtLastPall is unbound and txtTimeNow is unbound.
In Line 4 I am trying to get the message to display if the button is clicked too soon.
i.e.the button should display the message if less than 30 minutes has elapsed since the last time it was clicked.
I have tried many variations of the DateAdd formula in Line 4 but cannot get it to do what I need.
Please point me in the right direction.

As always, your time is appreciated.
Thanks in advance
0
Comment
Question by:Stephen Byrom
  • 3
  • 3
7 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 37053948
try this  revision
Private Sub btnAdd_Click()
On Error GoTo Err_btnAdd_Click
     txtTimeNow = Now   
If txtLastPall > DateAdd("n", -30, txtTimeNow) Then
    MsgBox "Too soon to have made another Pallet", vbInformation, "Not Possible"
    Exit Sub
End If

    txtLastPall = Now
    txtPallDate = Date
    txtPallTime = Time
    Me.Dirty = False
    DoCmd.GoToRecord , , acNewRec
    PallGraph.Requery
Exit_btnAdd_Click:
    Exit Sub
Err_btnAdd_Click:
    MsgBox Err.Description
    Resume Exit_btnAdd_Click
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 37053955
Thanks Cap,
but it allows me to keep clicking the button and adding records.
I need it to exit the sub if less than 30 min has elapsed.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37053968
close the form and open, then try the operation again.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37053979
take note that this line
       txtLastPall = Now
was also changed
0
 
LVL 1

Author Closing Comment

by:Stephen Byrom
ID: 37053994
Thanks Cap,
I had missed the other change.
I copied/pasted the entire code and it works fine.
Thanks for your time.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37054257
Personally, I would encourage you to use date/time variables rather than text variable to store date and time values.  I would also encourage you to force explicit declaration of all your variables.  You can do this by adding the line:

Option Explicit

right below:

Option Compare Database

at the top of each code module.  You can also do this by setting the Required Variable Declaration checkbox by opening the Options dialog box (open VBA, then click Tools, and Options).

You could also use:

If DateDiff("n", dtLastPall,  dtTimeNow) < 30 Then

instead of

If txtLastPall > DateAdd("n", -30, txtTimeNow) Then
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 37055246
Thanks for the advice Fyed
All the modules in the project are option explicit, someone else told me that some time ago.
I have declared the variables as you suggest, and also changed the line and textbox names..
Thank you very much for the advice.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

850 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