Message Alert

[Jen]

Hi Guys,
I've just finished a database which is working fine but i'd like to add
one more small thing. This database involves entering information about
assessments carried out by various members of staff and some of the fields
are

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

On opening the database i'd like a message to appear stating something
like

"Assessment no "xxx" is now overdue. Please complete assessment.


Is this possible ?

Thanks a lot,
Jenny

 

[Jen]

Sorry guys forgot to mention my table is called 'tbl_Main Records'

 

[Keith Wilby]

Hi Guys,
I've just finished a database which is working fine but i'd like to add
one more small thing. This database involves entering information about
assessments carried out by various members of staff and some of the fields
are

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

On opening the database i'd like a message to appear stating something
like

"Assessment no "xxx" is now overdue. Please complete assessment.

It is but it would get incredibly annoying for the users if, for example, 50
are overdue. Dismissing 50 message boxes would be tedious. Are you sure
that's what you want? If it is then something like this in the form's Open
event should work (air code, not tested):

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

rs.MoveFirst
With rs
Do Until .EOF
If ![Assessment Status] = "Overdue" Then MsgBox "Assessment no 'xxx'
is now overdue. Please complete assessment."
Loop
End With

rs.Close
Set rs = Nothing


Keith.
www.keithwilby.com

 

[Keith Wilby]

If ![Assessment Status] = "Overdue" Then MsgBox "Assessment no
'xxx' is now overdue. Please complete assessment."

Whoops, should read:

If ![Assessment Status] = "Overdue" Then MsgBox "Assessment no " &
![Assessment No] & " is now overdue. Please complete assessment."

Would probably be neater to declare a string variable.

Dim str As String
str = "Assessment no "
If ![Assessment Status] = "Overdue" Then
str = str & ![Assessment No]
str = str & " is now overdue. Please complete assessment."
MsgBox str
End If

 

[BruceM]

Are you looking for a message to appear for each record, or do you want a
summary statement of all records that are overdue, or what exactly? Also,
what do you mean by "on opening the database"? Is there a startup form, or
do you mean when a particular form is opened? Again, details are needed
about exactly what is to occur.
In any case, what you seek can be done, I expect, but more information will
help keep the answer targeted to your specific requirement rather than
considering a range of possibilities.

 

[Jen]

Thanks for replying guys.....i've been thinking, and you're probably right,
if there were 20 assessments overdue and 20 message boxes popped up one after
another, i'd probably pull my hair out. Would it be possible to compare
todays date to the date entered in the 'To be completed by' field, and if
that date has elapsed for one or more records, then display a general message
saying something like 'There are overdue assessments, please see report'.
I'll then create a report which they can open and see which ones are overdue.
Oh and yes, when the database opens, there's a switchboard and it's at this
point i'd prefer to see the message.

Thanks again guys,
Jenny

 

[egun]

You could limit it to one annoying message box by first building the message
box string (as below) for all the overdue items, then displaying the box once:

Dim overDue as String
dim isOverDue as Boolean

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

isOverDue = False

' chr(10) = line feed character
overDue = "Overdue Assessments: " & chr(10) & chr(10)

rs.MoveFirst
With rs
Do Until .EOF
If ![Assessment Status] = "Overdue" Then
overDue = overDue & "Assessment No. " & ![Assessment No] " was
due " & ![To be completed by] & chr(10)
isOverDue = True
Endif
Loop
End With

rs.Close
Set rs = Nothing

' Only show msgbox if there was at least one overdue item

If isOverDue Then msgbox(overDue)

---Something like that---

Eric

 

[Keith Wilby]

Thanks for replying guys.....i've been thinking, and you're probably
right,
if there were 20 assessments overdue and 20 message boxes popped up one
after
another, i'd probably pull my hair out. Would it be possible to compare
todays date to the date entered in the 'To be completed by' field, and if
that date has elapsed for one or more records, then display a general
message
saying something like 'There are overdue assessments, please see report'.
I'll then create a report which they can open and see which ones are
overdue.
Oh and yes, when the database opens, there's a switchboard and it's at
this
point i'd prefer to see the message.

Set up a suitable query then use DCount to count how many records are in it.
If there is more than zero records then display the message.

If DCount("MyField","qryMyQuery") > 0 then ...

HTH - Keith.
www.keithwilby.com

 

[m stroup]

Could you run a query on start-up with the "user" as a parameter and display
the assessments assigned to that individual that are overdue by xxx days?

 

[BruceM]

Eric has provided the basic code you would need to accomplish this. It's
late during a rather hectic day, and I don't think I will be able to provide
a full description of how to get this done. In general, you could make a
query based on tbl_MainRecords. Add the necessary fields, and set the
criteria for the DueDate field to:

Date()

Make a report (rptOverDue), and use the query as its record source. In
Eric's code, right after the MsgBox line of code use something like:
If isOverDue Then
If MsgBox(overDue & vbCrLf & _
"View Report?",vbOkCancel) = vbOK Then
Docmd.OpenReport "rptOverDue"
End If
End If

If you want to use the report at times for a general listing of records
rather than just the overdue records you will need to set the report's
RecordSource or a filter when you open the form. However, I will not be
available for follow-up questions until Monday. I'm not completely certain
this is the most efficient approach, and there are still some things about
your project I don't know, so I hope somebody else jumps in here. Good
luck.

 

[Jen]

Again thanks for the help all...i tried the code you suggested (Eric) and got
a compile error in the 3rd line (i moved it out a little). Bear in mind i'm
very weak on VB. I could be wrong but i think that particular code searches
for records marked "Overdue" and then gives a general message. What i really
need is something that searches for assessments that are not marked as
complete and also that have gone past the "To be completed by" date. If
records in the table match these criteria, then i'd like the message to say
"Overdue assessments found. Please view overdue report" (I've got the report
up and running)

Thanks again everyone,
Jen

Dim overDue as String
dim isOverDue as Boolean

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

isOverDue = False

' chr(10) = line feed character
overDue = "Overdue Assessments: " & chr(10) & chr(10)

rs.MoveFirst
With rs
Do Until .EOF
If ![Assessment Status] = "Overdue" Then
overDue = overDue & "Assessment No. " & ![Assessment No] " was
due " & ![To be completed by] & chr(10)
isOverDue = True
Endif
Loop
End With

rs.Close
Set rs = Nothing

' Only show msgbox if there was at least one overdue item

If isOverDue Then msgbox(overDue)

 

[Steve Sanford]

I noticed you are using an autonumber for the "Assessment No.". This is
considered VERY bad! What are you going to do when the autonumber goes
negative??



See this page: http://www.mvps.org/access/tencommandments.htm

Pay attention to #2, 3 and especially #7

Here is a list of reserved words:
http://allenbrowne.com/AppIssueBadWord.html

Naming Conventions:

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_20naming.asp

OK, enough of that. You get the picture......

So, after all that, here is some (untested) code that might work for you....

(Watch for line wrap........ this is untested code!!)

'------- beg code -------------------
Public Sub overDue()

'
'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 = "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 -------------------



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

Again thanks for the help all...i tried the code you suggested (Eric) and got
a compile error in the 3rd line (i moved it out a little). Bear in mind i'm
very weak on VB. I could be wrong but i think that particular code searches
for records marked "Overdue" and then gives a general message. What i really
need is something that searches for assessments that are not marked as
complete and also that have gone past the "To be completed by" date. If
records in the table match these criteria, then i'd like the message to say
"Overdue assessments found. Please view overdue report" (I've got the report
up and running)

Thanks again everyone,
Jen

Dim overDue as String
dim isOverDue as Boolean

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

isOverDue = False

' chr(10) = line feed character
overDue = "Overdue Assessments: " & chr(10) & chr(10)

rs.MoveFirst
With rs
Do Until .EOF
If ![Assessment Status] = "Overdue" Then
overDue = overDue & "Assessment No. " & ![Assessment No] " was
due " & ![To be completed by] & chr(10)
isOverDue = True
Endif
Loop
End With

rs.Close
Set rs = Nothing

' Only show msgbox if there was at least one overdue item

If isOverDue Then msgbox(overDue)

You could limit it to one annoying message box by first building the message
box string (as below) for all the overdue items, then displaying the box once:

Dim overDue as String
dim isOverDue as Boolean

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

isOverDue = False

' chr(10) = line feed character
overDue = "Overdue Assessments: " & chr(10) & chr(10)

rs.MoveFirst
With rs
Do Until .EOF
If ![Assessment Status] = "Overdue" Then
overDue = overDue & "Assessment No. " & ![Assessment No] " was
due " & ![To be completed by] & chr(10)
isOverDue = True
Endif
Loop
End With

rs.Close
Set rs = Nothing

' Only show msgbox if there was at least one overdue item

If isOverDue Then msgbox(overDue)

---Something like that---

Eric

 

[Jen]

The autonumber will do what ??

Noooooooooooooooooooo !!

At what point will it go negative ??....

I only used autonumber so people wouldn't use duplicate assessment no's

Those links were very helpful by the way...i'll be spending some time there
in the near future. I tried the code you suggested but unfortunately nothing
happened....

I noticed you are using an autonumber for the "Assessment No.". This is
considered VERY bad! What are you going to do when the autonumber goes
negative??



See this page: http://www.mvps.org/access/tencommandments.htm

Pay attention to #2, 3 and especially #7

Here is a list of reserved words:
http://allenbrowne.com/AppIssueBadWord.html

Naming Conventions:

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_20naming.asp

OK, enough of that. You get the picture......

So, after all that, here is some (untested) code that might work for you....

(Watch for line wrap........ this is untested code!!)

'------- beg code -------------------
Public Sub overDue()

'
'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 = "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 -------------------



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

Again thanks for the help all...i tried the code you suggested (Eric) and got
a compile error in the 3rd line (i moved it out a little). Bear in mind i'm
very weak on VB. I could be wrong but i think that particular code searches
for records marked "Overdue" and then gives a general message. What i really
need is something that searches for assessments that are not marked as
complete and also that have gone past the "To be completed by" date. If
records in the table match these criteria, then i'd like the message to say
"Overdue assessments found. Please view overdue report" (I've got the report
up and running)

Thanks again everyone,
Jen

Dim overDue as String
dim isOverDue as Boolean

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

isOverDue = False

' chr(10) = line feed character
overDue = "Overdue Assessments: " & chr(10) & chr(10)

rs.MoveFirst
With rs
Do Until .EOF
If ![Assessment Status] = "Overdue" Then
overDue = overDue & "Assessment No. " & ![Assessment No] " was
due " & ![To be completed by] & chr(10)
isOverDue = True
Endif
Loop
End With

rs.Close
Set rs = Nothing

' Only show msgbox if there was at least one overdue item

If isOverDue Then msgbox(overDue)

You could limit it to one annoying message box by first building the message
box string (as below) for all the overdue items, then displaying the box once:

Dim overDue as String
dim isOverDue as Boolean

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

isOverDue = False

' chr(10) = line feed character
overDue = "Overdue Assessments: " & chr(10) & chr(10)

rs.MoveFirst
With rs
Do Until .EOF
If ![Assessment Status] = "Overdue" Then
overDue = overDue & "Assessment No. " & ![Assessment No] " was
due " & ![To be completed by] & chr(10)
isOverDue = True
Endif
Loop
End With

rs.Close
Set rs = Nothing

' Only show msgbox if there was at least one overdue item

If isOverDue Then msgbox(overDue)

---Something like that---

Eric

 

[Douglas J. Steele]

Autonumbers are Long Integers. That means their values can be anything
between -2,147,483,648 and 2,147,483,647

Assuming you've set the NewValues property of the Autonumber field to
Increment (as opposed to Random), your numbers will continue growing from 1
up to 2,147,483,647, and then will wrap around
to -2,147,483,648, -2,147,483,647, -2,147,483,646 and so on until they reach
0.

Odds are it'll never affect you, unless you have billions of assessments!

However, the point that Autonumbers shouldn't be used if their value has
some meaning is still relevant. Incremental Autonumbers are not guaranteed
not to have gaps, and gaps in numbering sometimes get auditors concerned.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The autonumber will do what ??

Noooooooooooooooooooo !!

At what point will it go negative ??....

I only used autonumber so people wouldn't use duplicate assessment no's

