Drawing Price from a Lookup Table Outside the Table the Form is Built On

[PinkDuster]

Thank you for your helpful suggestions so far with my OrderDetail subform. I am able to add products with a combobox, and select the PriceType (retail or wholesale) from a related table (tblPriceType) using a SELECT DISTINCT FROM INNER JOIN statement. I would like to be able to have the "Sold As Price" field populate with the correct price from the tblPriceType After Update from the PriceType selection, but keep getting errors. Attached is a snapshot of the subform (the prices were manually typed in to check that the line total and subtotal calculations were working), and of the tblProducts with child table tblPriceType.

[ssanfu]

Can't really tell what your table designs/relationships are, but just looking at the image above, I would use the "PriceType" combo box to get the prices, then code in the combo box after update event would enter the price into "PRICE" field.


What is the SQL of the "PriceType" combo box Row source?


It would help to see the dB. Would you post it?

[PinkDuster]

Below is the Row Source for the PriceType combo box:

SELECT DISTINCT lkpPriceType FROM tblProducts INNER JOIN tblProductPrices ON tblProducts.ProductID = tblProductPrices.fkProdID;

And here is the AfterUpdate procedure for the SoldAsPrice:

Private Sub fkPriceType_AfterUpdate()
Me![UnitPrice] = Me![SoldAsPrice]

fkPriceType_AfterUpdate_Exit:
Exit Sub

End Sub

And here are the table relationships



Thank you for any suggestions1

[June7]

What is the exact error message? I expect it has something to do with the UnitPrice field not existing on the form.

If you want to save price into SoldAsPrice, your expression is backwards.

Each product can have a wholesale and a retail price? Those are the only two types?

Include the UnitPrice as a column in the combobox. Then reference the combobox column by index to save the price into SoldAsPrice. Index begins with 0 so if the price is in second column its index is 1.

Might want to limit the combobox to only the two options relevant to selected product.

SELECT lkpPriceType, UnitPrice FROM tblProductPrices WHERE tblProductPrices.fkProdID = [ProductID];

Me![SoldAsPrice] = Me.cbxPrice.Column(1)

Then code in fkPriceType combobox GotFocus event to requery its RowSource: Me.fkPriceType.Requery

[PinkDuster]

What is the exact error message? I expect it has something to do with the UnitPrice field not existing on the form.

If you want to save price into SoldAsPrice, your expression is backwards.

Exactly, that statement in the VBA code was highlighted. A rookie mistake.

Each product can have a wholesale and a retail price? Those are the only two types? Correct there are two types in the PriceTypes table.

Include the UnitPrice as a column in the combobox. Then reference the combobox column by index to save the price into SoldAsPrice. Index begins with 0 so if the price is in second column its index is 1. (Yes, did that)

Might want to limit the combobox to only the two options relevant to selected product. (Check, did that)

SELECT lkpPriceType, UnitPrice FROM tblProductPrices WHERE tblProductPrices.fkProdID = [ProductID];

Me![SoldAsPrice] = Me.cbxPrice.Column(1)

Both of the above fixed the problem!

Then code in fkPriceType combobox GotFocus event to requery its RowSource: Me.fkPriceType.Requery

When I added this code, then if I switched the price type in a new row, all previous price types & their associated prices switched. So I took this out. I already had a requery VBA in the Enter event for the fkPriceType combobox.

Thank you!!

[June7]

Sounds like the combobox not bound to fkPriceType field.

What is the form's RecordSource - table, query, SQL statement?

If you want to provide db for analysis, follow instructions at bottom of my post.

[PinkDuster]

Thank you, please find attached.

[June7]

The qryOrderDetails shows LineItemID data twice. Could change the SQL so that does not happen. However, since the query is referencing autonumber PK LineItemID to order records, probably don't need ORDER BY in query. The query (or use table as RecordSource) should order by the PK index by default.

SELECT tblOrderDetails.* FROM tblOrderDetails ORDER BY tblOrderDetails.LineItemID;

TotalPrice should not be a field in OrderDetails unless you use Calculated type field.


Should enforce referential integrity. Use the combobox NotInList event if users need to add products 'on the fly'.

For fkProductID combobox, none of the fields from tblProductPrices are retrieved. tblProductPrices and DISTINCT are not needed.
RowSource: SELECT ProductID, Description FROM tblProducts;
ColumnCount: 2
ColumnWidths: 0";1"

Advise naming combobox different from the field it is bound to, such as cbxProd. No event code needed for this combobox.


