Re: Message Alert

in the near future. I tried the code you suggested but unfortunately nothing
happened....


OK, I build a table and form and tested the code. The problem was with
spaces in the names.


Here is the modified code:

'----------end code---------------
Private Sub overDue_Click()

'
'Requires: A reference set to Microsoft DAO 3.6 Object Library
'

Dim rs As DAO.Recordset
Dim strOverDue As String
Dim strSQL As String

'Fields:
'Assessment No ' (Autonumber)
'Assessment Date ' (Date)
'To be completed by ' (Date)
'Assessment Status ' (Combo Box) "Proposed" "Complete" "Overdue"

strSQL = "SELECT [Assessment Status], [To be completed by] FROM
[tbl_Main Records]"
strSQL = strSQL & " WHERE [Assessment Status] = 'Proposed' AND [To be
completed by] > #" & Date & "#"

strSQL = strSQL & " ORDER BY [To be completed by];"

' Debug.Print strSQL

'open recordset
Set rs = CurrentDb.OpenRecordset(strSQL)

'check for records
If Not rs.BOF And Not rs.EOF Then
rs.MoveLast

strOverDue = "There are " & rs.RecordCount & " overdue assessments
as of today" & vbCrLf & vbCrLf
strOverDue = strOverDue & "Please check the report"
' strOverDue = "Overdue assessments found. Please view overdue
report"

MsgBox strOverDue

End If

' cleanup
rs.Close
Set rs = Nothing

End Sub
'----------end code---------------



Let me know what happens.......

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
.

Relevant Pages

  • Re: Message Alert
    ... Dim strOverDue As String ... Dim strSQL As String ... as of today" & vbCrLf & vbCrLf ...
    (microsoft.public.access.modulesdaovba)
  • Re: Newbie: Find additional text strings
    ... The code uses "Sheet1" as the name of the sheet containing your data. ... I think I will keep the modified code for possible future use. ... Dim wksCopyFrom As Excel.Worksheet ... Dim rngFoundAll As Excel.Range ...
    (microsoft.public.excel.programming)
  • Re: Convert ODBCDirect Codes to ADO in Access 2007
    ... here you can find how to write a connection string: ... 'Dim wrkODBC As Workspace ... Dim cnn As ADODB.Connection ... 'Modified Code ...
    (microsoft.public.access.modulesdaovba)
  • VBScript RegExp only working with access2007 ?
    ... Using the modified code below that was obtained from the thread at ... Dim xmlHttpRequest As Object ... Set regularExpression = CreateObject ...
    (microsoft.public.access.modulesdaovba)
  • Re: How to calculate days:Hours:Minutes:
    ... Here is modified code that will handle more than one range, ... Function AverageTimeAs Variant) As String ... Dim Dur As Double ...
    (microsoft.public.excel.worksheet.functions)
Loading