MS Access Hebrew Dates Class (revisited)

This article has been updated and is now available here.

I've just finished the new version of the Hebrew Dates code.
All thanks to Moishy at Access World Forums for some really useful feedback on the original.
The new version can be downloaded for free via:

• 4Shared
• Access World Forums

Here's a description of the functionality

clsHebrewDate Class
  PROPERTIES
    .HDay
        Read/Write (long)
        stores the value of the current Hebrew day
        When set, the Gregorian Date will automatically be updated.
    .HMonth
        Read/Write (long)
        stores the value of the current Hebrew month
        When set, the Gregorian Date will automatically be updated.
    .HYear
        Read/Write (long)
        stores the value of the current Hebrew year
        When set, the Gregorian Date will automatically be updated.
    .HDaysInMonth
        Read Only (integer)
        returns the number of days in current Hebrew month.
    .GDate
        Read/Write (date)
        stores the value of the current Gregorian Date
        When set, the Hebrew Date will automatically be updated.
    .GAfterSunset
        Read/Write (boolean)
        used to mark the Gregorian date as occuring after sunset.
        NB: Gregorian days start at midnight but Hebrew days start
        after sunset
        TRUE = After sunset
        FALSE = Before sunset
        When set, the Hebrew Date will automatically be updated.
    .IsHLeapyear
        Read Only (boolean)
        Returns TRUE if the current Hebrew Year is a Hebrew leapyear
        NB: Hebrew leap years include a whole extra month (Adar II)
        and occur in a pattern that follows a 19 year cycle (see below)
    .IsValid
        Read Only (boolean)
        Returns TRUE if the current Hebrew Date is valid
  METHODS
    .Copy()
        Function returns clsHebrewDate type
        Creates and returns a new copy of the current instance
        of the clsHebrewDate class.
    .SetHebrewDate()
        Sets the Hebrew date and automatically updates the Gregorian
        date.
    .YahrzeitStep([,])
        Alters the current Hebrew Date moving it forward or backward
        x years according to the value of YearStep in accordance with
        the selected algorithm selected by CalcStyle.
        NB: You should always calculate additional Yahrzeit dates from
        the orginal date of the aneversary. DO NOT be tempted to calc
        a Yahrzeit from a previous Yahrzeit! This will lead to errors!
        YearStep: integer value can be positive or negative
        CalcStyle: (OPTIONAL)
                    1: Traditional (DEFAULT)
                    2: Reform
                    3: CatchAll

Module modHebrewDate
Function's
 GetHebrewMonthsList(bLeapYear As Boolean) As Variant
  returns a list of month numbers and their names delimited with semicolons
  this is particularly useful for generating combobox dropdown lists
  NB: Hebrew leap years have a extra month, hence the need for bLeapYear 
  to specify if the list should generate months for a leap year

 FormatHDate(myDate As clsHebrewDate, myFormat As Variant) As Variant
  Format hebrew date and output as a variant string.
  Variant string is needed so unicode letters can be used i.e. for Hebrew text
  the format parameters are based on the ones uses in PHP. See http://php.net/manual/en/function.date.php
  Format codes:
   Misc
    \ escape character
   Day
                d Date, 2 digits with leading zeros (00..30)
                f Textual representation of date as stored in the language table (HDate01..HDate30)
                j Date as number without leading zeros (1..30)
                l NB: this is a lowercase letter L
     Textual representation of the day of the week as stored in the language table (HDay1..HDay7)
                S English ordinal suffix for the day of the month, 2 characters (st, nd, rd, th)
                w returns 1..7 where 1=Sunday (Yom Rishon)
   Month
                F Textual representation of a month as stored in the language table
     (HMonth01..05, HMonth07..13, HMonth06Leap, HMonth07Leap)
                m Numeric representation of a month, with leading zeros (01..13)
                n Numeric representation of a month, without leading zeros (1..13)
                t Number of days in the given month (29..30)
   Year
                L Whether it's a leap year (1=yes 0=no)
                Y A full numeric representation of a year, all digits (eg 5758)
                y A two digit representation of a year (eg 58)
   Time
                a Textual representation of time of day in the Gregorian Date part as stored in Language Table (GTimeNight, GTimeDay)
Sub's
 SetHebrewDateFromGUI(ByRef MyHebrewDate As clsHebrewDate)
  Shows a hebrew date picker dialog box and returns any selected date back to the carable passed to MyHebrewDate
  NB: if you prefer the dialog box to display in Right to Left mode and have days that change according to the hebrew
  rather than the gregorian calendar (i.e. at sunset rather than midnight) edit the dlgHebDatePick13/14 form and set
  Const CDisplayRTL = True
  
  
  

Internationalisation in Access

Working with Joomla, I'm used to messing around with language files for the user interface.
Each language file supplies text that should be displayed (as part of the interface) to the user depending on their language.
So today, I decided it was high time I built something similar in Access. Below is the (surprisingly easy) result, using Kernel32 to identify the current language of the user...

First of all you'll need a table to store your text. Access text fields store text as unicode so you can even include text from right-to-left languages like Arabic or Hebrew.

The table will need 3 fields

1. swmVar to store the variable name
   Type: Text
   Size: 25
   Required: Yes
   Input Mask:  >Aaaaaaaaaaaaaaaaaaaaaaaaa
2. lwmLanguageID to store the ID of the Language the text is for
   Type: Long (Long Integer)
   Required: Yes
   Default Val: 0
3. swoData to store the text itself
    Type: Text
   Size: 255

I can't think of a reason why table would need a primary key but it does need to make sure that you don't store duplicate values of swmVar within the same Language ID so for ease I've set swmVar + lwmLanguageID as a composite primary key. However, you could create a separate primary key and just have these to fields as a composite unique index.

NB: I'm using my standard naming convention here where the first letter of the field represents the field type, the 2nd notes if the field is editable (w=write, r=read-only) and the 3rd shows if the field is optional (o) or mandatory (m).

Next you'll need the module
Module: ModLanguage

Option Compare Database
' Module to assist internationalisation of database interface
' written by Marc Ozin
' version: rc2012.02.02
' Website: http://marcozin.blogspot.com


' this module requires a lanaguage table like this:

' Table Name: tblLanguage
' Fields:
'   swmVar
'       Type:        Text
'       Size:        25
'       Required:    Yes
'       Input Mask:  >Aaaaaaaaaaaaaaaaaaaaaaaaa
'   lwmLanguageID
'       Type:        Long Integer
'       Required:    Yes
'       Default Val: 0
'   swoData
'       Type:        Text
'       Size:        255
'
' Primary Key: Composite of swmVar & lwmLanguageID
'
' this table will store language varables & text for each lanaguage code
' plus language varables & text to default to if language code not found
' signifed using lwmLanguageID of zero (0)

' Notes:
' Table of Locale ID's can be found on these pages:
' http://msdn.microsoft.com/en-us/goglobal/bb895996
' http://msdn.microsoft.com/en-us/goglobal/bb964664

' Examples:
' to set the caption for the button called ButtonCancel in a form when it opens
' edit the OnLoad event for the form and insert the following line:
'   ButtonCancel.Caption = GetLangTxt("CANCEL")
' Then in the language table create a row with these values:
'   swmVar:         CANCEL
'   lwmLanguageID:  0
'   swoData:        &Cancel
' "&Cancel" will be the default text when a particular user's language cannot be found
' to have different text for German, add the following row:
'   swmVar:         CANCEL
'   lwmLanguageID:  1031
'   swoData:        &Kündigen




'Get the language ID for the current user's locale from kernel32
'returns Long number representing the Language ID
Public Declare Function GetUserDefaultLCID% Lib "kernel32" ()


Public Function GetLangTxt(sLangVar As String, Optional ByVal lLanguageID As Long = -1) As String
' returns language text from specified variable stored in the Language Table
' function will try and return text for specified varable for current language (or language specified by lLanguageID)
' if no text found, then it will try and return text for varable with a language id of zero
' if still no text found, then it will return the name of the varable incased in square brackets

Const cLanguageTable = "tblLanguage"
Const cLanguageDataField = "[swoData]"
Const cLanguageVarableNameField = "[swmVar]"
Const cLanguageIDField = "[lwmLanguageID]"

Dim vTemp As Variant
    
    If lLanguageID = -1 Then
        lLanguageID = GetUserDefaultLCID()
    End If

    'try for a specific language id
    vTemp = DLookup(cLanguageDataField, cLanguageTable _
    , cLanguageVarableNameField & " like '" & UCase(Trim(sLangVar)) & "' and " & cLanguageIDField & "=" & CStr(lLanguageID))
    
    ' if nothing found
    If IsNull(vTemp) Then
        'try for language zero
        vTemp = DLookup(cLanguageDataField, cLanguageTable _
        , cLanguageVarableNameField & " like '" & UCase(Trim(sLangVar)) & "' and " & cLanguageIDField & "=0")
    End If
    GetLangTxt = Nz(vTemp, "[" & sLangVar & "]")
