***NEW CUSTOMER OFFER***
Get 15 minutes of free Live Help!

Code Snippets + Tips

Get a list of form controls sorted by tab order

Jan 29, 2014 in Arrays, Collections, Controls, Forms, Loops, Microsoft Access, VBA | 0 comments

When you iterate through a collection of controls in a Microsoft Access form, the order of the list of controls that you end up with may not be sorted as you expect. Rather than being sorted by the tab index, the list will be sorted by the order in which the controls were drawn on the form. You can manually change the order by using the Bring to Front or Send to Back buttons on the ribbon when the form is in design view. Or you can create your own collection of the form controls that is sorted using the tab order. The VBA function below uses...

read more

Scroll in a Microsoft Access text box using the mouse wheel

Jan 27, 2014 in Forms, Uncategorized, VBA, Windows API | 5 comments

Microsoft Access does not have built-in mouse wheel scrolling in form text boxes. You can provide mouse wheel scrolling by writing some VBA code in the Form_MouseWheel() event procedure. Trying to scroll in a text box can be particularly frustrating if the bottom of the text box is below the bottom of the screen and you wish to navigate downward without having to alternately use the form scroll bar then the text box scroll bar. I was surprised to learn that the mouse scroll wheel that I use for everything does not work by default in a text...

read more

Get total hours and minutes from summing columns of Hours and Minutes

Oct 22, 2013 in Date, Time, and Age Functions, Microsoft Access, Number Functions, VBA | 0 comments

Get total hours and minutes from summing columns of Hours and Minutes

This function can be used to return total hours or total minutes from a column of Hours and a column of Minute values.  For example, in the table below, an Hours value was captured separately from a Minutes value in a time clock application.  If the columns are just summed, some of the Minutes should be converted to Hours and added to the Hours column. To display a sum of those columns and keep the hours and minutes format, the CalcHoursOrMinutes() function is used twice; once for creating the hours value and once for creating the minutes...

read more

Use VBA to add days to a date but exclude Saturday and Sunday

Oct 8, 2013 in Date, Time, and Age Functions, Microsoft Access, Microsoft Excel, VBA | 0 comments

This function finds a date a certain number of days from a start date when weekends are not included. It can be used when your work week does not include Saturday and Sunday and you want to find a business day in the future. Microsoft Excel provides the WORKDAY worksheet function, but Microsoft Access does not have a built-in solution. The function only looks forward; if you use a negative number for NumberDays, it will return a Null value. It will also return a Null if either of the arguments is Null. Code: Public Function...

read more

“Folder In Use” Error After Using DoCmd.OutputTo for PDF Export

Oct 1, 2013 in Errors, Microsoft Access, VBA | 0 comments

“Folder In Use” Error After Using DoCmd.OutputTo for PDF Export

I got a “Folder In Use” error after using a loop that included the DoCmd.OutputTo macro action to export a Microsoft Access report to PDF files.  The error occurred when I tried to rename the folder that the PDF files were exported to. Using the DoCmd.OutputTo created a handle on the folder itself that was not released at the end of the function, preventing me from altering the folder.  I could delete the files in the folder, but not rename the folder. To fix the problem, I used the DoEvents method after the call to DoCmd.OutputTo in the loop...

read more

Live Chat Software