How do you display reference details (preferably read only) in a sub form

Question

I have a form with Order Header (tblOrder) and a sub form with order product lines (tblOrderProd) linked by the OrderNumber. The problem is that when a product is entered, all the "user friendly reference" details about the product (name, colour etc) are on another table ie tblProduct.

So how do you display reference details preferably read only in a sub form?

 Note  I have created a query for the subform based on tblOrderProd and tblProduct with the Order number still linking the 2 forms. This works great with existing orders and order lines. But when I try and add an product to an order, Access stops and gives a message that the Order Number on the tblOrderProd has not got a value

Answer 1

Hi,

Rather than use a query combining the two separate tables for the subform, I would recommend just binding the subform to the table tblOrderProd and use a Combobox based on tblProduct for selecting the product.

When you create the Combobox, include all the reference details in the dropdown. You can then use the Column() property to display the reference details in unbound textboxes on the subform. For example:

=[ComboboxName].[Column](1)

Hope it helps...

Answer 2

I have created a query for the subform based on tblOrderProd and tblProduct with the Order number still linking the 2 forms.

That's the way I'd do it.  The subform's RecordSource property would be along these lines:

 

SELECT tblOrderProd.*,

ProductColour, ProductSize

FROM tblOrderProd INNER JOIN tblProduct

ON tblOrderProd.ProductID = tblProduct.ProductID

ORDER BY ProductName;

 

The LinkMasterFields and LinkChildFields properties of the subform control would be OrderNumber in each case.  The subform would contain a Combo box bound to ProductID, with a RowSource property of:

 

SELECT ProductID, UnitPrice, ProductName

FROM tblProduct

ORDER BY ProductName;

 

Its BoundColumn property would be 1, its ColumnCount property 3 and its ColumnWidths property 0cm;0cm;8cm to hide the first two columns.

 

Typically, the subform would also contain two text boxes, bound to ProductColour and ProductSize respectively, with their Locked property as True (Yes), and their Enabled property as False (No) in each case to make them read-only, text boxes bound to UnitPrice and Quantity columns in tblOrderProd, and an unbound text box with a ControlSource property =[UnitPrice]*[Quantity] to show the gross price per order line.

 

In the AfterUpdate event procedure of the ProductID combo box assign a value to the UnitPrice column in tblOrderProd with:

 

Me.UnitPrice = Me.cboProduct.Column(1)

 

The Column property is zero-based, so this is the second column.

 

If you are also including each product's VAT (or similar) rate in a column in tblOrderProd this would be assigned in the same way.  NB: assign the rate not the computed VAT amount, which is computed at order level; for an example of this see InvoicePDF.zip in my public databases folder at:

 

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

 

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly. 

 

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

 

Given a set up along the above lines there's no reason why it should not work as expected.

---

Ken Sheridan, Stafford, England