Cannot Grab Value From Another Sub-Form

[Guest]

I have a main form called "Properties". I have a Pop-Up Subform called
"Total Operating Expenses". Both forms use the same database table. On the
"Properties" form I have a button called Total Operating Expenses. Right
next to the button, I have a field called Operating Expenses which should
pull the total value from the "Total Operating Expenses" form for that
record. It isn't.

When I click that button next to the field, the "Total Operating Expenses"
form pops up just fine. There are a bunch of expense fields on that form and
then I have a summary field at the bottom that sums up all expenses. The
formula works fine. And the pop-up form displays the extended information
for that database record successfully. So when I'm on Record #1, and I click
the Total Operating Expenses button on Record #1, it shows the expenses
associated for Record #1 just fine. Same for Record #2 and so forth.

Here's the problem... The value on my "Properties" form right next to the
Total Operating Expenses button is displaying #Name? I'm having trouble
pulling the value from the sub-form back into the main form.

I need the total sum value in the "Total Operating Expenses" form to display
correctly on my "Properties" form FOR THAT RECORD. The field on the
"Properties" form is called Operating Expenses. The field on the pop-up form
is called "Total Operating Expenses". The control source for Operating
Expenses on the "Properties" form is as follows.... =Forms![Total Operating
Expenses]![Total Operating Expenses].

I think the field on my properties form is having trouble grabbing that
field from the subform. How can I get this field to grab from the right
information for that record? So if I'm on record #2, I need it to grab Total
Operating Expenses from Record #2 in the Total Operating Expenses form.

Please help.

Thanks.

Frank

 

[John Vinson]

I need the total sum value in the "Total Operating Expenses" form to display
correctly on my "Properties" form FOR THAT RECORD. The field on the
"Properties" form is called Operating Expenses. The field on the pop-up form
is called "Total Operating Expenses". The control source for Operating
Expenses on the "Properties" form is as follows.... =Forms![Total Operating
Expenses]![Total Operating Expenses]

Well, having the name of the control identical to the name of the form
may be a problem right there.

But the syntax for referring to a control on a Subform is different
than if the form is a main form. You must navigate to it using the
main form, and the Name property *of the Subform control* on the main
form; the name of the Form used in the subform control is irrelevant,
though Access will usually use the name of the Form as the name of the
"box" that it puts the form into.

If the [Operating Expenses] textbox is in the second subform control,
and the Name property of the subform control containing the [Total
Operating Expenses] form is [sbfrmTOO], and the name of the mainform
is named [MyMainForm], the syntax would be either

=[Forms]![MyMainForm]![sbfrmTOO].Form![Total Operating Expenses]

or

=[Parent]![sbfrmTOO].Form![Total Operating Expenses]

Of course if the subform control is *ALSO* named Total Operating
Expenses you'ld use that name in place of sbfrmTOO.

John W. Vinson[MVP]

 

[Guest]

John,

Thanks for the reply. I tried, but no luck. Perhaps I'm not comprehending.
Let me give you more detail. I made some changes to faciliate this a bit.

Main Form Name = [SF Property]
Field Name on Main Form in question = [Operating Expenses]

Sub-Form Name = [Total Operating Expenses]
Field Name I want to use from this form = [Total OE] (I changed it so it
doesn't match the name of the form).

Control Source for [Total OE] = [Accounting]+[Advertising]+.... (every list
of expenses) --- The formula works fine.

Both Forms [SF Property] and [Total Operating Expenses] use the same
database table.

I link the sub-form [Total Operating Expenses] to the main Form [SF
Property] in the following manner...

Record Source is the same for both forms. Filter on [Total Operating
Expenses] reads ID=1. Cycle reads CURRENT PAGE. Maybe I'm not linking the
forms correctly. (remember, it's a Pop-Up form for the same DB table). I
just didn't have enough real estate on the same Main Form [SF Property] to
collect these data entry fields.

The Event Procedure for the button on the Main Form [SF Property] reads...
Private Sub Command375_Click()
DoCmd.OpenForm "Total Operating Expenses", , , "ID =" & Me.txtID
End Sub

The field in question, [Total OE] on the main form [SF Property] is the one
displaying ?Name. I tried typing in what you suggested on the control source
for [Total OE] and it didn't work. I tried the following...

=[Forms]![SF Property]![Total OE].Form![Total Operating Expenses]
.... that didn't work... so I tried....

=[Forms]![SF Property]![Total Operating Expenses].Form![Total OE]
.... and that didn't work either

John, I greatly appreciate your help on this. I've been struggling w/ this
all day which is why I decided to contact you guys. Maybe I'm just doing
something wrong in the Event Procedure or the way I'm linking the forms and
trying to grab this value.

Any additional advice is great. Thanks.

Frank

I need the total sum value in the "Total Operating Expenses" form to display
correctly on my "Properties" form FOR THAT RECORD. The field on the
"Properties" form is called Operating Expenses. The field on the pop-up form
is called "Total Operating Expenses". The control source for Operating
Expenses on the "Properties" form is as follows.... =Forms![Total Operating
Expenses]![Total Operating Expenses]

Well, having the name of the control identical to the name of the form
may be a problem right there.

But the syntax for referring to a control on a Subform is different
than if the form is a main form. You must navigate to it using the
main form, and the Name property *of the Subform control* on the main
form; the name of the Form used in the subform control is irrelevant,
though Access will usually use the name of the Form as the name of the
"box" that it puts the form into.

If the [Operating Expenses] textbox is in the second subform control,
and the Name property of the subform control containing the [Total
Operating Expenses] form is [sbfrmTOO], and the name of the mainform
is named [MyMainForm], the syntax would be either

=[Forms]![MyMainForm]![sbfrmTOO].Form![Total Operating Expenses]

or

=[Parent]![sbfrmTOO].Form![Total Operating Expenses]

Of course if the subform control is *ALSO* named Total Operating
Expenses you'ld use that name in place of sbfrmTOO.

John W. Vinson[MVP]

 

[John Vinson]

Record Source is the same for both forms. Filter on [Total Operating
Expenses] reads ID=1. Cycle reads CURRENT PAGE. Maybe I'm not linking the
forms correctly. (remember, it's a Pop-Up form for the same DB table). I
just didn't have enough real estate on the same Main Form [SF Property] to
collect these data entry fields.

Ah. I thought it was a Subform. A separate popup form IS NOT A SUBFORM
and my advice does not apply.

If it's a matter of screen real estate, you may seriously want to
consider a different approach: put a Tab Control on the main form, and
put some controls on the first tab page, more on the second, a few
more on the third, etc. Having two forms open both editing the same
record is angling for an error "The record is already open for editing
by another user"!


John W. Vinson[MVP]