Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-25-2019, 06:30 AM   #1
Ravi Kumar
Newly Registered User
 
Join Date: Aug 2019
Posts: 21
Thanks: 28
Thanked 0 Times in 0 Posts
Ravi Kumar is on a distinguished road
Getting notifications from report

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.

Ravi Kumar is offline   Reply With Quote
Old 08-25-2019, 06:35 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,112
Thanks: 45
Thanked 963 Times in 945 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Getting notifications from report

Hi. Have you tried?
Code:
MsgBox "You have to send this " & Me.Equipment & " to " & Me.[Equipment Calibration Agency] & this week."
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Ravi Kumar (08-25-2019)
Old 08-25-2019, 06:40 AM   #3
Ravi Kumar
Newly Registered User
 
Join Date: Aug 2019
Posts: 21
Thanks: 28
Thanked 0 Times in 0 Posts
Ravi Kumar is on a distinguished road
Re: Getting notifications from report

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

Ravi Kumar is offline   Reply With Quote
Old 08-25-2019, 06:41 AM   #4
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 860
Thanks: 10
Thanked 181 Times in 171 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Getting notifications from report

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.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 08-25-2019, 07:48 AM   #5
Ravi Kumar
Newly Registered User
 
Join Date: Aug 2019
Posts: 21
Thanks: 28
Thanked 0 Times in 0 Posts
Ravi Kumar is on a distinguished road
Re: Getting notifications from report

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.
Ravi Kumar is offline   Reply With Quote
Old 08-25-2019, 08:50 AM   #6
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 860
Thanks: 10
Thanked 181 Times in 171 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Getting notifications from report

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:
Code:
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.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)

Last edited by Micron; 08-25-2019 at 08:52 AM. Reason: code correction
Micron is offline   Reply With Quote
Old 08-25-2019, 09:02 AM   #7
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,961
Thanks: 64
Thanked 2,532 Times in 2,432 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Getting notifications from report

Code:
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

__________________
"Never stop learning, because life never stops teaching"

Last edited by arnelgp; 08-25-2019 at 09:11 AM.
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Ravi Kumar (08-25-2019)
Old 08-25-2019, 10:06 AM   #8
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 860
Thanks: 10
Thanked 181 Times in 171 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Getting notifications from report

Quote:
If ![Due Date] > Date - 7 Then
That will return everything from 6 days ago into the future - even if years into the future, no?
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
Ravi Kumar (08-25-2019)
Old 08-25-2019, 09:40 PM   #9
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,961
Thanks: 64
Thanked 2,532 Times in 2,432 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Getting notifications from report

then revise it:

'1 week past due and 3 days before due date
If ![Due Date] >= Date() - 7 And ![Due Date] <= Date() + 3 Then
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Ravi Kumar (08-25-2019)
Old 08-25-2019, 11:59 PM   #10
Ravi Kumar
Newly Registered User
 
Join Date: Aug 2019
Posts: 21
Thanks: 28
Thanked 0 Times in 0 Posts
Ravi Kumar is on a distinguished road
Re: Getting notifications from report

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

Last edited by Ravi Kumar; 08-26-2019 at 01:03 AM.
Ravi Kumar is offline   Reply With Quote
Old 08-26-2019, 06:27 AM   #11
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 860
Thanks: 10
Thanked 181 Times in 171 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Getting notifications from report

Quote:
Originally Posted by arnelgp View Post
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.
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
Ravi Kumar (08-26-2019)
Old 08-26-2019, 07:06 AM   #12
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,961
Thanks: 64
Thanked 2,532 Times in 2,432 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Getting notifications from report

it simply says that any [due date] between aug-19-2019(Date()-7) and aug-29-2019(Date()+3) needs to be calibrated.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Ravi Kumar (08-26-2019)
Old 08-26-2019, 03:44 PM   #13
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 860
Thanks: 10
Thanked 181 Times in 171 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Getting notifications from report

Quote:
Originally Posted by arnelgp View Post
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.

Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
Ravi Kumar (08-26-2019)
Reply

Tags
notifications , pop up , report , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question Sending Notifications gmatriix General 1 11-11-2008 01:29 PM
Printing Notifications Brian1960 Modules & VBA 0 08-15-2006 03:46 AM
disabling notifications toddbingham General 1 10-24-2003 01:10 PM
Notifications/Alarms Justin Modules & VBA 2 02-08-2002 01:34 AM




All times are GMT -8. The time now is 03:56 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World