Drop Down List Box Problem

[jwr]

I have an order details subform that has the following fields:
LineItem
ProductCode
ProductDescription
Quantity
UnitPrice


I want to enter the line item number and then have the ProductCode field as
a drop down list box where the product code is pulled from the Products
table. When that code is selected, I want the description and unit price
for that particular code (also in Products table) to pull forward also. How
do I achieve this? or can it be done?

Thanks in advance,
JR

 

[Guest]

To accomplish what you want, add a combo box to your form. Right click on
the combo box and select the Properties optons to display the Properties of
the combo box. On the Data tab place your cursor in the Row Source field.
You will see a button with three dots at the right end of the field. Click
the button to display the QBE window. Add the Products table to the QBE. Then
place the Product Code field, then the Description field and then the Unit
Price field. Close the QBE window. On the Format tab of the Properties
dialog box, set the Column Count to 3.

I will assume that you have a text box for the Description field and one for
the Unit Price. Select the Description field. In the Data tab of the
properties dialog box for the Description field, add the following line to
the Control Source:

=Combo2.column(1)

Where I have Combo2 put the name of your combo box control.

Select the Unit Price field. In the Data tab of the properties dialog box
for the Unit Price field, add the following line to the Control Source:

=Combo2.column(2)

Where I have Combo2 put the name of your combo box control.

Try your form. You should be able to pull down the combo box and see the
list of product codes. Selecting one will cause each of the other two fields
to be entered into each of the respective fields.

Just a note: If you already have the Product table, then you do not have to
write the description and unit price to any other table. You can simply link
the subform that displays data from the products table to the main form that
has the Product code field. Link the two forms by the product code fields.

 

[jwr]

Mr. B --

Thank you for your response. I must say, it is one of the easiest to follow
that I have seen. thank you for your patience with a novice like me.

I have a question please. I created the combo box; right clicked, selected
3 dots to right of Row Source, added Products table to the
QBE, Product Code, Description and Unit Price. When I attempted to close
the QBE window, I get error: "You have entered an operand without an
operator."

How should this look?
=[Products]![ProductCode][Products]!ProductDescription][Products]!ProductUni
tPrice]

Or

since this is based upon the products table: =
[ProductCode][ProductDescription][ProductUnitPrice]


I was unable to continue past this due to the error.

Thank you in advance.

 

[Guest]

You may have used the QBE incorrectly. Just use it to design a query just as
you would design any other query. Then when you close the QBE, Access will
use the SQL statement from the QBE to build the correct statement in the Row
Source for your combo box.

The statement in the Row Source property of your combo box will actually
look something like:

SELECT tblProducts.Product, tblProducts.Description, tblProducts.Price FROM
tblProducts;

If you have more problems using the QBE, take a look in the Access help file.

Any combo box or list simply uses an SQL statement to return the list that
it then displays. These SQL statements can be quite simple or very complex.

--
HTH

Mr B

Mr. B --

Thank you for your response. I must say, it is one of the easiest to follow
that I have seen. thank you for your patience with a novice like me.

I have a question please. I created the combo box; right clicked, selected
3 dots to right of Row Source, added Products table to the
QBE, Product Code, Description and Unit Price. When I attempted to close
the QBE window, I get error: "You have entered an operand without an
operator."

How should this look?
=[Products]![ProductCode][Products]!ProductDescription][Products]!ProductUni
tPrice]

Or

since this is based upon the products table: =
[ProductCode][ProductDescription][ProductUnitPrice]


I was unable to continue past this due to the error.

Thank you in advance.

To accomplish what you want, add a combo box to your form. Right click on
the combo box and select the Properties optons to display the Properties of
the combo box. On the Data tab place your cursor in the Row Source field.
You will see a button with three dots at the right end of the field. Click
the button to display the QBE window. Add the Products table to the QBE. Then
place the Product Code field, then the Description field and then the Unit
Price field. Close the QBE window. On the Format tab of the Properties
dialog box, set the Column Count to 3.

I will assume that you have a text box for the Description field and one for
the Unit Price. Select the Description field. In the Data tab of the
properties dialog box for the Description field, add the following line to
the Control Source:

=Combo2.column(1)

Where I have Combo2 put the name of your combo box control.

Select the Unit Price field. In the Data tab of the properties dialog box
for the Unit Price field, add the following line to the Control Source:

=Combo2.column(2)

Where I have Combo2 put the name of your combo box control.

Try your form. You should be able to pull down the combo box and see the
list of product codes. Selecting one will cause each of the other two fields
to be entered into each of the respective fields.

Just a note: If you already have the Product table, then you do not have to
write the description and unit price to any other table. You can simply link
the subform that displays data from the products table to the main form that
has the Product code field. Link the two forms by the product code fields.

 

[jwr]

Please excuse my ignorance. I must be doing something wrong and it is so
simple that I cannot see it! Below is the SQL for a form that is working
correctly. (I did not design this so I am not understanding why it is not
working when I change it.)


