Chase Error, Option Explicit, Test Constructed SQL, Rethink Structure
-------
Hi Gus,
since you didn't tell me what to look at, I closed ytour Switchboard and
found the routine using SendObject -- then I found the code that called
it, behind form --> EmailQuery
To see what was going on, I added the STOP and DEBUG lines to the code
for your 'Send Email' button. These statements are lined up on the Left
because they are temporary and will be removed (easier to spot that
way). Everything else is as you had it -- except for 'Option Explicit'
'~~~~~~~~~~~~~~
Option Explicit
Private Sub cmdSendEmail_Click()
Dim strSQL As String
strSQL = "SELECT tblUSers.User_FName AS UserName " _
& ", tblAgreements.RACA_Agr_Num " _
& ", tblAgreements.Action_Item " _
& ", tblAgreements.Date_Action_Reminder " _
& ", tblAgreements.Date_Action_Required " _
& ", tblUSers.Team " _
& ", tblUSers.Email " _
& " FROM tblUSers " _
& " INNER JOIN tblAgreements" _
& " ON tblUSers.User_ID = tblAgreements.UserID" _
Debug.Print strSQL
Stop
LoopAgmtsSendEmail strSQL
End Sub
'~~~~~
Notice how the 'Option Explicit' statement has been added to the top of
the module sheet...
--- Option Explicit ---
It is always a good idea to DIMension your variables before you use them
Dim mStr As String
to force variable delaration (which I HIGHLY recommend), put this at the
top of your module:
Option Explicit
you can do Tools, Options... and check "require variable declaration" so
that any new modules created will have the Option Explicit statement
automatically at the top. Modules already existing will need to have
the line added manually
Another handy thing in declaring variables is that, if Access recognizes
the variable name you have typed as a variable that has been
dimensioned, it will change the case for you after you move off the line.
mstr --> mStr
When you compile your code, if any variable names are used that are
different than what was dimensioned, or is the wrong data type, Access
will complain -- one less problem to track down at Run-Time...
***
~~~~~~~~~~~~~~~~~~~~~~~`
anyway, the reason Debug.Print was done was to test the SQL of what is
being generated.
when the code came to the STOP statement, it stops and that statement is
highlighted in yellow
Because we don't want to leave code hanging open, Reset the code so you
are no longer in execution mode
--> from the menu --> Run, Reset
1. press CTRL-G to show the debuG window, if it is not displayed.
2. highlight the SQL that was generated and CUT (Ctrl-X)
3. make a New Query
4. go to the SQL view and paste the SQL statement that was generated.
Now, click on the Design View button of the Query.
Best fit all the columns on the grid -- or make each wide enough to show
the contents.
Notice how Access has added 'Expr1:' to one of your columns -- that is
because you have specified a fieldname -- RACA_Agr_Num -- that does not
exist...
here is what the column says:
Expr1: tblAgreements.RACA_Agr_Num
you DO, however, have a field called 'Agr_Num' and this is what I assume
you mean
this line of code:
& ", tblAgreements.RACA_Agr_Num " _
is changed to this -->
& ", tblAgreements.Agr_Num " _
also, you have not given the SQL criteria for the record that is
displayed ... don't you want JUST the person shown on the form to get
email? Or the Agreement shown? Or other criteria (such as
'Date_Action_Reminder >= (Date()-1)' ... and how do you plan to make the
records that are resolved?
To make the SQL easier to read, it is good use ALIASES for your
tablenames...
FROM tblUSers AS U ...tblAgreements AS A
'~~~~~~~~~~~~~~
Private Sub cmdSendEmail_Click()
Dim strSQL As String
'save record if changes have been made
If Me.Dirty Then Me.Dirty = False
strSQL = "SELECT U.User_FName AS UserName" _
& ", A.Agr_Num" _
& ", A.Action_Item" _
& ", A.Date_Action_Reminder" _
& ", A.Date_Action_Required" _
& ", U.Team" _
& ", U.Email" _
& " FROM tblUSers AS U " _
& " INNER JOIN tblAgreements AS A " _
& " ON U.User_ID = A.UserID "
Debug.Print strSQL
Stop
'---- add criteria if a current record is displayed
' If Not Me.NewRecord Then
'... but wait, we can't -- we need to examine the
'SQL that is constructed and see which field is also on the form
'need to do some checking on structures before we can finish
' LoopAgmtsSendEmail strSQL
End Sub
'~~~~~~~~~~~~~~
Background:
tblUsers has UserID autonumber primary key
tblAgreements has AgrID autonumber primary key
tblAgreements has UserID as a foreign key to the parent table
Gus, is this form going to used for data entry or changes? I see that
the recordSource for your form is a query that gets its information from
the parent and child tables...
If you will add and change data here, you need to set this up as a
main/subform -- or just a main form that DISPLAYS data from Users and
allows modifications to Agreements.
At any rate, UserID needs to be added to the RecordSet to best identify
the user that the output will be filtered for.
If you are just going to send Agreements (even though some users may get
2 of them), then AgrID should be added to the form RecordSet (since it
is the Primary Key) so you can capture it.
It is a good idea to make sure the ID fields for each table you are
getting data from are also in the RecordSet (Query) -- User_Id and AgrID
in this case.
'~~~~~~~~~~~~~~~~~
as for limiting by date...
Would it not be more wise to create a table for items to follow-up on
Agreements something like this:
*Followup*
FupID, autonumber
AgrID, long integer, DefaultValue --> null -- FK to Agreements
FolTypID, long integer, DefaultValue --> null -- FK to FollowTypes
DatePlan, date
DateDone, date
and then if there are more field such as who it is assigned to, etc ...
depends how much you want to track
you now have a way to see if the item can drop off reports -- If
DateDone is filled. You also have a way to set up differnt types of
actions ... lunch, meeting, call, ...
*FollowTypes*
FolTypID, autonumber
FollowType, text --> Reminder, Required, etc
maybe also some kind of category for this followup type
~~~
It seems you have 2 date fields -- each for a different type of followup
in your Agreements table. You need to re-think this ... are you going
to add a field everytime you need to set up another type of contact?
Wouldn't it be nice to see a report by date regardless of what needs to
be done?
Until the data structure is solid, there is no point to discuss how to
make the forms work
~~~~
now that you have absorbed more information about structuring data, do this:
1. Print your relationship diagram (instructions in the 30-page doc for
good layout)
2. make a pot of hot tea
3. get a clipboard, paper, pencils, pen, marker, highlighter -- whatever
4. now get comfy in a nice chair -- sit for a few hours, think, and make
notes.
The time you invest now into making the foundation of your database
strong will more than pay for itself in the future.
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
