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