Using combobox columns to update other fields is causing duplication

[Watson88]

Hi there guys,

The problem I'm having relates to data entry in a sub form. I'm using a Combo box to select a product code, then using the other columns of the combo box to auto populate other fields in the subform through the Afterupdate event. The issue is, when I go to add a new record to the subform, it changes all the fields on the previous record to reflect the new product I have chosen from the Combobox. I'm almost certain the solution will be straight forward, but i cannot think of a way around it.

I've tried Dlookup instead and have found the same issues. The one workaround I can think of involves using a hidden text field that holds the value of the combo box for each record and then base all relevant DLookUps on this field (as it's static and not subject to change like the combobox)

What are you thoughts/suggestions on a cleaner solution?

Thank you
Adam

PS one of the other members of this forum, Sneuberg, has been helping me with an unrelated issue elsewhere on the forum so didn't think it appropriate to change that posts focus.

 

[fat controller]

Just a very quick guess off the top of my head (and I am no expert by the way), but how about setting the combo value = Null at the end of the code for the After Update event?

 

[Watson88]

I'll give it a try! At this point, I'll try any solution.

 

[Watson88]

Still results in the same issue unfortunately. I can see you logic with the suggestion though.

 

[fat controller]

Your idea with the textbox might well be the way to go then

 

[missinglinq]

The problem you describe is caused by the Textboxes that are 'changing' to the latest entries being Unbound! These Controls need to be Bound to Fields in the underlying Table, in order to be Record-specific.

With Unbound Controls, the latest data entered becomes the data in all Records for that Control.

Linq ;0)>

 

[Watson88]

Hi Missinglinq,

Thank you for your answer. Some of the text fields in the subform are not to be stored in the underlying table, instead I wanted them there just for user experience (Product description for example), with this in mind, could you perhaps suggest a way I can both resolve the issue I first posted about yet still keep these text boxes present for the benefit of the user?

Thank you
Adam

 

[jdraw]

Can you post a copy of the database--remove anything confidential - then zip ant attach.

 

[Minty]

If you make the underlying record source a query including a right join to the product code table, I think you can still make additions to the left hand table on the form and the fields will populate automatically.