I need to bind a text field to either of two tables

[BubbaDave]

Hi,
I have two tables, each with a 'Remarks' field. Both tables are bound to sub-forms on my main form. Both sub-forms display multiple records. My screen space is limited, so I don't want/need to show the remarks for all the records displayed. I want to display only the remarks, in a text box on the main form, for a record that the user has selected by clicking on the 'Name' field in either sub-form.

Any help would be appreciated.
Thanks,
Dave

 

[GingerR]

in the NAME control in each subform, in the OnClick event for both, put

forms!MainFormName!Remarks = me.Remarks

substitute your main form name for MainFormName.
substitute the name of the remarks field for Remarks if necessary.

This will put whatever is in the REMARKS field in the subform of the record you're clicking on into the field REMARKS on the main form. I'm assuming your recordsource for each of the two subforms are i.e. Table1 and Table2? if not, if the record sources are queries based on those tables, make sure you bring in the field Remarks (even tho you are not displaying that field in the subforms) otherwise the code won't find it.

g

 

[BubbaDave]

Thanks GingerR,

It worked for one of the subforms but not the other. I couldn't find any difference between the two subforms that might cause this problem. I figured a workaround, though...I added the REMARKS fields to the subforms and made them invisible, then equated the Main form REMARKS to them. This works fine...your tip was very helpful in figuring this out.

Now, all I have to do is determine how to go the other way, i.e., fill the table REMARKS field from the Main form REMARKS control. Since the field is in the subform, when I leave the subform to enter data into the Main REMARKS, focus is lost and the wrong record gets updated. I set the tab to go to the Main REMARKS, but the focus still goes to the next record in the subform. Any thoughts?

D.

 

[Taff07]

Have the records in the Sub form got a unique identifier? If so then you can store this and use it to select the correct record to focus on when you you return to write the data.

Another way is to set a bookmark on the current record just before you leave the sub-form. Then just call the bookmark before wrting the data to the record.

Either of these methods should work for you

Ian