Those links were very helpful by the way...i'll be spending some time
there
in the near future. I tried the code you suggested but unfortunately
nothing
happened....

I noticed you are using an autonumber for the "Assessment No.". This is
considered VERY bad! What are you going to do when the autonumber goes
negative??



See this page: http://www.mvps.org/access/tencommandments.htm

Pay attention to #2, 3 and especially #7

Here is a list of reserved words:
http://allenbrowne.com/AppIssueBadWord.html

Naming Conventions:

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_20naming.asp

OK, enough of that. You get the picture......

So, after all that, here is some (untested) code that might work for
you....

(Watch for line wrap........ this is untested code!!)

'------- beg code -------------------
Public Sub overDue()

'
'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 = "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 -------------------



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

Again thanks for the help all...i tried the code you suggested (Eric)
and got
a compile error in the 3rd line (i moved it out a little). Bear in mind
i'm
very weak on VB. I could be wrong but i think that particular code
searches
for records marked "Overdue" and then gives a general message. What i
really
need is something that searches for assessments that are not marked as
complete and also that have gone past the "To be completed by" date. If
records in the table match these criteria, then i'd like the message to
say
"Overdue assessments found. Please view overdue report" (I've got the
report
up and running)

Thanks again everyone,
Jen

Dim overDue as String
dim isOverDue as Boolean

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

isOverDue = False

' chr(10) = line feed character
overDue = "Overdue Assessments: " & chr(10) & chr(10)

rs.MoveFirst
With rs
Do Until .EOF
If ![Assessment Status] = "Overdue" Then
overDue = overDue & "Assessment No. " & ![Assessment No] "
was
due " & ![To be completed by] & chr(10)
isOverDue = True
Endif
Loop
End With

rs.Close
Set rs = Nothing

' Only show msgbox if there was at least one overdue item

If isOverDue Then msgbox(overDue)


:

You could limit it to one annoying message box by first building the
message
box string (as below) for all the overdue items, then displaying the
box once:

Dim overDue as String
dim isOverDue as Boolean

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

isOverDue = False

' chr(10) = line feed character
overDue = "Overdue Assessments: " & chr(10) & chr(10)

rs.MoveFirst
With rs
Do Until .EOF
If ![Assessment Status] = "Overdue" Then
overDue = overDue & "Assessment No. " & ![Assessment No]
" was
due " & ![To be completed by] & chr(10)
isOverDue = True
Endif
Loop
End With

rs.Close
Set rs = Nothing

' Only show msgbox if there was at least one overdue item

If isOverDue Then msgbox(overDue)

---Something like that---

Eric

 

[Jen]

Well thank Jaysus for that !!.....i think there'll be a few thousand at most
and it doesn't matter if there's a little gap in the sequence as long as
they're all different.

Thanks a lot for the help (again)

Could still do with a little assistance with the message box code.....

Autonumbers are Long Integers. That means their values can be anything
between -2,147,483,648 and 2,147,483,647

Assuming you've set the NewValues property of the Autonumber field to
Increment (as opposed to Random), your numbers will continue growing from 1
up to 2,147,483,647, and then will wrap around
to -2,147,483,648, -2,147,483,647, -2,147,483,646 and so on until they reach
0.

Odds are it'll never affect you, unless you have billions of assessments!

However, the point that Autonumbers shouldn't be used if their value has
some meaning is still relevant. Incremental Autonumbers are not guaranteed
not to have gaps, and gaps in numbering sometimes get auditors concerned.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The autonumber will do what ??

Noooooooooooooooooooo !!

At what point will it go negative ??....

I only used autonumber so people wouldn't use duplicate assessment no's

Those links were very helpful by the way...i'll be spending some time
there
in the near future. I tried the code you suggested but unfortunately
nothing
happened....

I noticed you are using an autonumber for the "Assessment No.". This is
considered VERY bad! What are you going to do when the autonumber goes
negative??



See this page: http://www.mvps.org/access/tencommandments.htm

Pay attention to #2, 3 and especially #7

Here is a list of reserved words:
http://allenbrowne.com/AppIssueBadWord.html

Naming Conventions:

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_20naming.asp

