"mike" <m***@discussions.microsoft.com> wrote in message
news:F7BCF31E-5D00-4691-BB6F-6B3D7C74C261@microsoft.com...
> Hello. I'm trying to design an MS Access form that will fire off an email
> when a combo-box is changed. I need the email to consist of a brief
> message
> as well as the results of a query.
>
> Since both the view and the email message need to be tailored to the
> selection, I've written code to delete the existing view and proc and
> recreate them with the new data. I receive the email as long as I don't
> attach the view. When I attach the view, I get nothing -- no email, no
> error
> message, no error in the SQL log -- and since I can't figure out what the
> problem is I can't design a solution.
>
> Here's the code from the Access form:
>
> *********************************************
> *********************************************
> Private Sub Status_Level_AfterUpdate()
> Dim strSQL As String, strMsg As String
>
> 'create query for attachment
>        strSQL = "IF EXISTS (SELECT [name] FROM sysobjects WHERE [name] =
> 'vwStatusChange' AND [type] = 'V') DROP VIEW vwStatusChange"
>        DoCmd.RunSQL strSQL
>        strSQL = "SELECT [Client Number], [Current Status] AS [Client
> Number
> Status] FROM [Client Numbers] WHERE [Advertiser ID] = " & Me.[Advertiser
> ID]
>        DoCmd.RunSQL "CREATE VIEW vwStatusChange AS " & strSQL
>
> 'create stored proc to send email
>        strSQL = "IF EXISTS (SELECT [name] FROM sysobjects WHERE [name] =
> 'sp_SendStatusChangeEmail' AND [type] = 'P') DROP PROC
> sp_SendStatusChangeEmail"
>        DoCmd.RunSQL strSQL
>        strMsg = "'" & Me.Advertiser_Name & " has changed to status " &
> Me.Status_Level & " effective " & Me.Status_Date & "." & _
>                 " Please update the Client Number status accordingly.'"
>
>        strSQL = "CREATE PROC sp_SendStatusChangeEmail AS EXEC
> master..xp_sendmail @recipients = 'MIKE', @subject = 'Status Change', " &
> _
>                 "@message = " & strMsg & ", @query =
> 'dev..vwStatusChange'"
>        DoCmd.RunSQL strSQL
>
>        DoCmd.SetWarnings False
>        DoCmd.OpenStoredProcedure "sp_SendStatusChangeEmail"
>        DoCmd.SetWarnings True
>
> End Sub
>
> *********************************************
> *********************************************
>
> One last question: If possible, I'd like to get around hard-coding the
> server name (@query = dev..) and recipient names (@recipients = 'MIKE') in
> the proc. Can I do that in SQL, or should I use Access for that task?
>

"John 3:16" wrote:

> Mike,
> I typically use stored procs for the @query = param.
> Try creating an sp that returns the recs in the view.
>
> hth,
> bob mcclellan
>
> "mike" <m***@discussions.microsoft.com> wrote in message
> news:F7BCF31E-5D00-4691-BB6F-6B3D7C74C261@microsoft.com...
> > Hello. I'm trying to design an MS Access form that will fire off an email
> > when a combo-box is changed. I need the email to consist of a brief
> > message
> > as well as the results of a query.
> >
> > Since both the view and the email message need to be tailored to the
> > selection, I've written code to delete the existing view and proc and
> > recreate them with the new data. I receive the email as long as I don't
> > attach the view. When I attach the view, I get nothing -- no email, no
> > error
> > message, no error in the SQL log -- and since I can't figure out what the
> > problem is I can't design a solution.
> >
> > Here's the code from the Access form:
> >
> > *********************************************
> > *********************************************
> > Private Sub Status_Level_AfterUpdate()
> > Dim strSQL As String, strMsg As String
> >
> > 'create query for attachment
> >        strSQL = "IF EXISTS (SELECT [name] FROM sysobjects WHERE [name] =
> > 'vwStatusChange' AND [type] = 'V') DROP VIEW vwStatusChange"
> >        DoCmd.RunSQL strSQL
> >        strSQL = "SELECT [Client Number], [Current Status] AS [Client
> > Number
> > Status] FROM [Client Numbers] WHERE [Advertiser ID] = " & Me.[Advertiser
> > ID]
> >        DoCmd.RunSQL "CREATE VIEW vwStatusChange AS " & strSQL
> >
> > 'create stored proc to send email
> >        strSQL = "IF EXISTS (SELECT [name] FROM sysobjects WHERE [name] =
> > 'sp_SendStatusChangeEmail' AND [type] = 'P') DROP PROC
> > sp_SendStatusChangeEmail"
> >        DoCmd.RunSQL strSQL
> >        strMsg = "'" & Me.Advertiser_Name & " has changed to status " &
> > Me.Status_Level & " effective " & Me.Status_Date & "." & _
> >                 " Please update the Client Number status accordingly.'"
> >
> >        strSQL = "CREATE PROC sp_SendStatusChangeEmail AS EXEC
> > master..xp_sendmail @recipients = 'MIKE', @subject = 'Status Change', " &
> > _
> >                 "@message = " & strMsg & ", @query =
> > 'dev..vwStatusChange'"
> >        DoCmd.RunSQL strSQL
> >
> >        DoCmd.SetWarnings False
> >        DoCmd.OpenStoredProcedure "sp_SendStatusChangeEmail"
> >        DoCmd.SetWarnings True
> >
> > End Sub
> >
> > *********************************************
> > *********************************************
> >
> > One last question: If possible, I'd like to get around hard-coding the
> > server name (@query = dev..) and recipient names (@recipients = 'MIKE') in
> > the proc. Can I do that in SQL, or should I use Access for that task?
> >
>
>
>