Access Variable to use for input box (1 Viewer)

[gcarpenter]

I have a function that capture a report activity ,open, close, updated by query, etc.... I have a report that has a inputbox on the open event that tell the user this date the report will display is from a previous update and ask if they want to run the query to update the data.

What I was hoping to do, is have this inputbox only fire if the date and time written when the last update occurs was 24 hours ago.

Can I capture the date and time that the function captures and writes to a table as a variable and you that date and time to detemine whether that inputbox fires?

 

[Ranman256]

if you have a form open and the date in a field box...

if datediff("h", txtDate,now()) >23 then
vAns = inputbox("set new date","Enter Date")
'post vANS to the table

endif

 

[gcarpenter]

I need to transfer the variable so the open event on the report will compare the date the function wrote into a table tblMontrealDoc "OpenDateTime" field and evaluate if this date is over 24 hours old. I don't believe this can be done with the expression builder and I need to write it into my vba code.

 

[Ranman256]

you dont need an exprerssion....put it in the query...calc the hours
then compare the 2 query fields.

 

[gcarpenter]

I'm confused now. I will try to explain best I can.
I have a report, when that report opens, an inputbox ask if the user if they want to update the data that supplies the report, if user answers yes, a delete query erases the data from the table that feeds the report, and then runs an append query to put new data in the table that feed the report.
When this report or, or if the user chooses to update the data, either one will write a date and time to a table. This is done with a function.
What I was hoping to do, is when the user opens the report the open event will compare the last date writing to the table by the function, and see if it was less than 24 hours ago. If that date and time was less than 24 hours ago, it will not fire the code in the open event procedure.
Hope this helps.

 

[arnelgp]

Private Sub Report_Open(Cancel As Integer)

    Dim lngHr As Long
    
    'Calculate how many hours have elapsed since last updated the table
    lngHr = DateDiff("h", Date, DLookup("OpenDateTime", "tblMontrealDoc"))
    
    If lngHr < 24 Then
        ' less than 24 hours then cancel this report
        MsgBox "The table was updated " & lngHr & " ago. Report will not run"
      ' cancel our report
        Cancel = True
    Else
        ' your input box here
        '
        ' the rest of your code here...
    End If
End Sub

 

[gcarpenter]

I get error 91 on this section of your code.
MsgBox "The table was updated " & lngHr & " ago. Report will not run"

Object Variable or With Block not set

 

[gcarpenter]

This is my code.

Private Sub Report_Open(Cancel As Integer)
Dim AckTime As Integer, MsgBox As Object, InFoBox As Object, pword As String, lngHr As Long
Set InFoBox = CreateObject("WScript.Shell")

AckTime = 2
Me.Auto_Header0.caption = "Montreal Unfiltered Shipments"

'Calculate how many hours have elapsed since last updated the table
lngHr = DateDiff("h", DLookup("OpenDateTime", "tblMontrealDoc"), Now())

If lngHr > 24 Then
' less than 24 hours then cancel this report
MsgBox "The table was updated " & lngHr & " ago. Report will not update"
' cancel our report
'Cancel = True
Else

pword = InputBox("The workbook you are opening ""Montreal Rail Shipments"" contains data from previous update." _
& vbCrLf & vbCrLf & "Type ""y"" to update to new data before the report opens, type ""n"" below or click ""Cancel"" to view current data, data can be updated with the update button later.", "Data that will be displayed!")
If pword = ("y") Then GoTo Code:

If pword <> ("y") Then GoTo WrongPass:

'MsgBox "Just click the ""RED"" update button to update the data on this report", AckTime, "Updating Data on this report"

Code:
DoCmd.SetWarnings False
Application.Echo False
Dim strQueryName As String
Dim sngStart As Single, sngEnd As Single
Dim sngElapsed As Single
sngStart = Timer ' Get start time.
DoCmd.OpenQuery "App_MontrealShipmentCriteria", acViewNormal, acEdit ' Run query.
sngEnd = Timer ' Get end time.
sngElapsed = Format(sngEnd - sngStart, "Fixed") ' Elapsed time.


' MsgBox ("The update for " & "'Montreal Shipment Data'" & " took " & sngElapsed _
' & " seconds to update."), vbInformation + vbOKOnly, "AlphaPet Shipment Data Update"
' DoCmd.OpenReport "MontrealShipmentCriteria", acViewReport

DoCmd.Requery
Call LogMontrealOpen(Me)

DoCmd.SetWarnings True
Me.QueryTimer.SetFocus
Application.Echo True
WrongPass:
Exit Sub
End If
End Sub