SELECT DISTINCTROW [Order Details].OrderDetailID, [Order Details].OrderID,
[Order Details].LineItem, [Order Details].ProductID, [Order
Details].SerialNum, [Order Details].Quantity, [Order Details].UnitPrice,
[Order Details].Discount, Round([Quantity]*[Order
Details].UnitPrice*(1-[Discount]),2) AS [Line Total], Products.ProductCode,
[HandlingPct]*Round([Quantity]*[Order Details].UnitPrice*(1-[Discount]),2)
AS HandlingChg, [Order Details].Notes
FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID
ORDER BY [Order Details].LineItem;

This form has a combo box for Product Description.
SELECT DISTINCTROW Products.*
FROM Products
ORDER BY Products.ProductName;


When I attempt to change this where the combo box is on Product Code, then I
can no longer enter a line item, and I am getting incorrect info in the
product code box.

I have looked at access help, but cannot see what I am doing wrong.

Thank you.

You may have used the QBE incorrectly. Just use it to design a query just as
you would design any other query. Then when you close the QBE, Access will
use the SQL statement from the QBE to build the correct statement in the Row
Source for your combo box.

The statement in the Row Source property of your combo box will actually
look something like:

SELECT tblProducts.Product, tblProducts.Description, tblProducts.Price FROM
tblProducts;

If you have more problems using the QBE, take a look in the Access help file.

Any combo box or list simply uses an SQL statement to return the list that
it then displays. These SQL statements can be quite simple or very complex.

--
HTH

Mr B

Mr. B --

Thank you for your response. I must say, it is one of the easiest to follow
that I have seen. thank you for your patience with a novice like me.

I have a question please. I created the combo box; right clicked, selected
3 dots to right of Row Source, added Products table to the
QBE, Product Code, Description and Unit Price. When I attempted to close
the QBE window, I get error: "You have entered an operand without an
operator."

How should this look?
=[Products]![ProductCode][Products]!ProductDescription][Products]!ProductUni
tPrice]

Or

since this is based upon the products table: =
[ProductCode][ProductDescription][ProductUnitPrice]


I was unable to continue past this due to the error.

Thank you in advance.

To accomplish what you want, add a combo box to your form. Right click on
the combo box and select the Properties optons to display the

Properties
of

the combo box. On the Data tab place your cursor in the Row Source field.
You will see a button with three dots at the right end of the field. Click
the button to display the QBE window. Add the Products table to the

QBE.
Then

place the Product Code field, then the Description field and then the Unit
Price field. Close the QBE window. On the Format tab of the Properties
dialog box, set the Column Count to 3.

I will assume that you have a text box for the Description field and

one
for

the Unit Price. Select the Description field. In the Data tab of the
properties dialog box for the Description field, add the following line to
the Control Source:

=Combo2.column(1)

Where I have Combo2 put the name of your combo box control.

Select the Unit Price field. In the Data tab of the properties dialog box
for the Unit Price field, add the following line to the Control Source:

=Combo2.column(2)

Where I have Combo2 put the name of your combo box control.

Try your form. You should be able to pull down the combo box and see the
list of product codes. Selecting one will cause each of the other two fields
to be entered into each of the respective fields.

Just a note: If you already have the Product table, then you do not

have
to

write the description and unit price to any other table. You can

simply
link

the subform that displays data from the products table to the main

form
that

has the Product code field. Link the two forms by the product code fields.

--
HTH

Mr B


:

I have an order details subform that has the following fields:
LineItem
ProductCode
ProductDescription
Quantity
UnitPrice


I want to enter the line item number and then have the ProductCode

field
as

a drop down list box where the product code is pulled from the Products
table. When that code is selected, I want the description and unit price
for that particular code (also in Products table) to pull forward

also.
How

do I achieve this? or can it be done?

Thanks in advance,
JR

 

[jwr]

Could you look a t this for me. I cannot get it to work.

Please excuse my ignorance. I must be doing something wrong and it is so
simple that I cannot see it! Below is the SQL for a form that is working
correctly. (I did not design this so I am not understanding why it is not
working when I change it.)


SELECT DISTINCTROW [Order Details].OrderDetailID, [Order Details].OrderID,
[Order Details].LineItem, [Order Details].ProductID, [Order
Details].SerialNum, [Order Details].Quantity, [Order Details].UnitPrice,
[Order Details].Discount, Round([Quantity]*[Order
Details].UnitPrice*(1-[Discount]),2) AS [Line Total], Products.ProductCode,
[HandlingPct]*Round([Quantity]*[Order Details].UnitPrice*(1-[Discount]),2)
AS HandlingChg, [Order Details].Notes
FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID
ORDER BY [Order Details].LineItem;

This form has a combo box for Product Description.
SELECT DISTINCTROW Products.*
FROM Products
ORDER BY Products.ProductName;


When I attempt to change this where the combo box is on Product Code, then I
can no longer enter a line item, and I am getting incorrect info in the
product code box.

I have looked at access help, but cannot see what I am doing wrong.

