Recently, I saw a post regarding moving a Query (SQL) to VBA.  I remember I saw something somewhere that automated this task but could not remember where.  Well, after a little surfing of my favorite sites I found it… Copy SQL statement from query to VBA by Allen Browne.  And, because I couldn’t leave well enough alone, I created the database following the steps listed and, for fun, added a *Copy to Clipboard* button just so you can now click the button and paste, with Ctrl+V, right into the VB Editor…


…and you can download it here.

11.13.2015  Amended so you can now also convert INSERT, DELETE and UPDATE queries as well.  Enjoy!

23 total views, no views today

Count characters

Normally, it’s not an issue if an End User tries to type in more characters than the Control will allow… they just can’t type anymore. BUT, if you are using an UNBOUND Form for data entry this is a big problem, they will not be able to Save and with a not so nice message.  Let’s fix that…

Step 1

Add a TEXT BOX under the Control on your Form named txtCharactersAllowed. In the Properties window for this Control set Enabled to False and Locked to True.  This will make it so the TEXT BOX will not react when the User mouses over it and will display a *### characters left* message.

Step 2

In the On_Change Event Procedure for the Control on your Form place…

Me.txtCharactersAllowed = 150 – Len(Me.txtYourControlName.Text) & ” characters left”
If Len(Me.txtYourControlName.Text) = 150 Then
MsgBox “No more characters allowed – Entry will not save!”, vbCritical + vbOKOnly, “Character Limit”
End If

Note, in this example the maximum length of the field is 150, be sure to change to reflect the maximum characters allowed for your field.

Step 3

Just in case they ignore that message, I add this to the Save Command Button right at the top.

If Len(Me.txtYourControlName.Text) = 150 Then
MsgBox “You have exceeded the character limit allowed, adjust and try again!”, vbCritical + vbOKOnly, “Save”
Exit Sub
End If

Stops the Save until they adjust and no ugly message and, oops, all their hard work just gone.

5 total views, no views today

Microsoft opens a User Voice site for Access

Yep, you read that right… Microsoft has opened up a UserVoice site where YOU can suggest features or improvements you’d like to see in Microsoft Access Desktop and Web Apps, as well as, vote on suggestions made by others. This is a great opportunity for YOU to pass on your ideas directly to the Product Team.  Have a look…  Welcome to Access Suggestion Box!

8 total views, no views today

What’s new with Access 2016?

Here you go… click here

15 total views, 1 views today

How to integrate a Microsoft Access database with QuickBooks?

by Guest Poster Priyanka Chouhan


Microsoft Access is a Database Management System, a part of Microsoft office suite of products. This application allows Users to utilize QuickBooks data files just like Access databases.

Steps to create a table in Microsoft Access with QuickBooks:

  1. Run MS Access 2013 (Windows Start button ->All Programs ->Microsoft Office 2013)
  2. The next window opens a default database named as Database1 in the box. This default name can be changed according to your need you can select a more relevant name.
  3. Now, select “Create” button to create a blank database
  4. When you are ready with the new database, Navigate to “External Data
  5. Create a connection by selecting External Data -> More -> ODBC Database, or you can directly select ODBC database option located at External Data menu
  6. In the next step, a new window named Get External Data appears along with two options
    1. Import the source data into a new table in the current database: This option directly extracts tables into Access Database, which can be viewed and edited according to the needs. However all of these changes made in these tables will not be uploaded unless you build another query to process the same.
    2. Link to the data source by creating a linked table: This option also extracts the table like first one, but will continue updating it to QuickBooks as well; therefore, all the operations you perform on these tables will be directly uploaded to QuickBooks. The data available in the table also syncs with QuickBooks.

