To get value in a textbox on a subform

LenkaL
LenkaL used Ask the Experts™
on
Hello experts,
     I have a subform on a main form. This subform is based on a query and works perfect, bringing into the subform the right amount of records, different each time.
 
     On the same subform to the side,  I have a text box
 that should bring a data for each record on this subform
 from another different table, not the one that other
 textboxes use. So in a way I need to link the records
 that come into the form to record from another table
 and display it in those textboxes on the side.
 
 To make it simpler:
 
 Textbox1 textbox2 textbox3        textboxFromOtherTable
 Textbox1 textbox2 textbox3        textboxFromOtherTable
 
 On the subform I obviously have one row of boxes, but
 when it runs it creates 10 or more rows.
 
 Two table have fields that they can be linked or joined.
 
 Is that possible?
 I can't use Dlookup function as it
 returns only one record, but my textboxFromOtherTable
 expands into many together with other boxes on the Subform.

 
 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
What your trying to do is a one to many to many relationship and this is best accomplished with two subforms.

  User selects a record in the main form, 1st subform displays related records.  Then when they select a record in the 1st subform, the related records are displayed in the 2nd subform.

  Correct?  If so, I'll explain how to accomplish this.  If not, please explain further.

Jim.

Author

Commented:

 I thought of that way - you think that's the best?
 Please explain further, you got the idea.
 
 Thanks.
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<I thought of that way - you think that's the best?
Please explain further, you got the idea.>>

  Yes.  If you have anything more then a field or two, that is the best.

  The trick is to use an invisible text control on the main form to keep the two subforms synched.

  The way it works is this: the MasterLink property of a subform can be a *control* as well as a field.

  So you put your first subform on the form as normal (this you already have).  Next create a text box on the main control and in the first subforms OnCurrent event, set that text control to the key that the second subform should use.  For example:

  Me.Parent![myTextControl] = Me![EmployeeID]

  Now add the second subform, but when specifying the masterlink, name the text control:

  Forms![myFormName]![myTextControl]

  As the user selects the records in the first subform, the text control will be updated and the 2nd subform will refresh with the correct records.

Jim.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial