This is driving me nuts.
I have an unbound textbox (txt_SumSize) in my subform that holds the total of a field (Debitage_Size_ Quantity)seen in datasheet view (control source =Sum([Debitage_Size_Q uantity]).
I have an unbound textbox on my form (txt_SizeTotal) that gets the value from the subform footer when the form loads: -
Private Sub Form_Load()
-
Me.txt_SizeTotal = Me!sbfrm_DEBITAGE_Size.Form![txt_SumSize]
-
End Sub
-
This works great. But I need the total to refresh or requery when more data is added to the subform.
Nothing I have tried works. Either nothing happens or I get an error. I have tried putting code on the form, on the subform, on the subform footer textbox after update, on change and also on the main form total textbox after update and on change and nothing works.
I am not committed to using the on load function but it was the only code that worked in trying to get the total from the footer to show up on the main form.
Any help appreciated!
7  9310 
Help? This is the only thing hanging me up (for the moment) and nothing I've searched for works!
Hi. I think that using multiple indirect references to unbound controls on forms is a very complicated approach, as you have to keep requerying all the controls to keep them up to date. You don't tell us what the calculation is, or whether it is a sum of values that span more than one row in the subform (that is, a group total).
I would suggest that you either include a calculated field in the underlying query on which your subform is based (the best and simplest solution if the total is a row total) or continue to use an unbound textbox on the main form, but instead of indirectly referring to another textbox use just the one unbound control with a DSUM or whatever domain aggregate function is suitable for your purposes to calculate the total directly. With the unbound textbox you would need to trigger a requery from the after update and after insert events of the subform (at the very least). You might have to requery from the On Current event too - I can't be sure as you haven't told us how the total relates to the records in your main form/subform setup. If you don't use On Current then you would still have to set the initial value of the unbound textbox from the main form's On Load event as at present.
-Stewart
I'm sorry, I should have been more detailed. Also, I am totally open to completely redoing how this is done.
I have a form and a subform. The subform is a datasheet view with just two fields: size (a combobox, called Debitage_Size) and quantity (Debitage_Size_ Quantity). Then there is the unbound text in the footer that sums the quantity (txt_SumSize). I don't have much experience with unbound fields doing a sum in the footer of a form so I assume it is updating as records are added?
Then there is the unbound field on the form (txt_SizeTotal) that is supposed to grab the total from txt_SumSize and display it.
The code I supplied in my first post is all I have and again, I am not bound to it. It's just the only thing I have tried that actually worked until I hit the wall I'm at now.
Any other suggestions are welcome!
OK. As there are only two fields on your subform, is it safe to say it is not linked to the main form in a parent/child (1..many) relationship? If this is the case then the unbound footer textbox is simply performing a running sum of the quantity for all records in the subform recordset.
Forms in datasheet view do not have visible headers and footers, so any controls you place on these areas will work as normal but cannot be seen by the users.
In a test subform I placed an unbound textbox in the footer called txt_SumSize (as in your example) and set its control source to:
=Sum([Debitage_Size_Q uantity])
This is indeed updated automatically on each record add or change as you mentioned.
I placed a second unbound textbox on the main form. Instead of trying to update this via code, I simply set its control source property to refer to the field on the subform as shown below. I don't know the name of your main form, so in the example I have used [Debitage Test] for the main form and Debitage for the subform. You would have to replace these with the real names of your main form and subform.
=Forms![Debitage Test].Debitage.Form! txt_SumSize
This tracks the running total in the footer of the subform with no code whatsoever - no event procedures at all. See attached screenshot for an example of the way it operates.
Of course, if a group total is needed the simple Sum in the footer has to change to a DSum or similar as previously discussed.
-Stewart
That works beautifully, thank you. I had tried something like that before but I must have had the syntax wrong because I got the dreaded Error in the total box on the main form.
A question though - there are only two fields shown on the subform but the underlying table contains a few more, namely an autonumber PK and a FK that is the PK of the table that underlies the main form. And yes, the subform IS linked to the main form in a one to many. So does that affect or change things? Yes the unbound footer textbox should just be a running sum of the quantity in the subform.
Thanks again for your help!
Hi again AI. I am glad this approach is working out for you.
The parent/child relationship between the fields of the mainform and subform should take care of the grouping - in effect a WHERE clause is being applied to the subform's recordset automatically to limit its scope to only those records matching the linked parent fields. All being well you should not have to do any more to the unbound textbox in your main form, which should show the correct running total when you select a different grouping in your main form...
Cheers
Stewart
Yep, works great, thanks!
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Dalan |
last post by:
I'm sure there is a workaround for this, but haven't found it yet. I
have a mainform with two subforms and after one or both of the
subforms are updated, then clicking the Refresh button on the mainform
(to show updated values), causes the updated subform(s) to
automatically scroll up to one blank line if a new record was entered,
or to the line that was edited. All of the prior subform records are
not viewable or accessible until exiting...
|
by: Tim Marshall |
last post by:
Here's the situation. A form, frmSetUp, with a subform control called
subExplain with a source object form frmSetUpSubDefineSides. The source
object is a bound form, displaying a few records, no edit, adds,
filters, etc, are permitted on the subform.
An add or edit button on the subform is clicked opening an unbound form,
frmSetUpSideAdd, (populated with values from the above subform if this
is an edit of an existing record) for data...
|
by: Dave Boyd |
last post by:
Hi,
I have two very similar forms each with a subform. The main form gets
a few fields from the user and passes this back to a query that the
subform is bound to. The requery is done when the user enters the
last qualifying field on the main form.
In one case this works fine, the subform shows the data the user wants
to update -- which means showing all the data put in previously (ie
showing this via the requery and the continuous...
|
by: Andrew Chanter |
last post by:
I have a situation where I am using an unbound dialog form to update data in
an Access 2002 split back end / front end scenario. The data update is done
via an ADO call (direct to the back end db) when the user clicks the save
button. The dialog then closes and the user should be able to see the
result of their edit in a list view that now has focus. This is where I run
into a problem. The list view wont refresh to pick up the data...
|
by: S. van Beek |
last post by:
Dear reader,
I have a Form with a SubForm.
On the MainForm there is a field receiving his content from a group query.
If a change or append or delete a record in the SubForm the group query will
cam up with another value.
| | |
by: ken |
last post by:
Hi,
I have a main form with a text box and a filter button. I also have a
subform in the main form. When I make an entry into the filter text box
and click the filter button, I change the query definition of the
subform and then try to requery the subform, but no matter what I do it
does not requery?
While my main form is open and after I clicked on filter, I can open
the subform query and see that the sql changed correctly. I can open...
|
by: midlothian |
last post by:
Hello,
I have conditional formatting set up on a subform based on a calculated
value in the underlying query. For instance, if Sales are >$1000, the
query displays "Yes," otherwise it displays "No." The conditional
formatting is set up to read the 'Yes' or 'No' value and color the text
accordingly.
I have an event in the subform that allows users to update the sales.
It actually runs an update query behind the scenes. What I want to...
|
by: Stinky Pete |
last post by:
Hi everyone,
I have been updating a file that uses a main form that contains a
subform (as a datasheet). The main form really does not do anything
on opening except maximizes to the users screen size. The subform
however runs an "embedded" query (cause I don't know the right term)
to show a list of records based on the embedded query. BTW, by
embedded, I'm indicating the form does not run an external query but
uses the query included...
|
by: ApexData |
last post by:
COMBOBOX REFRESH DILEMMA
' I have been working for hours trying to figure out how to requery a
combobox in a subform, from a Popup form
' that this subform had launched. Basically, I designed a form that
pops up when the user dbl-clicks a combobox.
' This popup form displays the table that the combobox uses. The user
can change the content, and the combobox
' rowsource in-turn will change. This all works great in my mainform,
but...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
