Updating values from a subform to a main form

[Guest]

I am putting together a form for various tenant transactions. The main form
has the tenant general info, while the subform contains transaction
information. A field in the main form is for the tenant's total balance, and
I have a field in the subform that has it calculated. The problem that I'm
having is that I can't seem to get the updated information from the subform
to the main form. It seemed that using some VBA code would help, but I don't
know how to represent a field that isn't in the form the code is written for.

On a similar note, I was wondering if there was a way to write a macro
attached to a command button that can automatically fill in a record using
data from one form and a user defined string. (as in enter the name of a
month in a text box, and push a button to add a record that automatically
enters a rent amount determined in a field from one form in a defined field
from another form as well as the description of "[month] Rent" to another)

As it is, the database is nearly finished, I just need to fix the data
connections between forms and subforms. I just can't seem to get them to work.

 

[Steve Schapel]

Silva,

I will just tackle the first question for now...

It seems strange to me that you want the total balance amount to show on
the main form, if it is already visible on the subform. But I suppose
you must have your reasons.

So, on the main form, just put an unbound textbox. And in the Control
Source property of the textbox, put the equivalent of this:
=[NameOfSubform]![TotalBalance]
.... substituting of course the actual name of your subform control, and
the actual name of the control on the subform where the total balance is
calculated.

 

[silva]

Actually, the total balance isn't intended to be seen on the subform. The
method you provided worked very well, but I still have a small issue. The
number sent from the subform to the main form needs to be stored to a field
bound to a field in a table. For smaple's sake, let's name the subform field
[B_Sum], the receiving field in the main form [Transfer_Field], and the field
bound to a table [Balance]. The problem I now have is that the VBA code
updates [Balance] to [Transfer_Field] before the expression that defines
[B_Sum] ( =Sum([Record_Value]) ) can be processed, thus giving [Balance] the
value that existed before the real update. Is there some way to perform the
Sum() function within the VBA code when moving to the next record? Or is
there another method which will allow the updating code to be processed after
the expression? I haven't found a way that works outside of setting update
code to GetFocus on other fields in the subform. I hope I wrote this in a
manner that makes sense.

Silva,

I will just tackle the first question for now...

It seems strange to me that you want the total balance amount to show on
the main form, if it is already visible on the subform. But I suppose
you must have your reasons.

So, on the main form, just put an unbound textbox. And in the Control
Source property of the textbox, put the equivalent of this:
=[NameOfSubform]![TotalBalance]
.... substituting of course the actual name of your subform control, and
the actual name of the control on the subform where the total balance is
calculated.

--
Steve Schapel, Microsoft Access MVP

I am putting together a form for various tenant transactions. The main form
has the tenant general info, while the subform contains transaction
information. A field in the main form is for the tenant's total balance, and
I have a field in the subform that has it calculated. The problem that I'm
having is that I can't seem to get the updated information from the subform
to the main form. It seemed that using some VBA code would help, but I don't
know how to represent a field that isn't in the form the code is written for.

On a similar note, I was wondering if there was a way to write a macro
attached to a command button that can automatically fill in a record using
data from one form and a user defined string. (as in enter the name of a
month in a text box, and push a button to add a record that automatically
enters a rent amount determined in a field from one form in a defined field
from another form as well as the description of "[month] Rent" to another)

As it is, the database is nearly finished, I just need to fix the data
connections between forms and subforms. I just can't seem to get them to work.

 

[silva]

Edit: "...VBA code updates [Balance] to [Transfer_Field] before the
expression..." Should be:

"...VBA code updates [Transfer_Field] to [Balance] before the expression..."

Actually, the total balance isn't intended to be seen on the subform. The
method you provided worked very well, but I still have a small issue. The
number sent from the subform to the main form needs to be stored to a field
bound to a field in a table. For smaple's sake, let's name the subform field
[B_Sum], the receiving field in the main form [Transfer_Field], and the field
bound to a table [Balance]. The problem I now have is that the VBA code
updates [Balance] to [Transfer_Field] before the expression that defines
[B_Sum] ( =Sum([Record_Value]) ) can be processed, thus giving [Balance] the
value that existed before the real update. Is there some way to perform the
Sum() function within the VBA code when moving to the next record? Or is
there another method which will allow the updating code to be processed after
the expression? I haven't found a way that works outside of setting update
code to GetFocus on other fields in the subform. I hope I wrote this in a
manner that makes sense.

Silva,

I will just tackle the first question for now...

It seems strange to me that you want the total balance amount to show on
the main form, if it is already visible on the subform. But I suppose
you must have your reasons.

So, on the main form, just put an unbound textbox. And in the Control
Source property of the textbox, put the equivalent of this:
=[NameOfSubform]![TotalBalance]
.... substituting of course the actual name of your subform control, and
the actual name of the control on the subform where the total balance is
calculated.

--
Steve Schapel, Microsoft Access MVP

