Data entered in subform not saving to table correctly

[GodivaGirl]

Happy New Years.

I hope some one can help. I need detailed instructions. I haven't written a data base in about 10 years and I am VERY rusty.

I am creating a database to manage the ordering of supplies for my school clinics. I created a Main form, frmPOinput, with record source: tblPOinfo that has the following fields: OrderID(PrimaryKey & Autonumber), Date, Vendor, StreedAddress, CityStateZip, & VendorNo.

The subform, subfrmPOinput, with record source qryOrders that has the fields: Quantity, ItemNo, Product, UnitPrice, Total, which has control source =IIf(IsNull([Quantity]*[UnitPrice]),0,([Quantity]*[UnitPrice])), & UnitNo.

On the Products field, I have the following code to auto fill the ItemNo and UnitPrice:

Private Sub Product_Change()
'Select a product from the combo box
'and then automatically fill in the
'cataloog number and price per item.


Me.ItemNo = Me.Product.Column(1)
Me.UnitNo = Me.Product.Column(4)
Me.UnitPrice = Me.Product.Column(2)
'to format the price per item as currency
Me.UnitPrice = Val(Product.Column(2))
End Sub

The form and subform are master/child linked by OrderID

Relationships: tblPOinfo[OrderID] 1 to many with tblOrders[OrderID], tblPOinfo[Products] linked to tblProducts[ID], tblPOinfo[Vendor] linked to tblVendor[Vendor], & tblOrders[ID] to qryOrders[ID].


I can enter all of the data into the form and subform and the calculation for the Total works fine and the Vendodr address and Vendor number fill in the fields appropriately. When I save it, the Products field on the both the tblPOInfo and tblOrders has the Item No in it instead of the name of the product. The ItemNo field also displays the Item No.

I hope there is help out there because I am stuck and can't move forward from here.

Thank you.

[June7]

You are duplicating data. Should not have to save all that vendor and product info into the order record.

tblPOInfo should not have product info.

The Total calc can be simplified with: =Nz([Quantity]*[UnitPrice],0)

[GodivaGirl]

Thank you.

Where should the string of code be placed for the total calculation. I have tried the one I have in the query and it didn't work. It only works in the field on the form.

The only field on the tblPOInfo is related to products is the product field which is linked in the relationships to the tblProducts. Is that correct? I know my initial post was long and confusing.

[Missinglinq]

...The form and subform are master/child linked by OrderID...

...the Products field on the both the tblPOInfo and tblOrders has the Item No in it...

If ItemNo is appearing in the Product Field, then the Bound Column for the Product Combobox (Properties - Data -Bound Column) is apparently set to Column 1; it should be Bound to the column that actually holds the 'product' info, probably, from your post, Column 0.

Your data scheme is somewhat confusing. Generally speaking, Tables are related by a single Field, in this case the OrderID field, as you indicated. This is the only Field that should appear in both Tables, which begs the question of why the Product field would be saved in the tblPOInfo table as well as in the tblOrders table.

Linq ;0)>

Late note: Sorry, June7, got distracted, mid-reply, and didn't check before sending it!

[June7]

No problem, Linq.

The Total calc should work in query or textbox.

As Linq indicated, combobox column index begins with 0. If the value you want is in column 3, the index is 2.

[GodivaGirl]

Thank you Missinglinq for taking time to help. You fixed my first of many problems---You were right. I misinterpreted which column I needed to have the field bound to.

So since the main form does not contain information about the product, should I eliminate that field completely from tblPOInput? If so, in the relationships, do I need to have a link between the Products table and the tblPOInput at all?

[June7]

Eliminate field. No link between those two tables.

[GodivaGirl]

Ok! Thank you.

[GodivaGirl]

Now my product name is saving correctly and I have taken the Product information out of the main table and only have it going to one table. Again, thank you both.

I still do not have the calculated Total field saving to the table. As I mentioned in the first post, I have the string, control source =IIf(IsNull([Quantity]*[UnitPrice]),0,([Quantity]*[UnitPrice])), on the Total field on my subform. Everything else from the subform saves correctly, but the Total field on my table is blank. The total field calculates correctly on the subform view.

Could you explain further where I need to put the string of code so that I can see it on my subform as I complete the form and then have it save to my table?

Thank you so much for your big brains! :-)

[June7]

Should not save the calculated Total. Do calc when needed. Requires code to save calculated data and is usually not advisable. Raw data and calc value can get 'out of sync'.

[GodivaGirl]

Okay. Often, though, I adjust the # of items I am purchasing based on the total of the purchase to try and stay within budget.

Can I keep what is working in the form so that I can see it and then do a calculation from the raw data saved to the table to get a total. The end point I want to reach is creating a PO (Report) that I can print and send for the purchase. I have to have all of the totals per item and the total for the whole order on the PO.

What is the correct approach to arrive at this end point?

Again, thank you.

[June7]

Do calc on form and report when needed.

[GodivaGirl]

Ahh, okay. Thanks, I will try that.

[June7]

Please understand that it is basic principles of relational db to not save duplicate data or calculated data. However, as rules go, sometimes they must be broken. If you expect product prices to change must configure database to allow this without affecting existing order records. Options are:

1. every time a product price changes, create a new record in Products table and flag the previous price record as 'inactive' and exclude it from combobox list

or

2. save the price with each purchase order item record - this requires code