Using Query to Put Data in E-Mail Body (1 Viewer)

W

Wayne

Crazy Canuck
Local time
Today, 07:44
Joined
Nov 4, 2012
Messages
176
I'm trying to put multiple records of data from a subform in a single e-mail, and my Outlook calendar. I have a contracting business, and on my Orders form (which details a single client), I have a sub-form that lists all the items to be done at that client's home (a different record for each work item). When I send an e-mail, or post the appointment to the Outlook calendar, I am trying to list all those records in the body of the e-mail, and/or in the Notes section of the appointment. I created the query to call the records, but have not figured out a way to use it in the code. I have searched the forums for endless hours looking for the solution, but so far, no joy. Can anyone point me in the right direction?

Any help would be appreciated. Thank you.
 
MarkK

MarkK

bit cruncher
Local time
Today, 04:44
Joined
Mar 17, 2004
Messages
8,186
You need to open a recordset. The query is like a command that defines the data to select from one or more tables. The recordset uses the query to load the selected data into an in-memory dynamic navigable structure a bit like a fancy array. A recordset has a methods like MoveNext, MoveFirst, AddNew, Edit, Update, RecordCount, to name a few. Not all queries allow updates, etc...
Hope this helps,
 
W

Wayne

Crazy Canuck
Local time
Today, 07:44
Joined
Nov 4, 2012
Messages
176
Thanks Mark. Is there an existing thread somewhere in the archive that demonstrates the use of it?
 
MarkK

MarkK

bit cruncher
Local time
Today, 04:44
Joined
Mar 17, 2004
Messages
8,186
I would just run a search, on google or on here, for "open dao.recordset" This is such a common operation, it'll be ubiquitous, but here's an example of using a recordset to update a single record (if it exists) . . .
Code: 
   Dim rst As DAO.Recordset
   
   Set rst = dbs.OpenRecordset( _
      "SELECT Description, MarkUp, MaterialsID, FSCType " & _
      "FROM tMaterialClass " & _
      "WHERE mcID = " & Me.mcID)

   With rst
      If Not .EOF Then
         .Edit
         !Description = Me.Description
         !MarkUp = Me.MarkUp
         !MaterialsID = Me.cboCostTypeID
         !FSCType = Me.cboFSCType
         .Update
         .Close
      End If
   End With

And thanks for welcoming people to the forum. :)
 
W

Wayne

Crazy Canuck
Local time
Today, 07:44
Joined
Nov 4, 2012
Messages
176
Cool. Thanks. I'm going to play with it for a while and see what I can do. I really appreciate the help. Hopefully your Canucks can pull it out with Calgary. (At least your team made the playoffs!)

Wayne
 
W

Wayne

Crazy Canuck
Local time
Today, 07:44
Joined
Nov 4, 2012
Messages
176
Hi Mark,

I have tried the OpenRecordset suggestion. Here is my code:

Code: 
Private Sub btnCreateAppointment_Click()
On Error GoTo Err_btnCreateAppointment_Click
    'First Save the Current Record
    DoCmd.RunCommand acCmdSaveRecord
    
    'Exit the procedure if the appointment has already been added to the Outlook Calendar
    If Me.ApptAddedtoOutlook = True Then
        MsgBox "This appointment has already been added to the Outlook Calendar.", vbOKOnly, "Crate & Pack"
        Exit Sub
    Else
        Dim olObj As Outlook.Application
        Dim olAppt As Outlook.AppointmentItem
        Dim strLocation As String
        Dim strContact As String
        Dim strSQL As String
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
    
        Set olObj = CreateObject("Outlook.Application")
        Set olAppt = olObj.CreateItem(olAppointmentItem)
    
        If Not IsNull(Me.ClientAptNumber) Then
            strLocation = (Me.ClientAptNumber & " - " & Me.ClientStreetAddress & ", " & Me.ClientCityName & ", " & Me.ClientState & "  " & Me.ClientZipCode)
        Else
            strLocation = (Me.ClientStreetAddress & ", " & Me.ClientCityName & ", " & Me.ClientState & "  " & Me.ClientZipCode)
        End If
        
        If Not IsNull(Me.ClientPhone2) Then
            strContact = (Me.ClientPhone1 & " " & Me.ClientPhoneType1 & "  " & Me.ClientPhone2 & " " & Me.ClientPhoneType2)
        Else
            strContact = (Me.ClientPhone1 & " " & Me.ClientPhoneType1)
        End If
        
        Set db = CurrentDb()
        Set rst = db.OpenRecordset("qryServiceDetails", dbOpenDynaset)
        
        With rst
            Do Until rst.EOF
            .MoveFirst
            strSQL = .Fields(0) & vbTab & _
            .Fields(1) & vbTab & _
            .Fields(2) & vbCrLf
            DoCmd.SendObject acSendNoObject, , , , , , , strSQL, False, False
            .MoveNext
            Loop
        End With
                                
    With olAppt
        .Start = Me.ServiceDate & " " & Me.ApptTime
        .Duration = Me.ApptDuration
        .Subject = (Me.CustNumber & " - Service Appointment - " & Me.OrderNumber & "    " & Me.ClientUserlastName & ", " & Me.ClientUserFirstName)
        .Location = strLocation & "   " & strContact
        If Me.ApptReminder = True Then
        .ReminderSet = True
        .ReminderMinutesBeforeStart = Me.ReminderMinutes
        End If
        .RequiredAttendees = "[EMAIL="wayne0765@hotmail.com"]myemail[/EMAIL]"
        .Body = strSQL
        .Save
        
    End With
    End If
    
        'Release the Outlook object variables
        Set olObj = Nothing
        Set olAppt = Nothing
        Set rst = Nothing
        Set db = Nothing
    
        'Set the Added to Outlook flag, save the record, and display a message
        Me.ApptAddedtoOutlook = True
        DoCmd.RunCommand acCmdSaveRecord
        MsgBox "Appointment Added", vbOKOnly, "Crate & Pack"
        
    Exit Sub
          
Exit_btnCreateAppointment_Click:
    Exit Sub
    
Err_btnCreateAppointment_Click:
    MsgBox Err.Description, vbOKOnly, "Crate & Pack"
    Resume Exit_btnCreateAppointment_Click
        
End Sub

I get an error message that says "Too few parameters. Expected 1." when I execute. Here is my SELECT Query:

Code: 
SELECT tblOrderDetails.Quantity, tblOrderDetails.ServiceType, tblOrderDetails.ServiceDetails
FROM tblOrders INNER JOIN tblOrderDetails ON tblOrders.OrderNumber = tblOrderDetails.OrderNumber
WHERE (((tblOrderDetails.ServiceType)<>"Trip Charge" Or (tblOrderDetails.ServiceType)<>"Tax Exempt") AND ((tblOrderDetails.OrderNumber)=[Forms]![frmOrders]![OrderNumber]))
ORDER BY tblOrderDetails.ServiceType;

I've been trying to fix this for a few hours now but can't seem to figure it out. Any ideas?

Thanks in advance,

Wayne
 
stopher

stopher

AWF VIP
Local time
Today, 12:44
Joined
Feb 1, 2006
Messages
2,395
The problem is that the query requires a parameter value and by submitting the query to the recordset like this, the parameter value is not determine so the recordset treats it as the form string rather than the actual value from the form.

Build your query in VBA then you can ensure the actual parameter value is used instead of the form link.

Code: 
mysql = "SELECT tblOrderDetails.Quantity, tblOrderDetails.ServiceType, tblOrderDetails.ServiceDetails " & _
        "FROM tblOrders INNER JOIN tblOrderDetails ON tblOrders.OrderNumber = tblOrderDetails.OrderNumber " & _
        "WHERE tblOrderDetails.ServiceType <> 'Trip Charge' Or tblOrderDetails.ServiceType <> 'Tax Exempt' " & _
        "And tblOrderDetails.OrderNumber = " [COLOR="Red"]& [Forms]![frmOrders]![OrderNumber] &[/COLOR] " " & _
        "ORDER BY tblOrderDetails.ServiceType"
        
Set db = CurrentDb()
Set rst = db.OpenRecordset(mysql, dbOpenDynaset)
 
W

Wayne

Crazy Canuck
Local time
Today, 07:44
Joined
Nov 4, 2012
Messages
176
Thanks stopher. I did that, but now I get a message stating " Syntax error (missing operator) in query expression". I know the answer is staring me in the face, but I can't seem to get it to work.

