Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Main form text box reflects update of subform record

Posted on 2011-02-23
8
425 Views
Last Modified: 2012-05-11
I have a parent form with a total weight text box and a subform. Each subform record contains a weight. After each subform record is created or changed the total weight textbox on the parent form should reflect the total. I can use a select statement to query the uderlying subform table to calculate the total. My question is where do I put the select statement or what code do I need to add so that the parent form textbox reflects the total weight after a subform record is added or changed? Thanks.
0
Comment
Question by:StampIT
  • 4
  • 4
8 Comments
 
LVL 9

Expert Comment

by:borki
ID: 34965336
There is an easier solution.

In your subform add a total text box, that autosums the weights in the form. You can then reference that total text box in your main form.

See:
MS Access documentation onthe web
0
 

Author Comment

by:StampIT
ID: 34965390
borki,
    Do I put a text box in the subform footer that totals the weight? Do I reference the subform textbox with Forms!SubformName!txtBoxName in the main form? thanks
0
 
LVL 9

Accepted Solution

by:
borki earned 250 total points
ID: 34965520
Yes and yes, the text box goes in the subform footer and the text box in the main form has a data source of '=Forms!SubformName!txtBoxName'

Another example is at
http://www.databasedev.co.uk/subform-total.html

Good luck!
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:StampIT
ID: 34969957
borki,
    This worked as you said. However I neglected to say that the tot weight field that is updated in the main form is a field in a table. The form serves as data entry to a table. I can update a textbox fine. How do I update the total weight control in the main form that is a field in the table? I don't know how clear this is. Don't know how else to explain it. Hope you can help. Thanks.
0
 
LVL 9

Expert Comment

by:borki
ID: 34973359
OK, you are saying that the total eight is not only just shown but is also a table field in the table that is bound to the main form. Generally, it is not recommended to have calculated fields in a table.

Once again there is an elegant solution: The filed showing the total from the subform needs to be unbound, it can not have the total weight as a control source. You the add another text box with the total weight as the control source. You set this text box's visible attribute to 'No'. You then add an event procedure to the 'Before Update' (and maybe 'Before Insert', if you are adding records to the main table) event. The code would simply be

Me.HiddenBoundTotalWeightField = Me.DisplayedCalculatedTotalWeightFromSubform

with the appropriate control names used.

HTH
0
 

Author Comment

by:StampIT
ID: 34988239
borki,
   I now have a bound text box called TotWeight and a unbound textbox called txtTotWtDisplay in the Main Form. txtTotWtDisplay contains the total weight from the subform and this appears to work fine. In the BeforeUpdate event of the Main Form I have Me.TotWeight = Me.txtTotWtDisplay. Unfortunately the TotWeight field remains empty even after I have closed out of the form. I also tried this in the BeforeInsert event with the same result. Am I missing something? Thanks.
0
 
LVL 9

Expert Comment

by:borki
ID: 34995845
I think you are starting to stretch the scope of the original question...

As I said before, calculated field values are always a bad idea, because the minute someone edits the related table's value (weight in your case) the data in your main table is invalid.

To get back to your immediate problem, I suspect that no data is changed in your main form, therefore the OnUpdate event probably does not fire...

Your best option is to initialise the weight when you first open a record (hence overwriting the existing value) using another bit of code:

Private Sub Form_Current()
   Me.TotalWeight = DSum("Weight", "tblSub", "MainID = " & Me.ID)
End Sub

HTH
0
 

Author Closing Comment

by:StampIT
ID: 34996977
Thanks for the help. I see what you mean about calculated fields in a table.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question