October 30th, 2010
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 |
Share and Enjoy
MS Access VBA Programming | 
1 Comment »
October 29th, 2010
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.
Share and Enjoy
MS Access, MS Access General Information | No Comments »
October 28th, 2010
Have you ever had a client throw you the nice curveball of wanting to run an MS Access database on a Linux OS? Well, there is a workable solution.
You can install Wine on most Linux distributions. You can check out their website at http://www.winehq.org/download. You can also check out their brief page regarding MS Access at http://appdb.winehq.org/objectManager.php?sClass=application&iId=12
Overall, this seems to be a very good option for older versions, and slightly less reliable for the newer version (2007) with no data regarding 2010 compatibility. Depending on you needs, this is definitely worth checking out and the price is right, it’s free (you have to love open-source)!
Share and Enjoy
MS Access, MS Access General Information | No Comments »
October 22nd, 2010
Have you ever needed to calculate the number of weekdays (Monday through Friday) there were between two dates, that is exactly what the VBA function below does.
'---------------------------------------------------------------------------------------
' Procedure : CalcNoWeekDays
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Count the number of weekdays between two specified dates
' 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:
' ~~~~~~~~~~~~~~~~
' dtFirstDate The first of 2 dates to count the number of weekdays between
' dtLastDate The second of 2 dates to count the number of weekdays between
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' CalcNoWeekDays(#2010-10-6#,#2010-10-23#) =23
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2010-Jul-09 Initial Release
'---------------------------------------------------------------------------------------
Function CalcNoWeekDays(dtFirstDate As Date, dtLastDate As Date) As Integer
On Error GoTo Error_Handler
Dim dtDay As Date
'Ensure that the dates provided are in the proper order
If dtFirstDate > dtLastDate Then
dtDate1 = dtLastDate
dtDate2 = dtFirstDate
Else
dtDate1 = dtFirstDate
dtDate2 = dtLastDate
End If
CalcNoWeekDays = 0 'Initialize our weekday counter variable
For dtDay = dtFirstDate To dtLastDate
iDayOfWeek = Weekday(dtDay)
If iDayOfWeek <> vbSunday And iDayOfWeek <> vbSaturday Then
CalcNoWeekDays = CalcNoWeekDays + 1
End If
Next dtDay
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: CalcNoWeekDays" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, _
"An Error has Occured!"
Resume Error_Handler_Exit
End Function |
Share and Enjoy
MS Access Date/Time, MS Access VBA Programming | No Comments »
October 19th, 2010
Once you start doing some VBA programming in your database you will at some point or another want to run/execute queries through VBA. However, you may not want your user’s to receive the action query confirmation prompts, such as:
MS Access Action Query Confirmation Prompt Message
Nothing could be easier. To disable all confirmation prompts simply use the following line of code
DoCmd.SetWarnings False 'Turn off warnings |
Of course do not forget to turn them back on after running your code so that legitimate messages are displayed.
DoCmd.SetWarnings True 'Turn warnings back on |
Share and Enjoy
MS Access VBA Programming | 2 Comments »
October 18th, 2010
Now here is an interesting question!
My first reaction to such a question is why? Since proper database design implicitly prohibits the users from ever directly accessing the database tables, this should never truly be an issue for any developer. But then again, as I have learnt of the course of the years, there are so many exceptional cases…
A Few Solutions to Make a Table Read-Only
- If you are using an mdb format, then you could simply implement User-Level Security (ULS) and simply apply the proper permissions to make your desired tables read-only to your users.
- A more general solution, and one that would also work in all versions of MS Access, would be to migrate the tables you wish to be in Read-Only mode, into a second back-end file and then set the file attributes to Read-Only. Then link the tables to your front-end.
Share and Enjoy
MS Access General Information, MS Access Tables | No Comments »
October 12th, 2010
Have you ever needed to use trig functions in your database?
You may have noticed that although MS Access does offer basic trig functions:
- Sin
- Cos
- Tan
- Arctangent (atn)
it does not offer any of the advance, ‘Derived Math Functions’, such as:
- Secant
- Cosecant
- Cotangent
- Inverse Sine
- …
- Hyperbolic Sine
- …
- Inverse Hyperbolic Sine
- …
As a developer you have one of two options:
- Create, or find, a function to replicate these functions
- Utilize Excel’s trig functions from within you database
Create, or find, a function to replicate these functions
If you simply lookup the term ‘Derived Math Functions’ in the VBE’s help file you will find all the necessary information to build your own custom functions. That said, why not simply benefit from the fact that others before you have already done this work for you and simply perform a quick Google search to locate and existing module with these functions. For instance:
Utilize Excel’s trig functions from within you database
If you know that the database will be utilized on a computer that also has Excel installed on it, why not simply use it’s powerful library of trig function! Nothing could be easier to do. Simply use a procedure such as the one presented below
'---------------------------------------------------------------------------------------
' Procedure : Atanh
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Calculate the Inverse Hyperbolic Tangent by using Excel's built-in
' function
' 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:
' ~~~~~~~~~~~~~~~~
' x Value in Rads
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' Atanh(-0.9) Gives -1.4722...
' Atanh(0) Gives 0
'---------------------------------------------------------------------------------------
Function Atanh(x As Double) As Double
'This procedure requires a reference be set to the Microsoft Excel xx.x Library
On Error GoTo Error_Handler
Dim oXls As Excel.Application
Set oXls = New Excel.Application
Atanh = oXls.WorksheetFunction.Atanh(x)
Error_Handler_Exit:
On Error Resume Next
oXls.Quit
Set oXls = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: YourModuleName/ListDbTables" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, _
"An Error has Occured!"
Resume Error_Handler_Exit
End Function |
Share and Enjoy
MS Access General Information, MS Access VBA Programming | No Comments »
October 7th, 2010
Before delving into actual programming functions and sub-routine, we must first establish an error handler. An error handler is a bit of code which will do pre-defined actions whenever an error occurs. For instance, generate a message to the user or developper describing the nature of the error. For an error handler to be useful, it must provide a minimum of information in its message to the user. Below is a typical example of an error handler I use. Modify it in any way to suit your exact needs.
On Error GoTo Error_Handler
'Your code will go here
Error_Handler_Exit:
On Error Resume Next
Exit {PROCEDURE_TYPE}
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: {PROCEDURE_NAME}/{MODULE_NAME}" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, _
"An Error has Occured!"
Resume Error_Handler_Exit |
A Concrete Example
Sub HelloWorld()
On Error GoTo Error_Handler
MsgBox "Hello Word!"
Error_Handler_Exit:
On Error Resume Next
Exit Sub
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: YourModuleName/HelloWorld" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, _
"An Error has Occured!"
Resume Error_Handler_Exit
End Sub |
Note: Although VBA provides programmers with the possibility of using the err.source statement, it sadly does not help truly identify the culprit of the current error. This is why you must manually enter in the {MODULE_NAME} / {PROCEDURE_NAME} for each error handler. Trust me, although it may take a few extra seconds to do, it will same you loads of troubleshooting time later on (I’m talking from experience)!!!
Also, if you are going to be doing some serious vba (MS Access, Word, Excel, …) work and not just a little tinkering, you should most probably seriously consider looking into the Mz-Tools add-in (free with no strings attached) in conjunction with implementing Allen Browne Error Log (for database developers). These two tools/approaches will greatly simplify and standardize your work!!!
Share and Enjoy
MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | No Comments »