Here's my code:
Code: 
Private Sub btnCreateAppointment_Click()
On Error GoTo Err_btnCreateAppointment_Click
    'First Save the Current Record
    DoCmd.RunCommand acCmdSaveRecord
    
    'Exit the procedure if the appointment has already been added to the Outlook Calendar
    If Me.ApptAddedtoOutlook = True Then
        MsgBox "This appointment has already been added to the Outlook Calendar.", vbOKOnly, "Crate & Pack"
        Exit Sub
    Else
        Dim olObj As Outlook.Application
        Dim olAppt As Outlook.AppointmentItem
        Dim strLocation As String
        Dim strContact As String
        Dim strSQL As String
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
    
        Set olObj = CreateObject("Outlook.Application")
        Set olAppt = olObj.CreateItem(olAppointmentItem)
    
        If Not IsNull(Me.ClientAptNumber) Then
            strLocation = (Me.ClientAptNumber & " - " & Me.ClientStreetAddress & ", " & Me.ClientCityName & ", " & Me.ClientState & "  " & Me.ClientZipCode)
        Else
            strLocation = (Me.ClientStreetAddress & ", " & Me.ClientCityName & ", " & Me.ClientState & "  " & Me.ClientZipCode)
        End If
        
        If Not IsNull(Me.ClientPhone2) Then
            strContact = (Me.ClientPhone1 & " " & Me.ClientPhoneType1 & "  " & Me.ClientPhone2 & " " & Me.ClientPhoneType2)
        Else
            strContact = (Me.ClientPhone1 & " " & Me.ClientPhoneType1)
        End If
        
        Set db = CurrentDb()
        Set rst = db.OpenRecordset("SELECT tblOrderDetails.Quantity, tblOrderDetails.ServiceType, tblOrderDetails.ServiceDetails" & _
                "FROM tblOrderDetails INNER JOIN tblOrderDetails ON tblOrders.OrderNumber = tblOrderDetails.OrderNumber" & _
                "WHERE (((tblOrderDetails.ServiceType) <> 'Trip Charge' Or (tblOrderDetails.ServiceType) <> 'Tax Exempt')" & _
                "And ((tblOrderDetails.OrderNumber) = '&[Forms]![frmOrders]![OrderNumber]&'))" & _
                "ORDER BY tblOrderDetails.ServiceType;", dbOpenDynaset)
                
        strSQL = ("SELECT tblOrderDetails.Quantity, tblOrderDetails.ServiceType, tblOrderDetails.ServiceDetails" & _
                "FROM tblOrderDetails INNER JOIN tblOrderDetails ON tblOrders.OrderNumber = tblOrderDetails.OrderNumber" & _
                "WHERE (((tblOrderDetails.ServiceType) <> 'Trip Charge' Or (tblOrderDetails.ServiceType) <> 'Tax Exempt')" & _
                "And ((tblOrderDetails.OrderNumber) = '&[Forms]![frmOrders]![OrderNumber]&'))" & _
                "ORDER BY tblOrderDetails.ServiceType")
        
        With rst
            Do Until rst.EOF
            .MoveFirst
            strSQL = .Fields(0) & vbTab & _
            .Fields(1) & " - " & _
            .Fields(2) & vbCrLf
            DoCmd.SendObject acSendNoObject, , , , , , , strSQL, False, False
            .MoveNext
            Loop
        End With
                                
    With olAppt
        .Start = Me.ServiceDate & " " & Me.ApptTime
        .Duration = Me.ApptDuration
        .Subject = (Me.CustNumber & " - Service Appointment - " & Me.OrderNumber & "    " & Me.ClientUserlastName & ", " & Me.ClientUserFirstName)
        .Location = strLocation & "   " & strContact
        If Me.ApptReminder = True Then
        .ReminderSet = True
        .ReminderMinutesBeforeStart = Me.ReminderMinutes
        End If
        .RequiredAttendees = "[EMAIL="wayne0765@hotmail.com"]myemail[/EMAIL]"
        .Body = strSQL
        .Save
        
    End With
    End If
    
        'Release the Outlook object variables
        Set olObj = Nothing
        Set olAppt = Nothing
        Set rst = Nothing
        Set db = Nothing
    
        'Set the Added to Outlook flag, save the record, and display a message
        Me.ApptAddedtoOutlook = True
        DoCmd.RunCommand acCmdSaveRecord
        MsgBox "Appointment Added", vbOKOnly, "Crate & Pack"
        
    Exit Sub
          
Exit_btnCreateAppointment_Click:
    Exit Sub
    
Err_btnCreateAppointment_Click:
    MsgBox Err.Description, vbOKOnly, "Crate & Pack"
    Resume Exit_btnCreateAppointment_Click
        
End Sub

Can you point me in the right direction please.

Wayne
 
W

Wayne

Crazy Canuck
Local time
Today, 07:44
Joined
Nov 4, 2012
Messages
176
I cleaned up the code a little bit, but still get the same error message.

Code: 
Private Sub btnCreateAppointment_Click()
On Error GoTo Err_btnCreateAppointment_Click
    'First Save the Current Record
    DoCmd.RunCommand acCmdSaveRecord
    
    'Exit the procedure if the appointment has already been added to the Outlook Calendar
    If Me.ApptAddedtoOutlook = True Then
        MsgBox "This appointment has already been added to the Outlook Calendar.", vbOKOnly, "Crate & Pack"
        Exit Sub
    Else
        Dim olObj As Outlook.Application
        Dim olAppt As Outlook.AppointmentItem
        Dim strLocation As String
        Dim strContact As String
        Dim strSQL As String
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
    
        Set olObj = CreateObject("Outlook.Application")
        Set olAppt = olObj.CreateItem(olAppointmentItem)
    
        If Not IsNull(Me.ClientAptNumber) Then
            strLocation = (Me.ClientAptNumber & " - " & Me.ClientStreetAddress & ", " & Me.ClientCityName & ", " & Me.ClientState & "  " & Me.ClientZipCode)
        Else
            strLocation = (Me.ClientStreetAddress & ", " & Me.ClientCityName & ", " & Me.ClientState & "  " & Me.ClientZipCode)
        End If
        
        If Not IsNull(Me.ClientPhone2) Then
            strContact = (Me.ClientPhone1 & " " & Me.ClientPhoneType1 & "  " & Me.ClientPhone2 & " " & Me.ClientPhoneType2)
        Else
            strContact = (Me.ClientPhone1 & " " & Me.ClientPhoneType1)
        End If
        
        strSQL = ("SELECT tblOrderDetails.Quantity, tblOrderDetails.ServiceType, tblOrderDetails.ServiceDetails" & _
                "FROM tblOrderDetails INNER JOIN tblOrderDetails ON tblOrders.OrderNumber = tblOrderDetails.OrderNumber" & _
                "WHERE (((tblOrderDetails.ServiceType) <> 'Trip Charge' Or (tblOrderDetails.ServiceType) <> 'Tax Exempt')" & _
                "And ((tblOrderDetails.OrderNumber) = '&[Forms]![frmOrders]![OrderNumber]&'))" & _
                "ORDER BY tblOrderDetails.ServiceType")
                
        Set db = CurrentDb()
        Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
               
        With rst
            Do Until rst.EOF
            .MoveFirst
            strSQL = .Fields(0) & vbTab & _
            .Fields(1) & " - " & _
            .Fields(2) & vbCrLf
            DoCmd.SendObject acSendNoObject, , , , , , , strSQL, False, False
            .MoveNext
            Loop
        End With
                                
    With olAppt
        .Start = Me.ServiceDate & " " & Me.ApptTime
        .Duration = Me.ApptDuration
        .Subject = (Me.CustNumber & " - Service Appointment - " & Me.OrderNumber & "    " & Me.ClientUserlastName & ", " & Me.ClientUserFirstName)
        .Location = strLocation & "   " & strContact
        If Me.ApptReminder = True Then
        .ReminderSet = True
        .ReminderMinutesBeforeStart = Me.ReminderMinutes
        End If
        .RequiredAttendees = "[EMAIL="wayne0765@hotmail.com"]wayne0765@hotmail.com[/EMAIL]"
        .Body = strSQL
        .Save
        
    End With
    End If
    
        'Release the Outlook object variables
        Set olObj = Nothing
        Set olAppt = Nothing
        Set rst = Nothing
        Set db = Nothing
    
        'Set the Added to Outlook flag, save the record, and display a message
        Me.ApptAddedtoOutlook = True
        DoCmd.RunCommand acCmdSaveRecord
        MsgBox "Appointment Added", vbOKOnly, "Crate & Pack"
        
    Exit Sub
          
