Another kind of (field) population?

[Guest]

I need to populate a field in a subform with calculated results from an
unbound field in a mainform.

I have used two methods:

Method # 1:

I tried populating an unbound field “amount†in a subform with results from
an unbound field “amount†that has the following code in its’ control source,
“=Sum([amount])â€, in mainform.

A. budgetevent2 (bound form with the unbound field, “amountâ€) - Mainform

B. budget_detail (bound form with the unbound field, “amountâ€) – Subform

I placed the following code in the Subform’s, “On Current†properties:

Private Sub Form_Current()
On Error GoTo ErrLine
Me!amount = Forms![mainform].Form!amount

ExitLine:
Exit Sub
ErrLine:
Resume ExitLine
End Sub


The Subform’s, unbound “amount†field was supposed to retrieve this
calculated information from the Mainform’s, unbound “amount†field and
perform an additional calculation with the following code in its’ control
source, “=Sum([amount]/12)â€.

Populating the unbound field, “amount†would not work. I thought that the
problem might be in trying to populate an unbound field with information from
another unbound field and/or trying it between a main and subform.

Method #2:

Then I tried populating a bound “amount†field in the subform with results
from the same unbound “amount†field in the mainform.

A. budgetevent2 (bound form with the unbound field, “amountâ€) - Mainform

B. Budget_detail (bound form with the bound field, “amountâ€) - Subform

I left placed the above code in the Subform’s, “On Current†properties:

The subform “amount†field is supposed to retrieve this calculated
information, but this time, perform no additional calculation.

I then made this subform’s, bound “amountâ€, field transparent and created a
new unbound field to complete the calculations, “=Sum([amount]/12)â€, off the
“amount†field from the same subform instead of the mainform.

However, while this works; it only works if I manually place the actual
dollar amount in the bound “amount†field. I would like populating of the
bound, “amount†field to be automatic.

John

 

[Marshall Barton]

I need to populate a field in a subform with calculated results from an
unbound field in a mainform.

I have used two methods:

Method # 1:

I tried populating an unbound field “amount” in a subform with results from
an unbound field “amount” that has the following code in its’ control source,
“=Sum([amount])”, in mainform.

A. budgetevent2 (bound form with the unbound field, “amount”) - Mainform

B. budget_detail (bound form with the unbound field, “amount”) – Subform

I placed the following code in the Subform’s, “On Current” properties:

Private Sub Form_Current()
On Error GoTo ErrLine
Me!amount = Forms![mainform].Form!amount

ExitLine:
Exit Sub
ErrLine:
Resume ExitLine
End Sub


The Subform’s, unbound “amount” field was supposed to retrieve this
calculated information from the Mainform’s, unbound “amount” field and
perform an additional calculation with the following code in its’ control
source, “=Sum([amount]/12)”.

Populating the unbound field, “amount” would not work. I thought that the
problem might be in trying to populate an unbound field with information from
another unbound field and/or trying it between a main and subform.

Method #2:

Then I tried populating a bound “amount” field in the subform with results
from the same unbound “amount” field in the mainform.

A. budgetevent2 (bound form with the unbound field, “amount”) - Mainform

B. Budget_detail (bound form with the bound field, “amount”) - Subform

I left placed the above code in the Subform’s, “On Current” properties:

The subform “amount” field is supposed to retrieve this calculated
information, but this time, perform no additional calculation.

I then made this subform’s, bound “amount”, field transparent and created a
new unbound field to complete the calculations, “=Sum([amount]/12)”, off the
“amount” field from the same subform instead of the mainform.

However, while this works; it only works if I manually place the actual
dollar amount in the bound “amount” field. I would like populating of the
bound, “amount” field to be automatic.

The issue here is that control expression calculations are
performed by a separate task that runs at a lower priority
from the VBA in the event procedures. This means that you
can not reasonably determine when the Sum expression has the
correct result in a VBA procedure.

The usual way to deal with this is to perform both operation
in the same context. Since Sum is not readily available in
VBA, the standard approach is to us an expression in the
subform text box:
=Parent.Amount
or
=Forms![mainform].Form!Amount

Note: It may be ok in this particular case, but you should
not name a text box the same as a field named in its control
source expression.

 

[Guest]

Problem resolved; thanks.

John

I need to populate a field in a subform with calculated results from an
unbound field in a mainform.

I have used two methods:

Method # 1:

I tried populating an unbound field “amount†in a subform with results from
an unbound field “amount†that has the following code in its’ control source,
“=Sum([amount])â€, in mainform.

A. budgetevent2 (bound form with the unbound field, “amountâ€) - Mainform

B. budget_detail (bound form with the unbound field, “amountâ€) – Subform

I placed the following code in the Subform’s, “On Current†properties:

Private Sub Form_Current()
On Error GoTo ErrLine
Me!amount = Forms![mainform].Form!amount

ExitLine:
Exit Sub
ErrLine:
Resume ExitLine
End Sub


The Subform’s, unbound “amount†field was supposed to retrieve this
calculated information from the Mainform’s, unbound “amount†field and
perform an additional calculation with the following code in its’ control
source, “=Sum([amount]/12)â€.

Populating the unbound field, “amount†would not work. I thought that the
problem might be in trying to populate an unbound field with information from
another unbound field and/or trying it between a main and subform.

Method #2:

Then I tried populating a bound “amount†field in the subform with results
from the same unbound “amount†field in the mainform.

A. budgetevent2 (bound form with the unbound field, “amountâ€) - Mainform

B. Budget_detail (bound form with the bound field, “amountâ€) - Subform

I left placed the above code in the Subform’s, “On Current†properties:

The subform “amount†field is supposed to retrieve this calculated
information, but this time, perform no additional calculation.

I then made this subform’s, bound “amountâ€, field transparent and created a
new unbound field to complete the calculations, “=Sum([amount]/12)â€, off the
“amount†field from the same subform instead of the mainform.

However, while this works; it only works if I manually place the actual
dollar amount in the bound “amount†field. I would like populating of the
bound, “amount†field to be automatic.

The issue here is that control expression calculations are
performed by a separate task that runs at a lower priority
from the VBA in the event procedures. This means that you
can not reasonably determine when the Sum expression has the
correct result in a VBA procedure.

The usual way to deal with this is to perform both operation
in the same context. Since Sum is not readily available in
VBA, the standard approach is to us an expression in the
subform text box:
=Parent.Amount
or
=Forms![mainform].Form!Amount

Note: It may be ok in this particular case, but you should
not name a text box the same as a field named in its control
source expression.