Creating Functions Modules

[molly00mayhem]

hi all

um, lets see, im a 4th year elementary school teacher in a large urban area, msaccess novice, new to this forum, who has been helped greatly by the pros here. i took a break from reading some articles they sent to me, made me a BLT sandwich and a big glass of homemade sun tea, relocated the cat who keeps falling into the <little> dent i make on my end of the couch, and read through about 30 of the threads here just to be nosy.

well, i was blown away by the depth and breadth of the questions these guys field, no pun intended haha. ive seen everything from my piddly little calculated control question to advanced sql troubleshooting, geographical mapping, loops, linked tables, relationships, NULL conundrums, all manner of runtime errors, encryption algorithms, multilingual dbs and everything in between. einstein move over!

so, being the little sneak that i am, i started copying and pasting some of those little gems into a table i have in one of my dbs called "tbl_MSAcessTipsandTricks" which of course then got me to thinking well maybe i should do an aggregate parse (my contribution to msaccess slang) and group these little jewels into modules. grampa says that if you find one penny on the ground theres probably another one close by since everybody has 2 cents they want to throw in.

so my question is is this a good idea for a novice to be thinking about so early when she is still figuring out how to join 2 tables properly? ill accept opinions from any of the great poobahs :)

cheers!

[twinnyfo]

It is always a good idea to keep a collection of things that you might consider useful, even if you have no current use for them. Just create an empty module, put

Option Compare Database
Option Explicit

At the top of it and add your Functions/Subs. Just make sure all of them start with Public and not Private. This will allow you to acces them directly from your immediate window if you want to test them.

So, even though you have no need for finding out how many Tuesdays or Thursdays there are in a month, you can add a function like that to your module, and then, when you get asked a questions about days or weeks in months, you have something to look at and review and modify to suit your needs!

[zmbd]

I'm with twinnyfo
I have a database in its 11th version that I have module after module
I name the modules after what type of content they may have in them
"ADOB_ConnectionExample"; "Email_Code"; "Encrypt_AES_CBC_File"; etc...
I also have several queries setup that I've found very useful over the years showing examples of Union Queries and Cross-Tab queries - one of my favorite comes from a thread here: Show numbers not used: Post #7 used for finding some missing values.

You might want to consider some sort of tagging scheme so that you can find the things you want in your tables...

[PhilOfWalton]

I attach an ancient DB, which I really ought to update (not created by me), and not all in VBA, but those functions not written in VBA are not difficult to change.

It contains 2700 odd code snippets, and I have used it quite frequently. It really used to be my bible until I cam across the experts on this website.

Unfortunately, the upload failed despite the Zip File only being 3.4Mb and allegedly we are allowed a 5Mb file, so, if anyone wants a copy, I will have to email it out privately.

So, as usual, my first sentence was a load of …

Phil

[molly00mayhem]

hi twinnyfo
yes, public! i would have forgotten. they need to be accessed globally. thanx

[molly00mayhem]

hi zmbd
yes, i was thinking of doing queries, more specifically sql, first. cant get around sql as an all purpose strategy. for me, a tagging scheme would come later once i get enough code. thanx

cheers!

[molly00mayhem]

hi philofwalton
thanx, i would very much appreciate a copy. unfortunately, i have hit my 3 email limit on the forum and dont feel comfortable giving an email address out publicly. maybe later ok?

cheers!

[PhilOfWalton]

Hi Molly,

Fine. Send me a private message when you're ready.

Phil

[Nauticalgent]

I have bought the FMS inc Total Access code book which installs itself as an Add-In to Access. Lots of great things in there but it is not for a novice who gets easily discouraged. Fortunalty, that only applies to half of me...

There IS a way to import your own code libraries but I have not found anyone who claims this as a worthwhile undertaking - instead they resort to Phil's concept. Also, although I have not really dedicated am real effort, but I have been unable to locate any "how-to" articles on this subject.

Edit: Found one, just had to ask Dr. Google a better question.
https://www.devhut.net/2017/11/14/ms...ary-databases/

[Nauticalgent]

Good afternoon Molly!

I know you didn't ask for this but I figured you might find it useful.

The link below is the first of 9 (I think) videos from Philipp Stiefel. He has named it the "Better VBA" series and it contains some of the best practices I have ever seen.

If you are wanting to get your hands dirty but are unsure of how to begin, this series is for you.

https://www.youtube.com/watch?v=PqIu0NY5ihI&t=8s

The intro music leaves a little to be desired but he IS German after all!

Philipp Stiefel is a huge contributor to the Access community and I believe he just earned his MVP award from Microsoft. Once you get through this first series the rest of this channel has some great stuff too...

Let me know what you think!

John

[NeoPa]

Hi NauticalGent.

Some good ideas there. Luke Chung (FMS Inc) is another MVP for Access (or now technically Office Apps and Services within the updated structure). A very clever guy with lots of good software out there.

Philipp Stiefel, on the other hand, is possibly not an MVP at this time. I was discussing with Peter Doering just the other day how we're both now the only MVPs left in our own countries (UK & Germany) :-( You can always check using Find an MVP. I couldn't find Philipp anywhere. That's not to say that his resources aren't quality, of course. Thanks for the link.

Other video resources, though not necessarily just for new users, are My YouTube Channel and a longer video on Microsoft's Channel9 (Free Tools for Microsoft Access) which includes Crystal Long and myself discussing various free offerings when we were in Redmond (Annual MVP Summit) a few years back.

Molly:
so my question is is this a good idea for a novice to be thinking about so early when she is still figuring out how to join 2 tables properly?

Yes. A very good idea.

I've set myself up a database that contains various references - mostly Bytes.com references - that I often use to help me answer questions here. Many of the questions have been covered before so I grab a link (The database is designed to formulate the link for me with a visible description as well as the actual web address, and in the format required for links here on Bytes.com.) and include that in any reply.

[molly00mayhem]

hi Nauticalgent

you know yesterday when i read your post i thought aha thats for me so i downloaded a trial version, figured 15 days was enough time to check it out. yiiiiiikes! i remember once when i was a kid i got this horse to run inside the corral but when he came to the fence instead of jumping he stopped dead in his tracks and ole molly girl saw earth from a distance. they say the crash from my landing still echoes in the hills :) i suspect i would be rubbing my butt again if i tried to tackle this one, better wait til im oh say in my third set of false teeth heehee

cheers!

[molly00mayhem]

hi again nauticalgent

yup, just put it into my shopping bag to watch later today. i have the summer off from teaching and giamberto is still mumbling about me whiling away my days watching the breezes blow while he slaves over hot code, well poor baby, nyuk.

as for getting my hands dirty, i tossed the dove cleansing bar and hauled out the lava soap my second day on this forum. havent looked back though and its ok if hes german, turns out im part deutsch myself!

weidersehen!

[molly00mayhem]

hi neopa

re: your database. you know, i see pros as those who have accumulated many thin layers of wisdom over time, sort of like seasoning on a good cast iron skillet. never occurred to me that beneath all that polish you guys still roll up your sleeves and get 'er done. cant wait to see you in action! my respects :)

[NeoPa]

Molly:
cant wait to see you in action!

Well, the second of those links includes me talking. Incidentally it also includes the Cystal Long whose music you liked. The first is my YouTube channel that just includes the work I'm showing with my voice explaining what's happening. I hope you enjoy them - even if some are too long to sit through comfortably.

Molly:
never occurred to me that beneath all that polish you guys still roll up your sleeves and get 'er done.

I guess most of us still earn our livings doing this stuff. How bad is that. My hobby is my living.