Exit_btnCreateAppointment_Click:
    Exit Sub
    
Err_btnCreateAppointment_Click:
    MsgBox Err.Description, vbOKOnly, "Crate & Pack"
    Resume Exit_btnCreateAppointment_Click
        
End Sub

Frustrated.
 
stopher

stopher

AWF VIP
Local time
Today, 12:44
Joined
Feb 1, 2006
Messages
2,395
Wayne said:
Thanks stopher. I did that, but now I get a message stating " Syntax error (missing operator) in query expression". I know the answer is staring me in the face, but I can't seem to get it to work.

Here's my code:
Code: 
Private Sub btnCreateAppointment_Click()
On Error GoTo Err_btnCreateAppointment_Click
    'First Save the Current Record
    DoCmd.RunCommand acCmdSaveRecord
    
    'Exit the procedure if the appointment has already been added to the Outlook Calendar
    If Me.ApptAddedtoOutlook = True Then
        MsgBox "This appointment has already been added to the Outlook Calendar.", vbOKOnly, "Crate & Pack"
        Exit Sub
    Else
        Dim olObj As Outlook.Application
        Dim olAppt As Outlook.AppointmentItem
        Dim strLocation As String
        Dim strContact As String
        Dim strSQL As String
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
    
        Set olObj = CreateObject("Outlook.Application")
        Set olAppt = olObj.CreateItem(olAppointmentItem)
    
        If Not IsNull(Me.ClientAptNumber) Then
            strLocation = (Me.ClientAptNumber & " - " & Me.ClientStreetAddress & ", " & Me.ClientCityName & ", " & Me.ClientState & "  " & Me.ClientZipCode)
        Else
            strLocation = (Me.ClientStreetAddress & ", " & Me.ClientCityName & ", " & Me.ClientState & "  " & Me.ClientZipCode)
        End If
        
        If Not IsNull(Me.ClientPhone2) Then
            strContact = (Me.ClientPhone1 & " " & Me.ClientPhoneType1 & "  " & Me.ClientPhone2 & " " & Me.ClientPhoneType2)
        Else
            strContact = (Me.ClientPhone1 & " " & Me.ClientPhoneType1)
        End If
        
        [COLOR="Red"]Set db = CurrentDb()
        Set rst = db.OpenRecordset("SELECT tblOrderDetails.Quantity, tblOrderDetails.ServiceType, tblOrderDetails.ServiceDetails" & _
                "FROM tblOrderDetails INNER JOIN tblOrderDetails ON tblOrders.OrderNumber = tblOrderDetails.OrderNumber" & _
                "WHERE (((tblOrderDetails.ServiceType) <> 'Trip Charge' Or (tblOrderDetails.ServiceType) <> 'Tax Exempt')" & _
                "And ((tblOrderDetails.OrderNumber) = '&[Forms]![frmOrders]![OrderNumber]&'))" & _
                "ORDER BY tblOrderDetails.ServiceType;", dbOpenDynaset)
                
        strSQL = ("SELECT tblOrderDetails.Quantity, tblOrderDetails.ServiceType, tblOrderDetails.ServiceDetails" & _
                "FROM tblOrderDetails INNER JOIN tblOrderDetails ON tblOrders.OrderNumber = tblOrderDetails.OrderNumber" & _
                "WHERE (((tblOrderDetails.ServiceType) <> 'Trip Charge' Or (tblOrderDetails.ServiceType) <> 'Tax Exempt')" & _
                "And ((tblOrderDetails.OrderNumber) = '&[Forms]![frmOrders]![OrderNumber]&'))" & _
                "ORDER BY tblOrderDetails.ServiceType")[/COLOR]
        
        With rst
            Do Until rst.EOF
            .MoveFirst
            strSQL = .Fields(0) & vbTab & _
            .Fields(1) & " - " & _
            .Fields(2) & vbCrLf
            DoCmd.SendObject acSendNoObject, , , , , , , strSQL, False, False
            .MoveNext
            Loop
        End With
                                
    With olAppt
        .Start = Me.ServiceDate & " " & Me.ApptTime
        .Duration = Me.ApptDuration
        .Subject = (Me.CustNumber & " - Service Appointment - " & Me.OrderNumber & "    " & Me.ClientUserlastName & ", " & Me.ClientUserFirstName)
        .Location = strLocation & "   " & strContact
        If Me.ApptReminder = True Then
        .ReminderSet = True
        .ReminderMinutesBeforeStart = Me.ReminderMinutes
        End If
        .RequiredAttendees = "[EMAIL="wayne0765@hotmail.com"]myemail[/EMAIL]"
        .Body = strSQL
        .Save
        
    End With
    End If
    
        'Release the Outlook object variables
        Set olObj = Nothing
        Set olAppt = Nothing
        Set rst = Nothing
        Set db = Nothing
    
        'Set the Added to Outlook flag, save the record, and display a message
        Me.ApptAddedtoOutlook = True
        DoCmd.RunCommand acCmdSaveRecord
        MsgBox "Appointment Added", vbOKOnly, "Crate & Pack"
        
    Exit Sub
          
