Getting notifications from report

[Ravi Kumar]

I have a query table and report (agency due)having three columns, equipment name, equipment calibration agency and due date.
How to get notifications or message box saying"you have to send this [equipment] to this [agency] this week".as soon as I click reminder button? kindly give the code syntax please.
Kindly help me.

 

[theDBguy]

Hi. Have you tried?

MsgBox "You have to send this " & Me.Equipment & " to " & Me.[Equipment Calibration Agency] & this week."

 

[Ravi Kumar]

Dear Sir,
Will it work even if there are multiple items?

 

[Deleted member 147267]

Your requirements are a bit vague. If this is Monday and something is due on Friday that's sort of obvious. If this is Thursday and it is due on Monday, that is not this week, but it is less than 5 days, so then what? Also, this reminder is for one particular piece or you want a whole list of pieces that are due this week (or within x number of days)?

You've asked for code but for what, a button click event? Maybe all you want is the general syntax for a query sql that gets all the pieces due in some period? Too many questions to provide focused help.

 

[Ravi Kumar]

Dear sir..thank you so much... don't mind asking questions as all you are rendering your valuable time for me.
1.yes it will be like exactly what you said(sometimes 5 days behind too!)
2.i want list of the due for the week(equipment name, agency name,due date)
3.s it is for button click event (cmdreminder)
4.if message box is not helpful.then pls show me the code to autofilter the same so I can generate a report of the same list and send a mail to "qqqqq"(using docmd.sendobject)

Hope this answers all questions.
Pls ask if you need more.

 

[Deleted member 147267]

It might be better if you just posted a sample db because I'd say I'm not that much further ahead after your reply. There are lots of approaches you can take:
- create a new (or modify your existing) query to use <= Date-7 on the date field, as long as you have not named that field "Date".
- apply a filter to the report date field when you click the button to open the report
- create a sql statement in code and have it look for items due in the next 7 days. If any are found, present a list of them somehow. A message box would serve as a reminder, but all you can do with that is look at it then dismiss it.

Not sure how much time I can devote to this at present as I have guests arriving for a few days. Maybe you can incorporate something like the following untested code into your own event:

Dim rs As DAO.Recordset
Dim sql As String, strList As String

sql = "SELECT tbl1.[equipment name], tbl1.[equipment calibration agency], tbl1.[due date] FROM yourTable WHERE tbl1.[due date] <= Date() + 7"

Set rs = CurrentDb.OpenRecordset(sql)
If Not (rs.EOF And rs.BOF) Then
  Do While Not rs.EOF
    rs.MoveFirst
    If rs.Fields("nameOfDateField") <= Date()-7 Then 
      strList = strList & rs.Fields("equipment name") & vbCrLf
    End If
    rs.MoveNext
  Loop
  MsgBox "The following are past due or due within 7 days:" & vbCrLf & strList
End If

Obviously you have to use your own names for the fields and table involved. As I said, you can often shorten the path to a resolution by posting a db copy.

 

[arnelgp]

Private Sub cmdReminder_Click()
    Dim RS As DAO.Recordset
    Dim strMsg As String
    
    Set RS = CurrentDb.OpenRecordset("select * from (" & Me.RecordSource & ")", dbOpenSnapshot, dbReadOnly)
    
    With RS
        If Not (.BOF And .EOF) Then
            .MoveFirst
            While Not .EOF
                If ![Due Date] > Date - 7 Then
                  strMsg = strMsg & ![Equipment Name] & vbtab & vbtab & [Agency Name] & vbtab & vbtab & [Due Date] & vbCrLf
                End If
                .MoveNext
            Wend
        End If
        .Close
    End With
    Set RS = Nothing
    If strMsg <> "" Then
        strMsg = "You have to send the following:" & vbCrLf & vbCrLf & _
                  "------------------------------------------------------------" & vbCrLf & _
                  "Equipment Name" & vbTab & vtab & "Agency Name" & vtab & vtab & "Due Date" & vbCrLf & _
                  "------------------------------------------------------------" & vbCrLf & _
                  strMsg
    Else
        strMsg = "No record to is due for calibration"
    End If
    
    MsgBox strMsg, vbInformation + vbOKOnly
End Sub

 

[Deleted member 147267]

If ![Due Date] > Date - 7 Then

That will return everything from 6 days ago into the future - even if years into the future, no?

 

[arnelgp]

then revise it:

'1 week past due and 3 days before due date
If ![Due Date] >= Date() - 7 And ![Due Date] <= Date() + 3 Then

 

[Ravi Kumar]

dear sir ,
sorry the equipment number is listing & showing according to criteria(only of this week) , but agency and due date is showing same as first row..
what to do??

 

[Deleted member 147267]

then revise it:

'1 week past due and 3 days before due date
If ![Due Date] >= Date() - 7 And ![Due Date] <= Date() + 3 Then

Sorry, but I still don't see the point of that either. Unless one does not want to know about anything that is 8 days late. I don't see what was wrong with <=Date()-7. If 8 days in the future, no record. If 7 or less, then a record, even if it was missed long ago (e.g. 21 days ago) still will produce a record.

 

[arnelgp]

it simply says that any [due date] between aug-19-2019(Date()-7) and aug-29-2019(Date()+3) needs to be calibrated.

 

[Deleted member 147267]

it simply says that any [due date] between aug-19-2019(Date()-7) and aug-29-2019(Date()+3) needs to be calibrated.

Acknowledged. However if anything was overlooked for whatever reason, and the date associated with that oversight is earlier than the earlier limit the expression imposes, then it is not captured and goes unnoticed. That is the point I was trying to make. With no "earlier" limit, anything that would otherwise fall into that trap gets captured.