Solved Updating Fields Automatically On A Continuous Form (1 Viewer)

[greensleeves149]

Hello all,

I have a Combo box (ProductName) on a continuous form (this form will be used as a subform) and what I was trying to do, was when the user updates/selects a Product, the respective UPC, Size and Variety fields will automatically populate for that line item. I'm not sure how to approach this on a continuous form- I tried putting a filter on the "product" on the query that runs the form, but that clearly doesn't work.

Would someone please enlighten me how to do this? I've attached a sample database to hopefully make things clearer- but please let me know if you need more information.

Thank you.

 

[bob fitz]

Here's two ways to do this depending on whether or not you need to save the updated fields to the forms underlaying table.
Post back if you have questions on either solution.

 

[greensleeves149]

Hi Bob,

Thank you for the time you have taken to put these two solutions to me- very much appreciated.

When selecting a product, your fix updates the respective UPC, Size and Variety perfectly- just as I wanted.

In testing, I've found some unexpected behavior.

1) Let's say I change the Product in the first record, if I move the cursor to any field in the second row/record, it will update the product of the second record automatically to match the first. I've tried it on a few records, and it will either modify the record above or below it.

2) It randomly will start renaming products to numbers instead.

These two things seemed to happen for me on both databases. Could you please have a look or tell me if I'm doing something wrong at my end?

Thank you.

 

[arnelgp]

the ControlSource must be set to ProductID not on ProductName.
ProductName belongs to Product table not in tblProductInvestigationCosts table.

 

[greensleeves149]

the ControlSource must be set to ProductID not on ProductName.
ProductName belongs to Product table not in tblProductInvestigationCosts table.

Awesome- thank you arnelgp