Exit_btnCreateAppointment_Click:
    Exit Sub
    
Err_btnCreateAppointment_Click:
    MsgBox Err.Description, vbOKOnly, "Crate & Pack"
    Resume Exit_btnCreateAppointment_Click
        
End Sub

Can you point me in the right direction please.

Wayne
Click to expand...

Try replacing the bits I've highlighted in red with the code I supplied in my previous post - in the order I supplied it.
 
W

Wayne

Crazy Canuck
Local time
Today, 07:44
Joined
Nov 4, 2012
Messages
176
I have tried copying your SQL statement exactly, and I'm getting a new error message saying "Too few parameters. Expected 2.".

Here is the code I am using:

Code: 
Private Sub btnCreateAppointment_Click()
On Error GoTo Err_btnCreateAppointment_Click
    'First Save the Current Record
    DoCmd.RunCommand acCmdSaveRecord
    
    'Exit the procedure if the appointment has already been added to the Outlook Calendar
    If Me.ApptAddedtoOutlook = True Then
        MsgBox "This appointment has already been added to the Outlook Calendar.", vbOKOnly, "Crate & Pack"
        Exit Sub
    Else
        Dim olObj As Outlook.Application
        Dim olAppt As Outlook.AppointmentItem
        Dim strLocation As String
        Dim strContact As String
        Dim strSQL As String
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
    
        Set olObj = CreateObject("Outlook.Application")
        Set olAppt = olObj.CreateItem(olAppointmentItem)
    
        If Not IsNull(Me.ClientAptNumber) Then
            strLocation = (Me.ClientAptNumber & " - " & Me.ClientStreetAddress & ", " & Me.ClientCityName & ", " & Me.ClientState & "  " & Me.ClientZipCode)
        Else
            strLocation = (Me.ClientStreetAddress & ", " & Me.ClientCityName & ", " & Me.ClientState & "  " & Me.ClientZipCode)
        End If
        
        If Not IsNull(Me.ClientPhone2) Then
            strContact = (Me.ClientPhone1 & " " & Me.ClientPhoneType1 & "  " & Me.ClientPhone2 & " " & Me.ClientPhoneType2)
        Else
            strContact = (Me.ClientPhone1 & " " & Me.ClientPhoneType1)
        End If
        
        strSQL = "SELECT tblOrderDetails.Quantity, tblOrderDetails.ServiceType, tblOrderDetails.ServiceDetails " & _
                "FROM tblOrders INNER JOIN tblOrderDetails ON tblOrders.OrderNumber = tblOrderDetails.OrderNumber " & _
                "WHERE tblOrderDetails.ServiceType <> 'Trip Charge' Or tblOrderDetails.ServiceType <> 'Tax Exempt' " & _
                "And tblOrderDetails.OrderNumber = " & [Forms]![frmOrders]![OrderNumber] & " " & _
                "ORDER BY tblOrderDetails.ServiceType"
                
        Set db = CurrentDb()
        Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
               
        With rst
            Do Until rst.EOF
            .MoveFirst
            strSQL = .Fields(0) & vbTab & _
            .Fields(1) & " - " & _
            .Fields(2) & vbCrLf
            DoCmd.SendObject acSendNoObject, , , , , , , strSQL, False, False
            .MoveNext
            Loop
        End With
                                               
    With olAppt
        .Start = Me.ServiceDate & " " & Me.ApptTime
        .Duration = Me.ApptDuration
        .Subject = (Me.CustNumber & " - Service Appointment - " & Me.OrderNumber & "    " & Me.ClientUserlastName & ", " & Me.ClientUserFirstName)
        .Location = strLocation & "   " & strContact
        If Me.ApptReminder = True Then
        .ReminderSet = True
        .ReminderMinutesBeforeStart = Me.ReminderMinutes
        End If
        .RequiredAttendees = "[EMAIL="wayne0765@hotmail.com"]myemail[/EMAIL]"
        .Body = strSQL
        .Save
        
    End With
    End If
    
        'Release the Outlook object variables
        Set olObj = Nothing
        Set olAppt = Nothing
        Set rst = Nothing
        Set db = Nothing
    
        'Set the Added to Outlook flag, save the record, and display a message
        Me.ApptAddedtoOutlook = True
        DoCmd.RunCommand acCmdSaveRecord
        MsgBox "Appointment Added", vbOKOnly, "Crate & Pack"
        
    Exit Sub
          
