Re: Message Alert

In addition to what Steve has written, I will add that your simplest option
is to uncheck the box for Show Database Window in the Startup options (Tools
Startup), and to give your users buttons on the switchboard for the
various forms and reports they need. I will add that the Switchboard
Manager, as I understand it, limits the number of buttons on a switchboard,
so your best choice may be to create a new unbound startup form if you need
more buttons than the Switchboard Manager will allow.
You or another user can press the F11 key to show the database window
(although this option can be disabled as well it the startup options). If
it is disabled in the startup options, you can open the database while
holding the Shift key.
The main point here is that showing all of the database objects to all users
may not be the best option.

"Jen" <Jen@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0A2D5E71-A191-4F9D-AF29-4FE3763248FA@xxxxxxxxxxxxxxxx
Bruce/Steve,
I think we've cracked it. Just before the Switchboard loads i'm getting
a
message saying "There are 3 overdue assessments as of today. Please view
the
report.". This is correct and when i change the table, the message
reflects
the changes. One other slight problem is that the message is displayed
while
the Database Window is still visible....could it be displayed after the
Switchboard has actually loaded ?. I've got the code in the OnOpen event
at
the moment.

Thanks for the help guys, i really appreciate it !
Jenny
"BruceM" wrote:

Wow, thanks. If somebody wants to post it publicly they are welcome.
I'll
even revise it to make it more generic.

"Steve Sanford" <limbim53 at yahoo dot com> wrote in message
news:AB11B04A-AF1C-4B7B-87E3-CB1AB978D42B@xxxxxxxxxxxxxxxx
Bruce, excelent instructions on how to step thru code. It should be
posted
somewhere so beginning debuggers have a "How to" reference.

Jen,
It looks like there have been a couple of name changes to the controls.
The combo box name was "Assessment Status" and now appears to be "CA
Status".

So the two strSQL lines needs to be changed also:

strSQL = "SELECT [CA Status], [Corrective Action Date] FROM [tbl_Main
Records]"

strSQL = strSQL & " WHERE [CA Status] = 'Proposed' AND [Corrective
Action Date] > #" & Date & "#"


BTW, you should copy these and first paste them into NotePad. Edit the
lines
- there should be two lines. Then copy them and paste into Access.
Access
will add extra double quotes (") at the end of text lines if it thinks
one
is
needed.

In re-reading the posts, if you want all overdue assessments, you might
want
to change the second strSQL line to:

(Again, watch for extra double quotes)

strSQL = strSQL & " WHERE [CA Status] <> 'Complete' AND [Corrective
Action
Date] > #" & Date & "#"

This will check for both "Proposed" & "Overdue" having a [Corrective
Action
Date] greater than today, not just "Proposed".


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


"Jen" wrote:

Sorry for the delay guys...got distracted with something else for a
day
or
so. When i load the Swichboard now, i'm getting a message box which
says

Run-time error '3601':
Too few parameters. Expected 1.

I did what you suggested regarding the breakpoint Bruce and when i got
back
into the code and pressed F8, i hovered the mouse where you told me
and
got a
little box which said

strSQL=""

Here's the exact code i'm using at the moment ;

Private Sub Form_Open(Cancel As Integer)

'
'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)
'Corrective Action Date ' (Date)
'CA Status ' (Combo Box) "Proposed" "Complete" "Overdue"

strSQL = "SELECT [Assessment Status], [Corrective Action Date]
FROM
[tbl_Main Records] "
strSQL = strSQL & " WHERE [Assessment Status] = 'Proposed' AND
[Corrective Action Date] > #" & Date & "#"

strSQL = strSQL & " ORDER BY [Corrective Action Date];"

' 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

Else

MsgBox "No overdue assessments"

End If

' cleanup

rs.Close
Set rs = Nothing

End Sub

Thanks,
Jen



"BruceM" wrote:

Try it another way. To set a breakpoint, click a line of code, then
press
the F9 key on your keyboard. A red dot should appear to the left of
the
line of code, which will be highlighted. Close the form, then open
it
again. This will trigger the Load event. When the Load Event
reaches
the
highlighted line of code, the code will stop running, and you will
be
shown
the code window. Press F8 to move to the next line of code. Place
the
mouse over strSQL to see what shows up.

"Jen" <Jen@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F0E250E2-DA8C-450E-9F9C-8A9DC046B5C7@xxxxxxxxxxxxxxxx
The red line of text disappeared when i took out the extra
quotation
mark.
It
wasn't a typo as i copied and pasted my code onto the post i made.
I
also
changed a couple of settings in VB editor but still nothing....

I read what you suggested about setting a breakpoint but i was
going
crosseyed after 3 or 4 lines....sorry :)

"BruceM" wrote:

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.

"Jen" <Jen@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:37C776AE-B10A-4353-BAB9-1A68B9C8A7CB@xxxxxxxxxxxxxxxx
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



.