Renaud posted some comments to the previous post about transparency forms and made some really cool enhancements to the code, along with a note about using a class module to make it easier to use. This is how I use transparency forms in my own implementations so let's update the post to show how you can do this with a class. I've incorporated Renaud's changes to the API code which adds the following functionality:

• Disable transparency when Access is running under Remote Desktop
• Cover the entire screen or just the Access window

The class module shown here will make this easier to use by adding properties and methods that:

• Control the opacity
• Set the back color of the lightbox form
• Display the lightbox form

Here's how you can create this functionality using a class module. Incidentally, class modules are one of my favorite features in VBA. They're really great for this sort of thing.

Create the LightboxForm class

The bulk of the work is going to be done in a new class module called LightboxForm. The class will subclass the Access Form object using the WithEvents keyword so that we can handle the Form_Resize event directly in the LightboxForm class.

Declarations

Start by adding the API declarations as before. There are a few new ones this time as well: IsZoomed, GetWindowRect, and MoveWindow. Also shown here are the private data, constants, and types.

' API declarations, types, and constants
Private Declare Function IsZoomed Lib "user32" (ByVal hWnd As Long) As Long

Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
  (ByVal hWnd As Long, _
   ByVal nIndex As Long) As Long

Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
  (ByVal hWnd As Long, _
   ByVal nIndex As Long, _
   ByVal dwNewLong As Long) As Long

Private Declare Function SetLayeredWindowAttributes Lib "user32" _
  (ByVal hWnd As Long, _
   ByVal crKey As Long, _
   ByVal bAlpha As Byte, _
   ByVal dwFlags As Long) As Long

Private Declare Function GetWindowRect Lib "user32" _
   (ByVal hWnd As Long, _
    rectangle As RECT) As Long

Private Declare Function MoveWindow Lib "user32" _
   (ByVal hWnd As Long, _
    ByVal x As Long, _
    ByVal y As Long, _
    ByVal width As Long, _
    ByVal height As Long, _
    ByVal repaint As Boolean) As Long

Private Type RECT
   x1 As Long
   y1 As Long
   x2 As Long
   y2 As Long
End Type

Private Const LWA_ALPHA     As Long = &H2
Private Const GWL_EXSTYLE   As Long = -20
Private Const WS_EX_LAYERED As Long = &H80000

' Access event property string
Private Const CON_EVENT_PROC As String = "[Event Procedure]"

' name of the lightbox form
Private Const CON_LIGHTBOX_FORM As String = "frmLightbox"

' Private data
Private m_sngOpacity         As Single
Private m_lngBackColor       As Long
Private WithEvents m_objForm As Form

Helper Routines

There is one helper routine in the class to add the event hook. Access forms, reports, and controls will not respond to events unless the event property is set to the string "[Event Procedure]". This routine sets the event property for a given form to this string.

Private Sub AddEventHook(strEventProperty As String)
    m_objForm.Properties(strEventProperty) = CON_EVENT_PROC
End Sub

Properties

The LightboxForm class makes transparency a little easier and more flexible by adding properties for opacity and back color. There's also a property which determines whether Access is running under Remote Desktop.

Public Property Get OpenInRemoteDesktop() As Boolean
    OpenInRemoteDesktop = (Environ$("SESSIONNAME") Like "RDP*")
End Property

Public Property Get Opacity() As Single
    Opacity = m_sngOpacity
End Property

Public Property Let Opacity(sngOpacity As Single)
    ' make sure opacity is between 0 and 1
    If (sngOpacity < 0 Or sngOpacity > 1) Then
        Err.Raise vbObjectError + 513, TypeName(Me), "Invalid value for opacity"
        Exit Property
    End If
    m_sngOpacity = sngOpacity
End Property

Public Property Get BackColor() As Long
    BackColor = m_lngBackColor
End Property

Public Property Let BackColor(lngBackColor As Long)
    m_lngBackColor = lngBackColor
End Property

Form Property

The Form property of the LightboxForm class is the property which binds frmLightbox to the LightboxForm class. This property ensures that the form is in the correct state by setting other properties such as ScrollBars, RecordSelectors, and NavigationButtons. It also changes the color of the Detail section to enable different colors to be used for the transparency.

Public Property Get Form() As Form
    Set Form = m_objForm
End Property

Public Property Set Form(objForm As Form)
    ' make sure there is a valid object
    If (objForm Is Nothing) Then
        Err.Raise vbObjectError + 514, TypeName(Me), "Form object is Nothing"
        Exit Property
    End If
    Set m_objForm = objForm
    ' hook the Resize event
    AddEventHook "OnResize"
    ' set properties of the form
    m_objForm.RecordSelectors = False
    m_objForm.NavigationButtons = False
    m_objForm.ScrollBars = 0  ' Neither
    m_objForm.Section(acDetail).BackColor = m_lngBackColor

End Property

Show Method

The Show method in the class is used to display the frmLightbox form.

Public Sub Show()
    On Error GoTo ShowErrorHandler
    With DoCmd
        .Echo False
        .OpenForm CON_LIGHTBOX_FORM
    End With

ShowExit:
    DoCmd.Echo True
    Exit Sub

ShowErrorHandler:
    If (Err = 2501) Then
        Resume ShowExit
    Else
        ' return to the error
        Resume
    End If
End Sub

Resize Event

In the previous post, we used the Resize event of the form to set the opacity. Since we're handling this event from the LightboxForm class, we'll add this code here. This code also includes Renaud's updates to enable you to choose whether to cover the entire screen or just the Access window. I've modified this only slightly to maximize the lightbox form if the Access window is maximized (as determined by the IsZoomed API). If Access is not maximized, then we'll position the lightbox form to cover the Access window using the MoveWindow API.

Private Sub m_objForm_Resize()

    Dim lngStyle As Long
    Dim r        As RECT

    ' disable screen updates
    m_objForm.Painting = False

    ' If the Access window is maximized, then maximize the lightbox form.
    ' If the Access window is not maximized, then
    ' position the lightbox form so that it covers the Access window

    If IsZoomed(hWndAccessApp()) Then
        DoCmd.Maximize
    Else
        GetWindowRect Application.hWndAccessApp(), r
        MoveWindow m_objForm.hWnd, r.x1, r.y1, (r.x2 - r.x1), (r.y2 - r.y1), True
    End If

    ' get the current window style, then set transparency
    lngStyle = GetWindowLong(m_objForm.hWnd, GWL_EXSTYLE)
    SetWindowLong m_objForm.hWnd, GWL_EXSTYLE, lngStyle Or WS_EX_LAYERED
    SetLayeredWindowAttributes m_objForm.hWnd, 0, (m_sngOpacity * 255), LWA_ALPHA

    ' enable screen updates
    m_objForm.Painting = True

End Sub

Initialization

Lastly, there are a couple defaults we want to set in the class for opacity and back color:

Private Sub Class_Initialize()
    m_sngOpacity = 1         ' initialize opacity to 100% (not transparent)
    m_lngBackColor = vbBlack ' initialize back color to black
End Sub

Create supporting module

Since we're wrapping most of the functionality in a class module, we need an instance of the class. To do this, create a new standard module with the following code:

Private m_objLightbox As LightboxForm

Public Property Get LightboxForm() As LightboxForm
    If (m_objLightbox Is Nothing) Then
        Set m_objLightbox = New LightboxForm
    End If
    Set LightboxForm = m_objLightbox
End Property

To interact with the instance of the LightboxForm class, you'll use this LightboxForm property in the standard module.

Create the Lightbox form

We'll start this time by creating the lightbox form. This is the form that we'll use to display the transparency. Create a new form called frmLightbox and set the following properties:

• AutoCenter = Yes
• Popup = Yes
• BorderStyle = None

The other properties and the back color are managed in the class.

Next, add the following code to the Open event of frmLightbox. This will "bind" the form to the class by setting the Form property of the class.

Private Sub Form_Open(Cancel As Integer)
    ' if the form is open in a remote desktop window, then
    ' cancel the lightbox form effect
    If (LightboxForm.OpenInRemoteDesktop) Then
        Cancel = True
        Exit Sub
    End If

    ' bind the lightbox form class
    Set LightboxForm.Form = Me
End Sub

Create the Login form

The last thing to do before we try this out is to create the login form. You can create a straight forward dialog form as was created in the previous post. This time however, we've modified the code in the Load and Unload events to use the LightboxForm property created in the previous step.

Create another form called frmLogin as described in the previous post. Add the following code to this form.

Private Sub Form_Load() 
    LightboxForm.BackColor = vbBlue
    LightboxForm.Opacity = 0.5
    LightboxForm.Show
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If (Not LightboxForm.Form Is Nothing) Then
        If (CurrentProject.AllForms(LightboxForm.Form.Name).IsLoaded) Then
            DoCmd.Close acForm, LightboxForm.Form.Name, acSaveNo
        End If
    End If
End Sub

Try it out

Try this out by opening the Login dialog. Play around with different values for the .Opacity property and .BackColor property of the class. Here's a screen shot showing a green lightbox with 50% opacity:

Many thanks to Renaud for extending the code and for a great post!

If you've taken a look at some web sites recently, you may have noticed an effect where items (usually images or videos) are brought to the foreground using an effect called a lightbox. The effect is used to dim (or sometimes lighten) the background of the active window while bringing another window to the foreground. The window in the foreground appears to have more prominence because the windows in the background have been dimmed or made transparent. Our friend Tim uses this on his site to display images of sports cards and it looks pretty cool.

Windows Vista uses this effect in a security feature called User Account Control. This feature provides prompts when launching a process with elevated privilege. In order to draw attention to the prompt, the desktop and other windows are dimmed which makes the elevation prompt stand out. Vista also places the machine in a "sandbox" of sorts so that you cannot interact with other portions of the UI. The solution presented here does not do this. Users will still be able to switch to other applications running at the time.

Now let's say that you want to do something like this to draw attention to a form in Access. There are a few places in an application where this might be interesting. For example:

• Login dialog
• Image viewer
• About dialog
• User entry screen
• On screen help

As you can see, almost any case where you might use a modal form in an Access application might be a good candidate to add a lightbox effect. That said - the effect could feel a bit heavy handed after a while so you might not use it for all modal forms, but certainly for those that are more important than others.

You can add this effect to your Access applications with another form by adding transparency on the form window using the SetLayeredWindowAttributes and SetWindowLong Windows API functions. To start, create a new dialog form with the Modal property set to Yes. I used a Login type form called frmLogin as shown below:

Next, create a new form called frmTransparent where we will set transparency so that you can see through it. To set the appearance that the form is being dimmed, change the following properties of frmTransparent.