Exit_btnCreateAppointment_Click:
    Exit Sub
    
Err_btnCreateAppointment_Click:
    MsgBox Err.Description, vbOKOnly, "Crate & Pack"
    Resume Exit_btnCreateAppointment_Click
        
End Sub

I have searched through the forum, been to the Allen Browne site, and checked out a couple other forums for an answer, but I'm kinda stumped here. Can you spot what I'm missing here? Any help would be appreciated.

Wayne
 
MarkK

MarkK

bit cruncher
Local time
Today, 04:44
Joined
Mar 17, 2004
Messages
8,186
This error occurs when there are field names in your SQL that do not exist in the table. Check your spellings. Check what fields you are using.

Another thing I very commonly do is copy my SQL (after I've customized it in code) and then paste it into the query design grid (in SQL view), and test it there. Errors are highlighted in the query text, which helps, and sometimes error messages are a little more verbose and descriptive.

But the error you are getting: there are definitely identifiers in your SQL that are not recognized as valid Field Names or Tables.
 
W

Wayne

Crazy Canuck
Local time
Today, 07:44
Joined
Nov 4, 2012
Messages
176
Thanks Mark. I found a small problem and changed it. Now I get the error message "Syntax error in JOIN operation." I've highlighted the changes I made in red below:

Code: 
Private Sub btnCreateAppointment_Click()
On Error GoTo Err_btnCreateAppointment_Click
    'First Save the Current Record
    DoCmd.RunCommand acCmdSaveRecord
    
    'Exit the procedure if the appointment has already been added to the Outlook Calendar
    If Me.ApptAddedtoOutlook = True Then
        MsgBox "This appointment has already been added to the Outlook Calendar.", vbOKOnly, "Crate & Pack"
        Exit Sub
    Else
        Dim olObj As Outlook.Application
        Dim olAppt As Outlook.AppointmentItem
        Dim strLocation As String
        Dim strContact As String
        Dim strSQL As String
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
    
        Set olObj = CreateObject("Outlook.Application")
        Set olAppt = olObj.CreateItem(olAppointmentItem)
    
        If Not IsNull(Me.ClientAptNumber) Then
            strLocation = (Me.ClientAptNumber & " - " & Me.ClientStreetAddress & ", " & Me.ClientCityName & ", " & Me.ClientState & "  " & Me.ClientZipCode)
        Else
            strLocation = (Me.ClientStreetAddress & ", " & Me.ClientCityName & ", " & Me.ClientState & "  " & Me.ClientZipCode)
        End If
        
        If Not IsNull(Me.ClientPhone2) Then
            strContact = (Me.ClientPhone1 & " " & Me.ClientPhoneType1 & "  " & Me.ClientPhone2 & " " & Me.ClientPhoneType2)
        Else
            strContact = (Me.ClientPhone1 & " " & Me.ClientPhoneType1)
        End If
        
        strSQL = "SELECT tblOrderDetails.Quantity, tblOrderDetails.ServiceType, tblOrderDetails.ServiceDetails " & _
                "FROM [COLOR=red]tblOrderDetails[/COLOR] INNER JOIN tblOrderDetails ON tblOrders.OrderNumber = tblOrderDetails.OrderNumber " & _
                "[COLOR=red]WHERE tblOrderDetails.ServiceType <> " & "Trip Charge" & " Or tblOrderDetails.ServiceType <> " & "Tax Exempt" & " " & _
                "And tblOrderDetails.OrderNumber = " & [Forms]![frmOrders]![OrderNumber] & " " & _
[/COLOR]                "ORDER BY tblOrderDetails.ServiceType"
                
        Set db = CurrentDb()
        Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
               
        With rst
            Do Until rst.EOF
            .MoveFirst
            strSQL = .Fields(0) & vbTab & _
            .Fields(1) & " - " & _
            .Fields(2) & vbCrLf
            DoCmd.SendObject acSendNoObject, , , , , , , strSQL, False, False
            .MoveNext
            Loop
        End With
                                               
    With olAppt
        .Start = Me.ServiceDate & " " & Me.ApptTime
        .Duration = Me.ApptDuration
        .Subject = (Me.CustNumber & " - Service Appointment - " & Me.OrderNumber & "    " & Me.ClientUserlastName & ", " & Me.ClientUserFirstName)
        .Location = strLocation & "   " & strContact
        If Me.ApptReminder = True Then
        .ReminderSet = True
        .ReminderMinutesBeforeStart = Me.ReminderMinutes
        End If
        .RequiredAttendees = "[EMAIL="wayne0765@hotmail.com"]myemail[/EMAIL]"
        .Body = strSQL
        .Save
        
    End With
    End If
    
        'Release the Outlook object variables
        Set olObj = Nothing
        Set olAppt = Nothing
        Set rst = Nothing
        Set db = Nothing
    
        'Set the Added to Outlook flag, save the record, and display a message
        Me.ApptAddedtoOutlook = True
        DoCmd.RunCommand acCmdSaveRecord
        MsgBox "Appointment Added", vbOKOnly, "Crate & Pack"
        
    Exit Sub
          
Exit_btnCreateAppointment_Click:
    Exit Sub
    
Err_btnCreateAppointment_Click:
    MsgBox Err.Description, vbOKOnly, "Crate & Pack"
    Resume Exit_btnCreateAppointment_Click
        
End Sub

Should I be referring to the calling form rather than the underlying tables in the SQL statement? I have checked ALL of the field names for accuracy, and they are correct. I built the SQL statement in design view, and it works fine there, but not in the form. I'm close, but can't see what I'm missing. Can you help? Or point me in the right direction?

Wayne

Wayne
 
stopher

stopher

AWF VIP
Local time
Today, 12:44
Joined
Feb 1, 2006
Messages
2,395
As Mark mentioned, output the result of strSQL to the screen then copy it to the query designer where you can look at it and test it properly. Use Debug.Print or MsgBox to output to screen.

Also, copy the result here so we can help.
 
W

Wayne

Crazy Canuck
Local time
Today, 07:44
Joined
Nov 4, 2012
Messages
176
Thanks for the help. When my form is open to a specific client work order, and I run the query, it works fine, and returns the exact records I want for that order in query view. It tests absolutely fine every time I try it. However, it doesn't work when I try to add an appointment to the Outlook Calendar.

Code: 
Private Sub btnCreateAppointment_Click()
On Error GoTo Err_btnCreateAppointment_Click

    'First Save the Current Record
    DoCmd.RunCommand acCmdSaveRecord
    
    'Exit the procedure if the appointment has already been added to the Outlook Calendar
    If Me.ApptAddedtoOutlook = True Then
        MsgBox "This appointment has already been added to the Outlook Calendar.", vbOKOnly, "Crate & Pack"
        Exit Sub
    Else
        Dim olObj As Outlook.Application
        Dim olAppt As Outlook.AppointmentItem
        Dim strLocation As String
        Dim strContact As String
        Dim strSQL As String
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
    
        Set olObj = CreateObject("Outlook.Application")
        Set olAppt = olObj.CreateItem(olAppointmentItem)
    
        If Not IsNull(Me.ClientAptNumber) Then
            strLocation = (Me.ClientAptNumber & " - " & Me.ClientStreetAddress & ", " & Me.ClientCityName & ", " & Me.ClientState & "  " & Me.ClientZipCode)
        Else
            strLocation = (Me.ClientStreetAddress & ", " & Me.ClientCityName & ", " & Me.ClientState & "  " & Me.ClientZipCode)
        End If
        
        If Not IsNull(Me.ClientPhone2) Then
            strContact = (Me.ClientPhone1 & " " & Me.ClientPhoneType1 & "  " & Me.ClientPhone2 & " " & Me.ClientPhoneType2)
        Else
            strContact = (Me.ClientPhone1 & " " & Me.ClientPhoneType1)
        End If
        
        strSQL = "SELECT tblOrderDetails.Quantity, tblOrderDetails.ServiceType, tblOrderDetails.ServiceDetails " & _
                "FROM tblOrders INNER JOIN tblOrderDetails ON tblOrders.OrderNumber = tblOrderDetails.OrderNumber " & _
                "WHERE tblOrderDetails.ServiceType <> ""Trip Charge"" Or tblOrderDetails.ServiceType <> ""Tax Exempt"" " & _
                "And tblOrderDetails.OrderNumber = " & [Forms]![frmOrders]![OrderNumber] & " " & _
                "ORDER BY tblOrderDetails.ServiceType"
                               
        Set db = CurrentDb()
        Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
               
        With rst
            .MoveFirst
            Do Until rst.EOF
            strSQL = rst.Fields("Quantity") & vbTab & _
            rst.Fields("ServiceType") & " - " & _
            rst.Fields("ServiceDetails") & vbCrLf
            [COLOR="Red"]DoCmd.SendObject acSendNoObject, , , , , , strSQL, , False, False[/COLOR]
            .MoveNext
            Loop
        End With
                                               
    With olAppt
        .Start = Me.ServiceDate & " " & Me.ApptTime
        .Duration = Me.ApptDuration
        .Subject = (Me.CustNumber & " - Service Appointment - " & Me.OrderNumber & "    " & Me.ClientUserlastName & ", " & Me.ClientUserFirstName)
        .Location = strLocation & "   " & strContact
        If Me.ApptReminder = True Then
        .ReminderSet = True
        .ReminderMinutesBeforeStart = Me.ReminderMinutes
        End If
        .RequiredAttendees = "myemail"
        .Body = strSQL
        .Save
        
    End With
    End If
    
        'Release the Outlook object variables
        Set olObj = Nothing
        Set olAppt = Nothing
        Set rst = Nothing
        Set db = Nothing
    
        'Set the Added to Outlook flag, save the record, and display a message
        Me.ApptAddedtoOutlook = True
        DoCmd.RunCommand acCmdSaveRecord
        MsgBox "Appointment Added", vbOKOnly, "Crate & Pack"
        
    Exit Sub
          
Exit_btnCreateAppointment_Click:
    Exit Sub
    
Err_btnCreateAppointment_Click:
    MsgBox Err.Description, vbOKOnly, "Crate & Pack"
    Resume Exit_btnCreateAppointment_Click
        
End Sub

When I try to add an appointment, I get the error message "Too few parameters. Expected 2.". Have tried re-writing parts of the code here and there but with the same results. I am stumped here.

I highlighted the SendObject command in the code. Is this just for sending an e-mail? I am trying to add an appointment to my Outlook Calendar, and have the service items put in the Notes section of the appointment. I looked through every book and forum I could find for the answer, but have come up empty. I would appreciate anything to point me in the right direction please.

Wayne
 
MarkK

MarkK

bit cruncher
Local time
Today, 04:44
Joined
Mar 17, 2004
Messages
8,186
I don't understand the problem. There is a line highlighted in red, but that line almost certainly does not generate the "Too few parameters. Expected 2." error you cite, and your description of your purpose is that you are adding an Outlook Appointment, so I don't see why there is a SendObject line at all.

What am I missing here? What line causes the error?

Also, consider breaking this routine up into meaningfully named shorter routines so it is clearer what each step is for. For instance, where you declare a bunch of new variables here . . .
Code: 
        Dim olObj As Outlook.Application
        Dim olAppt As Outlook.AppointmentItem
        Dim strLocation As String
        Dim strContact As String
        Dim strSQL As String
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
. . . you are obviously starting a distinct operation. Break it out into a new routine, and name it appropriately, and then call it with parameters. This breaks your code into shorter, simpler, more easily understood chunks of functionality, and you'll start to find that other future consumers can call the same code with different parameters, which is called "code re-use." If you bury this functionality inside the other routine, then you have to rewrite it if you need it somewhere else.

Hope this helps,
 
W

Wayne

Crazy Canuck
Local time
Today, 07:44
Joined
Nov 4, 2012
Messages
176
Thanks for the quick response Mark. First off the reason I highlighted that line wasn't to say that it caused the error, but to question if it should even be there. Personally, I think the SQL statement is causing the error, and I am trying my damdest to figure out why. It works fine when I run the query by itself, but not when the code is executed. I will eventually figure that part out on my own.

I am no expert (hence me asking the forum for help), but I'm not trying to "Send" an Object, just trying to put the records from the SQL statement into the Notes section of the appointment calendar. I just copied some code from another thread for an email, and was trying to see if it worked or not, and it doesn't. Time to try something else.

I guess my question boils down to this - How do I get the records that are selected in the SQL statement to appear in the notes section of the appointment? If it's not a "SendObject" command, what should it be? I'm not asking anyone to write the code for me, just point me in the right direction. I really would like to learn for myself. I am actually enjoying myself learning to do this.

As for breaking up the code into usable routines, that will come later. If I can get it to work. Thanks again for your help.

Wayne
 
MarkK

MarkK

bit cruncher
Local time
Today, 04:44
Joined
Mar 17, 2004
Messages
8,186
OK, well, I suspect the notes section of an appointment is just a long string, and if you want data from multiple records in there you need a loop. Modifying what you posted . . .
Code: 
    dim notes as string
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
               
    With rst
[COLOR="Green"]        '.MoveFirst 'not required.  if there are records the first will be current[/COLOR]
        Do Until .EOF
[COLOR="Green"]            'here we concatenate data from each record into a single variable[/COLOR]
            notes = notes & !Quantity" & vbTab & !ServiceType & " - " & !ServiceDetails
            .MoveNext
        Loop
    End With

[COLOR="Green"]    'call the create appointment routine, passing in the notes[/COLOR]
    CreateAppointment notes
Does this get at what you are trying to do?
 
You must log in or register to reply here.

Similar threads

M
E-mail hyperlinks not working from a table ...
Replies
6
Views
378
Marshall Brooks
M
D
Picture sent in body (not attached) by automated E-Mail
2
Replies
20
Views
1,121
The_Doc_Man
The_Doc_Man
L
Automatic send e-mail on table field change
2
Replies
38
Views
1,556
LeoM
L
M
Solved Form Field and SendKeys
2
Replies
39
Views
1,478
Marshall Brooks
M
R
Query result is being truncated
Replies
16
Views
474
RogerCooper
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom