Continuous form field changes for current record

I have a form that is used as a continuos form subform. It's bound to Table1 (for sake of the explanation). One of the fields on the form is a calculation that is imported from a query. I have the sql for the Row Source functioning so that, if the subform is removed from it's parent, it works as it should, displaying the proper calculation for each record. However, as a subform, as focus changes to the new record, the fields for all records change to the value for the current record.

Is there a way I can fix this without reinventing the wheel?

Thanks.
RWW
PstWoodAsked:
Who is Participating?
 
shanesuebsahakarnConnect With a Mentor Commented:
Wait, there *is* a way, thinking about it.

Make the text box a combo box. Bind it to the same field as the other text box, but make it non-editable (set its Enabled property to No and Locked to Yes). Make sure the combo box has 2 columns, the first one is the item ID and the second is the item category. Hide the first column and I think that will do it.
0
 
rockmuncherConnect With a Mentor Commented:
Only by including the control source in the underlying recordsource for the form.  That is, you can't use a separate query if you want the value to display correctly for each record in continuous forms view.

Be aware that this sometimes gets in the way of the ability to edit if the resultant query is not updatable.
0
 
rockmuncherCommented:
>>  Bind it to the same field  << the problem will be that the text box in question is not bound to the form's recordsource.  If there is no context link between the recordsource of the form and a control then you can't expect them to remain in sync
0
 
shanesuebsahakarnCommented:
Yes, but you can bind it - in which case they will be kept in sync, as changing the editable control will change the non-editable one too.
0
 
PstWoodAuthor Commented:
rockmuncher and shanesuebsahakarn were both right and both wrong. :-)

rock was right that the control source had to be in the underlying recordsource in order to work and shane was right that the text box had to be converted to a combo box. The solution was to make a query based on the Table1 and including in that query the field from the first query, then binding the resultant field in question to the combobox on the form. Then the right select statement in the Row Source makes the correct info appear in the combo box and stay there when the next record in the continuous form is changed.

Thanks to both for ideas to get it fixed. You split the points.
RWW
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.