Here are the steps to build a linked table in MS Access:

  • After selecting the access method according to you, select OK button and you will be prompted to a Select Data Source window
  • Here choose the QuickBooks related DSN you setup with the QODBC driver. This DSN could be one of your pre-installed DSN names or the one you have created. If you are using MS Access with QuickBooks for the first time, it will ask you to grant permissions for this application
  • Choose ‘Yes, Always’ option and click Continue then ‘Done’ in the next window
  • Once you have built the connection successfully, Access will list all the available tables in Select Tables window. You can select tables according to your need and extract data. Here you are allowed to select multiple tables.
  • If the Import and Linking works properly without any issues, you will be able to view the linked tables created in the table list of MS Access
  • If you want to view QuickBooks records in this linked table, just double click the linked server and you will prompted to the records
  • Theses linked tables are always in the sync with QuickBooks Data, therefore you will not able to add or delete any columns from the table. However, you can directly modify the data available in linked table and QODBC will automatically update the modification you have done to the QuickBooks.

Note: If you are working with the application, which is not fully compatible with ODBC, but getting data from Access tables and want to make it compatible with QuickBooks tables, then run Microsoft Access setup like above mentioned steps.

Author Bio: Priyanka Chouhan is a technical writer in Stellar Data Recovery with 5 years of experience and has written several articles on MS Access databases. In her spare time she loves reading and gardening.

53 total views, no views today

Set Default Value on List Box

You have List Box and you want the first item to be selected…

For VBA use…
Me.lstActivities.DefaultValue = Me.lstActivities.ItemData(0)


In the Properties window, on the Default Value line, of the List Box use…

44 total views, no views today

Run Query from Desktop

To run a Query from your Desktop without actually opening your Database…

1. Open your Database

2. Select the Query you want to run and drag it to your Desktop

3. Close your Database and then double-click the Query on your Desktop

356 total views, no views today

Get Initials

I use Initials in Combo Boxes for space conservation on a Form.  I don’t depend on the User to enter them, instead, I enter the below in the After_Update event of the Last Name Control on my Form…

Me.txtAssociateInitials = Trim(Left(Me.txtFirstName, 1)) & Trim((” ” + Left(Me.txtMiddleInitial, 1))) & Trim((” ” + Left(Me.txtLastName, 1)))

6,220 total views, no views today

Expand SubDatasheets

Okay, so we all know I recommend turning off the SubDatasheet but I can see by the Forums they are still quite popular.  For those of you that are still using them…SubDatasheetClosed

When you first open your Main Form/Subform (Datasheet View) it opens closed (Little plus signs).  This is often quite annoying to Users as they are the ones that have to click all those little plus signs to see the data in the SubDatasheet.  So, let’s fix that!


In the On_Load event of the Main Form put…

Private Sub Form_Load()
        With Forms(“YourMainFormName“)
         .SubdatasheetExpanded = True
        End With
End Sub

Then the next time you open the Form…SubDatasheetExpanded  Enjoy!


2,601 total views, no views today

Limit Number of Record Entries

Every once in a while you may want to restrict how many records can be entered. Maybe because of space limitations on a Report or perhaps you want to limit CD rentals to 4 per Customer or book a specific amount people per room. Whatever the reason here’s some code to make it happen… 

What I used to use (and it worked)…

Private Sub Form_BeforeInsert(Cancel As Integer)

   If DCount(“ICS”, “qryIncidentCrimes”) = 4 Then
       MsgBox “Only 3 Charges allowed per report, if you have additional charges you must start a new report!”, vbExclamation
       DoCmd.RunCommand acCmdUndo
       DoCmd.GoToRecord , , acPrevious
       Forms![frmIncidentInformation]![txtTimeOfOccurrence].SetFocus ‘Here I set focus back to the Main Form
   End If 

End Sub

But then I found a more efficient way… 

In a Subform
Private Sub Form_BeforeInsert(Cancel As Integer) 

   If Me.NewRecord = True And Me.RecordsetClone.RecordCount = 4 Then
       MsgBox “Only 4 sizes allowed per sheet!”, vbExclamation + vbOKOnly, “FF&E Size”
       DoCmd.GoToRecord , , acPrevious
   End If

End Sub 

In a Main Form 
Private Sub Form_Current() 

   If Me.NewRecord = True And Me.RecordsetClone.RecordCount = 4 Then
       MsgBox “Only 4 sizes allowed per sheet!”, vbExclamation + vbOKOnly, “FF&E Size”
       DoCmd.GoToRecord , , acPrevious
   End If 

End Sub

3,629 total views, no views today