Textbox result from different form...

[pablavo]

Hi folks!
I would like to display the results of an object in a form from a different form.

There is the Main form (frmMain) which holds all other froms but all forms are linked to the sfrmYear subform. on one form (sfrmApproved), there is a currency amount.

If it is £200, how would I go about showing the results on sfrmYear as well?
I know I can do it by changing the sfrmyears' record source to a Mutli-table query which will have sfrmApproved table fields included! That would show me the result but I cannot update the data on the form from this query.

It would be better for me just to have a text box that would reference to the other source object than to use a query control source, I'm just not sure on what the code is!

Is the YearID and the ApprovedID included in it?

Can anyone help!

 

[RuralGuy]

If the other form is open then you can get to it through the Forms collection:
Forms!OtherFormName.TheControlYouWant
...using YOUR form and control names of course.-

 

[pablavo]

hmm

firstly, thanks for replying RuralGuy!

Yes the form is open as it's the subform. however, I've tried that expression and it's not refrencing to that particular record. sure, what you suggest would perhaps work if it the target control was unbound.

the target control is bound (which I should have expressed in my first message) and I would have to reference the unbound control to the target bound control with it's ID. I'm just not sure how the codes expression is laid out!

Hope someone can help

 

[RuralGuy]

The syntax works whether or not the control is bound. However, you need a different syntax when you are referencing a SubForm. =SubFormControl.FORM.ControlName will work as the ControlSource on a MainForm for referencing a control on a SubForm of that form. If your SubForm is in Continuous Form mode then this syntax references the control in the Current Record.

 

[boblarson]

The syntax works whether or not the control is bound. However, you need a different syntax when you are referencing a SubForm. =SubFormControl.FORM.ControlName will work as the ControlSource on a MainForm for referencing a control on a SubForm of that form. If your SubForm is in Continuous Form mode then this syntax references the control in the Current Record.

Just as an additional learning bit this might help you with how to use subform syntax:

1. When you are dealing with subforms, you are actually dealing with two parts - the subform CONTAINER (that which houses the subform on the main form) and the subform itself.

2. The subform and subform container can be named the same, but are not necessarily so. You need to check before writing the code. If they are the same then it simplifies things but it doesn't really matter if it is, or isn't, because you just have to refer to the container.

3. When you are doing things, like setting the recordsource on the subform, you are not really requerying the container, as it doesn't have a requery method, but the subform itself does. So, when you are referring to a property, or method, on the actual subform (not the container), you need to have the subform container name and then .Form. between the container name and the method, or property, so Access knows you want to refer to the form's method or property and not the container's method or property.

 

[pablavo]

#Name?

Thanks for the help I'm getting here, I've been given good support on this forum!

I'm still having bother with this control even with trying the two methods desbribed.

I don't know if this will make a difference. The subfrmApproval and frmYear are all subforms of frmMain.

The subfrmApproval is linked to the frmYear with the child link -YearID (within subfrmApproval) and a master link-txtlink on the frmMain(which refrences to the YearID (Primary key) within the frmYear.)

I'm totally stuck on this one as I keep getting #Name?. There is obviously something that I'm missing and I guess if I can crack this one, it will make sense for the future.

 

[boblarson]

Thanks for the help I'm getting here, I've been given good support on this forum!

I'm still having bother with this control even with trying the two methods desbribed.

I don't know if this will make a difference. The subfrmApproval and frmYear are all subforms of frmMain.

The subfrmApproval is linked to the frmYear with the child link -YearID (within subfrmApproval) and a master link-txtlink (which refrences to the YearID (Primary key) within the frmYear.)

I'm totally stuck on this one as I keep getting #Name?. There is obviously something that I'm missing and I guess if I can crack this one, it will make sense for the future.

Have you made sure that your CONTAINER that is displaying subfrmApproval and frmYear are actually NAMED subfrmApproval and frmYear as well (not just the subforms, but the containers).

 

[pablavo]

The container name for the subform is actually tblApproved. so I tried =tblApproved.form.ControlName.

Still getting the problem, #Name?.

 

[RuralGuy]

Did you replace the ControlName with the actual name of a control on your SubForm?

 

[pablavo]

Yes, In this instance the expression would be =tblApproved.form.AmountApproved
the tblApporved is the Container name as apposed to the actual backend table name.

would that be right? I mean, I've used similar expressions before i.e.
=Forms!frmMain!Code & "/" & [Suffix] which the frmYear uses and references to its parent form. Works fine.

On the other hand when I use the expressions suggested and the above ones on a control within frmYear which is in reference to a control on a subform, I get #Name?

Perhaps I should give up my day job

 

[RuralGuy]

#Name still indicates a referencing issue. You know you are looking at the SubFormControl when the "Data" tab has the LinkChild/MasterFields properties. The actual name of the control is the 1st item on the "Other" tab.

 

[boblarson]

If you still can't get it to work, can you either post the DB or email it?

 

[pablavo]

Post by RuralGuy
#Name still indicates a referencing issue. You know you are looking at the SubFormControl when the "Data" tab has the LinkChild/MasterFields properties. The actual name of the control is the 1st item on the "Other" tab.

This part is clear. I think I know what might be causing this. sfrmApproval and frmYear are essentially two subforms to the frmMain (frmMain & frmYear having direct relationship at the table level).

So Instead of linking the sfrmApproval to the frmMain (indirect relationship), I've used a text box on the frmMain to Link sfrmApproval with frmYear. The txtlink has the expression *=frmYear.Form!YearID. This will be used as the masterlink on sfrmApproval.

Perhaps I'm getting the reference issue because I'm using the txtlink as a master field rather than frmYears' direct table ID "YearID"

In regards to emailing the database. what address would I send it to? undoubtedly to help me on this level would be a generous act. I'd need to get the authorisation first though.
Thanks

 

[RuralGuy]

Perhaps I'm getting the reference issue because I'm using the txtlink as a master field rather than frmYears' direct table ID "YearID"

This is not causing your referencing issue. I use that technique all of the time. It has to be that either "tblApproved" is not the name of the SubFormControl or "AmountApproved" is not the name of a field or control on the SubForm.

 

[pablavo]

Thanks to the help on this thread I have found the answer.

This is the code for other peoples benifit.

Forms![frmMain]![tblAppoval].Form![AmountApproved]

The control resides on the subform with the container name "tblApproved" The subform resides on "frmMain"

Unfortunately it's not working properly due to a design issue perhaps.
The sfrmYear (where I want the control with the expression to reside) is a continuous form and it should show different amounts for each record but shows the same amount for all records.
Back to the drawing board

 

[neileg]

Just a general observation. Mostly data doesn't live in forms, it lives in the underlying dataset. Your original posting is all about data in forms and I think you would do well to consider if what you really need is to include the data in your dataset.

 

[pablavo]

Thanks for replying Neileg!

Understandably the data is already present in the backend. I was just curious how to reference the records field info on an expression within a control on a form with a different table record source

I hope this is the right response to your reply

 

[neileg]

I hope this is the right response to your reply

Just trying to help. Sometimes you can lost in the solution instead of keeping an eye on the original quaetion. I know I do!

 

[pablavo]

You've Helped in the past and I appreciate ALL help. It's important when starting out to get as much help as possible.

Thanks Neileg