Why are you duplicating product description into OrderDetails? Textbox for ProductDescription should have an expression: =[cbxProd].Column(1)


Should not need code to save quantity since this is a direct entry textbox bound to field. Set its DefaultValue to 1 if you want make sure value is greater than 0.


For fkPriceType combobox things get complicated. Since each product can have 2 price types, best to restrict user options to only the type records associated with selected product. This is called 'cascading' or 'dependent' comboboxes. Normalization calls for the PK of tblProductPrices to be saved and the descriptive text alias would just be displayed. Unfortunately this does not work nicely on continuous or datasheet forms. There are many, many, many threads discussing this issue and how to deal with. However, if you use save the descriptive type then don't have the alias issue. Won't be able to link the two tables in queries but really would not need to if you save the type and price. As already suggested for RowSource:

SELECT lkpPriceType, UnitPrice FROM tblProductPrices WHERE tblProductPrices.fkProdID = [cbxProd];

Then code in this combobox GotFocus event to requery and AfterUpdate event to save price.


If you prefer to have one combobox serve for product/type/price selection, then user would either be required to select item from dropdown or the combobox RowSource would have to include a concatenated field for user typing to match.

RowSource: SELECT ProductID, [Description] & ":" & [lkpPriceType] AS ProdType, UnitPrice, lkpPriceType, [Description]
FROM tblProducts INNER JOIN tblProductPrices ON tblProducts.ProductID = tblProductPrices.fkProdID;
ColumnCount: 5
ColumnWidths: 0";2";0";0";0"

And code in this combobox AfterUpdate event would save the type ID and/or its text description and/or price.

[PinkDuster]

Should enforce referential integrity. Use the combobox NotInList event if users need to add products 'on the fly'.

The reason I'm not enforcing the referential integrity is that this database will serve both "inventory" products, which belong in the Products table, and "non-inventory" [ad hoc] products which I don't want to add to the Products table. I want to capture the ad hoc products in the Orders/OrderDetails, but not bulk up the Products table since that will be used to manage inventory.

Having said that, does that change the remainder of your assessment?

Thank you very much for all your assistance!

For fkProductID combobox, none of the fields from tblProductPrices are retrieved. tblProductPrices and DISTINCT are not needed.
RowSource: SELECT ProductID, Description FROM tblProducts;
ColumnCount: 2
ColumnWidths: 0";1"



For fkPriceType combobox things get complicated. Since each product can have 2 price types, best to restrict user options to only the type records associated with selected product. This is called 'cascading' or 'dependent' comboboxes. Normalization calls for the PK of tblProductPrices to be saved and the descriptive text alias would just be displayed. Unfortunately this does not work nicely on continuous or datasheet forms. There are many, many, many threads discussing this issue and how to deal with. However, if you use save the descriptive type then don't have the alias issue. Won't be able to link the two tables in queries but really would not need to if you save the type and price. As already suggested for RowSource:

SELECT lkpPriceType, UnitPrice FROM tblProductPrices WHERE tblProductPrices.fkProdID = [cbxProd];

Then code in this combobox GotFocus event to requery and AfterUpdate event to save price.


If you prefer to have one combobox serve for product/type/price selection, then user would either be required to select item from dropdown or the combobox RowSource would have to include a concatenated field for user typing to match.

RowSource: SELECT ProductID, [Description] & ":" & [lkpPriceType] AS ProdType, UnitPrice, lkpPriceType, [Description]
FROM tblProducts INNER JOIN tblProductPrices ON tblProducts.ProductID = tblProductPrices.fkProdID;
ColumnCount: 5
ColumnWidths: 0";2";0";0";0"

And code in this combobox AfterUpdate event would save the type ID and/or its text description and/or price.[/QUOTE]

[PinkDuster]

SELECT lkpPriceType, UnitPrice FROM tblProductPrices WHERE tblProductPrices.fkProdID = [cbxProd];

Then code in this combobox GotFocus event to requery and AfterUpdate event to save price.

I tried replacing my source query with the above, however I get the following syntax error:

[June7]

How does [cbxProd] become cbo[ProductID] with misplaced [?

Okay, don't set referential integrity. But then you need to save product description into OrderDetails. Set combobox properties appropriately. Code will have to save ProductID if you want it.

Still don't understand the aversion to saving adhoc products to Products table. How does Products table bear on inventory control? Stock on hand should really be a calculation based on transaction records of incoming/outgoing product. Regardless, can exclude the 'adhoc' products from report.