Thank you.

You may have used the QBE incorrectly. Just use it to design a query

just

as
you would design any other query. Then when you close the QBE, Access will
use the SQL statement from the QBE to build the correct statement in the Row
Source for your combo box.

The statement in the Row Source property of your combo box will actually
look something like:

SELECT tblProducts.Product, tblProducts.Description, tblProducts.Price FROM
tblProducts;

If you have more problems using the QBE, take a look in the Access help file.

Any combo box or list simply uses an SQL statement to return the list that
it then displays. These SQL statements can be quite simple or very complex.

--
HTH

Mr B

=[Products]![ProductCode][Products]!ProductDescription][Products]!ProductUni

tPrice]

Or

since this is based upon the products table: =
[ProductCode][ProductDescription][ProductUnitPrice]


I was unable to continue past this due to the error.

Thank you in advance.




To accomplish what you want, add a combo box to your form. Right click on
the combo box and select the Properties optons to display the Properties
of
the combo box. On the Data tab place your cursor in the Row Source field.
You will see a button with three dots at the right end of the field. Click
the button to display the QBE window. Add the Products table to the QBE.
Then
place the Product Code field, then the Description field and then

the

Unit

dialog

box

 

[jwr]

Could you look a t this for me. I cannot get it to work.

Please excuse my ignorance. I must be doing something wrong and it is so simple that I cannot see it! Below is the SQL for a form that is working
correctly. (I did not design this so I am not understanding why it is not
working when I change it.)


SELECT DISTINCTROW [Order Details].OrderDetailID, [Order Details].OrderID,
[Order Details].LineItem, [Order Details].ProductID, [Order
Details].SerialNum, [Order Details].Quantity, [Order Details].UnitPrice,
[Order Details].Discount, Round([Quantity]*[Order
Details].UnitPrice*(1-[Discount]),2) AS [Line Total], Products.ProductCode,
[HandlingPct]*Round([Quantity]*[Order Details].UnitPrice*(1-[Discount]),2)
AS HandlingChg, [Order Details].Notes
FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID
ORDER BY [Order Details].LineItem;

This form has a combo box for Product Description AND is not working:

SELECT DISTINCTROW [Order Details].OrderDetailID, [Order Details].OrderID,
[Order Details].LineItem, Products.ProductName, [Order Details].SerialNum,
[Order Details].Quantity, [Order Details].UnitPrice, [Order
Details].Discount, Round([Quantity]*[Order
Details].UnitPrice*(1-[Discount]),2) AS [Line Total], Products.ProductCode,
[HandlingPct]*Round([Quantity]*[Order Details].UnitPrice*(1-[Discount]),2)
AS HandlingChg, [Order Details].Notes
FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID
ORDER BY [Order Details].LineItem;
;

When I attempt to change this where the combo box is on Product Code,

then

I
can no longer enter a line item, and I am getting incorrect info in the
product code box.

I have looked at access help, but cannot see what I am doing wrong.

Thank you.

just the
Row help
file. to
follow

=[Products]![ProductCode][Products]!ProductDescription][Products]!ProductUni

tPrice]

Or

since this is based upon the products table: =
[ProductCode][ProductDescription][ProductUnitPrice]


I was unable to continue past this due to the error.

Thank you in advance.




To accomplish what you want, add a combo box to your form. Right click on
the combo box and select the Properties optons to display the Properties
of
the combo box. On the Data tab place your cursor in the Row

Source
field.

You will see a button with three dots at the right end of the

field.
Click

the button to display the QBE window. Add the Products table to

the
QBE.

Then
place the Product Code field, then the Description field and then

the

Unit
Price field. Close the QBE window. On the Format tab of the Properties
dialog box, set the Column Count to 3.

I will assume that you have a text box for the Description field

and
one

for
the Unit Price. Select the Description field. In the Data tab of the
properties dialog box for the Description field, add the following line to
the Control Source:

=Combo2.column(1)

Where I have Combo2 put the name of your combo box control.

Select the Unit Price field. In the Data tab of the properties

dialog

box
for the Unit Price field, add the following line to the Control Source:

=Combo2.column(2)

Where I have Combo2 put the name of your combo box control.

Try your form. You should be able to pull down the combo box and

see
the

list of product codes. Selecting one will cause each of the other two
fields
to be entered into each of the respective fields.

Just a note: If you already have the Product table, then you do

not
have

to
write the description and unit price to any other table. You can simply
link
the subform that displays data from the products table to the main form
that
has the Product code field. Link the two forms by the product code
fields.

--
HTH

Mr B


:

I have an order details subform that has the following fields:
LineItem
ProductCode
ProductDescription
Quantity
UnitPrice


I want to enter the line item number and then have the

ProductCode
field

as
a drop down list box where the product code is pulled from the Products
table. When that code is selected, I want the description and unit
price
for that particular code (also in Products table) to pull

forward
also.

How
do I achieve this? or can it be done?

Thanks in advance,
JR