Docmd send object data type error

[thmsjlmnt3953]

Hi,

Im getting a data type error when i try the below and cant seem to figure it out?

Private Sub Command2_Click()
Dim Product As String
Product = Me.txtProd.Column(1)
If MsgBox("Is Outlook Open?", vbYesNo, "Outlook") = vbYes Then
DoCmd.RunSQL "INSERT INTO tblRequestedPallets ( P_Code_Id ) " & vbCrLf & _
"SELECT tblProducts.prod_id " & vbCrLf & _
"FROM tblProducts " & vbCrLf & _
"WHERE (((tblProducts.prod_id)=[forms]![frmNavigation]![navigationsubform]![txtprod]));"
DoCmd.SendObject acSendNoObject, , , "thomas.lamont@xyz.com", , , , Product, "Test"
Else
End
End If
End Sub

 

[arnelgp]

create a breakpoint and inspect Product value.
also no need for vbCRLF in your sql statement.

 

[thmsjlmnt3953]

Hi,

Ive examined Product and it returns a string in the form of "product - description" just a standard text string?

The SQL part works ok so i would leave that just now although i know its a bit messy.

its just to get the string product into the subject line i need?

 

[jdraw]

I agree with arnelgp --some debugging on your part will help.
You do NOT need the vbCrLF within the SQL.

I suggest you try to dim your intended sql as a string (total air code here)
Also, I'm using prod_id as if it were a text string, thus the single quotes.

....
Dim sSQL as string
sSQL ="INSERT INTO tblRequestedPallets ( P_Code_Id ) "  & _
           "SELECT tblProducts.prod_id "   & _
           "FROM tblProducts "  & _
           "WHERE (((tblProducts.prod_id)=[COLOR="Red"]'[/COLOR]" & _
           [forms]![frmNavigation]![navigationsubform]![txtprod])) & "[COLOR="Red"]'[/COLOR];" 

debug.print sSQL  ' see what ACCESS has rendered from your SQL
.....
.....

Whenever you are building SQL from within vba, it is good to check the rendered code before doing the execution.

 

[arnelgp]

DoCmd.SendObject To:="thomas.lamont@xyz.com", Subject:=product, messagetext:="test"

 

[thmsjlmnt3953]

The SQL part works as intended, appending a product to a table.

its the docmd sendobject that fails