Code help

S

Simon

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
 
S

strive4peace

Hi Simon,

change

ProductID --> ProductCode

in your code

also, if ProductCode is text, you will need to delimit the values with
quotes

Why did you quit using the autonumber ProductID? That is a great way to
relate tables

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
S

Simon

Hi thanks for helping

The reson i changed from the autonumber ProductID to typeing my own
Product Codes it that i auto number were no use for when i was placing
an order so i wanted to type in product codes, there for i changed it.
Thisnking about it now i could of left autonumber and also added
product code.

Yes ProductCode is a text field. How do u delimit the values with
quotes. i am new to all this


Thanlks

Simon said:
Hi Simon,

change

ProductID --> ProductCode

in your code

also, if ProductCode is text, you will need to delimit the values with
quotes

Why did you quit using the autonumber ProductID? That is a great way to
relate tables

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.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
Click to expand...
Click to expand...
 
S

strive4peace

Hi Simon,

You can still use ProductID to relate behind the scenes but pick
ProductCode...and this is best...

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Hi thanks for helping

The reson i changed from the autonumber ProductID to typeing my own
Product Codes it that i auto number were no use for when i was placing
an order so i wanted to type in product codes, there for i changed it.
Thisnking about it now i could of left autonumber and also added
product code.

Yes ProductCode is a text field. How do u delimit the values with
quotes. i am new to all this


Thanlks

Simon said:
Hi Simon,

change

ProductID --> ProductCode

in your code

also, if ProductCode is text, you will need to delimit the values with
quotes

Why did you quit using the autonumber ProductID? That is a great way to
relate tables

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dlookup not working 2
INSERT INTO 4
Problem with INSERT INTO code 5
Insert into code 1
Add record, then show it on the form 7
Form question 1
Help with Data Control Source 1
At a Loss - Need some code help please 3

Top