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

Help with Code

P: n/a
Hi, I need some help form someone.

I use my database for my online shop, so once I have entered a order
onto the database I have a button that creates the Email to the
customer to let them know the order has been placed.

I use to use [ProductID] as a auto number but I have just changed it to
[ProductCode] Which is not a auto number as I type them in.

But now my VB code for the email button does not work.
Can any one help me

Old Code

strSql = "SELECT * FROM tblOrderProduct WHERE OrderNumber =" &
Me.OrderNumber
CRS1.Open strSql, CurrentProject.Connection, adOpenKeyset,
adLockReadOnly
While Not CRS1.EOF
strItemsOrdered = strItemsOrdered & "Item : " & _
DLookup("[ProductName]", "tblProduct", "[ProductID] =" &
CRS1.Fields("ProductID")) & vbCrLf & "Qty : " &
CRS1.Fields("Quantity") & vbCrLf

strItemsOrdered = strItemsOrdered & "Unit Price : " & Chr(163) &
DLookup("[PriceIncVAT ]", "tblProduct", "[ProductID] =" &
CRS1.Fields("ProductID")) & vbCrLf
The email use to look like this

Item : Test Strips
Qty : 2
Unit Price : 15
Total (Inc VAT): 30.00

I have changed ProductID to ProductCode in the VB code but it does not
work

strSql = "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")) & vbCrLf & "Qty : " &
CRS1.Fields("Quantity") & vbCrLf

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

Can any one help I am new to all this

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


P: n/a
Simon wrote:
Hi, I need some help form someone.

I use my database for my online shop, so once I have entered a order
onto the database I have a button that creates the Email to the
customer to let them know the order has been placed.

I use to use [ProductID] as a auto number but I have just changed it to
[ProductCode] Which is not a auto number as I type them in.

But now my VB code for the email button does not work.
Can any one help me

Old Code

strSql = "SELECT * FROM tblOrderProduct WHERE OrderNumber =" &
Me.OrderNumber
CRS1.Open strSql, CurrentProject.Connection, adOpenKeyset,
adLockReadOnly
While Not CRS1.EOF
strItemsOrdered = strItemsOrdered & "Item : " & _
DLookup("[ProductName]", "tblProduct", "[ProductID] =" &
CRS1.Fields("ProductID")) & vbCrLf & "Qty : " &
CRS1.Fields("Quantity") & vbCrLf

strItemsOrdered = strItemsOrdered & "Unit Price : " & Chr(163) &
DLookup("[PriceIncVAT ]", "tblProduct", "[ProductID] =" &
CRS1.Fields("ProductID")) & vbCrLf
The email use to look like this

Item : Test Strips
Qty : 2
Unit Price : 15
Total (Inc VAT): 30.00

I have changed ProductID to ProductCode in the VB code but it does not
work

strSql = "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")) & vbCrLf & "Qty : " &
CRS1.Fields("Quantity") & vbCrLf

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

Can any one help I am new to all this
Go to Queries/New/Design. Add tables tblOrderProduct and tblProduct.
Link ProductCode between the two tables. Drag ProductDesc to a column
too. Then run the query. Do you get any results?

Personally, I'd save the query when you get the query correct, click
View/SQL, copy the SQL, and make strSQL that string. You'll need to
modify the SQL string with quotes as needed to separate the variable
OrderNumber like you have above but that way you don't need the Dlookup.
Aug 12 '06 #2

P: n/a
"Simon" <S.*******@shos.co.ukwrote in
news:11**********************@b28g2000cwb.googlegr oups.com:
Hi, I need some help form someone.

I use my database for my online shop, so once I have entered a
order onto the database I have a button that creates the Email
to the customer to let them know the order has been placed.

I use to use [ProductID] as a auto number but I have just
changed it to [ProductCode] Which is not a auto number as I
type them in.

But now my VB code for the email button does not work.
Can any one help me

Old Code

strSql = "SELECT * FROM tblOrderProduct WHERE OrderNumber =" &
Me.OrderNumber
CRS1.Open strSql, CurrentProject.Connection, adOpenKeyset,
adLockReadOnly
While Not CRS1.EOF
strItemsOrdered = strItemsOrdered & "Item : " &
_
DLookup("[ProductName]", "tblProduct", "[ProductID] ="
&
CRS1.Fields("ProductID")) & vbCrLf & "Qty : " &
CRS1.Fields("Quantity") & vbCrLf

strItemsOrdered = strItemsOrdered & "Unit Price : " &
Chr(163) & DLookup("[PriceIncVAT ]", "tblProduct",
"[ProductID] =" & CRS1.Fields("ProductID")) & vbCrLf
The email use to look like this

Item : Test Strips
Qty : 2
Unit Price : 15
Total (Inc VAT): 30.00

I have changed ProductID to ProductCode in the VB code but it
does not work

strSql = "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")) & vbCrLf & "Qty : " &
CRS1.Fields("Quantity") & vbCrLf

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

Can any one help I am new to all this
Is productID stored in a text field?

If you have changed the type from (auto) number to text, you will
have to add some delimiters (quotes) to the Dlookup code

To add delimiters inside a string that's already in a string, you
have to double thenm up.

The existing.
DLookup("[ProductName]", "tblProduct", "[ProductID] =" &
CRS1.Fields("ProductID"))

needs to be
DLookup("[ProductName]", "tblProduct", "[ProductID] =""" &
CRS1.Fields("ProductID") & """")

Make the same change in the other dlookup.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 12 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.