I wanted to take a query and format part of its output into English; a list of names separated by commas. The DAO Object Library includes a completely pointless Recordset.GetRows command which returns an unwieldy multi-dimensional array. How pointless and unwieldy? Well, the supplied example to Debug.Print FirstName, LastName and Title from Northwind.mdb’s Employees table [...]
All posts in category Microsoft Access
Microsoft Access VBA: Convert Query or SQL to String
Posted by Mister Slimm on September 17, 2009
http://misterslimm.wordpress.com/2009/09/17/microsoft-access-vba-convert-query-or-sql-to-string/
Ordinal Suffixes for all dates and numbers; Show 3rd instead of 3
A surprising omission from the extensive number formatting goodies in Visual Basic (VB) is the ability to append the appropriate ordinal suffix to a number. This is most often used when writing dates in prose. For example, you would write 3rd February 2009, not 3 February 2009. This VB / VBA function returns your number [...]
Posted by Mister Slimm on February 13, 2009
http://misterslimm.wordpress.com/2009/02/13/ordinal-suffixes-for-all-dates-and-numbers-show-3rd-instead-of-3/
Disappearing borders in Microsoft Access 2002
So, amazingly, if you set the BackColor and BorderColor to the same value in a Microsoft Access 2002 TextBox then set BackStyle to Transparent, the border disappears. The solution is simply to adjust one of the values by 1 but still.
Posted by Mister Slimm on July 15, 2008
http://misterslimm.wordpress.com/2008/07/15/disappearing-borders-in-microsoft-access-2002/
Microsoft Access Visual Basic Form Helper: SetOpacity
This function allows you to set the opacity for a PopUp form. Normally, forms are fully opaque and you cannot see through the form. When it is partially opaque, you can partially see what is behind the form. I use it for smoothly fading in and out forms in my Microsoft Access application (specifically, a [...]
Posted by Mister Slimm on December 13, 2007
http://misterslimm.wordpress.com/2007/12/13/microsoft-access-visual-basic-form-helper-setopacity/
Microsoft Access Visual Basic Form Helper: Replacement for DoCmd.OpenForm for neater code
Frequently in Microsoft Access Visual Basic you will want to programmatically display a different record on a currently open form but this common requirement turns out to be a bit more fiddly than you might expect. Manually setting the Filter and FilterOn form properties or using RecordSetClone and FindFirst are all somewhat less than intuitive. [...]
Posted by Mister Slimm on December 5, 2007
http://misterslimm.wordpress.com/2007/12/05/microsoft-access-replace-docmdopenform-for-neater-code/
Microsoft Access Visual Basic Form Helper: BringToTop function
This function allows you to bring a specific form to the top so that you can be certain that the user can see it. To use: Save this code into a new module called FormHelper. Call FormHelper.BringToTop with the Form object you wish to bring to the top. The code is fully commented and [...]
Posted by Mister Slimm on November 28, 2007
http://misterslimm.wordpress.com/2007/11/28/microsoft-access-form-helper-bringtotop-function/
Microsoft Access Visual Basic Form Helper: FormOpen function
Sometimes in Microsoft Access Visual Basic you will want to know whether a form is open or not. This common requirement turns out to be a bit more fiddly than you might expect. My original version of this function also returned True if the form was open but hidden or open in Design View. This [...]
Posted by Mister Slimm on November 26, 2007
http://misterslimm.wordpress.com/2007/11/26/microsoft-access-form-helper-formopen-function/
Microsoft Access 2002 – Emulate Vista Dimming / Attention Focus
UPDATE 29 June 2007: You must extract the MDB from the zip for it to work (!). It will not work on a read-only database. Download ApplicationDimmer.zip While I have no intention of upgrading to Vista at this time I did note an interesting user interface paradigm that helps call undivided attention to a message [...]
Posted by Mister Slimm on June 29, 2007
http://misterslimm.wordpress.com/2007/06/29/microsoft-access-2002-emulate-vista-dimming-attention-focus/
Microsoft Access 2002 – Emulate hyperlink cursor change
I’m still using Microsoft Access 2002 and thought I’d share this little tip. You know how in web browsers hyperlinks are underlined in blue and your cursor changes to a little hand when you move over them? Using a slightly bizarre but agreeable undocumented feature you can emulate the cursor change in Microsoft Access 2002. [...]
Posted by Mister Slimm on June 21, 2007
http://misterslimm.wordpress.com/2007/06/21/microsoft-access-2002-emulate-hyperlink-cursor-change/
When is Sunday Monday?
Time for an infinite wisdom post now. On my system and in Microsoft Access 2002 the following VBA command: ?WeekdayName(vbSunday) yields the result Monday Awesome! Why does this happen? Well, it turns out that the help file is right but the actual command in Access doesn’t do what it says on the tin. DescriptionReturns a [...]
Posted by Mister Slimm on May 10, 2007
http://misterslimm.wordpress.com/2007/05/10/when-is-sunday-monday/
Microsoft Access 2002: Complex forms load really slowly
After a Microsoft Access wizard left two of my databases in a corrupt condition (an undocumented feature?) I felt fortunate to be able to reconstruct them by importing the objects into a new MDB file. Slow Annoyingly, my forms were now taking an age to load. As soon as I loaded it the first time [...]
Posted by Mister Slimm on December 28, 2006
http://misterslimm.wordpress.com/2006/12/28/microsoft-access-2002-complex-forms-load-really-slowly/
