Master Form/Subform problems

[jwr]

Following is the SQL View of my subform. This is a subform within my Orders
form.

It contains: Line Item, ProductID, Product Description, Order Codes, Serial
Number, Quantity, and Unit Price. I can change the unit price and it does
not change my master Product Form. However, if I change order codes, they
are changed on my master Product Form.

How do I create a subform that will pull the product information from the
product form, BUT allow change on each customer's order IF it is not an
exact match with the master file and not change the master file??

I hope I explained myself. I don't quite know how to word this.

Thanks in advance,
JR


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;

 

[Steve Schapel]

JR,

The query that the subform is based on includes a ProductCode field from
the Products table. Is that what you are referring to? Do you mean you
want the form to show the ProductCode for the selected product, but then
for you to complete the order with the option of changing this value for
the specific order? If so, I think you will need to add a field in your
OrderDetails table for this order code. And then have a textbox on the
subform for this new field, instead of the existing ProductCode control.

How are you entering the product into the order details subform? Is it
via a combobox which lists your products? If so, I would assume the Row
Source of this combobox would be based on the Products table. If so,
you could make this a multi-column combobox, so that it includes the
ProductCode field, and use code on the After Update event of the
combobox like this...
Me.OrderCode = Me.ProductID.Column(2)
(assuming the ProductCode is the 3rd column in the combobox's row source)
This will set the value of the OrderCode to the ProductCode of the
selected product, but also allow you to change it if you wish, and if so
the changes will be written to the new OrderCode field in the Order
Details table, rather than back to the ProductCode field in the Products
table.

Hope this makes sense, and hope that I have correctly understood the
situation you are working with there.

By the way, if you do as suggested, then I think you would not need to
include the Products table in the subform's Record Source query at all,
in other words it could be simplified to...
SELECT [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], [Order
Details].OrderCode, [HandlingPct]*Round([Quantity]*[Order
Details].UnitPrice*(1-[Discount]),2) AS HandlingChg, [Order Details].Notes
FROM [Order Details]
ORDER BY [Order Details].LineItem;

--
Steve Schapel, Microsoft Access MVP

Following is the SQL View of my subform. This is a subform within my Orders
form.

It contains: Line Item, ProductID, Product Description, Order Codes, Serial
Number, Quantity, and Unit Price. I can change the unit price and it does
not change my master Product Form. However, if I change order codes, they
are changed on my master Product Form.

How do I create a subform that will pull the product information from the
product form, BUT allow change on each customer's order IF it is not an
exact match with the master file and not change the master file??

I hope I explained myself. I don't quite know how to word this.

Thanks in advance,
JR


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;

 

[jwr]

You understood exactly what I was attempting.

I have a product form containing 500+ products with description, order
codes, unit price, and weight of each product.

To enter the products on the order details subform, I have a multi-column
(3) combo box that lists the product and "pulls" forward the product
description, order codes, and unit price so, assuming all things match the
customer's order, so no entry will be required. If, however, the customer,
for example, adds an order code that will also increase/decrease the selling
price, I would like to be able to change the description, order code and
price WITHOUT changing the master table.

I am really not very smart when it comes to this. I have gotten lucky and
gotten things to work so far.

I think that I should do exactly as you suggested. Please guide me.
In my Order Details Table I have: In My Products
Table I Have
Product ID
Product ID
Quantity
Product Name
Unit Price
Product Code
Line Item
Quantity
SerialNum Unit
Price

Line Item

SerialNum

On my subform, the ProductID from the Orders Details is pulling the Product
Name description forward. That is what I want; I am not sure why I have to
use Product ID instead of Product Name to get the correct information.

Do I need to add Product Name and Product Code to the Order Details Table?

Please bear with me. It is so refreshing to find someone who will take the
time to assist. AND it is very rewarding to learn something new.

Thanks,
JR

JR,

The query that the subform is based on includes a ProductCode field from
the Products table. Is that what you are referring to? Do you mean you
want the form to show the ProductCode for the selected product, but then
for you to complete the order with the option of changing this value for
the specific order? If so, I think you will need to add a field in your
OrderDetails table for this order code. And then have a textbox on the
subform for this new field, instead of the existing ProductCode control.

How are you entering the product into the order details subform? Is it
via a combobox which lists your products? If so, I would assume the Row
Source of this combobox would be based on the Products table. If so, you
could make this a multi-column combobox, so that it includes the
ProductCode field, and use code on the After Update event of the combobox
like this...
Me.OrderCode = Me.ProductID.Column(2)
(assuming the ProductCode is the 3rd column in the combobox's row source)
This will set the value of the OrderCode to the ProductCode of the
selected product, but also allow you to change it if you wish, and if so
the changes will be written to the new OrderCode field in the Order
Details table, rather than back to the ProductCode field in the Products
table.

Hope this makes sense, and hope that I have correctly understood the
situation you are working with there.

By the way, if you do as suggested, then I think you would not need to
include the Products table in the subform's Record Source query at all, in
other words it could be simplified to...
SELECT [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], [Order
Details].OrderCode, [HandlingPct]*Round([Quantity]*[Order
Details].UnitPrice*(1-[Discount]),2) AS HandlingChg, [Order Details].Notes
FROM [Order Details]
ORDER BY [Order Details].LineItem;

--
Steve Schapel, Microsoft Access MVP

Following is the SQL View of my subform. This is a subform within my
Orders form.

It contains: Line Item, ProductID, Product Description, Order Codes,
Serial Number, Quantity, and Unit Price. I can change the unit price and
it does not change my master Product Form. However, if I change order
codes, they are changed on my master Product Form.

How do I create a subform that will pull the product information from the
product form, BUT allow change on each customer's order IF it is not an
exact match with the master file and not change the master file??

I hope I explained myself. I don't quite know how to word this.

Thanks in advance,
JR


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;

 

[Steve Schapel]

JR,

Sorry, the line wrapping in the newsreader made it very difficult to
decipher your list of table fields. But I am still confused about the
same thing I was confused about before... You are talking about "order
codes", but I don't see any such field listed in either table.

The general concept here, I think, is that apparently the Unit Price as
stored in the Products table, you want to use as the *default* price for
the product when processing the order, but it ain't necessarily so...
you might want to change it. Right? So, that means you need a Unit
Price field in both tables. One in the Products table, to show what the
default price for the product will be. And one in the Order Details
table, to show what the actual price will be. And of course, in many
cases, the value in these two fields will be the same as each other.
But sometimes they won't. So, the instance of the Unit Price that you
see on the Order Details subform should be the one form the Order
Details table. But at the point of entry of an order, you also need to
have a procedure to automatically enter the default price as per the
Products table. So, to stick with the Unit Price example, it sounds
like your productID combobox on the subform has the Products.UnitPrice
field as one of the columns in the combobox's Row Source. It is not
clear to me which column it is, but let's say it is the 3rd column. So,
as I suggested before, you would put code like this on the After Update
event of the combobox...
Me.Unit_Price = Me.ProductID.Column(2)
(this assumes that the combobox is named ProductID. Column numbering
starts at 0, so Column(2) is the 3rd column.

Hope that moves you forward a but further.

I would not expect you would need a Product Name field in the Order
Details table, the reason being that the Product Name, as defined in the
Products table, is the same Product Name that you will use for the
orders, in other words I presume you will not have occasion to show the
product name on the Order Details form different from the existing
Product Name. As for the Product Code, though, I am still not sure what
that refers to.

 

[jwr]

Steve -

I think we are on the same wave link. I will attempt to do as you suggest.
If I have more problems, may I reply to this same post, or should I repost?

Again, thank you,
JR

 

[Steve Schapel]

If you post any further questions back to this thread, JR, than I will
see it. On the other hand, posting to a new thread will give a better
chance of a fresh pair of eyes seeing it.