OK, enough of that. You get the picture......

So, after all that, here is some (untested) code that might work for
you....

(Watch for line wrap........ this is untested code!!)

'------- beg code -------------------
Public Sub overDue()

'
'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 = "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 -------------------



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


:

Again thanks for the help all...i tried the code you suggested (Eric)
and got
a compile error in the 3rd line (i moved it out a little). Bear in mind
i'm
very weak on VB. I could be wrong but i think that particular code
searches
for records marked "Overdue" and then gives a general message. What i
really
need is something that searches for assessments that are not marked as
complete and also that have gone past the "To be completed by" date. If
records in the table match these criteria, then i'd like the message to
say
"Overdue assessments found. Please view overdue report" (I've got the
report
up and running)

Thanks again everyone,
Jen

Dim overDue as String
dim isOverDue as Boolean

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

isOverDue = False

' chr(10) = line feed character
overDue = "Overdue Assessments: " & chr(10) & chr(10)

rs.MoveFirst
With rs
Do Until .EOF
If ![Assessment Status] = "Overdue" Then
overDue = overDue & "Assessment No. " & ![Assessment No] "
was
due " & ![To be completed by] & chr(10)
isOverDue = True
Endif
Loop
End With

rs.Close
Set rs = Nothing

' Only show msgbox if there was at least one overdue item

If isOverDue Then msgbox(overDue)


:

You could limit it to one annoying message box by first building the
message
box string (as below) for all the overdue items, then displaying the
box once:

Dim overDue as String
dim isOverDue as Boolean

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

isOverDue = False

' chr(10) = line feed character
overDue = "Overdue Assessments: " & chr(10) & chr(10)

rs.MoveFirst
With rs
Do Until .EOF
If ![Assessment Status] = "Overdue" Then
overDue = overDue & "Assessment No. " & ![Assessment No]
" was
due " & ![To be completed by] & chr(10)
isOverDue = True
Endif
Loop
End With

rs.Close
Set rs = Nothing

' Only show msgbox if there was at least one overdue item

If isOverDue Then msgbox(overDue)

---Something like that---

Eric

 

[Steve Sanford]

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

 

[Jen]

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

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 Sanford]

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

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

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

 

[Jen]

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

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

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

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

 

[BruceM]

I notice that there is an extra quote mark (after "assessments"). Could be
a typo when you posted here, but there is the observation anyhow.

First, check a few things in your VBA editor. Click Tools > Options. On
the editor tab, be sure "Require Variable Declaration" is checked, along
with "Auto List Members", "Auto Quick Info", and "Auto Data Tips". I would
uncheck "Auto Syntax Check", which I find more of an annoyance than a help.
I'm going to compile the code anyhow, and I would rather it not pop in with
messages when I do something like scroll up the page to check something.
For more information, see "Set Visual Basic Environment Options" in VBA
Help. Also, check:
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions
I don't think "Require Variable Declaration" will have an effect on an
existing code module, but you can just type "Option Explicit" below "Option
Compare Database".

To set a breakpoint, go to the code in the VBA editor and click to the left
of the white space where the code is written (a gray vertical bar, probably)
next to the first line of code other than one starting with Dim. I think it
will be strSQL = etc. in this case. This will put a red dot onto the gray
bar, and will highlight the line in red. This is a break point. When you
run the code (in this case, when the form loads, if I understand correctly)
the VBA editor will open with the break point line highlighted. Press F8 to
go to the next line of code. After you have done so, point to "strSQL" in
the preceding line. You should see the string pop up (like a tool tip
message). Proceed through the code, pointing the mouse at items of code as
you go to see if they are producing the expected results.
For another way of keeping track of things such as strSQL, highlight strSQL
and click Debug > Set Watch. A window will open at the bottom of the
editor, and will display strSQL as it is assembled by the code.
By the way, if it is not clear, Debug.Print will write to the immediate
window in the VBA editor. In this case, if the apostrophe is removed from
the beginning of the Debug.Print line of code, you can see the fully
assembled strSQL by pressing Ctrl + G , which opens the Immediate window,
after the code has run. You can also use the View menu in the VBA editor
for this.

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

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

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

:

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