MS Access Sample – Colors Converter

Microsoft Access Color Picker & Converter Utility

This straightforward Access database offers a user-friendly form that makes converting colors effortless. Whether you’re working with OLE Color values, RGB components, or HEX codes, this utility lets you seamlessly switch between all three formats.

MS Access Color Utility

As you can see by the above you can enter a OLE Color, RGB Color, HEX color value, move sliders (if you were trying to find a color) and they get converted back and forth between all the formats in real-time and you get a visual of the color in question at the top of the form.
 
Continue reading →

MS Access Sample – Common Issues

This database demonstrates common programming concepts, such as: basing one combo box’s list on another combobox’s value, changing a control’s background color, changing a form’s background color. This sample database will be updated as I have time to add to it.

Download the sample database: Common MS Access Issues Sample Database.

MS Access – VBA – Move A Folder

If you have ever tried to use the File Scripting Object to perform a FolderMove, you’ve quickly learnt that it spits out a Permission Denied error when you try to move a folder to another drive or try to move a folder which is not on the same drive as the move.exe. So how can you get around this problem, well actually, it is quite easy. The function below will perform the move for you. Instead of simply moving the directory, we actually copy it and then delete the source directory. Easy as pie! Enjoy

'---------------------------------------------------------------------------------------
' Procedure : MoveFolder
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Move a folder
'             Better version of the FSO's MoveFolder method which is basically a "rename"
'             method, hence it only works if the source and destination reside on
'             the same volume (same as move.exe under WinXP) and typically returns
'             a permission denied error.
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFolderSource         Folder to move
' sFolderDestination    Folder to move the folder to
' bOverWriteFiles       Whether to overwrite file(s) if the folder already exists
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' MoveFolder("C:\Temp", "D:\Development\New")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2010-Nov-14                 Initial Release
'---------------------------------------------------------------------------------------
Function MoveFolder(sFolderSource As String, sFolderDestination As String, _
                    bOverWriteFiles As Boolean) As Boolean
On Error GoTo Error_Handler
    Dim fs As Object
    
    MoveFolder = False
    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.CopyFolder sFolderSource, sFolderDestination, bOverWriteFiles
    fs.DeleteFolder sFolderSource, True
    MoveFolder = True

Error_Handler_Exit:
    On Error Resume Next
    Set fs = Nothing
    Exit Function

Error_Handler:
    If Err.Number = 76 Then
        MsgBox "The 'Source Folder' could not be found to make a copy of.", _
                vbCritical, "Unable to Find the Specified Folder"
    Else
        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: MoveFolder" & vbCrLf & _
               "Error Description: " & Err.Description, _
               vbCritical, "An Error has Occurred!"
    End If
    Resume Error_Handler_Exit
End Function

MS Access – VBA – Copy A Folder

Below is a simple little function which will allow you to make a copy of a folder since it uses the File Scripting Object it can be used in all VBA Applications (Word, Excel, Access, PowerPoint, …).

'---------------------------------------------------------------------------------------
' Procedure : CopyFolder
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Copy a folder
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFolderSource         Folder to be copied
' sFolderDestination    Folder to copy to
' bOverWriteFiles       Whether to overwrite file(s) if the folder already exists
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' CopyFolder("C:\Temp", "D:\Development\New", True)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2010-Nov-14                 Initial Release
'---------------------------------------------------------------------------------------
Function CopyFolder(sFolderSource As String, sFolderDestination As String, _
                    bOverWriteFiles As Boolean) As Boolean
On Error GoTo Error_Handler
    Dim fs As Object
    
    CopyFolder = False
    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.CopyFolder sFolderSource, sFolderDestination, bOverWriteFiles
    CopyFolder = True

Error_Handler_Exit:
    On Error Resume Next
    Set fs = Nothing
    Exit Function

Error_Handler:
    If Err.Number = 76 Then
        MsgBox "The 'Source Folder' could not be found to make a copy of.", _
                vbCritical, "Unable to Find the Specified Folder"
    Else
        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: CopyFolder" & vbCrLf & _
               "Error Description: " & Err.Description, _
               vbCritical, "An Error has Occurred!"
    End If
    Resume Error_Handler_Exit
End Function

MS Access Change -1/0 to Yes/No in a Report

If you ever made a report and bound a textbox to a Yes/No field you will have been surprised to see the output returned as 0s and 1s!

So how can we change it so they get returned as Yes/No values in the report?

I have seen people resort to IIF() statements to convert -1 to Yes and 0 to No, but you need not even do this!

The easiest, and I suppose proper way to handle this case, is to set the textbox’s Format property to Yes/no in the Format tab of the Properties window. Voila! You now have Yes/No value returned in your report.

MS Access – VBA – Run a Query in Another Database

