How can I change the application's title bar to a custom name?
	Is Dlookup function faster than using Recordsets?
	How can I disable/enable the autoexec macro?
	How do I reference controls in a subform or control?

How can I change the application's title bar to a custom name?
Keywords: Programming Change Application Name
Posted October 3, 1996

Microsoft Access 95 now comes with startup properties that you can set for the database. They can either be accessed via a dialog box found on the menu bar under Tools, Startup...or set at runtime.

Startup properties are properties of a Database object. A Database object is a data access object supplied by the Microsoft Jet database engine, but startup properties are defined by Microsoft Access, so they aren't automatically recognized by the Jet database engine. If a startup property hasn't been set previously, you must create it and add it to the Properties collection of the Database object. When you set startup properties from Visual Basic, you should include error-handling code to verify that the property exists in the Properties collection.

The names of the startup properties are different from the text that appears in the Startup properties dialog box, available by clicking Startup on the Tools menu. The following table provides the name of each startup property as it is used in Visual Basic code.

Unfortunately, Access 2.0 didn't come with comparable startup properties. In order to change the Application's tittle bar an additional entry had to be made into the MSACC20.INI file:

	[Run-Time Options]
	Titlebar=New application name
			

This could become cumbersome if it was a multi-user database. Usually this would force everyone to use the same INI file. Another and more preferred option would be to set the application title bar on startup using Window calls. To accomplish this a handle to the current application must be obtained. In Access 95 this is simple act of retrieving the "hWndAccessApp" property of the Application object. In Access 2.0 though, we have to make some Window calls to obtain this, i.e.:

Declarations Section of a Module

Option Compare Database   'Use database order for string comparisons
Option Explicit
	
 ''' Declarations used to obtain window handle to application
 Declare Sub setWindowtext Lib "User" (ByVal hWnd As Integer, ByVal lpString As String)
 Declare Function GetParent Lib "User" (ByVal hWnd As Integer) As Integer
 Declare Function GetActiveWindow Lib "User" () As Integer
			

Once we declare the DLLs then we write a function to obtain the window handle of the current application:

Function GetAccesshWnd () As Integer
    ''' Function:   GetAccesshWnd () As Integer
    '''
    ''' Comments:   Sets application title bar
    '''
    ''' Arguments:  none
    '''
    ''' Returns:    window handle of database
    '''
    ''' Called by:  SetAppTitle
    '''
    ''' Keywords:   APPLICATION WINDOW HANDLE
    '''
    ''' Date        Developer                               Action
    ''' -------------------------------------------------------------------------
    ''' 8/17/96    Marty Wasznicky                         Created
On Error Resume Next
         Dim hWnd As Integer
         Dim hWndAccess As Integer
 
         ' Get the handle to the currently active window.
         hWnd = GetActiveWindow()
         hWndAccess = hWnd
 
         ' Find the top window without a parent window.
         While hWnd <> 0
            hWndAccess = hWnd
            hWnd = GetParent(hWnd)
         Wend
 
         GetAccesshWnd = hWndAccess
 
End Function
			

Now that we have the window handle, now lets set the Application's title bar text:

Function SetAppTitle (strAppTitle) As Integer
    ''' Function:   SetAppTitle (strAppTitle) As Integer
    '''
    ''' Comments:   Sets application title bar
    '''
    ''' Arguments:  strAppTitle: Name to set title bar to
    '''
    ''' Returns:    true if successful
    '''
    ''' Called by:  Autoexec macro
    '''
    ''' Keywords:   APPLICATION TITLE BAR
    '''
    ''' Date        Developer                               Action
    ''' -------------------------------------------------------------------------
    ''' 8/17/96    Marty Wasznicky                         Created
On Error GoTo SetAppTitle_Err
    Dim strProcName As String * 255
    strProcName = "SetAppTitle"
    gintReturn = GetAccesshWnd()
    Call setWindowtext(gintReturn, strAppTitle)
    SetAppTitle = True
SetAppTitle_Exit:
    Exit Function
SetAppTitle_Err:
    Call ErrMsg(Err, "Could not set title bar for application!", Trim$(strProcName))
    SetAppTitle = False
    Resume SetAppTitle_Exit
End Function
			

This last function can be called from the Autoexec function of the application.

Is Dlookup function faster than using Recordsets?
Keywords: Dlookup Benchmarks
Posted October 3, 1996

Many people believe that using Dlookup is generally a poor way to retrieve a value to display in a control due to performance. Recently I conducted testing using Dlookup functions and Recordsets.

se tests were conducted against entire tables. Tables with 500 records, 4,000 records and 8,000 records were used. In the first round of tests, the field queried on and the field retrieved were not indexed. In the second round of tests these fields were indexed. Also, these tests were conducted against linked Access tables and local Access tables. The Dlookup function and OpenRecordset method was used in these tests. The dbOpenDynaset and dbOpenSnapshot option were both used with the OpenRecordset method.

Additionally, the FindFirst method was used in all OpenRecordset tests. The Seek method was not used for two reasons:

• It could only be used on local tables with indexed fields
• Most applications employ the policy of keeping data tables separate from the client front end

Description of tables used:

• Each table structure identical
• 25 columns
• Field queried on was text datatype
• Field returned was long datatype (counter field)

Listed below are the results of these tests:

The field returned by the functions was indexed as the primary key. The field queried on was indexed (duplicates allowed). The database was then compacted the following tests were conducted:

In Regards to the OpenRecordset method, the following function was used:

	Function Getlookup(varValue As Variant, szFieldValue As String, _ 
szFieldReturn As String, sztablename As String) As Variant
''' Function:   Getlookup(varValue As Variant, szFieldValue As String, szFieldReturn As String, _
'''             sztablename As String) As Variant
'''
''' Comments:   Retrieves a value from a table
'''
''' Arguments:  szFieldValue:   Field name to search on, part of criteria
'''             varValue:       Value to search on above field, part of criteria
'''             szFieldReturn:  Field to return value of
'''             sztablename:    Table to search in
'''
''' Called by:
'''
''' Keywords:   FIND RECORDSET
'''
''' Date        Developer                               Action
''' -------------------------------------------------------------------------
''' 7/29/96    Marty Wasznicky                         Created
On Error Resume Next
Dim dbCurrent As DATABASE
Dim rstCurrent As Recordset
Dim strCriteria As String
Set dbCurrent = DBEngine(0)(0)
'Set rstCurrent = dbCurrent.OpenRecordset(sztablename, dbOpenSnapshot)
Set rstCurrent = dbCurrent.OpenRecordset(sztablename, dbOpenDynaset)
strCriteria = szFieldValue & " = " & """" & varValue & """"
rstCurrent.FindFirst strCriteria
If rstCurrent.NoMatch Then
    Getlookup = Null
Else
    Getlookup = rstCurrent(szFieldReturn)
End If
rstCurrent.Close
End Function

The DLookUp function appeared as follows:

ReturnValue = DLookup("FieldtoReturn", "TableName", "[CriteriaField]=""" & Criteria & """")
			

CONCLUSION:

In summary, I found that in almost all cases Dlookup was just as fast (usually faster) than using the OpenRecordset method function. The exception appeared only in local indexed tables using the OpenRecordset method with the dbOpenDynaset option. In this case the OpenRecordset method was clearly faster. Most likely it would be even faster using the Seek method (as long as you had an indexed field).

How can I disable/enable the autoexec macro?
Keywords: Autoexec Macro
Posted Sept 30, 1996

With Access 2.0 users had only to hold the shift key down while opening the database to prevent the autoexec macro from executing. Well, in Access 95 users can still hold down the shift key to disable this startup function.

In some cases you might not want to let users bypass the autoexec macro. Access 95 now includes startup properties for the database object, one of which is "AllowBypassKey". To be used, this property must first be appended to the database object and assigned either a true or false value. When true, users may use the shift key to bypass the autoexec macro. When set to false the shift key bypass is disabled.

Below is a sample function:

Global Const gErrPropertyNotFound = 3270
Sub AllowShift(fsetting As Boolean)
''' Function: AllowShift(fsetting As Boolean)
'''
''' Comments:   	passes a boolean value, true or false to allow
'''		the use of the shift key to bypass the Autoexec macro
'''
''' Arguments: fsetting: Either true or false
'''
''' Returns:    none
'''
''' Called by:
'''
''' Keywords:   AUTOEXEC BYPASS 
'''
''' Date        Developer                               Action
''' -------------------------------------------------------------------------
''' 8/09/96    Marty Wasznicky                         created
    On Error GoTo AllowShift_Err
    Dim dbCurrent As DATABASE
    Set dbCurrent = CurrentDb
    
    dbCurrent.Properties!AllowBypassKey = fsetting
    
AllowShift_Exit:
    Exit Sub
    
AllowShift_Err:
    Select Case Err
        Case gErrPropertyNotFound
            dbCurrent.Properties.Append _
            dbCurrent.CreateProperty("AllowByPassKey", dbBoolean, fsetting)
            Resume Next
        Case Else
            ''' Global error handler goes here
            Resume AllowShift_Exit
    End Select
End Sub
			

How do I reference controls in a subform or control?
Keywords: Reference Control
Posted Sept 30, 1996

There are several ways to reference controls in a subform/subreport or in another control depending on the version of Access you are using. Typically, the reference in Access 2.0 is:

	Forms!FormName!SubFormName.Form!ControlName
			

For subreports the syntax is:

	Reports!ReportName!SubReportName.Report!ControlName
			

Whereas ".Form" or ".Report" gets you to the actual form or report property of that control.

In Access95 the following syntax works:

	Forms!FormName!SubFormName.Form.Controls!ControlName
			

Normally in Access 95 the controls are referenced through the Controls collection. Since the Controls collection is the default collection and the ".Form" is optional in VBA, the following syntax now works:

	Forms!FormName!SubFormName!ControlName
			

Without the explicit reference to the collections, as you might suspect, this syntax will execute more quickly. Additionally, depending on the current scope you could abbreviate the syntax. If referring to a control on a subform from the main form you could use the following abbreviated syntax:

	SubFormName!ControlName
			

Lastly, in either version to reference a control in another control for displaying a value or as part of a function call i.e. Dlookup etc. the following syntax is used:

	Form!ControlName			

In all cases, the best practice is to avoid hard coding form or control names in your references! Use module level variables for control names and global variables for form names. Within controls, you must use the syntax "Form!ControlName" as the control will not interpret variables.

Back to Top

Back to the FAQ Table of Contents

If things get any worse, I'll have to ask you to stop helping me.