Re: Message Alert

Ok i changed the code at the end as you suggested but still nothing (i have
deliberately changed 3 records to overdue just to be sure)

* There is a reference is set to the Microsoft DAO 3.6 Object Library
Done

* No red lines in the code
1 red line (below)

strOverDue = "There are " & rs.RecordCount & " overdue assessments " as of
today" & vbCrLf & vbCrLf

* The code is connected to the Open or Load event
Done

* The first two lines at the top of the code page are:

Option Compare Database
Option Explicit

No...only the line Option Compare Database is showing

* Have you set a breakpoint and stepped thru the code

Don't know what you mean by this :(










"Steve Sanford" wrote:

I had put the code in the click event of a button named "Overdue" .
I just tried putting the code in the Form Load event
Then I tried the Form Open event.

The message was displayed all three times.

I left the code in the Form Open event and changed "Proposed" to "Complete"
in my test table. When I opened the form, I did not receive a message. Maybe
you don't have any "Proposed" assessments that are overdue.

Try this. Change this part of the code:

MsgBox strOverDue

End If

' cleanup

to this

'------- Beg code change----------------
MsgBox strOverDue

Else

MsgBox "No overdue assessments"

End If

' cleanup
'------- end code change----------------


Since I can't see your MDB, here are some other things to check:

* There is a reference is set to the Microsoft DAO 3.6 Object Library
* No red lines in the code
* The code is connected to the Open or Load event
* The first two lines at the top of the code page are:

Option Compare Database
Option Explicit

* Have you set a breakpoint and stepped thru the code


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


"Jen" wrote:

Ok i tried it this morning and still nothing. I'm inserting the code in the
OnLoad event of the form...is this right ?. Before you fall around laughing,
remember i know zip about VB....;)

"Steve Sanford" wrote:

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