Results 1 to 4 of 4

Thread: Sql Error...

  1. #1
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74

    Unanswered: Sql Error...

    Checking box gives error on first sql line, unchecking gives error on second sql line. Any help please....

    Additional Information:
    > The form "frmNotesView" is bound to another table "tblNotes" where the check box is.
    > The form "frmNotesView" has a subform "tblTmpRecipients subform"
    There are no link between main form and subform, why, b/c
    All I am doing populating a temp table with e-mail address for this session
    so end user can send a particular mail to all the recipients at the same time
    > tbltmpRecipients is the table with only one field "TotalRecipients".
    Form "tblTmpRecipients subform" has one text box tie to TotalRecipents,
    Name of the text box is "txtRecipients"

    ================================================== ============
    Private Sub chkDM_Click()

    Dim varDM As Variant
    varDM = Me.txtDLDMemail

    If Me.chkDM.Value = -1 Then
    DoCmd.RunSQL "INSERT INTO tbltmpRecipients.TotalRecipients SELECT " & varDM & " AS txtRecipients;"
    ElseIf Me.chkDM.Value = 0 Then
    DoCmd.RunSQL "DELETE tbltmpRecipients.TotalRecipients FROM tbltmpRecipients WHERE (((tbltmpRecipients.totalRecipients)=" & varDM & "));"
    End If

    Me.tblTmpRecipientsSubform.Requery

    End Sub

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Test each of your SQL statements in the QBE ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74

    I got help from UtterAccess

    'New Working Version of the Code
    Dim varDM As Variant
    varDM = Me.txtDLDMemail

    If Me.chkDM.Value = -1 Then
    DoCmd.RunSQL "INSERT INTO tblTmpRecipients ( TotalRecipients ) SELECT " & " """ & varDM & " "" AS txtRecipient ;"
    ElseIf Me.chkDM.Value = 0 Then
    DoCmd.RunSQL "DELETE tbltmpRecipients.TotalRecipients FROM tbltmpRecipients WHERE (((tbltmpRecipients.totalRecipients)=" & " """ & varDM & """));"
    End If

    Me.tblTmpRecipientsSubform.Requery
    Last edited by doran_doran; 08-06-04 at 09:22.

  4. #4
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74

    Get e-mail addresses from table seperated by comma

    Ok, I can gather all the e-mail addresses in my temporary table. Showing in the subform on a main form and they are not tie (form and subform).

    How can I get all the e-mail addresses together seperated by comma before hitting "Send Mail" button.

    Thanks


    My Lotus Note Send e-Mail Code which works fine for one e-mail address:
    =====================
    Public Sub SendNotesMail()

    Dim Subject As String, Recipient As String, BodyText As String, SaveIt As Boolean

    'Set up the objects required for Automation into lotus notes
    Dim Maildb As Object 'The mail database
    Dim UserName As String 'The current users notes name
    Dim MailDbName As String 'THe current users notes mail database name
    Dim MailDoc As Object 'The mail document itself
    'The next line if for attachment
    'Dim AttachME As Object 'The attachment richtextfile object
    Dim Session As Object 'The notes session
    Dim EmbedObj As Object 'The embedded object (Attachment)

    'Start a session to notes
    Set Session = CreateObject("Notes.NotesSession")

    'Define recipient, body test, subject
    Recipient = Me.StrRecipient ' strRecipient is a text box on my main form
    BodyText = Me.strOriginalPassword
    Subject = "Your Password"


    'Next line only works with 5.x and above. Replace password with your password
    'Session.Initialize

    'Get the sessions username and then calculate the mail file name
    'You may or may not need this as for MailDBname with some systems you
    'can pass an empty string or using above password you can use other mailboxes.

    If Not fOSUserName = Me.txtUser Then
    MsgBox "You can only request password when you logged onto Windows NT with you AIG Valic ID"
    Exit Sub
    Else

    UserName = Session.UserName
    MailDbName = left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"

    'Open the mail database in notes
    Set Maildb = Session.GetDatabase("", MailDbName)
    If Maildb.IsOpen = True Then
    'Already open for mail
    Else
    Maildb.OPENMAIL
    End If

    'Set up the new mail document
    Set MailDoc = Maildb.CreateDocument
    MailDoc.Form = "Memo"
    MailDoc.sendto = Recipient
    MailDoc.Subject = Subject
    MailDoc.Body = BodyText
    MailDoc.SaveMessageOnSend = SaveIt

    'Set up the embedded object and attachment and attach it
    'If Attachment <> "" Then
    ' Set AttachME = MailDoc.CreateRichTextItem("Attachment")
    ' Set EmbedObj = AttachME.EmbedObject(1454, "", Attachment, "Attachment")
    ' MailDoc.CreateRichTextItem ("Attachment")
    'End If

    'Send the document
    MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
    MailDoc.Send 0, Recipient

    MsgBox "Your password has been sent. However, You may have to grant Plan Manager when prompted by Lotus Notes."

    'Clean Up
    Set Maildb = Nothing
    Set MailDoc = Nothing
    'Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj = Nothing

    End If

    End Sub

Posting Permissions

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