End Function

Here's an example of how to set the caption for the button called ButtonCancel in a form when it opens.

1. edit the OnLoad event for the form and insert the following line:

   ButtonCancel.Caption = GetLangTxt("CANCEL")

2. Then to set "&Cancel" as the default text when a particular user's language cannot be found, in the language table create a row with these values:
   
   • swmVar: CANCEL
   • lwmLanguageID: 0
   • swoData: &Cancel
3. to have different text for German, add the following row:
   
   • swmVar: CANCEL
   • lwmLanguageID: 1031
   • swoData: &Kündigen

Hebrew Dates as Class Module in Access

See new post with updated version!
Recently I've been messing around with a neat bit of code written to explain how to calculate Hebrew dates (which are based on a Lunar calendar).
I've extended it's functions, building it into a Class; creating Yartzeit calculation functionality (using Progressive, Traditional and Catch-All options); and hebrew date picker GUI dialogs.

You can find my first version of this code here:
http://www.4shared.com/file/_5c5GJcP/hebrewDates.html
or
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=40063

The file is is Access2010 format and contains an example form to show some of it's features.

NB you can open the file in Access 2007 but 2007 doesn't support the .BackColor property for Buttons so you'll need to alter this in the dlgHebDatePick code inside the Draw_MonthButtons Sub.

See new post with updated version!

Reports in Forms

You learn something new every day! I had no idea that in MS Access 2010 you could embed Reports inside Forms (new feature definitely not available in Access 2007).
See: http://social.technet.microsoft.com/Forums/en-US/office2010/thread/78dd8a46-1281-47b7-84e8-125c56e13b2a#6aaa6cf0-af99-49e7-96ee-3ab12a043b7b

That might come in very handy with displaying a list html formatted notes in a form.

Now to have a play...

MSSQL Calls through ODBC in MS Access

As MS Access no longer supports .adp projects any Access databases created have to link to MS-SQL back ends through ODBC.
However, sometimes you still need to call Stored Procedures, Views or create dynamic query statements to be passed directly to MS-SQL.

You can still achieve this by creating a passthru query in Access.

Below is a little bit of code I put together to allow you to run SQL statements using a passthru query called "SQLCall" or choosing one of your own.

To set things up, you'll need to create a passthru query in Access (see http://support.microsoft.com/kb/303968 for how to do this) called SQLCall and then drop this code into a module:

Public Sub SQLExec(ByVal sSQL As String _
 , Optional bRetRecords As Boolean = False _
 , Optional bExecute As Boolean = True _
 , Optional sQueryName As String = "SQLCall" _
 , Optional vConnect As Variant)
 
' vConnect   : Optional connection string
' bRetRecords   : Optional set return records on / off. Default: on
' sQueryName : Optional set alternate location for query storage

On Error GoTo ErrorHandler

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim sConnect As String

 Set db = CurrentDb
 Set qdf = db.QueryDefs(sQueryName)
 If IsMissing(vConnect) Then
  sConnect = qdf.Connect
 Else
  sConnect = CStr(vConnect)
 End If
 
 qdf.SQL = sSQL
 qdf.Connect = sConnect
 qdf.ReturnsRecords = bRetRecords
 If bExecute Then qdf.Execute

 GoTo ErrorExit
ErrorHandler:
 Select Case Err.Number
 Case Else
  MsgBox cErrorIntro & vbCrLf _
  & vbCrLf & Err.Number _
  & vbCrLf & Err.Description _
  & vbCrLf & Err.Source _
  , vbCritical + vbOKOnly, "Error"
 End Select
 Resume Next
ErrorExit:
End Sub

You can use this Sub to do thinks like:

Call the untag all contacts stored procedure

SQLExec "EXEC spUntagAllContacts;", False, True

Delete Contact #5

SQLExec "DELETE FROM tblContacts Where ContactID = 5;"

Call the SP to tag a contact based on ContactID

SQLExec "EXEC spTagContact " & Nz(ContactID, 0) & ";"

Do a select query and make the results accessible from the SQLContactList passthru

SQLExec "SELECT * from tblContacts WHERE ContactID < 100;", True, False, "SQLContactList"

Call the stored procedure to get the current users security groups and make the results accessible from the SQLSecurityGroupList passthru

SQLExec "EXEC spListMySecurityGroups '" & UserName & "';", True, False, "SQLSecurityGroupList"