none
I want run an access query several times with names input from a table? RRS feed

  • Question

  • I just wonder what kind of command to use? I want run a query several times by using the names of the table "email_contact_" and included the customized results in each generated email.  The [contact name] exist in a query "products linked", so with want kind of command can I run the query "products linked" for each name in the table "email_contact_"?

     I was thinking of including "Dlookup" in combination with DoCmd.OutputTo acOutputQuery, "", "Excel97-Excel2003Workbook(*.xls)", "Template", False, "template", , acExportQualityPrint  any suggestion here? 

    The working script is below, but not sure on the command to use?

    Dim olApp As Outlook.Application

    Dim toMulti As String

    Dim mItem As Outlook.MailItem  ' An Outlook Mail item

    Set olApp = CreateObject("Outlook.Application")

    Set mItem = olApp.CreateItem(olMailItem)

    Dim rs  As Recordset

       Set rs = CurrentDb.OpenRecordset("email_contact_")

       If rs.RecordCount > 0 Then

       rs.MoveFirst

    Do Until rs.EOF

    With mItem

              .BodyFormat = olFormatHTML

              toMulti = rs![Contact name]

              .To = toMulti

              .CC = ""

              .Subject = "Product template date - (" & WeekdayName(day) & " - " & Date & ")"

                 strbody = "Hi All,<br><br>" & _

                        "<B>Please check out the new simplified template <font face=""Times New Roman"" size=""3"" color=""blue""><I>'Template.xlsx'</I></Font> for International </B>" & _

                        "<br>" & _

                        "<br>" & _

                        "<B>Explanation file 'Template.xlsx':</B><br>" & _

                        "<br>" & _

                        "Regards,<br>" & _

                        "<br>" & _

                        "John"

              .HTMLBody = strbody & "<br>" & .HTMLBody

              .Display

              .Attachments.Add ("C:\Users\ Documents\Data-base\ \Template.xlsx")

      End With

        rs.MoveNext

    Loop

        Else

              MsgBox "No email address!"

        End If

        olApp.Quit

         Set olApp = Nothing

        Exit Sub

    End Sub

     


    Anri

    Friday, April 15, 2016 6:22 PM

Answers


  • Hi Imb,

    Excellent !!

     

    I played around and it working now.  The script below will create for every name in a list an email and include the results of a query based on the selected name.   The part below is really case and space sensitive. I am surprised that code is so unreliable?   On the internet it's hard to find good examples on error 3131.

     

    qdfTemp.SQL = " SELECT * FROM  Identified_name " _

                             & " WHERE [Name product manager] = " & As_text(waarde) _

                             & " ORDER BY [email product manager]"

     

     

    Below a working script

     

    Private Sub Command50_Click()

    Dim day As Integer

    day = Weekday(Date, vbSunday)

    Dim olApp As Outlook.Application

    Dim toMulti, waarde As String

    Dim mItem As Outlook.MailItem  ' An Outlook Mail item

    '********************************************************************************* command B38

    Dim dbs As Database

    Dim qdfTemp As QueryDef

    Dim qdfNew As QueryDef

    Dim originalSql As String

    Dim Identified_name As Recordset

    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb

    '********************************************************************************* command E38

    Set olApp = CreateObject("Outlook.Application")

    Set mItem = olApp.CreateItem(olMailItem)

    Dim rs  As Recordset

       Set rs = CurrentDb.OpenRecordset("Q200_email_contact")  ‘ Get name for the email distro

       If rs.RecordCount > 0 Then

       rs.MoveFirst

       Do Until rs.EOF

          With mItem

           'Set olApp = CreateObject("Outlook.Application")

              Set mItem = olApp.CreateItem(olMailItem)

              .BodyFormat = olFormatHTML

              toMulti = rs![Name product manager]   ‘ Find name in column name product manager

              waarde = toMulti

    '********************************************************************************* command B38

              For Each qdf In dbs.QueryDefs

               If qdf.Name = "test" Then

                   dbs.QueryDefs.Delete "test"

                   Exit For

               End If

              Next

              Set qdfTemp = dbs.CreateQueryDef("test")

               With dbs

                  'Run query on selected Name product manager

                 qdfTemp.SQL = " SELECT * FROM  Identified_name " _

                             & " WHERE [Name product manager] = " & As_text(waarde) _

                             & " ORDER BY [email product manager]"

                 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "test", "C:\Users\john.peacock\Documents\Data-base\Test.xlsx", True, "Data"

            End With

    '********************************************************************************* Command E38

              .To = toMulti

              MsgBox toMulti

              .CC = ""

              .Subject = "Wireless providers date - (" & WeekdayName(day) & " - " & Date & ")"

                 strbody = "Hi All,<br><br>" & _

                        "<B>Please check out the new simplified template <font face=""Times New Roman"" size=""3"" color=""blue""><I>'Template_PTT.xlsx'</I></Font> for International </B>" & _

                        "<br>" & _

                        "<br>" & _

                        "<B>Explanation file 'Template_PTT.xlsx':</B><br>" & _

                        "<br>" & _

                        "<br>" & _

                        "Regards,<br>" & _

                        "<br>" & _

                        "John"

              .HTMLBody = strbody & "<br>" & .HTMLBody

              .Display

             

              .Attachments.Add ("C:\Users\John.peacock\Documents\Data-base\ Test.xlsx")

      End With

        rs.MoveNext

    Loop

        Else

              MsgBox "No email address!"

        End If

        olApp.Quit

         Set olApp = Nothing

        Exit Sub

    End Sub


    Anri

    • Marked as answer by Anri2018 Monday, May 2, 2016 8:46 AM
    Monday, May 2, 2016 8:45 AM

All replies

  • Hi Anri. What kind of a query is "products linked" and why do you want to "run" it? Can you tell us what you're trying to accomplish? Thanks.
    Friday, April 15, 2016 7:07 PM
  • The complete script above is opening emails for every contact name in the table "email_contact_". After reviewing every generated email, I can confirm it by sending it or delete it. The same "contact name" field is also available in an access query ""products linked", as it contains the field "contact name".   So currently I am generating for every contact in the table "email_contact_" a email, but I want to include as well in an excel file a custumized query results. The query results contains for every contact only those products that are managed by the same "contact name", using the  "contact name" field as a filter value in  query ""products linked".

    Anri

    Friday, April 15, 2016 7:49 PM
  • Hi, Anri2018

    According to your description, please correct me if I have any misunderstandings on your question, you coudl use Database.CreateQueryDef method to creates a new QueryDef object, or set the SQL statement that defines the query executed by a QueryDef object.

    For more information, click here to refer about Database.CreateQueryDef Method and here to refer about QueryDef.SQL Property (DAO)

    In addition you can use tables and other select queries as data sources for a select query, so you also could use the TransferSpreadsheet method to export data between the current Access database or Access project (.adp) and a spreadsheet file. 

    For more information, click here to refer about DoCmd.TransferSpreadsheet Method (Access)

    Monday, April 18, 2016 5:02 AM
  • Good suggestion !!  Let me work this out first.


    Anri

    Monday, April 18, 2016 8:44 AM
  • Hi All,

     

    I have created the script for testing so later I can implement the logic in the email script within the do until logic. The script below that is linked to a button in access 2013 should filter out in query “Identified_name“  the product managers that have currently no name but  the value ‘No Std products available' in the field. Sub 38 is working but with debugging it’s complaining about the function command “qdfTemp.SQL = strSQL” with the warning “Syntax From Clause error in 3131” ??  Could the spaces in field name “Name product Manager” be the problem? Should I change it to “Name_product_Manager”??

     

    Private Sub Command38_Click()

       Dim dbs As Database

       Dim qdfTemp As QueryDef

       Dim qdfNew As QueryDef

       Dim Identified_name As Recordset

       Set dbs = CurrentDb

          ' Create temporary QueryDef.

       Set qdfTemp = dbs.CreateQueryDef("")

       With dbs

            'Run query on selected Name product Manager with value 'No Std products available'

            SQLOutput "SELECT * FROM Name product Manager " & _

            "WHERE Name product Manager = 'No Std products available' " & _

            "ORDER BY email product manager", qdfTemp

                    ' Create permanent QueryDef.

            Set qdfNew = .CreateQueryDef("NewQueryDef")

            SQLOutput "SELECT * FROM Categories", qdfNew

                    'Move query results to Permanent Query

                      'Save temporary Query results on hard drive

            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qdfNew ", "C:\Users\john peacock\Documents\Data-base\test.xlsx", True, “ “

            .QueryDefs.Delete qdfNew.Name

            .Close

      End With

    End Sub

     

     

    Function SQLOutput(strSQL As String, qdfTemp As QueryDef)

      Dim Identified_name As Recordset

      qdfTemp.SQL = strSQL

       Set Identified_name = qdfTemp.OpenRecordset

         With Identified_name

          Do While Not .EOF

              'MsgBox "Do you like blue color?", 1

             .MoveNext

          Loop

          .Close

       End With

    End Function


    Anri

    Monday, April 18, 2016 2:01 PM
  • >>>Sub 38 is working but with debugging it’s complaining about the function command “qdfTemp.SQL = strSQL” with the warning “Syntax From Clause error in 3131” ??  Could the spaces in field name “Name product Manager” be the problem? Should I change it to “Name_product_Manager”??<<<
     
    According to your description, you could wrap the table in brackets instead: [Name product Manager], otherwise I suggest that you could make sure whether your select query is correct with SQL View. 
    Tuesday, April 19, 2016 6:32 AM
  • The scripts below are giving no warnings, but I have still difficulties to workout the logic to filter out from existing query " Identified_name " only those records that shows for products manager name 'Francesco' and include the results with the following fields : [Name product Manager]  and  [email product manager]" in the excel file "test.xlsx" on the hard drive?  Any suggestion as the two field are the first two field in the access query " Identified_name "?

    Private Sub Command38_Click()

       Dim dbs As Database

       Dim qdfTemp As QueryDef

       Dim qdfNew As QueryDef

       Dim Identified_name As Recordset

       Set dbs = CurrentDb

          ' Create temporary QueryDef.

       Set qdfTemp = dbs.CreateQueryDef("")

       With dbs

            'Run query on selected Name product Manager with value 'No Std products available'

            SQLOutput "SELECT * FROM Identified_name " & _

            "WHERE [Name product Manager] = 'Francesco' " & _

            "ORDER BY [email product manager]", qdfTemp

            ' Create permanent QueryDef.

            On Error Resume Next

            CurrentDb.QueryDefs.Delete "qdfNew"

            Set qdfNew = .CreateQueryDef("NewQueryDef")

            SQLOutput "SELECT * FROM Identified_name", qdfNew

            'Move query results to Permanent Query

            'Save temporary Query results

            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qdfNew ", "C:\Users\John.peacock\Documents\Data-base\ test.xlsx", True, ""

            .QueryDefs.Delete qdfNew.Name

            .Close

      End With

    End Sub

     

     

    Function SQLOutput(strSQL As String, qdfTemp As QueryDef)

      Dim Identified_name As Recordset

       ' Set SQL property of temporary QueryDef object and open

       ' a Recordset.

       qdfTemp.SQL = strSQL

       Set Identified_name = qdfTemp.OpenRecordset

        With Identified_name

          ' Enumerate Recordset.

          Do While Not .EOF

     

               “Name product manager” <Not sure how I create the logic  to include this field from query “Identified_name” in permanent query “qdfNew” so it can be saved in the excel file test.xlsx?

               “email product manager” <Not sure how I create the logic  to include this field from query “Identified_name” in permanent query “qdfNew” so it can be saved in the excel file test.xlsx?

     

             .MoveNext

          Loop

          .Close

       End With

    End Function


    Anri

    Tuesday, April 19, 2016 9:56 AM
  • >>>The scripts below are giving no warnings, but I have still difficulties to workout the logic to filter out from existing query " Identified_name " only those records that shows for products manager name 'Francesco' and include the results with the following fields : [Name product Manager]  and  [email product manager]" in the excel file "test.xlsx" on the hard drive?  Any suggestion as the two field are the first two field in the access query " Identified_name "?<<<

    Sorry, I am not able to clear what is your logic. So I suggest that you could figure out your logic what it would do, or maybe provide screenshot. 

    Thanks for your understanding.
    Wednesday, April 20, 2016 9:46 AM
  • Step 1 Inthe first logic I am selecting from query [Identified_name], only  those records that have in the [Name product Manager] field  'Francesco'.

              SQLOutput "SELECT * FROM Identified_name " & _

            "WHERE [Name product Manager] = 'Francesco' " & _

            "ORDER BY [email product manager]", qdfTemp

    Step 2 Function SQLOutput - In this function I hope to workout the logic to show only for the identified records the linked  columns: “Name product manager” and “email product manager” from access uery [Identified_name]

    Layout query  [Identified_name]

    column A  “Name product manager”column B “email product manager”Column C etc

    Step 3 permanent query qdfNew. Not sure if this is even needed?  I thought this is the only way to export the results from step 2 to an excel file with the name  "test.xlsx"?

    I hope this will answer your questions


    Anri

    Wednesday, April 20, 2016 12:48 PM
  • Hi, Anri2018

    Thanks for you provide more details about your logic, acccording to your description, if I have no misunderstandings on your question, you could refer to below code:
    Dim originalSql As String
       
    originalSql = CurrentDb.QueryDefs("Identified_name").SQL
       
    CurrentDb.QueryDefs("Identified_name").SQL = "SELECT [Name product Manager],[email product manager] FROM TableName " & _
            "WHERE [Name product Manager] = 'Francesco' " & _
            "ORDER BY [email product manager]"
       
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Identified_name", "C:\Users\John.peacock\Documents\Data-base\test.xlsx", True
       
    CurrentDb.QueryDefs("Identified_name").SQL = originalSql


    Thursday, April 21, 2016 7:06 AM
  • Hi David,

    Not sure but maybe I make some wrong conclusions? I deleted the function and re-ordered the code see below.The script isn't giving me No warnings but isn't propulating the data in the excel template?  I expect that the commands "originalSql = "SELECT [Name product Manager],[email product manager] FROM Identified_name " & etc " will filter out the records based on the criteria [Name product Manager] = 'Francesco' and saves the result in the excel file "test.xlsx" or is this a wrong assumption as it has no Do Until finction included?

    Private Sub Command38_Click()
       Dim dbs As Database
       Dim qdfTemp As QueryDef
       Dim qdfNew As QueryDef
       Dim originalSql As String
       Dim Identified_name As Recordset
       Set dbs = CurrentDb
          ' Create temporary QueryDef.
         
      
       originalSql = CurrentDb.QueryDefs("Identified_name").SQL

       Set qdfTemp = dbs.CreateQueryDef("")
       With dbs
            originalSql = "SELECT [Name product Manager],[email product manager] FROM Identified_name " & _
            "WHERE [Name product Manager] = 'Francesco' " & _
            "ORDER BY [email product manager]"
            On Error Resume Next
            'Save temporary Query results
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "originalSql", "C:\Users\John.peacock\Documents\Data-base\test.xlsx", True, "Data"
        End With
    End Sub

     


    Anri

    Thursday, April 21, 2016 9:28 AM
  • Hi, Anri2018

    According to your description, if you comment "On Error Resume Next", you will get error. This issue is caused by DoCmd.TransferSpreadsheet have to need Query object name not SQL sentence,
    So I suggest that you could modify like below:
    Set qdfTemp = dbs.CreateQueryDef("Test")
    With dbs
         originalSql = "SELECT [Name product Manager],[email product manager] FROM Identified_name " & _
         "WHERE [Name product Manager] = 'Francesco' " & _
         "ORDER BY [email product manager]"
         On Error Resume Next
         'Save temporary Query results
         DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qdfTemp.Name, "C:\Users\John.peacock\Documents\Data-base\test.xlsx", True, "Data"
    End With
    Friday, April 22, 2016 6:53 AM
  • Hi David,

     

    I received all kind of warnings but solved a couple of them with setting “Set dbs =CurrentDb” and changing dbs.CreateQueryDef(“test”) to dbs.CreateQueryDef(test). The only warning I can’t solve is about

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qdfTemp.Name, "C:\Users\Johan.peacock\Documents\Data-base\Test.xlsx", True, "Data"  for some reason I get “Run time error “3011”  could not find object “Temporary QueryDef” ?  The path of the destination file and name is correct so it has to be the qdfTemp.Name?

     

     Below the full script how it's now

     

     

    Private Sub Command38_Click()

       Dim dbs As Database

       Dim qdfTemp As QueryDef

       Dim qdfNew As QueryDef

       Dim originalSql As String

       Dim Identified_name As Recordset

       Set dbs = CurrentDb

     

       Set qdfTemp = dbs.CreateQueryDef(test)

       With dbs

            'Run query on selected Name product Manager with value 'No Std products available'

            originalSql = "SELECT [Name product Manager],[email product manager] FROM Identified_name " & _

            "WHERE [Name product Manager] = 'Francesco' " & _

            "ORDER BY [email product manager]"

            'On Error Resume Next

            'Save temporary Query results

            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qdfTemp.Name, "C:\Users\Johan.peacock\Documents\Data-base\Test.xlsx", True, "Data"

        End With

    End Sub


    Anri

    Friday, April 22, 2016 8:58 AM
  • Hi, Anri2018

    According to your description, if you print out typename(test), you would get "Empty", it means that variable is uninitialized. So I suggest that you modify you could like below:

    Set qdfTemp = dbs.CreateQueryDef("test")
    ......
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qdfTemp.Name, "C:\Users\Johan.peacock\Documents\Data-base\Test.xlsx", True, "Data"

    since QueryDef'name is unique,

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "test", "C:\Users\Johan.peacock\Documents\Data-base\Test.xlsx", True, "Data"
    Monday, April 25, 2016 8:21 AM
  • Hi David,

     

    I have implemented the changes, but it continued to complain about test with the message ”Object test already exist”, so I added a delete rule in with “ QueryDefs.Delete.qdfTemp.Name”  and “ .Close” that fixed the warning but now it’s complaining about the following   See below

    This error occurs when an event has failed to run because the location of the logic for the event cannot be evaluated. For example, if the OnOpen property of a form is set to =[Field], this error occurs because a macro or event name is expected to run when the event occurs.

    Not sure what to do? If you compare query “ Identified_name” with just a table in has the values :

    column A Name product manager

    column B email product manager

    column C  City

    column D country

    column F etc

     

    The script how it looks now

     

    Private Sub Command38_Click()

       Dim dbs As Database

       Dim qdfTemp As QueryDef

       Dim qdfNew As QueryDef

       Dim originalSql As String

       Dim Identified_name As Recordset

       Set dbs = CurrentDb

       QueryDefs.Delete.qdfTemp.Name  <Added this as it’s complained about test that already exist?>

       .Close  <Added this as it’s complained about test that already exist?>

       Set qdfTemp = dbs.CreateQueryDef("test")

       With dbs

            'Run query on selected Name product manager with value 'No Std products available'

            originalSql = "SELECT [Name product manager],[email product manager] FROM Identified_name " & _

            "WHERE [Name product manager] = 'Francesco' " & _

            "ORDER BY [email product manager]"

            'On Error Resume Next

            'Save temporary Query results

            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "test", "C:\Users\John.peacock\Documents\Data-base\Test.xlsx", True, "Data"

        End With

    End Sub


    Anri

    Monday, April 25, 2016 11:06 AM
  • Hi, Anri2018

    According to your description, please correct me if I have any misunderstandings on your question, if you want to delete exists query, refer to below code:
    Dim qdf As DAO.QueryDef
     
    For Each qdf In dbs.QueryDefs
        If qdf.Name = "test" Then
           dbs.QueryDefs.Delete "test"
           Exit For
        End If
    Next

    Wednesday, April 27, 2016 7:26 AM
  • Hi David,

    The delete part is working fine. So currently I am deleting all temporarly access queries with the name "Test", after that I run a new query "test" and try to move the results to excel file "Test.xlsx".  But now it's complaining ""3066- Query must have at least one destination field." is the result of the following command: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "test", " C:\Users\John.peacock\Documents\Data-base\Test.xlsx ", True, "Data"?   The file is correct including the worksheet "data"?  Any suggestion on the loic why it's not moving the query results to the excel file?

    Private Sub Command38_Click()

       Dim dbs As Database

       Dim qdfTemp As QueryDef

       Dim qdfNew As QueryDef

       Dim originalSql As String

       Dim Identified_name As Recordset

       Dim qdf As DAO.QueryDef

       Set dbs = CurrentDb

       For Each qdf In dbs.QueryDefs

            If qdf.Name = "test" Then

                dbs.QueryDefs.Delete "test"

                Exit For

            End If

       Next

       'QueryDefs.Delete.qdfTemp.Name

       '.Close

       'Run query and move values to excel file

       Set qdfTemp = dbs.CreateQueryDef("test")

       With dbs

            'Run query on selected Name product manager with value 'No Std products available'

            originalSql = "SELECT [Name product manager],[email product manager] FROM Identified_name " & _

            "WHERE [Name product manager] = 'Francesco' " & _

            "ORDER BY [email product manager]"

            'On Error Resume Next

            'Save temporary Query results

            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "test", " C:\Users\John.peacock\Documents\Data-base\Test.xlsx ", True, "Data"

        End With

    End Sub


    Anri

    Thursday, April 28, 2016 7:33 AM
  • >>>But now it's complaining ""3066- Query must have at least one destination field." is the result of the following command: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "test", " C:\Users\John.peacock\Documents\Data-base\Test.xlsx ", True, "Data"?   The file is correct including the worksheet "data"?  Any suggestion on the loic why it's not moving the query results to the excel file?

    According to your description, I have reproduced this issue, you could modify like below:

    Private Sub Command38_Click()
       Dim dbs As Database
       Dim qdfTemp As QueryDef
       Dim qdfNew As QueryDef
       Dim originalSql As String
       Dim Identified_name As Recordset
       Dim qdf As DAO.QueryDef
       Set dbs = CurrentDb
       For Each qdf In dbs.QueryDefs
            If qdf.Name = "test" Then
                dbs.QueryDefs.Delete "test"
                Exit For
            End If
       Next
       'QueryDefs.Delete.qdfTemp.Name
       '.Close
       'Run query and move values to excel file
       Set qdfTemp = dbs.CreateQueryDef("test")
       With dbs
            'Run query on selected Name product manager with value 'No Std products available'
     qdfTemp.SQL= "SELECT [Name product manager],[email product manager] FROM Identified_name " & _
            "WHERE [Name product manager] = 'Francesco' " & _
            "ORDER BY [email product manager]"
            'On Error Resume Next
            'Save temporary Query results
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "test", " C:\Users\John.peacock\Documents\Data-base\Test.xlsx ", True, "Data"
        End With
    End Sub


    Thursday, April 28, 2016 8:31 AM
  • Hi David,

    Excellent !!!

    The logic works now and I have implemented it in the complete logic see below. The only problem I have is that the logic below works according to the following steps

    Step1 Create for every naam in the list a email  <Works>

    Step 2 Run a query on name and past results in test.xlsx file < Works but it's populating the results of all names in the file, I expected that it would overwrite the file with the results of the next name?? >

    Step 3 Attach the file in the email <Works>

    See complete logic below

    Private Sub Command50_Click()

    Dim day As Integer

    day = Weekday(Date, vbSunday)

    Dim olApp As Outlook.Application

    Dim toMulti As String

    Dim mItem As Outlook.MailItem  ' An Outlook Mail item

    '********************************************************************************* command B38

    Dim dbs As Database

    Dim qdfTemp As QueryDef

    Dim qdfNew As QueryDef

    Dim originalSql As String

    Dim Identified_name As Recordset

    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb

    '********************************************************************************* command E38

    Set olApp = CreateObject("Outlook.Application")

    Set mItem = olApp.CreateItem(olMailItem)

    Dim rs  As Recordset

       Set rs = CurrentDb.OpenRecordset("Q200_email_contact")

       If rs.RecordCount > 0 Then

       rs.MoveFirst

    Do Until rs.EOF

    With mItem

           'Set olApp = CreateObject("Outlook.Application")

           Set mItem = olApp.CreateItem(olMailItem)

              .BodyFormat = olFormatHTML

              toMulti = rs![Name product manager]

    '********************************************************************************* command B38

              For Each qdf In dbs.QueryDefs

               If qdf.Name = "test" Then

                   dbs.QueryDefs.Delete "test"

                   Exit For

               End If

              Next

              Set qdfTemp = dbs.CreateQueryDef("test")

               With dbs

                  'Run query on selected Name product manager with value 'No Std products available'

                qdfTemp.SQL = "SELECT  *  FROM Identified_name " & _

                "WHERE [Name product manager] " & _

                "ORDER BY [email product manager]"

            'On Error Resume Next

            'Save temporary Query results

                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "test", "C:\Users\anri.koekoek\Documents\Data-base\Check  eCOST_F&E_eASR_CM-carrier-map\Test.xlsx", True, "Data"

               End With

    '********************************************************************************* Command E38

              .To = toMulti

              .CC = ""

              .Subject = "Wireless providers date - (" & WeekdayName(day) & " - " & Date & ")"

                 strbody = "Hi All,<br><br>" & _

                        "<B>Please check out the new product  template <font face=""Times New Roman"" size=""3"" color=""blue""><I>'Template_PTT.xlsx'</I></Font> for International defect</B>" & _

                        "<br>" & _

                        "<br>" & _

                        "<B>Explanation file test_PTT.xlsx':</B><br>" & _

                        "<br>" & _

                        "<br>" & _

                        "<br>" & _

                        "Regards,<br>" & _

                        "<br>" & _

                        "John"

              .HTMLBody = strbody & "<br>" & .HTMLBody

              .Display

               .Attachments.Add ("C:\Users\ John.Peacock \Documents\Data-base\ Test.xlsx")

      End With

        rs.MoveNext

    Loop

        Else

              MsgBox "No email address!"

        End If

        olApp.Quit

         Set olApp = Nothing

        Exit Sub

    End Sub


    Anri

    Thursday, April 28, 2016 12:48 PM
  • >>>< Works but it's populating the results of all names in the file, I expected that it would overwrite the file with the results of the next name?? >

    According to your desciption, please correct me if I have any misunderstandings on your question, you could use rs![Name product manager] as sheet's name when you export query to Excel file:
    'Save temporary Query results
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "test", "C:\Users\anri.koekoek\Documents\Data-base\Check  eCOST_F&E_eASR_CM-carrier-map\Test.xlsx", True, rs![Name product manager]
    >>>qdfTemp.SQL = "SELECT  *  FROM Identified_name " & _
                "WHERE [Name product manager] " & _
                "ORDER BY [email product manager]"<<<

    In addition I find that there are some mistake in your SQL query sentence, so I suggest that you could check your Sql sentence is correct, and I suggest that you could set breakpoint to debug your  VBA code.

    Thanks for your understanding.
    Friday, April 29, 2016 5:55 AM
  • Hi David,

    I am showing in the msgbox the name of the product manager with  “MsgBox waarde”. Later in the script I am asking in the qdfTemp.SQL to filter on the values “WHERE [Name carrier manager] = waarde, but for some reason it’s asking me all the time to add in a value for “Waarde” ?  So the value of “waarde” is lost when I use it in SQL = "SELECT  * statement.  The value waarde in know as it has been defined in as String.  So how should I define the value in the SQL statement as it's coming from outside the query?

     

    Below Parts of the script

     

    Dim toMulti, waarde As String

     

     

     

     

    With mItem

           'Set olApp = CreateObject("Outlook.Application")

              Set mItem = olApp.CreateItem(olMailItem)

              .BodyFormat = olFormatHTML

              toMulti = rs![Name product manager]

              MsgBox toMulti

              waarde = toMulti

              MsgBox waarde  <<Show still the each name >>

    '********************************************************************************* command B38

              For Each qdf In dbs.QueryDefs

               If qdf.Name = "test" Then

                   dbs.QueryDefs.Delete "test"

                   Exit For

               End If

              Next

              Set qdfTemp = dbs.CreateQueryDef("test")

               With dbs

                  'Run query on selected Name product manager with value 'No Std products available'

                qdfTemp.SQL = "SELECT  *  FROM Identified_name " & _

                "WHERE [Name product manager] = waarde " & _  < Here the value of “waarde” is lost?>>

                "ORDER BY [email product manager]"


    Anri

    Friday, April 29, 2016 12:28 PM
  •            With dbs

                  'Run query on selected Name product manager with value 'No Std products available'

                qdfTemp.SQL = "SELECT  *  FROM Identified_name " & _

                "WHERE [Name product manager] = waarde " & _  < Here the value of “waarde” is lost?>>

                "ORDER BY [email product manager]"

    Hi Anri,

    You have used "waarde" inside the quoted sql-string, so the literal value of "waarde" is used, and you will prompted for a value of "waarde".

    When you place "waarde" outside the quoted sql-stiring, the value of the variable is used":

                " WHERE [Name product manager] = " & As_text(waarde) & _

                  " ORDER BY [email product manager]"

    My preference is to add a space at the beginning of a line instead of at the end, for better recognition of the spaces.

    As "waarde" is of type String, it must be quoted in the sql-string. To distinguish from the overall double quotes you can use single quotes. The small function As_text surrounds the value of waarde with single quotes, And doubles single quotes the are part of the value.

    Imb.

    Friday, April 29, 2016 5:36 PM
  • Hi Imb,

    I tried the space at the beginning with <<_ &  at the beginning

      <<_ & qdfTemp.SQL = "SELECT  *  FROM Identified_name "  &

           _&  "WHERE [Name product manager] = " As_text (waarde)  &   

           _&    "ORDER BY [email product manager]"  

    Access 2013 don't accept it so I probably didn't understand what you mean "spaccee at the beginning? Other question "As_text" needs to be defined as  QueryDef?   I tried it as it's complaining with error 91 "Object variable not set"?

    regards

    Anri


    Anri

    Saturday, April 30, 2016 10:55 AM
  • Access 2013 don't accept it so I probably didn't understand what you mean "spaccee at the beginning? Other question "As_text" needs to be defined as  QueryDef?   I tried it as it's complaining with error 91 "Object variable not set"?

    Hi Anri,

    I am afraid you did not understand what I meant.

    This is what you had:

                qdfTemp.SQL = "SELECT  *  FROM Identified_name " & _

                "WHERE [Name product manager] = waarde " & _  < Here the value of “waarde” is lost?>>

                "ORDER BY [email product manager]"

    This is what I suggested:

                " WHERE [Name product manager] = " & As_text(waarde) & _

                  " ORDER BY [email product manager]"

    You can study the differences.

    Imb.

    Saturday, April 30, 2016 11:44 AM
  • Hi Imb,

    We are both on the same page with the coding .  I have used : 

    " WHERE [Name product manager] = " & As_text(waarde) & _

                  " ORDER BY [email product manager]"

    But it's still complaining about error 91 "Object variable not set" for As_text?  I thought it needed to be defined as "Dim As_text as QueryDef " as it's mentioned in the SQL logic or do I make big mistake? I have not a lot of experience with vba working more with Hummingbird commands.

    Regards,

    Anri


    Anri

    Saturday, April 30, 2016 2:53 PM
  • But it's still complaining about error 91 "Object variable not set" for As_text?  I thought it needed to be defined as "Dim As_text as QueryDef " as it's mentioned in the SQL logic or do I make big mistake? I have not a lot of experience with vba working more with Hummingbird commands.

    Hi Anri,

    "As_text" is a function that is placed in a general module.

    In its most simple form it looks like:

    Function As_text(cur_text As Variant) As String
      If (Not IsNull(cur_text)) Then
        As_text = "'" & Replace(cur_text, "'", "''") & "'"
      End If
    End Function
    

    You can extend this function further depending on your needs, e.g. add the logic in the case cur_text has a Null value.

    Imb.

    Saturday, April 30, 2016 3:59 PM
  • Hi Imb,
     

    It’s still complaining about run-time error 3131 "syntax error in FROM clause"? I tried [] around the Query “indentified_name” but without success? I think the commands are right so maybe somewhere else the commands are wrong?  

     

                 qdfTemp.SQL = " SELECT * FROM & Identified_name " & _

                 "WHERE [Name product manager] = " & As_text(waarde) & _

                 "ORDER BY [email product manager]"

     

     

    Below the full script including function

    Function As_text(cur_text As Variant) As String

      If (Not IsNull(cur_text)) Then

        As_text = "'" & Replace(cur_text, "'", "''") & "'"

      End If

    End Function

     

     

    '------------------------------------------------------------

    ' Command50_Click

    '

    '------------------------------------------------------------

    Private Sub Command50_Click()

    Dim day As Integer

    day = Weekday(Date, vbSunday)

    Dim olApp As Outlook.Application

    Dim toMulti, waarde As String

    Dim mItem As Outlook.MailItem  ' An Outlook Mail item

    '********************************************************************************* command B38

    Dim dbs As Database

    Dim qdfTemp As QueryDef

    Dim qdfNew As QueryDef

    Dim originalSql As String

    Dim Identified_name As Recordset

    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb

    '********************************************************************************* command E38

    Set olApp = CreateObject("Outlook.Application")

    Set mItem = olApp.CreateItem(olMailItem)

    Dim rs  As Recordset

       Set rs = CurrentDb.OpenRecordset("Q200_email_contact")

       If rs.RecordCount > 0 Then

       rs.MoveFirst

       Do Until rs.EOF

          With mItem

           'Set olApp = CreateObject("Outlook.Application")

              Set mItem = olApp.CreateItem(olMailItem)

              .BodyFormat = olFormatHTML

              toMulti = rs![Name product manager]

              MsgBox toMulti

              waarde = toMulti

              MsgBox waarde

    '********************************************************************************* command B38

              For Each qdf In dbs.QueryDefs

               If qdf.Name = "test" Then

                   dbs.QueryDefs.Delete "test"

                   Exit For

               End If

              Next

              Set qdfTemp = dbs.CreateQueryDef("test")

               With dbs

                  'Run query on selected Name carrier manager with value 'No Std products available'

                 qdfTemp.SQL = " SELECT * FROM & Identified_name " & _

                 "WHERE [Name product manager] = " & As_text(waarde) & _

                 "ORDER BY [email product manager]"

                'On Error Resume Next

                'MsgBox [Name product manager]

                'Save temporary Query results

                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "test", "C:\Users\John.peacock\Documents\Data-base\Test.xlsx", True, "Data"

            End With

    '********************************************************************************* Command E38

              .To = toMulti

              MsgBox toMulti

              .CC = ""

              .Subject = "Wireless providers date - (" & WeekdayName(day) & " - " & Date & ")"

                 strbody = "Hi All,<br><br>" & _

    "<B>Please check out the new product  template <font face=""Times New Roman"" size=""3"" color=""blue""><I>'Template_PTT.xlsx'</I></Font> for International </B>" & _                    "<br>" & _

                        "<br>" & _

                        "<B>Explanation file Test.xlsx':</B><br>" & _

                       "<br>" & _

                        "<br>" & _

                        "<br>" & _

                        "Regards,<br>" & _

                        "<br>" & _

                        "John"

              .HTMLBody = strbody & "<br>" & .HTMLBody

              .Display

              .Attachments.Add ("C:\Users\ John.peacock \Documents\Data-base\Test.xlsx")

      End With

        rs.MoveNext

    Loop

        Else

              MsgBox "No email address!"

        End If

        olApp.Quit

         Set olApp = Nothing

        Exit Sub

    End Sub


    Anri

    Sunday, May 1, 2016 11:27 AM
  •              qdfTemp.SQL = " SELECT * FROM & Identified_name " & _

                 "WHERE [Name product manager] = " & As_text(waarde) & _

                 "ORDER BY [email product manager]"

     

    Hi Anri,

    I did not read your whole script, but in the above sql-string I miss the space between As_text(waarde) and ORDER. Besides that I see an & behind FROM that is not used correct.

    That is why I prefer to write the sql-string:

    qdfTemp.SQL = "SELECT * FROM & Identified_name" _ & " WHERE [Name product manager] = " & As_text(waarde) _ & " ORDER BY [email product manager]"

    where spaces and ampersands are more "readable".

    Imb.

    Sunday, May 1, 2016 9:12 PM
  • Hi Imb,

    No change after changing the format to:

    qdfTemp.SQL = " SELECT * FROM & Identified_name" _
                            & " WHERE [Name carrier manager] = " & As_text(waarde) _
                            & " ORDER BY [email carrier manager]"

    Any other suggestions for the code?

    Regards,

    Anri


    Anri

    Monday, May 2, 2016 8:14 AM
  • Hi Imb,

    No change after changing the format to:

    qdfTemp.SQL = " SELECT * FROM & Identified_name" _
                            & " WHERE [Name carrier manager] = " & As_text(waarde) _
                            & " ORDER BY [email carrier manager]"

    Any other suggestions for the code?

    Hi Anri,

    You still have that "strange" & after FROM!

    Imb.

    Monday, May 2, 2016 8:23 AM

  • Hi Imb,

    Excellent !!

     

    I played around and it working now.  The script below will create for every name in a list an email and include the results of a query based on the selected name.   The part below is really case and space sensitive. I am surprised that code is so unreliable?   On the internet it's hard to find good examples on error 3131.

     

    qdfTemp.SQL = " SELECT * FROM  Identified_name " _

                             & " WHERE [Name product manager] = " & As_text(waarde) _

                             & " ORDER BY [email product manager]"

     

     

    Below a working script

     

    Private Sub Command50_Click()

    Dim day As Integer

    day = Weekday(Date, vbSunday)

    Dim olApp As Outlook.Application

    Dim toMulti, waarde As String

    Dim mItem As Outlook.MailItem  ' An Outlook Mail item

    '********************************************************************************* command B38

    Dim dbs As Database

    Dim qdfTemp As QueryDef

    Dim qdfNew As QueryDef

    Dim originalSql As String

    Dim Identified_name As Recordset

    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb

    '********************************************************************************* command E38

    Set olApp = CreateObject("Outlook.Application")

    Set mItem = olApp.CreateItem(olMailItem)

    Dim rs  As Recordset

       Set rs = CurrentDb.OpenRecordset("Q200_email_contact")  ‘ Get name for the email distro

       If rs.RecordCount > 0 Then

       rs.MoveFirst

       Do Until rs.EOF

          With mItem

           'Set olApp = CreateObject("Outlook.Application")

              Set mItem = olApp.CreateItem(olMailItem)

              .BodyFormat = olFormatHTML

              toMulti = rs![Name product manager]   ‘ Find name in column name product manager

              waarde = toMulti

    '********************************************************************************* command B38

              For Each qdf In dbs.QueryDefs

               If qdf.Name = "test" Then

                   dbs.QueryDefs.Delete "test"

                   Exit For

               End If

              Next

              Set qdfTemp = dbs.CreateQueryDef("test")

               With dbs

                  'Run query on selected Name product manager

                 qdfTemp.SQL = " SELECT * FROM  Identified_name " _

                             & " WHERE [Name product manager] = " & As_text(waarde) _

                             & " ORDER BY [email product manager]"

                 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "test", "C:\Users\john.peacock\Documents\Data-base\Test.xlsx", True, "Data"

            End With

    '********************************************************************************* Command E38

              .To = toMulti

              MsgBox toMulti

              .CC = ""

              .Subject = "Wireless providers date - (" & WeekdayName(day) & " - " & Date & ")"

                 strbody = "Hi All,<br><br>" & _

                        "<B>Please check out the new simplified template <font face=""Times New Roman"" size=""3"" color=""blue""><I>'Template_PTT.xlsx'</I></Font> for International </B>" & _

                        "<br>" & _

                        "<br>" & _

                        "<B>Explanation file 'Template_PTT.xlsx':</B><br>" & _

                        "<br>" & _

                        "<br>" & _

                        "Regards,<br>" & _

                        "<br>" & _

                        "John"

              .HTMLBody = strbody & "<br>" & .HTMLBody

              .Display

             

              .Attachments.Add ("C:\Users\John.peacock\Documents\Data-base\ Test.xlsx")

      End With

        rs.MoveNext

    Loop

        Else

              MsgBox "No email address!"

        End If

        olApp.Quit

         Set olApp = Nothing

        Exit Sub

    End Sub


    Anri

    • Marked as answer by Anri2018 Monday, May 2, 2016 8:46 AM
    Monday, May 2, 2016 8:45 AM
  • Hi, Anri2018

    According to your description, I suggest that you could set breakpoint to get SQL query sentence then copy and run it with SQL View to make sure that SQL sentence is correct.

    In addition you could refer about Building SQL Statements that Include Variables and Controls in Access 2007

    Monday, May 2, 2016 9:00 AM
  • hi David,

    Good suggestion it works fine.

    I have used "msgbox" to view the values, as access has not a tool similar to the "watch" screen in "hummingbird"? In the watch screen you can populate any variable from your script that you want to monitor step by step.

    Regards,

    Anri 


    Anri

    Monday, May 2, 2016 11:42 AM
  • I have used "msgbox" to view the values, as access has not a tool similar to the "watch" screen in "hummingbird"? In the watch screen you can populate any variable from your script that you want to monitor step by step.

    Hi Anri,

    When you are in the debug mode, you can hoover with the mouse cursor over the variable to see its momentary value.

    Some expressions are too complex to see immediately in this way. In those cases I add a new line in the code, where the complex expression assigns its value to a global variable that can be read by hoovering with the mouse cursor.

    Imb.

    Monday, May 2, 2016 12:21 PM