So how can you run a query in another database?

Well, that depends! It depends on whether you simply need to run an action query or if you actually wish to get the results returned to you.

One method is to use DAO programming to access the remote db and simply execute the query. This implies that you are simply wanting to run an action query.

Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).OpenDatabase("FullPathAndFileNameOfThe2ndDb")
db.Execute "TheQueryNameYouWishToExecute", dbFailOnError
Set db = Nothing

Another method which will actually return the results of a SELECT query… is to add the 2nd database as a reference in your 1st database and then call a function which open you query. You can find a sample database illustrating this technique at http://www.access-programmers.co.uk/forums/showthread.php?t=156716 (in the second post by MStef).

MS Access – Determine if Runtime or Full Version

As you start to do more and more advanced vba development you may have the need to determine whether your user is using the full blown version of MS Access or the runtime version. Some code/procedures will throw errors in the runtime, that normally would not in the full blown version of MS Access. So how can you determine this? Well, it is surprisingly simple! A simple IF statement can determine this for you, as shown below.

    If SysCmd(acSysCmdRuntime) = False Then
        'The user is using a full blown version of MS Access
        
    Else
        'The user is using the runtime version of MS Access

    End If

MS Access – Auto Increment a Value

Have you ever needed to auto increment a revision number (ie:AZ -> BA or A1 -> A2)?

I can’t take credit for the following function, but knew it could help a lot of people. It works for AlphaNumeric value so it is very versatile compared to standard alpha incrementors. Once again, many thanks to both Graham Seach and Stefan Hoffman for sharing with us all!

Public Function IncrementAlphaNumCode(strCode As String) As String
'Author: Graham R Seach Microsoft Access MVP Sydney, Australia
'Source: http://social.answers.microsoft.com/Forums/en-US/addbuz/thread/6cc09fc4-4a58-4e5c-aa7d-d1cc36a5483c
'Based on code developed by Stefan Hoffman MVP
Dim lngASCII As Long
Dim lngCount As Long
Dim lngLength As Long
Dim strResult As String
Dim lngValues() As Long
Const BASE_DECIMAL As Long = 10
Const BASE_HEXAVIGESIMAL As Long = 26
Const BASE As Long = 0
Const VALUE As Long = 1

strCode = Trim(UCase(strCode))
lngLength = Len(strCode)

ReDim lngValues(lngLength, 1)
lngValues(lngLength, BASE) = BASE_DECIMAL
lngValues(lngLength, VALUE) = 0

'Decode to plain decimal
For lngCount = 0 To lngLength - 1
    lngASCII = Asc(Mid(strCode, lngLength - lngCount, 1))
    Select Case lngASCII
        Case 48 To 57 'Numeric digit, base 10, decimal
            lngValues(lngCount, BASE) = BASE_DECIMAL
            lngValues(lngCount, VALUE) = lngASCII - 48
        Case 65 To 90 'Alphabetical character, base 26, hexavigesimal (upper case)
            lngValues(lngCount, BASE) = BASE_HEXAVIGESIMAL
            lngValues(lngCount, VALUE) = lngASCII - 65
        Case Else 'Non-alphanumeric character
            Err.Raise vbObjectError + 512, "IncrementCode", "Invalid character in source string"
    End Select
Next lngCount    'Increment

lngValues(0, VALUE) = lngValues(0, VALUE) + 1    'Calculate the carry forward
For lngCount = 0 To lngLength - 1
    If lngValues(lngCount, VALUE) >= lngValues(lngCount, BASE) Then
        lngValues(lngCount, VALUE) = 0
        lngValues(lngCount + 1, VALUE) = lngValues(lngCount + 1, VALUE) + 1
    End If
Next lngCount

'Encode back to mixed decimal/hexavigesimal
strResult = ""
For lngCount = 0 To lngLength
    If lngCount = lngLength And lngValues(lngCount, VALUE) = 0 Then
        Exit For
    End If
    If lngValues(lngCount, BASE) = BASE_DECIMAL Then
        strResult = Chr(lngValues(lngCount, VALUE) + 48) & strResult
    Else
        strResult = Chr(lngValues(lngCount, VALUE) + 65) & strResult
    End If
Next lngCount

IncrementAlphaNumCode = strResult
End Function

MS Access – Running Access on a MAC

Ever needed to run an MS Access database on a MAC? It can be done.

One solution, is to install Parallels Desktop on the computer. This enables one to run Windows software directly on a MAC. Once it is installed, you then would install the OS of your choice and the MS Access. So you need valid OS license and a valid MS Access license or use the free run-time version.

That said, at $80 for parallels and then another $200+ to get a copy of windows this starts to become an expensive endeavour if it is only to use MS Access! Never the less, it is a viable option for those looking for a solution to a problem.