Updating a table using a form lookup

[Dougalsworld]

Can anyone help?

I'm having trouble storing autofilled data from one of my forms and I was wondering if anyone has any ideas.

My data is as follows

tblProducts
ProductID
Description
Nett

tblPurchaseOrders
PurchaseOrderID
PODate
Supplier

tblPurchaseOrderDetail
PurchaseOrderID
ProductID
Description
Qty
Nett

Now I have a standard order entry form which works fine, my problem is with the subform:
frmPurchaseOrderDetail
ProductID
Description
Qty
Nett

On my form Description and Nett are auto populated referencing ProductID but this information does not transfer to the table tblPurchaseOrderDetail. The main problem is that a nett price can occasionally chanege (inflation) but I need to be able to look back to a particular order for audit purposes and be able to see what I paid.

I'm sure there is w way round this, but I've been unable to figure it out. I've gone through loads of different threads on the forum and as a result am sure it can be done, I've also looked at the examples posted by several users, but none seem to approach the lookup quite from this angle, does any one have any idea...sorry for the long post, thanks.

 

[RuralGuy]

To start, you do not need, nor should there be a Description field in the tblPurchaseOrderDetail. That information is already in the tblProducts table and you have a reference to that table with ProductID. Are you using a ComboBox to insert the Product into the tblPurchaseOrderDetail table? If so then in the AfterUpdate event of the cbo add the following code:

[Nett] = Me.ComboBoxName.Column(n)

Where n = the column in the cbo where you are pulling the Nett from the tblProducts -1. Cbo's are 0 based so Column(2) is the 3rd column and Column(0) is the 1st.

 

[Dougalsworld]

Thanks for that.

I've put in the ammended coding and i know its correct...in VBA mode if I hover the cursor over the code it tells me its pulling the right value, however can't get it to show on my form. What do I use to link it to my Nett field on my form and in turn my table tblPurchaseOrderDetail?

 

[RuralGuy]

How about putting some test TextBoxes on your form named txtTest1 and txtTest2.
Then in the AfterUpdate event of the cbo put:
Me.txtTest1 = Me.ComboBoxName.Column(1)
Me.txtTest2 = Me.ComboBoxName.Column(2)

That should show you what is going on. What is the actual name of your ComboBox and what does the Row Source of the cbo look like?