Navigation:  Articles > Oct-1997 >

The Return of Access Answers

Previous pageReturn to chapter overviewNext page

Chris Bell          
 
In the return of our feature "Access Answers," Chris answers a few questions of special interest to Access 97 developers and issues a warning about a serious bug with subforms and the SourceObject property. The source code for selected items is available in the accompanying Download file.
 
Many of my continuous subforms are really just nicely formatted datasheets with one line per record. I want these to act like a datasheet when I press the up or down arrows -- that is, navigate to the next or previous record rather than use the default form navigation behavior.
 
The ability to intercept keystrokes and modify their behavior was greatly enhanced when the KeyPreview property was introduced for forms in Access 95. When the KeyPreview property is set to Yes, you can specify KeyDown, KeyPress, or KeyUp event procedures at the form level rather than specifying discrete keystroke interception for every control. KeyPreview forces every keystroke (regardless of the control with the focus) to be first passed to the form-level procedures before either control or AutoKey interception occurs.
 
I also have subforms where I'd prefer this more intuitive use of the up and down arrow keys, so I took the effort to write a reusable procedure that you can apply to any form or subform. The key to this reusability is that a form object is passed as an argument to the procedure. This form object can represent either a main form or subform, and the code can operate on the object without concern to its context. Each form/subform that requires the modified behavior requires the KeyPreview property set to Yes and a "stub" KeyPress [event procedure]. This stub calls the main procedure (which resides in a standard module), passing the form object, keycode, and shift key status as arguments.
 

   Sub Form_KeyDown( KeyCode, Shift)

       ProcessArrowKeys Me, KeyCode, Shift

   End Sub

 
 
I've included the full code and an example form in the Download file, but I'll discuss some of the issues I discovered along the way.
 
1. It's important to check whether the Shift key is down when the <Up>/<Down> arrow keys are pressed. The <Shift-Arrow> keys permit block selection of consecutive characters, records, or columns, so it isn't very friendly if you remove this ability. Additionally, Alt-Down> can be used to drop a combo box.
 
2. I decided to ignore the keystroke trap for controls that have multi-lines (for example, memos); otherwise, it'll be difficult for the user to edit these controls. I started by assuming all such fields will either have the default EnterKeyBehavior modified to "next line in field" or the vertical scroll bar enabled. Just in case that's not granular enough, I provided an override for any control (for example, vertical Option Group) by specifying "[Ignore Arrows]" in the Tag property.
 

   '----------------------------------------

   ' check for control overrides

   ' 1) EnterKeyBehavior = NextLine in Field

   ' 2) ScrollBars = Vertical

   ' 3) Tag has [Ignore Arrows]

   ' exit if either condition met

   '----------------------------------------

   On Error Resume Next

   Set ctlActive = Screen.ActiveControl

   fOverride = (ctlActive.EnterKeyBehavior <> 0)

   If Not fOverride Then

       fOverride = (ctlActive.ScrollBars <> 0)

   End If

   If Not fOverride Then

       fOverride = (InStr(ctlActive.Tag, _

         "[Ignore Arrows]") > 0)

   End If

   If fOverride Then Exit Sub

 
 
3. The keystroke behavior is only modified when the form is in continuous form view. The CurrentView property can be used to distinguish between datasheet and form view. I've included a precautionary check to ensure that the form was set up as a continuous form by examining the DefaultView property.
 

   If pfrm.CurrentView <> cDataSheet And _

       pfrm.DefaultView <> cContinuous Then Exit Sub

 
 
4. The actual navigation to the previous or next record is performed and then the keystroke is cancelled from further processing by setting its value to zero. While pre-navigation via the form's underlying recordset (RecordsetClone) is possible, I think it's simpler in this case to just go to the next or previous record. Any error that's raised if you're already at the beginning or end of a file is just ignored.
 

   On Error Resume Next

   DoCmd.GoToRecord , , _

      If(KeyCode = vbKeyDown, acNext, acPrevious)

   KeyCode = 0

 
 
Sound like too much effort? Have a look at the example form frmArrowKeys in the accompanying Download file. This contains two subforms with one exhibiting default and the other modified navigation. You'll find the necessary one line of code in the modified subform <frmArrowKeys_SubModified> KeyPress event.
 
I want to write an Access 97 utility add-in that is an optional component of my application. I'd like to be able to run code from the library without the need to establish a reference to it. Is there any way to do that?
 
Library databases don't need to be referenced in order for procedures within them to be executed. Automation provides a means for procedures in Access databases to be executed from non-Access applications such as Word, Excel, etc. You can use this same technique to initiate a procedure in one Access database from another without the use of references.
 
The Online help details an example on using the method from non-Access applications:
 

Sub RunAccessSub()

Dim app as application

 ' Create instance of Access Application object.

 Set app = CreateObject("Access.Application.8")

 ' Open database in Microsoft Access window.

 app.OpenCurrentDatabase _

  "C:\My Documents\WizCode.mda", False

 ' Run procedure.

 app.Run "My Procedure", "My Argument"

 Set app = Nothing

