Re: Message Alert

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

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






.