Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Posts
    2

    Unanswered: xp_sendmail, VB and variables

    There is a MainForm form in MS Access with ID field and other customer information. The form also has a button that activates Command_Click():

    -------------------------------------

    Private Sub Command_Click()
    On Error GoTo Command_Click_Err
    DoCmd.RunSQL "exec CustAccom @_id = 87220", 0
    Beep
    MsgBox "Done", vbInformation, "Done"
    Command_Click_Exit:
    Exit Sub
    Command_Click_Err:
    MsgBox Error$
    Resume Command_Click_Exit
    End Sub

    --------------------------------------

    Which runs CustAccom procedure:

    --------------------------------------

    Alter PROCEDURE CustAccom @_id varchar(15) AS
    DECLARE @_query VARCHAR (2000)
    SET @_query = 'SELECT ID, LastName, FirstName, HomePhone FROM Leads WHERE ID = ' + @_id
    exec master.dbo.xp_sendmail
    @recipients = 'someone@domain.com',
    @subject = 'test',
    @query = @_query,
    @no_header= 'TRUE',@width = 80,@dbuse = 'SQLServer'

    --------------------------------------

    I need a VB code that will take value of ID field from MainForm, and somehow pass it to CustAccom procedure or to "DoCmd.RunSQL "exec CustAccom @_id = 87220", 0" line.

    As you see from the example above, I was able to pass "87220". But I cannot make VB to take ID from the opened form.

    Greatly appreciate your help.

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    if you have ID called let's say MyID then it can look like this....

    DoCmd.RunSQL "exec CustAccom @_id = " & me.Controls("myID").Value, 0


    if you call it from function out of the form, then replace me with FORMS("NameOfTheForm")

    DoCmd.RunSQL "exec CustAccom @_id = " & FORMS("NameOfTheForm").Controls("myID").Value, 0

    there are other 'easier' calls like me!myID ,but let's keep full VB syntax




    jiri

  3. #3
    Join Date
    Feb 2003
    Posts
    2

    xp_sendmail, VB and variables

    -----------------------------------
    DoCmd.RunSQL "exec CustAccom @_id = " & me.Controls("myID").Value, 0
    -----------------------------------
    works. Thank you very much!

    Is there anything I could do to pass an additional value ("myPhoneNumber" for example) from the same MainForm form to the same CustAccom procedure?

    In other words:
    Can I pass two values ("myID" and " myPhoneNumber ") in the same "DoCmd.RunSQL "exec CustAccom ..." ?

    Thanks again!

  4. #4
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    your stored procedure has to be changed little bit...

    DoCmd.RunSQL "exec CustAccom @_id = " & me.Controls("myID").Value & ", @phone = " & me.Controls("myPhone").Value , 0


    generally, I like more ADO EXECUTE command instead of RunSQL.

    http://msdn.microsoft.com/library/de...cnnexecute.asp

    Last edited by playernovis; 02-06-03 at 22:08.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •