Message Alert

[Jen]

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

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

 

[BruceM]

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.

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

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

 

[Jen]

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

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.

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

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

 

[BruceM]

Setting the breakpoint and pressing F8 is called stepping through the code.
When you arrive at the line:
strSQL = "SELECT [Assessment Status], [Corrective Action Date] FROM
[tbl_Main Records] "
you will probably see:
strSQL = ""
When you move to the next line of code:
strSQL = strSQL & " WHERE [Assessment Status] = 'Proposed' AND [Corrective
Action Date] > #" & Date & "#"
you should see a different result when you point at strSQL. When you are
stepping through the code, the line at which you arrive is executed after
you move past it, not when you first arrive. That is why you don't see
anything for the first strSQL line until you have moved past the line.
You can obtain information about other variables such as strOverDue and
rs.RecordCount by the same pointing technique.
The information in the pop-up window is limited. To see the full result you
may need to do something such as removing the apostrophe from in front of
Debug.Print. This will write strSQL to the Immediate Window, which is an
extra code window you can view by pressing Ctrl + G. You could also use:
MsgBox strSQL
if you prefer to view the information that way.
I have lost track of the details here. Where does the switchboard enter
into the picture? Is this code in the switchboard?
Error handling is a big help in tracking down errors. After the Private Sub
line (or the line that contains "Sub" if it isn't a private sub) place a new
line at the left margin of the code window, something like:
On Error GoTo ProcErr
Just before the End Sub line, put something like:
ProcExit:
Exit Sub
ProcErr:
MsgBox "Error #" & Err.Number & ": " & Err.Description & _
" in Form_Current"
Resume ProcExit

This assumes the sub is in the form's Current event. If it is something
else (the Load event, or whatever), use that instead of Form_Current in the
MsgBox line of code. I use error handling in every event, unless it has
built-in error handling elsewhere in the code.

The error handling will help you identify the event that is generating the
error. Once you have identified the event, set a breakpoint in that event
so you can see which line causes the error. As you are stepping through the
code (pressing F8) the highlight will jump down to the MsgBox "Error #" etc.
line of code. Note which line the code was at just before it muped to the
error. That is the line that is causing the problem.

I don't see anything offhand that would be causing that error 3601, but I
may be missing something. In any case, the procedure I have outlined will
let you identify the offending code.

One further point, at the risk of confusing you. You can leave out the
error handling code. Instead, open the VBA editor, click Tools > Options,
click the General tab, and be sure "Break on unhandled errors" is selected.
When there is no error handling, this should highlight the line of code that
is causing the error. Specifically, you can identify the specific source of
error 3601.

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

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.

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

:

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

 

[Steve Sanford]

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

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

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.

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

:

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)

 

[BruceM]

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

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

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

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.

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

:

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)

 

[Jen]

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

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

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

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



:

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.

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

:

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

 

[Steve Sanford]

Hi Jen,

You could try the form load event.

This is from Help:

"When you open a form, the following sequence of events occurs for the form:

Open → Load → Resize → Activate → Current"

and

"If you are opening a form, these events occur after the events associated
with opening the form (such as Open, Activate, and Current), as follows:

Open (form) → Activate (form) → Current (form) → Enter (control) → GotFocus
(control)"


If the form load event doesn't display the message the way you want, you
could add an unbound text box in the upper left of the form. Set the special
effect property to flat and the back color to the same color as the form back
color. Set the tab order to 0 (zero). Put the code in the got focus event of
the text box. Add a line to the code just above the "End Sub" line:

Me.Visible = False


So the form would open, the text box would get the focus, the code would
run, then the text box would be hidden.


Another way would be to use a text box or a label on the form instead of the
message dialog box. The code would be in the form Open or Load event, but the
message (if any) would appear on the form. You could use a button or the
click event of the text box/label to hide the message.


Or maybe someone else has a better method......

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

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

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

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


:

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



:

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.

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

:

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"

 

[BruceM]

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.

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

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

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


:

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



:

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.

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

:

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

 

[Jen]

Hi Guys,
I decided the simplest thing to do was to disable the database window in
the Startup options and add a coulpe of buttons to the Switchboard.
Everything's working fine now. Bruce/Steve - thank you both for your help and
patience...i sure am grateful to ye both

Jenny xx

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.

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

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


:

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



:

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.

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

:

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" <[email protected]> wrote in message

 

[BruceM]

Simplest is often best. The database window can be hidden/shown through
code, but as I understand it custom properties and other such details come
into play. It's a lot of work when there is another, simpler option at
hand. Good luck with the project.

Hi Guys,
I decided the simplest thing to do was to disable the database window in
the Startup options and add a coulpe of buttons to the Switchboard.
Everything's working fine now. Bruce/Steve - thank you both for your help
and
patience...i sure am grateful to ye both

Jenny xx

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.

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
:

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


:

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



:

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.

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

:

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" <[email protected]> wrote in message

 

[Jen]

Sod it i'm getting an error now -

Compile Error
Else without If

It's highlighting the very last 'Else' in the code. Also the first line of
the code (Private Sub etc....) has turned yellow


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 [CA Status], [Corrective Action Date] FROM [tbl_Main
Records]"
strSQL = strSQL & " WHERE [CA Status] <> 'Complete' 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 assessment(s)
as of today" & vbCrLf & vbCrLf
strOverDue = strOverDue & "Please check your area in the Overdue
Report"
' strOverDue = "Overdue assessments found. Please view Overdue
Report ""

MsgBox strOverDue

Else <-------

MsgBox "No overdue assessments. Please continue."

End If

' cleanup


rs.Close
Set rs = Nothing

End Sub

Simplest is often best. The database window can be hidden/shown through
code, but as I understand it custom properties and other such details come
into play. It's a lot of work when there is another, simpler option at
hand. Good luck with the project.

Hi Guys,
I decided the simplest thing to do was to disable the database window in
the Startup options and add a coulpe of buttons to the Switchboard.
Everything's working fine now. Bruce/Steve - thank you both for your help
and
patience...i sure am grateful to ye both

Jenny xx

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.

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
:

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


:

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



:

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.

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

:

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)

 

[John Spencer]

Try changing this line

'check for records
If Not rs.BOF And Not rs.EOF Then rs.MoveLast

To

'check for records
If Not rs.BOF And Not rs.EOF Then
rs.MoveLast

If you include the something after the THEN on the IF line then VBA treats
that line as completing the IF statement. Since it appears that you want a
multi-line If construct you need to move the rs.Movelast onto the next line.

I would simplify this whole thing by using

Private Sub Form_Open(Cancel As Integer)
'Requires: A reference set to Microsoft DAO 3.6 Object Library
Dim LCount as Long
Dim strOverDue As String

'Fields:
'Corrective Action Date ' (Date)
'CA Status ' (Combo Box) "Proposed" "Complete" "Overdue"

LCount = DCount("*","[tbl_Main Records", _
"[CA Status] <> 'Complete' AND [Corrective Action Date] < Date()")

IF LCount > 0 Then
strOverDue = "There are " & LCount & _
" overdue assessment(s) as of today" & vbCrLf & vbCrLf
strOverDue = strOverDue & _
"Please check your area in the Overdue Report"
MsgBox strOverDue

Else
MsgBox "No overdue assessments. Please continue."

End If

End Sub

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Sod it i'm getting an error now -

Compile Error
Else without If

It's highlighting the very last 'Else' in the code. Also the first line of
the code (Private Sub etc....) has turned yellow


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 [CA Status], [Corrective Action Date] FROM [tbl_Main
Records]"
strSQL = strSQL & " WHERE [CA Status] <> 'Complete' 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 assessment(s)
as of today" & vbCrLf & vbCrLf
strOverDue = strOverDue & "Please check your area in the Overdue
Report"
' strOverDue = "Overdue assessments found. Please view Overdue
Report ""

MsgBox strOverDue

Else <-------

MsgBox "No overdue assessments. Please continue."

End If

' cleanup


rs.Close
Set rs = Nothing

End Sub

Simplest is often best. The database window can be hidden/shown through
code, but as I understand it custom properties and other such details
come
into play. It's a lot of work when there is another, simpler option at
hand. Good luck with the project.

Hi Guys,
I decided the simplest thing to do was to disable the database window
in
the Startup options and add a coulpe of buttons to the Switchboard.
Everything's working fine now. Bruce/Steve - thank you both for your
help
and
patience...i sure am grateful to ye both

Jenny xx

:

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.

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
:

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


:

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



:

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.

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

:

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)