Relating a txt box on main form to a record selected on subform?

[NewShoes]

Hey people,

If possible I would like to display a simple txt box on a main form that changes its content depending on on what record is currently selected on a subform.

For example: If I select (by select I mean click in) a record in the subform that has a related comment such as "This person only works a Wednesday and Thursday" I would liek this comment to be displayed in the comments box on the main form (just above the subform). Then, if I click on another record in the subform, say Employee Jane Doe, I would like the comments box on the main form to display the comments for Jane Doe.

Does this make sense!?!

Thanks for any help/pointers.

NS

 

[vbaInet]

Go to the Control Source of the textbox, click the button to open the Expression Builder and from there look for the field in the subform.

 

[NewShoes]

Go to the Control Source of the textbox, click the button to open the Expression Builder and from there look for the field in the subform.

Thanks vbaInet. Unfortunately I get a #Name? error in the text box. If you can provide any more info that would be awesome!!

NS

 

[vbaInet]

If you had used the Expression Builder properly you wouldn't get a #Name error. Open it up, double click the form on the left to expad it, click on the subform to expand it, then in the middle select the textbox by double clicking on it, or use the Field.

 

[NewShoes]

Humm, doesn't want to work for me. I have =[Forms]![qryTestsubf]![Comments] in the control source which is the correct subform and comments is the correct field within that subform.

I wonder if it's something do to with the fact my subform is based on a query.

Cheers,
NS

 

[vbaInet]

Actually, I might have been thinking of things the wrong way round. Here:

=[NameOfSubformControl].[Form]![NameOfField]

 

[NewShoes]

Actually, I might have been thinking of things the wrong way round. Here:

=[NameOfSubformControl].[Form]![NameOfField]

The way you previously said worked!! For some reason the expression builder didnt include the main form name!

However, I assumed this text field would be editable but it isnt. I am guessing this is just part of the design and there's no way to make it editable?

Many thanks for all this help too....you are an Access legend!

 

[vbaInet]

That was just a long winded way because I thought you were calling it from a different form.

Not edittable I'm afraid. Any edits would have to be done via the subform. However, this is possible in code of course. In the On Enter or On Got Focus event set the Control Source to the empty string that is "", then On Lost Focus or On Exit event set the control source back and send the value to the subform control.

 

[NewShoes]

That was just a long winded way because I thought you were calling it from a different form.

Not edittable I'm afraid. Any edits would have to be done via the subform. However, this is possible in code of course. In the On Enter or On Got Focus event set the Control Source to the empty string that is "", then On Lost Focus or On Exit event set the control source back and send the value to the subform control.

Ha, it seems VBA is the way forward for the trickier things! I will have a read of how to set the control source in code and fingers crossed it aint too complicated

Cheers,
-NS

 

[vbaInet]

In Access, it's the way forward

In addition there are the following to consider before setting the value of the subform's control:

1. Is the textbox on the main form Null or not? - IsNull()
2. Is the record on the subform a New Record or not? - Me.NewRecord
3. Am I in the right record or not?

Just let your users make any changes in the subform, it's much more logical.

 

[NewShoes]

Hi VbaInet,

This old chestnut again!

Can I do what I originally posted about IF the comments field is not a part of the form I want to show it on?

i.e. I'd like to show the 'Staff Comments' from the Employee table on a form that doesn't contain the subform where Staff Comments actually is.

Many thanks for any help,

-NS