By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,157 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,157 IT Pros & Developers. It's quick & easy.

Code Problem

P: n/a
Can any one see a problem with this code ( it does not work

Private Sub cmdEmailOrderPlaced_Click()
'On Error GoTo Error_Handler
Dim strTo As String
Dim strBody As String
Dim strSubject As String
Dim strItemsOrdered As String
Dim CRS1 As ADODB.Recordset
Dim strSql As String
Set CRS1 = New ADODB.Recordset

sstrSql = "SELECT * FROM tblOrderProduct WHERE OrderNumber =" &
Me.OrderNumber
CRS1.Open strSql, CurrentProject.Connection, adOpenKeyset,
adLockReadOnly
While Not CRS1.EOF

strItemsOrdered = strItemsOrdered & "Item : " &
DLookup("[ProductName]", "tblProduct", "[ProductCode] =" &
CRS1.Fields("ProductCode"))

strItemsOrdered = strItemsOrdered & "Unit Price : " & Chr(163) &
DLookup("[PriceIncVAT ]", "tblProduct", "[ProductID] =" &
CRS1.Fields("ProductID")) & vbCrLf

'& vbCrLf & "Total Excl VAT: " & Chr(163) &
Format(CRS1.Fields("TotalExVAT"), "##0.00")

If IsNull(CRS1.Fields("Discount")) Or CRS1.Fields("Discount") = 0
Then
'Don't put discount in there if there isn't one
Else
strItemsOrdered = strItemsOrdered & "Discount : " &
CRS1.Fields("Discount") & "%" & vbCrLf
End If
strItemsOrdered = strItemsOrdered & "Total (Inc VAT): " & Chr(163)
& Format(CRS1.Fields("TotalIncVAT"), "##0.00") & vbCrLf & vbCrLf
CRS1.MoveNext
Wend
CRS1.Close
Set CRS1 = Nothing

Aug 12 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
* Simon:
Can any one see a problem with this code ( it does not work

Private Sub cmdEmailOrderPlaced_Click()
'On Error GoTo Error_Handler
Dim strTo As String
Dim strBody As String
Dim strSubject As String
Dim strItemsOrdered As String
Dim CRS1 As ADODB.Recordset
Dim strSql As String
Set CRS1 = New ADODB.Recordset

sstrSql = "SELECT * FROM tblOrderProduct WHERE OrderNumber =" &
Me.OrderNumber
CRS1.Open strSql, CurrentProject.Connection, adOpenKeyset,
adLockReadOnly
While Not CRS1.EOF

strItemsOrdered = strItemsOrdered & "Item : " &
DLookup("[ProductName]", "tblProduct", "[ProductCode] =" &
CRS1.Fields("ProductCode"))

strItemsOrdered = strItemsOrdered & "Unit Price : " & Chr(163) &
DLookup("[PriceIncVAT ]", "tblProduct", "[ProductID] =" &
CRS1.Fields("ProductID")) & vbCrLf

'& vbCrLf & "Total Excl VAT: " & Chr(163) &
Format(CRS1.Fields("TotalExVAT"), "##0.00")

If IsNull(CRS1.Fields("Discount")) Or CRS1.Fields("Discount") = 0
Then
'Don't put discount in there if there isn't one
Else
strItemsOrdered = strItemsOrdered & "Discount : " &
CRS1.Fields("Discount") & "%" & vbCrLf
End If
strItemsOrdered = strItemsOrdered & "Total (Inc VAT): " & Chr(163)
& Format(CRS1.Fields("TotalIncVAT"), "##0.00") & vbCrLf & vbCrLf
CRS1.MoveNext
Wend
CRS1.Close
Set CRS1 = Nothing
Some of the MVPs have ESP, but for most everyone else it would be
helpful if you provided a clue as to what you mean by "it does not work."

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
Aug 13 '06 #2

P: n/a
If you set the options to "Break on All Errors," then it would highlight the
offending line, and stop, so you could copy the content of the error message
to help us help you.

Larry Linson
Microsoft Access MVP

"Simon" <S.*******@shos.co.ukwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
Can any one see a problem with this code ( it does not work

Private Sub cmdEmailOrderPlaced_Click()
'On Error GoTo Error_Handler
Dim strTo As String
Dim strBody As String
Dim strSubject As String
Dim strItemsOrdered As String
Dim CRS1 As ADODB.Recordset
Dim strSql As String
Set CRS1 = New ADODB.Recordset

sstrSql = "SELECT * FROM tblOrderProduct WHERE OrderNumber =" &
Me.OrderNumber
CRS1.Open strSql, CurrentProject.Connection, adOpenKeyset,
adLockReadOnly
While Not CRS1.EOF

strItemsOrdered = strItemsOrdered & "Item : " &
DLookup("[ProductName]", "tblProduct", "[ProductCode] =" &
CRS1.Fields("ProductCode"))

strItemsOrdered = strItemsOrdered & "Unit Price : " & Chr(163) &
DLookup("[PriceIncVAT ]", "tblProduct", "[ProductID] =" &
CRS1.Fields("ProductID")) & vbCrLf

'& vbCrLf & "Total Excl VAT: " & Chr(163) &
Format(CRS1.Fields("TotalExVAT"), "##0.00")

If IsNull(CRS1.Fields("Discount")) Or CRS1.Fields("Discount") = 0
Then
'Don't put discount in there if there isn't one
Else
strItemsOrdered = strItemsOrdered & "Discount : " &
CRS1.Fields("Discount") & "%" & vbCrLf
End If
strItemsOrdered = strItemsOrdered & "Total (Inc VAT): " & Chr(163)
& Format(CRS1.Fields("TotalIncVAT"), "##0.00") & vbCrLf & vbCrLf
CRS1.MoveNext
Wend
CRS1.Close
Set CRS1 = Nothing

Aug 13 '06 #3

P: n/a
The error line is
CRS1.Open strSql, CurrentProject.Connection, adOpenKeyset,
adLockReadOnly

Run time Error ' 2147217908(80040e0c)
Command text not set for the command object.

the code creats an email off the products my customer has bought.
The code us to work but I use to use Product ID with auto number for
each product i had, but then i changed it to 'Product Code' where i
type in my own code instead of auto number, i then changed the code
from Product ID to 'Product Code' and it does not work any more

Simon
Larry Linson wrote:
If you set the options to "Break on All Errors," then it would highlight the
offending line, and stop, so you could copy the content of the error message
to help us help you.

Larry Linson
Microsoft Access MVP

"Simon" <S.*******@shos.co.ukwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
Can any one see a problem with this code ( it does not work

Private Sub cmdEmailOrderPlaced_Click()
'On Error GoTo Error_Handler
Dim strTo As String
Dim strBody As String
Dim strSubject As String
Dim strItemsOrdered As String
Dim CRS1 As ADODB.Recordset
Dim strSql As String
Set CRS1 = New ADODB.Recordset

sstrSql = "SELECT * FROM tblOrderProduct WHERE OrderNumber =" &
Me.OrderNumber
CRS1.Open strSql, CurrentProject.Connection, adOpenKeyset,
adLockReadOnly
While Not CRS1.EOF

strItemsOrdered = strItemsOrdered & "Item : " &
DLookup("[ProductName]", "tblProduct", "[ProductCode] =" &
CRS1.Fields("ProductCode"))

strItemsOrdered = strItemsOrdered & "Unit Price : " & Chr(163) &
DLookup("[PriceIncVAT ]", "tblProduct", "[ProductID] =" &
CRS1.Fields("ProductID")) & vbCrLf

'& vbCrLf & "Total Excl VAT: " & Chr(163) &
Format(CRS1.Fields("TotalExVAT"), "##0.00")

If IsNull(CRS1.Fields("Discount")) Or CRS1.Fields("Discount") = 0
Then
'Don't put discount in there if there isn't one
Else
strItemsOrdered = strItemsOrdered & "Discount : " &
CRS1.Fields("Discount") & "%" & vbCrLf
End If
strItemsOrdered = strItemsOrdered & "Total (Inc VAT): " & Chr(163)
& Format(CRS1.Fields("TotalIncVAT"), "##0.00") & vbCrLf & vbCrLf
CRS1.MoveNext
Wend
CRS1.Close
Set CRS1 = Nothing
Aug 13 '06 #4

P: n/a

I don't suppose ProductCode is a text field by any chance. If so you
need to alter the following line, like this:-

DLookup("[ProductName]", "tblProduct", "[ProductCode] ='" &
CRS1.Fields("ProductCode") & "'")

If it's not clear on the screen, what you have in front of the first
ampersand is a single quote mark followed by a double quote mark, and
at the end is a double - single - double

HTH
Dave

Aug 13 '06 #5

P: n/a
* Simon:
The error line is
CRS1.Open strSql, CurrentProject.Connection, adOpenKeyset,
adLockReadOnly

Run time Error ' 2147217908(80040e0c)
Command text not set for the command object.

the code creats an email off the products my customer has bought.
The code us to work but I use to use Product ID with auto number for
each product i had, but then i changed it to 'Product Code' where i
type in my own code instead of auto number, i then changed the code
from Product ID to 'Product Code' and it does not work any more

Simon
Larry Linson wrote:
>If you set the options to "Break on All Errors," then it would highlight the
offending line, and stop, so you could copy the content of the error message
to help us help you.

Larry Linson
Microsoft Access MVP

"Simon" <S.*******@shos.co.ukwrote in message
news:11**********************@b28g2000cwb.googleg roups.com...
>>Can any one see a problem with this code ( it does not work

Private Sub cmdEmailOrderPlaced_Click()
'On Error GoTo Error_Handler
Dim strTo As String
Dim strBody As String
Dim strSubject As String
Dim strItemsOrdered As String
Dim CRS1 As ADODB.Recordset
Dim strSql As String
Set CRS1 = New ADODB.Recordset

sstrSql = "SELECT * FROM tblOrderProduct WHERE OrderNumber =" &
Me.OrderNumber
CRS1.Open strSql, CurrentProject.Connection, adOpenKeyset,
adLockReadOnly
While Not CRS1.EOF

strItemsOrdered = strItemsOrdered & "Item : " &
DLookup("[ProductName]", "tblProduct", "[ProductCode] =" &
CRS1.Fields("ProductCode"))

strItemsOrdered = strItemsOrdered & "Unit Price : " & Chr(163) &
DLookup("[PriceIncVAT ]", "tblProduct", "[ProductID] =" &
CRS1.Fields("ProductID")) & vbCrLf

'& vbCrLf & "Total Excl VAT: " & Chr(163) &
Format(CRS1.Fields("TotalExVAT"), "##0.00")

If IsNull(CRS1.Fields("Discount")) Or CRS1.Fields("Discount") = 0
Then
'Don't put discount in there if there isn't one
Else
strItemsOrdered = strItemsOrdered & "Discount : " &
CRS1.Fields("Discount") & "%" & vbCrLf
End If
strItemsOrdered = strItemsOrdered & "Total (Inc VAT): " & Chr(163)
& Format(CRS1.Fields("TotalIncVAT"), "##0.00") & vbCrLf & vbCrLf
CRS1.MoveNext
Wend
CRS1.Close
Set CRS1 = Nothing
I don't know if you used copy/paste to post the message, but per the
code that was posted, you are not setting a value for strSql. The
assignment is for sstrSql. That would account for the error that you are
getting.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
Aug 13 '06 #6

P: n/a
.... do you have Option Explicit in the declarations section of your module?
If not, put it there and at the top of all your modules. Then from the VBA
IDE go to Tools/Options and tick the "Require Variable Declaration" option,
this is off by default (Microsofts stupid make it easy to make a mess of
things) but should be the first thing you switch on whenever you install
Access.

--

Terry Kreft
"Randy Harris" <pl****@send.no.spamwrote in message
news:dP***************@newssvr13.news.prodigy.com. ..
* Simon:
The error line is
CRS1.Open strSql, CurrentProject.Connection, adOpenKeyset,
adLockReadOnly

Run time Error ' 2147217908(80040e0c)
Command text not set for the command object.

the code creats an email off the products my customer has bought.
The code us to work but I use to use Product ID with auto number for
each product i had, but then i changed it to 'Product Code' where i
type in my own code instead of auto number, i then changed the code
from Product ID to 'Product Code' and it does not work any more

Simon
Larry Linson wrote:
If you set the options to "Break on All Errors," then it would
highlight the
offending line, and stop, so you could copy the content of the error
message
to help us help you.

Larry Linson
Microsoft Access MVP

"Simon" <S.*******@shos.co.ukwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
Can any one see a problem with this code ( it does not work

Private Sub cmdEmailOrderPlaced_Click()
'On Error GoTo Error_Handler
Dim strTo As String
Dim strBody As String
Dim strSubject As String
Dim strItemsOrdered As String
Dim CRS1 As ADODB.Recordset
Dim strSql As String
Set CRS1 = New ADODB.Recordset

sstrSql = "SELECT * FROM tblOrderProduct WHERE OrderNumber =" &
Me.OrderNumber
CRS1.Open strSql, CurrentProject.Connection, adOpenKeyset,
adLockReadOnly
While Not CRS1.EOF

strItemsOrdered = strItemsOrdered & "Item : " &
DLookup("[ProductName]", "tblProduct", "[ProductCode] =" &
CRS1.Fields("ProductCode"))

strItemsOrdered = strItemsOrdered & "Unit Price : " & Chr(163) &
DLookup("[PriceIncVAT ]", "tblProduct", "[ProductID] =" &
CRS1.Fields("ProductID")) & vbCrLf

'& vbCrLf & "Total Excl VAT: " & Chr(163) &
Format(CRS1.Fields("TotalExVAT"), "##0.00")

If IsNull(CRS1.Fields("Discount")) Or CRS1.Fields("Discount") = 0
Then
'Don't put discount in there if there isn't one
Else
strItemsOrdered = strItemsOrdered & "Discount : " &
CRS1.Fields("Discount") & "%" & vbCrLf
End If
strItemsOrdered = strItemsOrdered & "Total (Inc VAT): " & Chr(163)
& Format(CRS1.Fields("TotalIncVAT"), "##0.00") & vbCrLf & vbCrLf
CRS1.MoveNext
Wend
CRS1.Close
Set CRS1 = Nothing

I don't know if you used copy/paste to post the message, but per the
code that was posted, you are not setting a value for strSql. The
assignment is for sstrSql. That would account for the error that you are
getting.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

Aug 13 '06 #7

P: n/a
"Terry Kreft" <te*********@mps.co.ukwrote in
news:4B********************@karoo.co.uk:
... do you have Option Explicit in the declarations section of
your module? If not, put it there and at the top of all your
modules. Then from the VBA IDE go to Tools/Options and tick the
"Require Variable Declaration" option, this is off by default
(Microsofts stupid make it easy to make a mess of things) but
should be the first thing you switch on whenever you install
Access.
Actually, it was Microsoft's stupid decision to make the Access VBE
consistent with VBE in all the other office apps because,
er, um, well, because consistency is a value all by itself, even
though fewer than .05% of those who code in Access are coming to
Access from coding in Excel or Word.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 14 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.