Sum records from a filtered subform

[molly00mayhem]

hi all
im new to this forum so be patient! ive been developing in access 2016 for a year. i have an unbound form with a subform, a listbox and 3 textboxes. the subform contains a querydef based on an inner join between a receipts table and items purchased table(which contains a "Cost" field). the multiselect listbox contains purchase item categories. 2 textboxes are options for filtering on dates and time frames.

i use a button to build the sql based on criteria from the listbox and 2 textboxes. once the subform is filtered i would like to have a button which sums the Cost field into the 3rd textbox on the form while preserving the records display.

problem is i cant figure out how to do it. theres probably a simple way to do it and ill bang my head against the wall when i see it. any help is appreciated.

[twinnyfo]

In the header section of your sub form, add a text box that had the following Record Source: =Sum([Cost])
This will sum that field for the records displayed.

[molly00mayhem]

thanx for the reply twinnyfo. i know that works well. with so many views and only one reply i was beginning to think it was a pretty dumb question! anyway, i should have mentioned that the subform control does not have a form in it. the sourceobject is the qrydef. i want to keep the display in datasheet view. i tried using a form which defaulted to datasheet view but got an error msg when using your code. ill try it again with a form. maybe using tables and qrydefs as sourceobjects isnt such a good idea.

[molly00mayhem]

hi twinnyfo
your suggestion worked this time. my boyfriend is an ibm programmer barely tolerates msaccess pointed out a field/control name conflict. im an elementary teacher trying to do a few things on my own. i think being a software developer requires more commitment than just being a weekend warrior <lol>. thanx again.

[twinnyfo]

I'm glad I could hepp! Just let us know if there is anything else we can do.

BTW, Welcome to Bytes!

[zmbd]

my boyfriend is an ibm programmer barely tolerates msaccess pointed out a field/control name conflict.

