Updating values from a subform to a main form

  • Follow


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.
0
Reply Utf 11/20/2007 10:10:02 PM

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

silva wrote:
> 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.
0
Reply Steve 11/20/2007 11:53:17 PM

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" wrote:

> 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
> 
> silva wrote:
> > 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.
> 
0
Reply Utf 11/23/2007 10:39:01 PM

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

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



"silva" wrote:

> 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" wrote:
> 
> > 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
> > 
> > silva wrote:
> > > 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.
> > 
0
Reply Utf 11/23/2007 10:45:01 PM

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

silva wrote:
> Edit: "...VBA code updates [Balance] to [Transfer_Field] before the 
> expression..." Should be:
> 
> "...VBA code updates [Transfer_Field] to [Balance] before the expression..."
> 
> 
> 
> "silva" wrote:
> 
>> 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.
0
Reply Steve 11/25/2007 8:49:25 AM

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.  :-)


"Steve Schapel" wrote:

> 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
> 
> silva wrote:
> > Edit: "...VBA code updates [Balance] to [Transfer_Field] before the 
> > expression..." Should be:
> > 
> > "...VBA code updates [Transfer_Field] to [Balance] before the expression..."
> > 
> > 
> > 
> > "silva" wrote:
> > 
> >> 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.
> 
0
Reply Utf 11/27/2007 7:09:03 PM

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.

-- 
Steve Schapel, Microsoft Access MVP

silva wrote:
> 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.
0
Reply Steve 12/1/2007 4:11:05 AM

6 Replies
264 Views

(page loaded in 0.1 seconds)


Reply: