Re: Message Alert

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

"Steve Sanford" wrote:

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


"Jen" wrote:

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)


"egun" wrote:

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

.

Relevant Pages

  • Re: Message Alert
    ... Odds are it'll never affect you, unless you have billions of assessments! ... Dim strOverDue As String ... strOverDue = "Overdue assessments found. ... dim isOverDue as Boolean ...
    (microsoft.public.access.modulesdaovba)
  • Re: Message Alert
    ... I only used autonumber so people wouldn't use duplicate assessment no's ... Dim strOverDue As String ... strOverDue = "Overdue assessments found. ... dim isOverDue as Boolean ...
    (microsoft.public.access.modulesdaovba)
  • Re: Message Alert
    ... Dim strOverDue As String ... Assessments as of today" & vbCrLf & vbCrLf ... strOverDue = "Overdue assessments found. ... dim isOverDue as Boolean ...
    (microsoft.public.access.modulesdaovba)
  • Re: Message Alert
    ... "Overdue assessments found. ... Dim overDue as String ... dim isOverDue as Boolean ...
    (microsoft.public.access.modulesdaovba)
Loading