Dave's Technophorical Times

A blog about Microsoft's Technologies!
SharePoint :: MVC :: ASP.NET :: IIS :: SQL Server :: Visual Studio :: MS Access

The entire Detail section of a Microsoft Access form goes blank sometimes. The text boxes you see in design view disappear when you go to use the form, leaving nothing but empty space.

The Cause

It happens when both these conditions are met:

  1. There are no records to display, and
  2. No new records can be added.

Condition (a) can be triggered in several ways. Examples:

  • The form's Data Entry property is set to Yes. (This means the form shows no existing records, i.e. it is for entering new ones only.)
  • The form has a Filter applied (or is opened with a WhereCondition) that yields no records.
  • The form is based on a query where the criteria yield no records.
  • The form is based on a table that has no records.

Condition (b) can be also be triggered by several things:

  • The form's Allow Additions property is set to No.
  • The form's Recordset Type property is set to something other than Dynaset.
  • The form is based on a read-only query. (If you cannot add a record directly to your query)

You can demonstrate the problem with any form, just by setting its Data Entry property to Yes, and Allow Additions to No.

If it has Form Header and Form Footer sections, controls in these sections will still be visible but they may not work properly.


You cannot prevent this from happening, unless you can avoid (a) and (b) being true at the same time.

If you have set Allow Additions to No so as to prevent new records, there is a simple workaround. Set Allow Additions back to Yes, so the form can show the new record when there are no others. You can prevent new records by canceling the BeforeInsert event of the form. Steps:

  1. Set the Before Insert property of the form to:    [Event Procedure]
  2. Click the Build button (...) beside this. Access opens the code window.
  3. Insert the line Cancel = True between the other two. The code will look like this:
        Private Sub Form_BeforeInsert(Cancel As Integer)
            Cancel = True
        End Sub

If you cannot prevent condition (b), you might be able to prevent condition (a). For example, if you are opening a form with a WhereCondition that could result in no records, you might prefer to display a dialog rather than the blank form. To do that, cancel the Open event of the form:

Private Sub Form_Open(Cancel As Integer)
    If Me.RecordsetClone.RecordCount = 0 Then
        Cancel = True
        MsgBox "No records"
    End If
End Sub

(Note: The procedure where you used OpenForm will be notified with error 2501 that the form did not open. Use error handling in that procedure to trap the message.)

There are times when you cannot prevent (a) and (b) happening together. A complex search form may have to use a read-only query, and the user's criteria may return no results. For these cases, place the criteria boxes in the form header section, and accept the fact that users will be okay if they see nothing when the search returns no results.

January 2010 update: In Access 2007 and later, check that the form's Filter On Load property is set to No so that it does not automatically apply a previously saved filter when you open  the form.

So you have RemoteApp up and running and you have made an MS Access database available in the list of Remote Apps, but when you open it you get a message stating that the Database is read-only and that you can only update Linked tables!

Most likely this is a permissions error. Make sure that the RemoteDesktop Users group, whatever that is named on your server, has full permissions on the Folder housing your database, and on the database file itself.

Hope this helps someone!

When developing your Excel or Access application, it’s not uncommon to need to capture your user’s domain/username for one reason or another. A user’s domain/username combination is the traditional unique identifier for a user - often captured by programmers to do things in like:

  • Help manage user login and permissions
  • Create a history of who used the application
  • Capture the name of each user who made the changes to data
  • Personalize outputs and program messages for users

There are literally half a dozen ways to capture a user’s domain name and user name. In this post, I’ll give you a very easy method using the Environ function.

Environ stands for Environment, and refers to environment variables in an operating system. Environment variables are specially named aliases for specific system properties, exposed as a kind of shortcut for system administrators and programmers.

Although majority of these Environment variables are useless to your average Excel/Access developer. There are a few that can prove to be useful. Two of these are the UserDomain and UserName variables. We can use these to capture a user’s Domain\UserName.

To demonstrate this, I’ve entered the following function into a standard module. You can call this from a form or query!

Function getReturnUName()
       getReturnUName = Environ(“UserDomain”) & “\” & Environ(“Username”)
End Function


  • Sub EnvironListing()
  •  Dim I As Integer
  •  I = 1
  •   Do Until environ(I) = ""
  •     Debug.Print environ(I)
  •     I = I + 1
  •   Loop
  • End Sub

  • Do you use the report wizard to help you create reports in Microsoft Access 2007? If you do, you are probably aware of the control layout. Many controls are automatically grouped into a layout when you use the wizard to create a report. These control layouts help you to arrange the controls, i.e. text boxes and labels, as a group. When you move or resize one control, the other controls will automatically adjust accordingly. While using layouts can save you time when manipulating controls, they can also be restricting. It is possible to remove a control layout. To remove a control layout, you need to view the report in the design or layout view. Once in the appropriate view, select the controls you want to remove from the layout. Then select the Arrange tab on the ribbon and click the Remove button located within the control layout section. With the control layout removed you will be able to independently move and resize the controls within your report.

    If you want to switch on the Trace log for MSAccess linked tables to SQL Server then set the following registry key to 1. If you can't see the key then create a new REG_DWORD key and set the value to 1. The log file will be called sqlout.txt and it will be produced in the Default database folder as indicated in MSAccess Tools->Options->General->Default database folder.

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC] TraceSQLMode

    Hope this helps! Also don't forget to switch it off when you don't need it.

    This is how to list all Tables and Fields in an Access Database Project (ADP).

    Option Compare Database
    Option Explicit
    Public Sub ListTablesAndFieldsADP()
        'Reference is needed for: Microsoft ADO Ext. 2.x for DDL and Security
        Dim cat As New ADOX.Catalog 
        Dim tbl As ADOX.Table       
        Dim col As ADOX.Column       
        Dim fs As Object
        Dim output As Object
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set output = fs.CreateTextFile("c:\Tables_Views_And_Fields.txt", True)
        Set cat.ActiveConnection = CurrentProject.Connection
        For Each tbl In cat.Tables
            If Left(tbl.Type, 6) <> "SYSTEM" Then
                output.WriteLine tbl.Name & " (" & tbl.Type & ")"
                For Each col In tbl.Columns
                    output.WriteLine "  " & col.Name
            End If
        Set col = Nothing
        Set tbl = Nothing
        Set cat = Nothing
    End Sub

    This is how to list all LINKED Tables and Fields in an MS Access Database.
    To list Local Tables, remove Len(tdfCurrent.Connect) > 0

    Public Sub ListTablesAndFields()
        Dim fld As DAO.Field
        Dim tdfCurrent As DAO.TableDef
        Dim dbCurrent As DAO.Database
        Set dbCurrent = CurrentDb
        Dim fs As Object
        Dim output As Object
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set output = fs.CreateTextFile("c:\Tables_Views_And_Fields.txt", True)
        For Each tdfCurrent In dbCurrent.TableDefs
            If Len(tdfCurrent.Connect) > 0 And Left(tdfCurrent.Name, 4) <> "~TMP" Then
                output.WriteLine tdfCurrent.Name
                For Each fld In tdfCurrent.Fields
                    output.WriteLine "   " & fld.Name
            End If
    End Sub

    The Blogger

    Dave Stuart I'm a Developer with a passion for coding. I enjoy the challengers that come with the job! SharePoint is one of my expert areas along with .NET Web Development with MVC and good old MS Access VBA coding. I Blog so that I can remember how I did that way back when; PLUS all this stuff is searchable! I constantly study and run my own business, Dafran Inc. I have passed 22 Microsoft Exams since 1998 when I first jumped on the treadmill of knowledge. I hope that you enjoy this Blog as much as I enjoy updating it. All the very best from Calgary, Alberta, Canada. contact me at linkedin @ dafran.ca


    <<  October 2018  >>

    View posts in large calendar

    Sign in