1. Set the BackColor property of the Detail section to black (#000000 in Access 2007)
2. Set the BorderStyle property of the form to None to remove the border.
3. Set the RecordSelectors property to No
4. Lastly, set the Popup property to Yes.

Now that the forms are created, it's time to add some code. Start by creating a new module called Module1. In the module, add the following code:

Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
  (ByVal hwnd As Long, _
   ByVal nIndex As Long) As Long

Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
  (ByVal hwnd As Long, _
   ByVal nIndex As Long, _
   ByVal dwNewLong As Long) As Long

Private Declare Function SetLayeredWindowAttributes Lib "user32" _
  (ByVal hwnd As Long, _
   ByVal crKey As Long, _
   ByVal bAlpha As Byte, _
   ByVal dwFlags As Long) As Long

Private Const LWA_ALPHA     As Long = &H2
Private Const GWL_EXSTYLE   As Long = -20
Private Const WS_EX_LAYERED As Long = &H80000

Public Sub SetFormOpacity(frm As Form, sngOpacity As Single)
    Dim lngStyle As Long

    ' get the current window style, then set transparency
    lngStyle = GetWindowLong(frm.hwnd, GWL_EXSTYLE)
    SetWindowLong frm.hwnd, GWL_EXSTYLE, lngStyle Or WS_EX_LAYERED
    SetLayeredWindowAttributes frm.hwnd, 0, (sngOpacity * 255), LWA_ALPHA
End Sub

Next, in the code behind the frmTransparent form, add the following code to the Resize event of the form. Using this code in the Resize event seems to reduce some of the flicker seen with other events such as Open and Load.

Private Sub Form_Resize()
    Me.Painting = False
    DoCmd.Maximize
    SetFormOpacity Me, 0.7
    Me.Painting = True
End Sub

Now, add the following code to the Load and Unload events of the login dialog form. This will open the transparent form when the dialog form opens, and close it when the dialog form closes.

Private Const CON_TRANSPARENT_FORM As String = "frmTransparent"

Private Sub Form_Load()
    With DoCmd
        .Echo False
        .OpenForm CON_TRANSPARENT_FORM
        .Echo True
    End With
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If (CurrentProject.AllForms(CON_TRANSPARENT_FORM).IsLoaded) Then
        DoCmd.Close acForm, CON_TRANSPARENT_FORM
    End If
End Sub

To test this, open the login dialog form which will subsequently open the transparent form. The transparency form appears dimmed which makes the login form stand out.

With the API functions created, there are some ways you might extend this in the future. For example, try experimenting with a background color other than #000000, or different values of opacity.

Enjoy!

One of my favorite tricks with forms in Access has always been what I call a kiosk form. A kiosk form is a form that takes up the entire screen and is one that you might find in a store or shopping center displaying information. For example, a real estate kiosk might display homes for sale in the area. These screens often take up the entire screen so your attention is drawn to the content on the screen. Interesting scenarios for a kiosk form include:

• Real estate information
• Dashboards
• Store directory
• Product searches

You can create a kiosk form in Access by maximizing a popup form. When you do this, the form will cover the entire desktop including the Access window and give it this kiosk effect. This is really easy to do by using the Maximize macro action in the Open event, or with the following line of code:

DoCmd.Maximize

If the form appears to flicker when being maximized, you might consider suspending screen updates using the Echo method as follows:

With DoCmd
    .Echo False
    .Maximize
    .Echo True
End With

Be sure to set the Popup property of the form to Yes to see this in action. The BorderStyle property of the form also has an impact on the form:

• When the BorderStyle property is set to None or Dialog, the form will also cover the Windows Taskbar
• When the BorderStyle property is set to Single or Thin, the form will not cover the Windows Taskbar

The folks over at My Access Program have posted 21 free video tutorials with topics ranging from data validation to VBA. 11 of the tutorials are Access 2007 specific covering anchoring, alternate row color and data collection via email.

Watch the Videos

Peter Mullen is today's guest writer. I don't anticipate this workaround will be required when SP2 is released but we won't know for sure as that work isn't defined at this point.

Those who have deployed the runtime RTM version know that you have to remove it before installing the SP1 runtime. This post describes how to chain that action as part of your normal runtime install.

Technique

Use a chained install to call Msiexec and pass in the product code to uninstall the Access Runtime before you install the new runtime.

Prerequisite knowledge

More information on what a chained install is can be found here:

http://msdn2.microsoft.com/en-us/library/bb687991.aspx

More information on how to use Msiexec to uninstall Office files can be found here:

http://support.microsoft.com/kb/296067/en-us

Details

If you use the setup wizard to deploy an application, you can package up the SP1 version of the runtime. This actually creates a chained install to not only install your application, but the runtime as well. This not only packages up the new SP1 runtime into your install, it also modifies the Setup.ini file. This file tells the install package what to install, and in what order.

You can further modify this file to use Msiexec to uninstall the runtime version.

After you make a setup file, you can find the.ini file in the Files\setup folder of your package directory.

Once you are here, you can simply edit the setup file. If you have included the runtime in your package, you will already have a chained install. Rename this to [ChainedInstall_2]. Then add on Chained install one that I define here. This is how your chained install should look.

[ChainedInstall_1]

TaskName=Uninstall Access RTM Runtime

TaskType=Exe

Path=msiexec.exe

IgnoreReturnValue=1

cmdline=/passive /x {90120000-001C-0409-0000-0000000FF1CE}

[ChainedInstall_2]

TaskName=Access SP1 Runtime Setup

TaskType=exe

Path=\Files\AccessRuntime.exe

IgnoreReturnValue=0

cmdline=/passive

Details of the Chained install 1

• TaskName is for logging purposes.
• TaskType defines the type of task you are using. You are calling Msiexec.exe in this example.
• Path defines the relative path to the file you want to run.
• Since msiexec.exe is in users default path directory by default, you simply need the file name.
• Ignore return value defines what to do in case of an error.
• In this case I have set it to 1. This means that the install will continue even if there is an error in this part of the chain. It needs to be set to 1 in case the user has already removed the RTM of the runtime, and there is nothing to uninstall.
• The cmdline passes in arguments to Msiexec.exe.
• Passive is a switch that shows the user what is happening, but does not give the user an option.
• You can remove this switch if you want to make sure your user clicks on “yes” to uninstall the previous version of the runtime.
• You can change this switch to quiet if you would like the uninstall of the RTM runtime to be minimized.
• x tells Msiexec that you are uninstalling a product
• The large number is the product code for the runtime.
• It is important to note that the product code for the RTM and the SP1 of the runtime are the same. So if you customer has updated to SP1 already, this will uninstall and reinstall the same thing.

You can test to make sure you have the correct version by looking at the file properties in your add / remove programs dialog. If the version does not match the version is not 12.0.6237.1003, you do not have the SP1 runtime.

Possible known issues

If your user finds and deletes the msi file for the Runtime, this automated process won’t work. This will cause your update to fail. If you think this might be an issue, you can take out the passive switch and the user will be aware that there is an error in uninstalling the SP1 runtime.

“In the clouds” ~ Using Microsoft Access to query and update Web sites
Garry Robinson shows off integrating web 2.0 into Access through REST and VBA.

Tip: How to create automatic input masks for Us/Canada Postal Codes

Doug Klippert posted a little sample about how to create automatic inputs masks for postal codes

Edit April 9th, 2008: It looks like the fix was checked into a post RTM version of the Package Wizard. It works on internal builds but I now need to figure out the plans for releasing a SP1 version of the Package Wizard.

 ----

One of the consistent feedback we got on this blog was that people expected to have runtime applications run as trusted applications. We wanted to make this happen for the RTM version of the runtime but some issues kept that from happening. A change was made to the Access 2007 SP1 Package Wizard where it now automatically writes the location of the application as a trusted location. This means your SP1 runtime applications will just work without any security prompts.

Enjoy!

When you try to open a compiled database file (.accde or .mde) or a compiled project file (.ade) in the original release of Microsoft Office Access 2007, you may receive the following error message:

This database is in an unrecognized format.

The database may have been created with a later version of Microsoft Office Access than the one you are using. Upgrade your version of Microsoft Office Access to the current one, and then open this database.

This error is also described in KB article 946205.

This will occur if the compiled database or project was created on a computer that has Office 2007 service pack 1 (SP1)  installed.

While you have been able to install Office 2007 service pack 1 (SP1) to correct this issue, that is not an option for some people.  If you were unable to or chose not to install Office 2007 SP1 you now have another option.  We have released a hotfix which will allow you to open a database or project that was compiled in Office 2007 SP1.  The hotfix will update the main Access executable file (msaccess.exe) and a few other key files to a version that is slightly than what you have after installing Office 2007 SP1 but it does not include all of the changes in Office 2007 SP1.

One thing to keep in mind is after you install this hotfix, any database or project that is compiled on your computer cannot be opened on a computer that still has the original version of Access 2007 installed.

For more information about and a link where you can request the hotfix see the KB article 943249.

Edited 4/8 @ 7:07 to correct a link to the correct KB article.

On Friday afternoon we released additional localized versions of the runtime.

Enjoy!

I'm thinking about putting together a blog post that shows what the community has done with ribbon extensibility. From my conversations with lots of developers people are still trying to wrap their heads around the new user model It seems that having a few examples might stimulate some ideas.

This will also be helpful as we think about the ribbon in Office 14.

If you want me to consider your app as one of the featured apps email my spam account a brief description with a few screen shots.

Mike Stowe has a great article and video demo of how to an add-in to a Access 2007 deployment package created from the Package Solution Wizard. He chains the XPS/PDF addin as the example but you can use the technique to install any addin package with your runtime application.

On a side note--what do folks think about the video demos the team has been producing? Have they been useful?

Today we released runtime builds for Arabic, Chinese (Simplified), Chinese (Traditional), Hebrew, Japanese, Korean, and Thai.

The other languages are working their way through the process.

Enjoy!

Some of our customers have reported problems printing mailing labels on contacts, customers, employees tables in the 2007 templates. The contacts type Address field was defined as a Memo data type. Unfortunately, the report mailing label wizard filters our memo fields from the list of fields. This was a pain point with many users who couldn't figure out why the Address field didn't show up when using the report Label Wizard. Jeff Conrad even created a special FAQ page to explain to users why they couldn't see their Address field in the wizard dialog pages and how to change the data type back to Text.

We revised the 24 English online Access 2007 templates with this change (these templates also show up in the Getting Started Screen). Any table in any of the templates that used Memo as the data type for the Address field has now been changed to Text. The next time anyone downloads one of these templates, they shouldn't have problems printing labels.

If you have other suggestions for the templates--send us an email. We would love to hear from you.

I know lots of you are very anxious to get your hands on this build. You can go download the SP1 runtime.

http://www.microsoft.com/downloads/details.aspx?FamilyId=D9AE78D9-9DC6-4B38-9FA6-2C745A175AED&displaylang=en

This version of the Access 2007 Runtime contains files whose versions are slightly higher than the level of the same files that are updated by Office 2007 Service Pack 1 (SP1). For additional information about Office 2007 Service Pack 1 and the Access 2007 issues fixed by Office 2007 Service Pack 1, click the following article numbers to view the article in the Microsoft Knowledge Base:

936982 Description of the 2007 Microsoft Office suite Service Pack 1
942378 Issues that are fixed in Access 2007 by the 2007 Microsoft Office suites Service Pack 1

The Access 2007 Runtime updates files to correct some specific Access 2007 runtime issues that were not previously documented in a Microsoft Knowledge Base article:

• PDF/XPS Export: You may see a message similar to "can't save the output data to the file you've selected" or "The OutputTo action was cancelled", when you try to export an object to the PDF or XPS file formats from within Access 2007 Runtime.
• Text/HTML Export: You see a message similar to "can't complete the Output operation" when you try to export a report to the Text or HTML file formats from within Access 2007 Runtime.
• Pivot Table/Chart Views: Pivot Charts and Pivot Tables do not show the correct results when opened in Access 2007 Runtime.
• Access Data Projects: You see a message similar to "The record source [RecordSourceName] specified on this form or report does not exist", when opening a report in an Access project (*.adp) which is bound to a SQL Server object even though the record source exists.
• Trusted Locations: the database folder is added to the trusted locations ensuring the application will run without secuirty warnings.

The international versions of the runtime should be available in the next few weeks (I don't have a specific date as intl testing is ongoing). We will post download links as they come available.

Enjoy!

Edited 4/8/08 to include mention of trusted locations.

There is a good article about SharePoint on Network World. Here are some of the more interesting quotes:

"Microsoft's SharePoint Server is on a billion dollar juggernaut to potentially become the next must-have technology, offering companies tools for building everything from collaborative applications to Internet sites..."

"Microsoft Office SharePoint Server (MOSS) 2007 is the fastest growing product in the company's history and seems to have as many uses as a Swiss Army knife. Its six focus areas are collaboration, portal, search, enterprise content management (ECM), business process management and business intelligence."

"In 2008, SharePoint has evolved into the prototypical Microsoft tool – good enough for small-to-midsize businesses, adaptable to large enterprises, and, most important, plenty of financial opportunities for third-party independent software vendors and systems integrators."

The article goes on to talk about the exploding growth, some of the current SharePoint weaknesses, and the looming battle with IBM. Interesting stuff...

From a personal perspective it has been rewarding to watch SharePoint grow up as a product. I can remember back in early 2002 when a small group of us invented the Web Part framework and the list web service APIs thus molding SharePoint into a developer platform. Interestingly, many of the key contributors of the first version of web parts went back to work on Access 2007 and are now working on Access vnext.