This is VERY common problem and usually results due to the fact that during form design when the user adds the control using the "Add Existing Fields" or by the form wizard the control defaults to the same name as the field which can create a lot of confusion in code and rarely between parent-form and subform controls/fields. Most often I will rename the controls, (IMHO this should be "best practice"), to avoid name conflicts and because the default names are usually useless (really, "Command2" for a button... when it's the submit or exit button :) )

Would be nice if Microsoft would prepend "ctrl_" to the default names for controls - this would avoid every single name conflict issue!

[NeoPa]

Molly00Mayhem:
my boyfriend is an ibm programmer barely tolerates msaccess pointed out a field/control name conflict.

That's not an atypical attitude Molly. It's unfortunate, and frankly not very informed. So many in the software world, in development and security areas, fail to understand that MS Access isn't simply a database container with a bit of flexibility.

There are many aspects to it and no-one is forced to use it as a poor man's RDBMS. As an introductory level RDBMS it's good to get people interested and starting to understand database concepts. It's not great beyond that level.

As a RAD (Rapid Application Development) tool it's frankly in a class of its own. There are some things it will struggle with, and though other DevEnvs generally take 2 to 3 times longer to develop, there are some things MS Access struggles with as far as sophisticated user interfaces go, but there's very little of actual practical use that it can't manage more quickly (as far as development goes) than any other development environment.

As far as naming conventions go, yes, the defaults are often rubbish. They're not imposed though. The lack of those that choose their own names is a reflection of how easy it is for those without much/any background in computing and database work generally to get started and productive with it. That benefit surely results in projects that are not very well designed or written. However, to compare those with other dev environments one shouldn't compare apples with oranges. If you compare the results of experienced developers then Access comes out well. If you compare the results of developers that don't have much experience but can do it in Access anyway, then you should be comparing them with the same type in other dev environments. That way you have poor, but generally working projects against - nothing at all. These types of people simply wouldn't make the first base in such environments.

So, it's easy to deride MS Access, but only if you don't understand the picture very well. You'll generally find that experienced developers, of any sort, are very fussy and particular. We need to be. Take that approach when assessing MS Access in the overall picture and you won't find it wanting.

[molly00mayhem]

hi twinnyfo
yes, thank you. it feels good to have a place to come to for advice and help. i may have misled you a bit. while ive been working with msaccess for about a year, it has not been full time and not as an aspiring professional. i really do try to figure things out on my own before i ask for help but sometimes i lay an egg that just wont hatch.
cheers!

[molly00mayhem]

hi zmbd
you nailed it. i used the add existing fields for the first time and when things started going downhill the path was well greased haha. so ive been thinking about your comment. what if i prefixed the default controlname with something like "text_" for textboxes or "list_" for listboxes when im putting forms together? i was using the default names because it seemed easier to keep track of but the problem with that is now obvious.
cheers!

[PhilOfWalton]

@zmbd

I was intrigued by posting no 6 about field names. If field is a text box, and the control source is a field from a table or query, I never change the name, and have never run into problems.

Obviously Combo boxes are prefaces with Cbo, List Boxes Lst, Calculated Text boxes Txt, Command Buttons Cmd, etc.

Would you care to elaborate on the conflicts you mentioned?

Phil

[molly00mayhem]

hi neopa
sorry, didnt mean to offend. my boyfriend giamberto is italian and drinks too much espresso and can put both feet into his mouth with room to spare.

he thinks msaccess is fine for a lot of things but wanted me to go open source when i decided to do the database thing. i tried openoffice base but found it nonintuitive and messy and did not want to learn so many languages. since our computers are equipped with msoffice, i thought msaccess it is. besides, rumor has it i might be teaching these little rolling buttons around here some basic programming next year and if i didnt understand it, well tough luck giamber heehee. im finding putting together basic objects to create basic applications is straightforward and understandable and would be fun for my students to learn.

i think your points are well taken. the more i delve into msaccess the more i realize it definitely has earned its place in professional application development. but more so, after reading some of these posts, especially that mind boggling tangle of sql from twinnyfo that is being discussed, i see there is an advanced level of msaccess i would never have imagined. my respects to all of you and wish me luck.

cheers!

[NeoPa]

Molly00Mayhem:
I really do try to figure things out on my own before I ask for help but sometimes I lay an egg that just wont hatch.

Fantastic attitude. Hopefully we'll be able to help whenever you need it.

Molly00Mayhem:
What if I prefixed the default controlname with something like "text_" for textboxes or "list_" for listboxes when I'm putting forms together?

While that would be helpful, there are certain prefixes that are pretty standard, such as below :

txt     TextBoxes
cbo     ComboBoxes
lbl     Labels
lst     ListBoxes
frm     Forms
rpt     Reports
chk     CheckBoxes
cmd     CommandButtons
sfm     Subform/Subreports
tab     Tab Controls
pg      Page (on a Tab Control)
tbl     Table
qry     Query
opt     OptionButton

Notice they're all lower case and prefixes. That tells the reader they're identifying the type of the object rather than its usage.

[NeoPa]

Molly00Mayhem:
sorry, didnt mean to offend. my boyfriend giamberto is italian and drinks too much espresso and can put both feet into his mouth with room to spare.

There is certainly no offense taken Molly. If I gave that impression then my expression of ideas is less precise and accurate than I'd expect it to be.

It is very common for people to underestimate MS Access - especially in the IT world. What am I talking about - even in MS itself.

I see my responsibility to disabuse people wherever and whenever I can, not by attacking the other forms of IT, but by pointing out many of the wonderful things that Access is very good at, and hopefully educating them as I go.

I'm sure your boyfriend is a very bright lad. I come from a time when programming was mostly IBM. That's not so much the case now of course, but nevertheless IBM still has a very strong and innovative reputation in the field. There's also a great deal to be said for open source generally. For database work though, I've yet to find anything like MS Access for introducing students to entry-level programming and concepts. Maybe Excel of course, but they're of the same MS Office family.

[NeoPa]

PhilOfWalton:
I was intrigued by posting no 6 about field names. If field is a text box, and the control source is a field from a table or query, I never change the name, and have never run into problems.

Obviously Combo boxes are prefaces with Cbo, List Boxes Lst, Calculated Text boxes Txt, Command Buttons Cmd, etc.

Maybe you've been lucky. Maybe you hit this issue but never identified the issue you hit.

Both the Fields and Controls collections on Forms and Reports can be referred to in shorthand simply with FormRef.{Name}. If they both have the same names then Access isn't confused. It will decide which to use based on its own syntax.

Would you know which was being referred to? Would most? Would people get into all sorts of potential problems related to this?

That's why there are standards on how to name objects in Access. These are not limited to unbound controls.

If there are controls that are never referenced in code then I may not rename them, but that certainly wouldn't include bound controls.

[molly00mayhem]

hi neopa
thanx. copied and pasted into my new table called, lets see, "tbl_MSAccessTipsAndTricks". yahoo

cheers!

[NeoPa]

A pleasure Molly. I don't typically feel the need to add the extra underscore (_) but if you want to, and are consistent in doing so, it shouldn't cause any problems.

I also keep a database of tips and tricks. Good thinking ;-)

[PhilOfWalton]

@ Neopa

Well it looks as if I have got away with murder for the last 25 years of using Access.

Who's a lucky (ancient) boy then?

Phil

[molly00mayhem]

hi neopa
oh, good i thought i had really bumbled up. good to know things are cool. you are passionate about your calling and your kind of people always get high marks in my grade book.

as for giamberto, yeah hes usually a watt above 100 and hes been asleep for the last few hours and so out of my hair but i hear the yawns and well, peace doesnt last forever!

cheers!

[NeoPa]

PhilOfWalton:
Well it looks as if I have got away with murder for the last 25 years of using Access.

It's more strange that you haven't seen any of the threads that have come up in here on the subject. On the other hand it may simply be that it being off your radar simply means you haven't noticed the issues when they did come up.

I suspect you'll notice it more now.
@Molly.
All good here. Not sure how Giamberto can be asleep already after all that expresso ;-)

[molly00mayhem]

hi neopa
you know, he inhales a bunch of those little cups and then runs around like a turkey being chased at thanksgiving. eventually he crashes. i keep telling him that all that sludge is messing with his sugar levels but try being logical with a hyped up italian!

cheers!