End Sub

 
 
Fortunately, inter-Access automation is more concise. It uses the built-in application object to launch the Run method directly.
 

Application.Run "MyProcedure, _

'MyArgument'"

 
 
This statement will execute the procedure called "MyProcedure" and pass a string value of "MyArgument" to that procedure. With this particular shortcut syntax, the procedure needs to be exposed in either the current or referenced database. However, the Run method also provides an optional Project Name qualifier to eliminate ambiguity when more than one public procedure has the same name:
 

Application.Run "[MyProject].MyProcedure, _

   'MyArgument'"

 
 
It's this project qualifier that provides the solution to executing procedures from un-referenced databases. However, there are two conditions for the Library database that need to be satisfied before the procedure will be able to be located and executed:
 
1. The Project and Database names are identical.
2. The Database resides in the Access directory.
 
The technique is especially useful for executing procedures in a library database from a command bar button. Say, for example, that you had a menu add-in that created a command bar and then exited. The basic idea is that pressing one of the buttons executes some code in the add-in. Because the functions are not within a referenced database you'll need to use the Run function to initiate
the automation.
 

OnAction:=Run("[MyProject].MyProcedure, 'MyArgument'")

 
 
The Run method and function have one major drawback: No value is returned regardless of whether the initiated function provides one. If you need to pass values between the two databases, you might have to look at using files or tables to hold the results.
 
Why are there two window style options for the Debug window? I've been used to the "float on top" popup window behavior in Access 2.0, but if I use this style, the maximize button disappears. I like the drill-down feature of the watch pane, but I'm constantly resizing the Debug window to see more detail. Is there some way I can maximize it by running some code?
 
In the initial beta versions of Access 95, the Debug window was provided solely as a child window. It took a great deal of persistence from developers to convince the VBA team that while it may be okay for Excel to use a child debug window, it was an inferior option for Access. The main problems were interference with the Activate and Deactivate events during debugging and the inability to use the window when echo was off. Eventually, the VBA team relented and Access 95 came with a popup debug window. It would seem, though, that old habits die hard, and we now have an option for either a popup ("float on top") or child debug window in Access 97.
 
Logistically, using a child window style does provide some small benefit. The debug window will automatically "hide" behind other windows when they get focus and you can maximize the viewing area using the standard windows controls.
 
Personally, I prefer to use the popup style, but I augment this with a support routine to maximize the debug window when I want to see the "big picture." I just type the procedure name in the debug window and the code locates and maximizes the popup. To restore the debug window I double-click on the title bar and let Windows do the work.
 
The procedure executes two API calls. The first one locates the debug/immediate window by its class name, and the second call does the maximize. Even though the term "Immediate window" has been replaced with "Debug window," the windows class name (OImmediate) has remained constant throughout the various version changes. As a result, the only differences in the code between Access Basic and VBA are the declarations of the 16- and 32-bit API support functions.
 
I've included the full function declarations for both versions in the accompanying Download file.
 
Once you've defined the function calls and created the procedure, just type "MaxDebug" in the debug window.
 

Sub MaxDebug ()

Dim varRet As Variant

varRet = _

 api_ShowWindow(api_FindWindow("OImmediate", 0&), 3)

End Sub

 
 
In Access 97, how do you hide a form's menu bar -- get rid of it completely? If I try to set the visible property of the "Menu bar" commandbar to false, I get an Automation error.
 
I can't find any documentation, but through trial and error it would appear that you can't turn the "Menu Bar" commandbar's visible property off while it's the ActiveMenuBar.
 
However, you can switch the ActiveMenuBar to a custom one that you've created. When this happens, the default "Menu Bar" is automatically hidden so that only one menu (the custom menu that you created) is active. The custom menubar visible property can then be changed without the automation error being raised.
 
I've created two procedures to turn the menu bar on and off. Beforehand, I created an empty toolbar called "NoMenu" and set its Type property to Menu Bar.
 
The MenuOff procedure completely eliminates any menu bar from the Access window. The first step changes the MenuBar property to point to the empty commandbar. Since Access's default menu bar is no longer the active menu bar (the ActiveMenuBar property no longer points to Access's default menu), we can set its visible property to false.
 

Sub MenuOff()

 Application.MenuBar = "NoMenu"

 CommandBars("NoMenu").Visible = False

End Sub

 
 
The MenuOn procedure will restore either the default or your own application-specific menu. Changing the MenuBar property also resets the ActiveMenuBar property to suit. Using a zero-length string for the menu name restores the default menu.
 

Sub MenuOn()

 Application.MenuBar = ""

End Sub

 
 
I've got lots of ideas for code builder wizards that can use the new Modules object properties. But I want to be able to call them directly from my own button on the Module Shortcut menu. In other words, I don't want the extra step in drilling down through the Build... list, but rather just have a single click or hotkey execute. I can add the button easily enough, but how can I tell which module and procedure is active as these arguments only seem to be passed when a code builder is initiated by Access?
 
The name of the active module can be retrieved using the CurrentObjectName property. However, getting the active procedure name requires a succession of API calls and a bit of inside knowledge on how event procedures are named. The aim is to locate the two combo boxes that are present in the module window, extract their contents, and then format the text into a procedure name.
 
Controls such as combos are windows in their own right. The two combos in question exist as child windows within their parent Module window. It, in turn, is a child window of the MDI Client window (see Figure 1). This parent window hierarchy exists from the very top Main Access window. You can get to the top of the tree (the main Access window) by retrieving its address (handle) from the hWndAccessApp property.

199710_cb1
Figure 1
 
Having established the handle of the Main Access window, you must then traverse all of the child windows until you locate the MDI Client window. The window class "MDIClient" identifies the MDI Client window.
 

   ' set handle to main Access window

   lnghWnd = Application.hWndAccessApp

   ' locate the MDI Client

   lnghWnd = sa_LocateChildWindow(lnghWnd, _

 "MDIClient", cParentHwnd)

 
 
Once you have the MDI Client window located, go down a level and look for the first child module window. The window class "OModule" identifies module windows. You only need to find the first module window. The order that windows appear in the windows child list varies as each window is brought forward. At all times, the module window that last had the focus will be the first one encountered in the list. And that's the one you want.
 

   ' locate the first module window

   lnghWnd = sa_LocateChildWindow(lnghWnd, _

 "OModule", cParentHwnd)

 
 
Once you've located the active module window, you must find the two child windows of the combobox class ("ComboBox") and then extract the text from each. The first and second combo boxes relate to the left- and right-hand side combos, respectively.
 

   ' locate the first combobox

   lnghWnd = sa_LocateChildWindow(lnghWnd, _

 "ComboBox", cParentHwnd)

   ' extract first combobox text

   strFirstCombo = String(144, Chr(0))

   lngTextLen = api_GetWindowText(lnghWnd, _

 strFirstCombo, 144)

   strFirstCombo = Left(strFirstCombo, lngTextLen)

   ' locate the second combobox

   lnghWnd = sa_LocateChildWindow(lnghWnd, _

 "ComboBox", cChildHwnd)

   ' extract second combobox text

   strSecondCombo = String(144, Chr(0))

   lngTextLen = api_GetWindowText(lnghWnd, _

 strSecondCombo, 144)

   strSecondCombo = Left(strSecondCombo, lngTextLen)

 
 
The final step builds a procedure name from the extracted combobox text. When an event procedure is active, the first combo will contain the name of either a control or the word "Form." The contents of this combo can have an underscore and the second combobox's text (which contains the event name) appended. If the first combo contains the literal "(General)," then only the second combo text is used to return the procedure name.
 

   ' Build the procedure name

   If strFirstCombo = "(General)" Then

       sa_GetActiveProcName = strSecondCombo

   Else

       sa_GetActiveProcName = strFirstCombo &_

         "_" & strSecondCombo

   End If

 
 
Now you have all the necessary tools to generate a "stand-alone" code builder that can be executed from any commandbar button or hotkey sequence.
 
In Access 97, when I change the SourceObject of a subform, all the records are returned. This is having disastrous results when users inadvertently update the wrong child record. This used to work fine in V2 and 95! Has anyone else come across this before?
 
This is a bug that has recently surfaced in Access 97. When the SourceObject of a subform is modified at run-time, the Master and Child Links are ignored and the entire recordset is presented for the subform (unfiltered).
 
The problem only seems to occur where the subform:
 
1. has a table or SQL string as its recordsource (saved queries seem okay)
2. has undergone some form of redesign after the recordsource property was last updated.
 
At present, the work-around I'm using is to change any direct table recordsources to equivalent saved queries and including the links from the main form to the subform as criteria within the query or SQL string.
 
Field: MyMasterId
Criteria: Forms!MyMainForm!MyMasterId
 
I have a library database that resides in the same directory as the application. When I move the databases to a new PC, it complains that it can't find a function. I expected to see a missing reference, and yet when I look at them they all appear okay and none are missing. If I just reset the reference manually to the library mdb, then everything works okay. What's going on here?
 
The Access project relocation seems to get stuck in "limbo" when a library database resides in the same directory as the current project database. The reference(s) are modified to point to the new directory location, but you can't execute any functions from that library.
 
The work-around is to move the library database to any of three locations:
 
1. The Access directory
2. The Windows directory
3. The Default Database Folder specified in the General Options
 
Download Q&A9710.EXE
 
Chris Bell is an independent Access consultant and trainer operating from Sydney, Australia. He's been a Microsoft Access MVP for the past two years and enjoys the variety and challenge that the online newsgroups and forums provide.