Unanswered: Subforum Value not updating correctly with SetValue
I have just started to use Access, and most of the problems I have encountered so far have been fairly easy for me to figure out. This one, though, I can't even think of what would be wrong.
I have a database for a fictional company that sells Opioids. In my invoice form (frmInvoice) I have a subform (currently tblInvoiceDetail that has ProdNum, Invoice #, ProdQty and Price fields). I am trying to get the price field to populate with the price from the table tblProducts.
Currently, I have a macro that will set the value of txtPrice (Which is linked to tblInvoiceDetail.Price) to the price in tblProducts.Price. This is using a query to look up the price:
SELECT tblProducts.ProdPrice, tblInvoiceDetails.ProdNum
FROM tblProducts INNER JOIN tblInvoiceDetails ON tblProducts.ProdNum = tblInvoiceDetails.ProdNum
The interesting thing about this method is that I have to either:
1) Select the item from the combobox, go to another record, come back to the record I'm trying to fill in, re-select the item from the combobox
2) Have another record in tblInvoiceDetail with that product number, with the price filled in from a previous doing of 1)
I can run the query manually and input an item number, unless there's an entry in tblInventoryDetail it will come up blank for that item. Same goes if I try to just query from tblProducts.
I would like to know what solutions there are for making this macro work. I'm aware that this can be done with VBA, but I would like to be able to work though this problem in case I have to deal with it in the future.
EDIT: I should also mention that the subform is a continuous form. Using Access 2k3.