I am putting together a form for various tenant transactions. The main form
has the tenant general info, while the subform contains transaction
information. A field in the main form is for the tenant's total balance, and
I have a field in the subform that has it calculated. The problem that I'm
having is that I can't seem to get the updated information from the subform
to the main form. It seemed that using some VBA code would help, but I don't
know how to represent a field that isn't in the form the code is written for.

On a similar note, I was wondering if there was a way to write a macro
attached to a command button that can automatically fill in a record using
data from one form and a user defined string. (as in enter the name of a
month in a text box, and push a button to add a record that automatically
enters a rent amount determined in a field from one form in a defined field
from another form as well as the description of "[month] Rent" to another)

As it is, the database is nearly finished, I just need to fix the data
connections between forms and subforms. I just can't seem to get them to work.

 

[Steve Schapel]

Silva,

You have mentioned something about VBA code, but it is not clear what
this code is, when it executes, and what it does.

I can perhaps say that the value of the B_Sum] control on the subform
will not be updated until the entry or editing of the record has been
saved to the table. It may be necessary to explicitly force this save,
using code like this:
DoCmd.RunCommand acCmdSaveRecord
.... and even then it may be necessary to force the refresh of the total
to the B_Sum control, using code like this:
Me.Recalc

However, it may also be worthwhile to revise the reason for the attempt
to save the total data to the Balance field. This is a calculated or
derived value, which can always be retrieved at any time, via the use of
a simple query. As such, we would normally regard the storing of the
result of the calculatiuon to be an invalid procedure, and would
normally advise to remove the Balance field from the table in any case.

--
Steve Schapel, Microsoft Access MVP

Edit: "...VBA code updates [Balance] to [Transfer_Field] before the
expression..." Should be:

"...VBA code updates [Transfer_Field] to [Balance] before the expression..."

Actually, the total balance isn't intended to be seen on the subform. The
method you provided worked very well, but I still have a small issue. The
number sent from the subform to the main form needs to be stored to a field
bound to a field in a table. For smaple's sake, let's name the subform field
[B_Sum], the receiving field in the main form [Transfer_Field], and the field
bound to a table [Balance]. The problem I now have is that the VBA code
updates [Balance] to [Transfer_Field] before the expression that defines
[B_Sum] ( =Sum([Record_Value]) ) can be processed, thus giving [Balance] the
value that existed before the real update. Is there some way to perform the
Sum() function within the VBA code when moving to the next record? Or is
there another method which will allow the updating code to be processed after
the expression? I haven't found a way that works outside of setting update
code to GetFocus on other fields in the subform. I hope I wrote this in a
manner that makes sense.

 

[silva]

I guessmy purposes aren't entirely clear, and it's a litle difficult to
explain what I want to do and why, but you have completely answered my
question. The "DoCmd.RunCommand acCmdSaveRecord" command seems to be doing
the trick. The way I'm trying to do things, I really needed the balance to
update in the form. Having to do it through a query would have been, I
suppose inconvenient is the best word, but not entirely accurate. This
database is essentially a multi-account ledger, and it's fairly necessary to
have everything able to be updated within the form.

Anyway, the problem is solved. Thank you.

Silva,

You have mentioned something about VBA code, but it is not clear what
this code is, when it executes, and what it does.

I can perhaps say that the value of the B_Sum] control on the subform
will not be updated until the entry or editing of the record has been
saved to the table. It may be necessary to explicitly force this save,
using code like this:
DoCmd.RunCommand acCmdSaveRecord
.... and even then it may be necessary to force the refresh of the total
to the B_Sum control, using code like this:
Me.Recalc

However, it may also be worthwhile to revise the reason for the attempt
to save the total data to the Balance field. This is a calculated or
derived value, which can always be retrieved at any time, via the use of
a simple query. As such, we would normally regard the storing of the
result of the calculatiuon to be an invalid procedure, and would
normally advise to remove the Balance field from the table in any case.

--
Steve Schapel, Microsoft Access MVP

Edit: "...VBA code updates [Balance] to [Transfer_Field] before the
expression..." Should be:

"...VBA code updates [Transfer_Field] to [Balance] before the expression..."

Actually, the total balance isn't intended to be seen on the subform. The
method you provided worked very well, but I still have a small issue. The
number sent from the subform to the main form needs to be stored to a field
bound to a field in a table. For smaple's sake, let's name the subform field
[B_Sum], the receiving field in the main form [Transfer_Field], and the field
bound to a table [Balance]. The problem I now have is that the VBA code
updates [Balance] to [Transfer_Field] before the expression that defines
[B_Sum] ( =Sum([Record_Value]) ) can be processed, thus giving [Balance] the
value that existed before the real update. Is there some way to perform the
Sum() function within the VBA code when moving to the next record? Or is
there another method which will allow the updating code to be processed after
the expression? I haven't found a way that works outside of setting update
code to GetFocus on other fields in the subform. I hope I wrote this in a
manner that makes sense.

 

[Steve Schapel]

Silva,

I'm happy to know that you have it working for you.

I also understand the need for accuracy in your displayed data.

It is a common mistake in database development to put user interface
considerations ahead of proper data modelling principles. Believe me,
there are ways to get the functionality you need, without resorting to
saving calculated values to a table.