Navigation:  Articles > Nov-1997 >

Access MVP Solutions to Your Biggest Problems

Previous pageReturn to chapter overviewNext page

Andy Baron, Jim Ferguson, Joe Maki, and John Viescas          
Every day, the MVPs (Most Valuable Professionals) volunteer their efforts responding to questions posted on the Microsoft newsgroups. We've contacted some of these experts and asked them to dig into their extensive Q&A archive and select the ones they've handled that are of the most interest to professional developers. As an added bonus, we've included much of the code involved as well as demo programs and other helpful information in download file MVP9710.EXE. In this and forthcoming issues, we'll present the selections of different MVPs. This month's items were compiled by Chris Bell.


199711_mvp1Figure 1

199711_mvp2 Figure 2

199711_mvp3 Figure 3

199711_mvp4 Figure 4

199711_mvp5 Figure 5

199711_mvp6 Figure 6

199711_mvp7 Figure 7


Andy Baron
I have a subform that shows a small number of records by default when the form opens. I want to allow the user to expand or maximize the window and see as many subform records as can fit on the adjusted window. I've tried changing the subform height to match that of the detail section, but nothing happens.
The detail section height doesn't change as a user resizes a form, so referencing the height of the detail section won't help you. However, you can retrieve the resized window height of the main form by using the new InsideHeight property. This measurement is provided in twips and excludes any active scroll bar and navigation buttons.
As you realized, visible form headers and footers do need to be deducted to derive the usable window space your subform can occupy. You'll also need to deduct the relative vertical position of the subform (the Top property) to allow for situations where a subform isn't at the very top of the detail section. And, finally, you need to avoid trying to set the subform height to a negative value.
The following ResizeControl() function does all this. You'd call it from the form's resize event by making the following entry in the On Resize property:


The function is passed two objects as arguments. The first is the current form object from which the resized window measurements are taken. The second is the control that is to be resized. While the above example uses a subform control, you could just as easily pass a listbox or textbox containing a memo field.
Download file MVP9710.EXE provides an example form <frmAB_Resize_Main> that contains a subform being resized to fit. The function is located in a standard module called <basAB_Resize>. Figure 1 shows the results of calling this function on a subform.

Function ResizeControl(frm As Form, _

                      ctl As Control)

Dim intAdjust As Integer

 On Error Resume Next ' may be no hdr and/or ftr

 intAdjust = frm.Section(acHeader).Height * _


 intAdjust = intAdjust + _

             frm.Section(acFooter).Height * _


 On Error GoTo 0

 intAdjust = Abs(intAdjust) + ctl.Top

 If intAdjust < frm.InsideHeight Then

   ctl.Height = frm.InsideHeight - intAdjust

 End If

End Function

Note:While the InsideHeight property works as expected, the companion InsideWidth property doesn't conform to the description in the online Help. The InsideWidth is adjusted for a vertical scrollbar, but record selectors are ignored. You'll need to adjust the value manually if you want to resize horizontally and a record selector is present.
I've got an integer [STATUS] field I'm using for bit operations. When a document is received, bit 1 is turned on; when the document is acknowledged, bit 2 turns on; and so on. I wrote a query and have a calculated field, BitScreen:[STATUS] AND 4 to retrieve bit 3 (0100), but all I'm getting is -1' s in the query result, even when that third bit isn't set. Any suggestions?
Rather than performing a bitwise AND operation, the query is doing a logical AND on the two values. Since any non-zero number is treated as True, the logical expression (1 AND 4) equates to (True AND True). The value of such an expression will always be True when the [STATUS] field contains any non-zero value. That's why the query is always returning the True value (-1).
To get the bitmath result that you want, create a function:

Function BitResult(lngInput1 As Long, _

                  lngInput2 As Long) As Long

 BitResult = lngInput1 And lngInput2

End Function

Then, in your query, use this expression:

BitScreen:BitResult([STATUS], 4)

Using a custom function such as the previous example is fairly straightforward, but it might not be available to you if you upsize to using a database server as your back end. You can achieve your bitmath result directly from the query without using any code whatsoever. This technique is composed of two parts, and their interaction emulates the way the bitwise AND operator works.
The first part is based on the fact that you're only interested in values of bits up to and including the one you're seeking. For example; if you want to know whether the third bit is on (value 4), then you don't care whether the higher order bits (4th, 5th, and so on) are on or not. The higher order digits can be "stripped" from the value using the MOD operator, leaving a remainder of the first 3 bit values. This remainder can then be used in the second part of the solution.
The second part requires a criteria clause to filter desired bit matches. The stripped-down remainder needs to be equal to or greater than 2 raised to the power of the bit position less 1. For example, if you want to know whether the third bit is on, then only "stripped down" values equal to or greater than 2^2 (3rd bit position minus 1) can have that bit on.
Assuming the rightmost bit position starts at 1 (rather than zero), here's the criteria you can use directly in a query:

Field: [Status] MOD (2^[BitPosition])

Criteria: >= 2^([BitPosition]-1)

The following SQL searches for matches on bit positions 2 and 3:

SELECT MyTable.Status FROM MyTable

WHERE ((([Status] Mod 2^2)>=2^(2-1)) AND

      (([Status] Mod 2^3)>=2^(3-1)));

Download file MVP9710.EXE provides an example query <qryAB_BitMath> that prompts for a bit position to match against a table with eight records numbered 1 through 8. For example, entering a bit position of 3 (bitvalue of 4) would select records 4 (3rd bit on), 5 (3rd and 1st bit on), 6 (3rd and 2nd bit on) and 7 (3rd, 2nd, and 1st bits on). See Figure 2 for an example of this process.
Jim Ferguson
Can anyone tell me whether WithEvents works as advertised in VBA, or do I need the full VB environment?
Access 97 can "consume" objects that raise events, but it can't create events of its own as VB 5.0 can. For example, if you wanted to intercept some Excel events from an Access form, you'd go through the following steps.
First, create a reference to Excel by opening a module window, selecting Tools | References from the menu and then selecting the "Microsoft Excel 8.0 Object Library" reference. (You must create a reference in order to use early binding, rather than using CreateObject, because Access must determine the types of events that the object exposes.)
Then, in a form module or class module where you wish to receive the events that Excel raises, declare an object variable of the type Excel.Application using the WithEvents keyword:

Private WithEvents xlobj As Excel.Application


Sub Form_Open(Cancel As Integer)

 Set xlobj = New Excel.Application

 xlobj.Visible = True

End Sub

Now, open the form's module in design mode and look for an item called "xlobj" in the drop-down list at the top of the module window. When you select it, the drop-down list on the right-hand side of the window will display all the events that Excel can raise in your application. There are events for NewWorkBook, SheetActivate, SheetCalculate, and so forth. See Figure 3 for a schematic of the relationships between references, declarations, and modules.
To respond to these events, simply place code in the subroutine for that event, such as the following code:

Sub xlobj_NewWorkbook _

   (ByVal Wb As Excel.Workbook)

 MsgBox "New workbook created: " & Wb.Name

End Sub

Is it possible to import Visual Basic 4.0 forms into Access 95 or 97 (for example, the calculator)?
You certainly can't convert a VB 4.0 form into an Access form. However, one cool thing you can do with VB4 is to create OLE DLLs that can act as in-process servers. These are very fast compared to executables (EXEs), which run as out-of-process servers.
In your case, you could turn a VB4 "calculator" form into an object that you could create and instantiate from your Access form. Hypothetically, the Access code would look something like this:

dim c as new VBCalculator

dim dblAnswer as Double

 'Calculator displays modal dialog,

 '  and then hides when user exits it

 dblAnswer = VBCalculator.Answer

 set c = nothing

There are restrictions that apply to in-process servers; one is that you can only display modal forms. Before you jump in, have a look at the other restrictions in the VB online help.
I'm trying to add a UserDefined type (UDT) to a collection object and I keep getting a ByRef error on the add method. Do I have to do something else?
Unfortunately, you can't use UDTs with collections. You can store variants, specific values, or object references, but not UDTs.
Rather than storing UDTs, you might want to create another CLASS and store object instances of that class in the collection. The properties of the class are used like the members of the UDT. [You'll find a comprehensive description on how to do this in Jim's March 1997 Smart Access article "Use Classes to Enhance Access 97's List and Combo Boxes." -- Chris Bell.]
I'm sure the SQL string I'm using is okay, but the report doesn't use the Order By I'm trying to set.
Access reports never use the ordering sequence specified in the query. It doesn't matter if you set the recordsource at runtime or not. You need to change the ControlSource property of the appropriate grouplevel() of the report.
In the following example, an option group on a report selection form is referenced to obtain the desired sort sequence, and the grouplevel() property is changed accordingly.

Sub Report_Open (Cancel As Integer)

Dim frm as form

 Set frm = forms!frmReportSelection

 Select Case frm!opgSortOrder

   Case 1 'Country

     Me.GroupLevel(0).ControlSource = "Country"

   Case 2 'Last Name

     Me.GroupLevel(0).ControlSource = "LastName"

 End Select

End Sub

Joe Maki
I'm using Access 7.0. Is it possible to highlight only the control you're on in the detail section of a continuous form subform?
It's not a perfect solution, but if you set the textbox or combobox BackStyle property to transparent, then when it receives the focus, the original BackColor property kicks in and gives a "highlight" effect. When the control loses the focus, the BackStyle reverts to transparent.
If you're modifying the properties via the User Interface, then the order the properties are set in is crucial. You need to set the BackColor to your highlight color first, then change the BackStyle to Transparent. Unfortunately, the highlight effect doesn't operate in Datasheet view and isn't officially supported by Microsoft. It works in Access 95 and 97, but it might not in future versions. Only time will tell if this is a bug or a feature.
In download file MVP9710.EXE, you'll find an example form/subform <frmJM_Highlight> that uses this effect.
My users occasionally add reports to one of my applications. It seems that if they add any code in an event procedure, the project becomes decompiled, and subsequent performance starts to suffer. Is there any way I can detect this change to a decompiled state and compile it automatically the next time they start the application? I'm using Access 97.
In Access 97, there's a new and welcome addition to the application properties called "IsCompiled." If this property is False, you know that the project has fallen into a decompiled state. The only method of recompiling a project is to use the SaveAndCompileAllModules menu option, and this option is only available when a module window has the focus. You can use the following method to ensure that the application is compiled and saved at Startup.
First, you need to create at least one module in the application. It doesn't matter what you call it, but if you prefix its name with Usys, the module won't be visible in the database window. It's only a stub module and doesn't need any actual code in it.
Next, you need to run the following function from your AutoExec macro. The function checks the compile state and, if necessary, opens the specified module in design view. This enables the module window menu, and the SaveAndCompileAllModules menu command can then be executed. Once the compilation is finished, the module window closes. An example of the RunCode action would be as follows:


Function Name: CompileCheck("USys_CompileStub")


Function CompileCheck(pstrModule as string)

 On Error GoTo Error_Handler

 If Application.IsCompiled Then

     Exit Function

 End If

 If SysCmd(acSysCmdRuntime) Then

   MsgBox "This application has lost its " & _

          "compile state.@" & _

          "Performance will suffer " & _

          "until corrected." & _

          "@Please notify the administrator.", _

          vbInformation, "Startup Check"

   Exit Function

 End If

 MsgBox "This project is being re-compiled " & _

      "to improve performance.@" & _

      "If this message continues to appear " & _

      "and no changes have been made " & _

      "to the application " & _

      "please contact your administrator.@", _

      vbInformation, "Startup Check"

 DoCmd.OpenModule pstrModule

 DoCmd.RunCommand acCmdCompileAndSaveAllModules

 DoCmd.Close acModule, pstrModule


 Exit Function


 MsgBox Err.Number & ":" & Err.Description

 Resume Exit_Point

End Function

Figure 4 shows the message box that this function displays before recompiling.
Note: There's one drawback with this technique: It can't be used in the runtime or against MDE files. Projects deployed for runtime execution must be compiled beforehand. If you're using security, then you'll need to ensure the module has sufficient permissions to allow users to open it in design view.
In download file MVP9710.EXE, you'll find the code and a sample autoexec macro that calls the function. We've even included the optional stub module <USys_CompileStub>.
I'm using Access 2.0 and trying to drop a combobox when it gets focus. I'm using Sendkeys "{F4}", which works fine most of the time. However, when the user clicks directly on the drop-down arrow rather than in the textbox portion, the list drops and closes again. Is there some way to avoid this?
Unfortunately, the only way to drop a combo list programmatically in Access 2.0 is to use SendKeys. Later versions provide a more reliable and robust DropDown method for this purpose.
Clicking the drop-down arrow or typing F4 or Alt-Down Arrow simply toggles the drop-down list state. If it's closed, it will be opened, and if it's open, it will be closed. When a user clicks on the drop-down arrow, the list is dropped by the SendKeys action in the Enter event and then ends up being closed by the drop-down arrow click.
The workaround is to place a transparent command button over the drop-down arrow portion of the combo. This will float over the drop-down arrow, preventing it from receiving the mouse click. The command button click event simply redirects focus to the combobox, which then uses the SendKeys statement to drop the list. Because the drop-down arrow wasn't actually clicked, the list remains open. Once the combo receives the focus, the command button will be sent "behind" the combo, and subsequent clicks on the drop-down arrow will operate normally. Figure 5 shows the states this button goes through.
Make sure the transparent command button has its TabStop property set to False. You only want this control to receive the focus if the user clicks on it with the mouse, not by tabbing around the form.
You can set focus explicitly to the combo in the command button's click event, but this solution requires the coding of a special event procedure for every transparent command button. Rather than add that overhead to every combo, it's preferable to call a generic function directly from the property sheet. If you put this common function in a standard module, it can be used by any combo on any form.

OnClick: =MyFunction()

There are two approaches you can use for this generic function:
1. Place the transparent command button as the next control in the Tab Order, immediately following the combo box. To set focus to the combo, it's a simple matter of using SendKeys to issue Shift-Tab to "back up" one control in the tab order.

  Function MyFunction()

     SendKeys "+{TAB}"

   End Sub

2. Create a naming convention for your transparent command button so that the name of the associated combo is embedded into its control name. For example, the name of the combo could be cboDemo, and the name of the transparent command button could be btnCovercboDemo. Using the Screen.ActiveControl object, you can parse out the name of the combo (everything after btnCover) and set focus to it directly. For this technique, you'll need to pass the active form object to the function as an argument:

OnClick: =MyFunction([Form])


 Function MyFunction (frm As Form)

 Dim strCombo As String

         strCombo = Screen.ActiveControl.Name

           strCombo = Mid$(strCombo, 9)


 End Function

When moving controls around in design view, remember to select both the combo and command button. Otherwise, you'll be constantly realigning the command button over the drop-down arrow.
You'll find an example form <frmJM_Combo> with a sample combo for each of the various solutions in download file MVP9710.EXE.
John Viescas
I'm opening an Informix database with the OpenDatabase method in conjunction with the ODBC connect. I've tried using a simple Select statement: "SELECT fullname (emp_fname, emp_lname) FROM employee; " where fullname is a stored procedure. I keep getting the error message: "undefined function `fullname' in expression." It looks as if it can't find the stored procedure (fullname). What am I missing?
Even though you've established a direct database object connection to Informix, you still have to run your query via either a querydef or tabledef that has its connect property set correctly.
Use the following code to do it with a querydef:

Dim dbJet as Database

Dim qdf As QueryDef

Dim rst As Recordset


 Set dbJet = CurrentDB()

 ' Create a temporary querydef

 Set qdf = dbJet.CreateQueryDef("")

 With qdf

   .Connect = "ODBC;DSN= ..."

   .SQL = "<SQL with stored procedure >"

 End With

 Set rst = qd.OpenRecordset(,dbSQLPassThrough)

If you have version 8, you can create a non-jet workspace and a Connection object to use ODBCDirect:

Dim wsODBC As WorkSpace

Dim cnPubs As Connection

 Set wsODBC = CreateWorkspace("NewODBCWS", _

         "admin", "", dbUseODBC)

 Set cnPub = wsODBC.OpenConnection("Connect1", _

         dbDriverNoPrompt, , _                


 cnPub.Execute "<SQL with stored procedure>", _


Given a list of job applicants and their skills as well as a list of available jobs and the skills required for each job, how do I find out which applicants have all the skills required for any given job?
This is an interesting example of how to use subqueries to filter the main or outer query. Let's assume the Skills database looks something like the database in Figure 6.
The query to list all candidates and the jobs for which they qualify is:

SELECT tblCandidates.CandidateName,


FROM tblCandidates, tblJobs

WHERE tblCandidates.CandidateID IN

(SELECT tblCandidateSkills.CandidateID

  FROM tblCandidateSkills INNER JOIN tblJobSkills

   ON tblCandidateSkills.SkillID =


  WHERE tblJobSkills.JobID = tblJobs.JobID

    AND tblCandidateSkills.CandidateId =


  GROUP BY tblCandidateSkills.CandidateID

  HAVING Count(tblCandidateSkills.SkillID) =

  (SELECT Count(J2.SkillID)

    FROM tblJobSkills As J2

    WHERE J2.JobID = tblJobs.JobID));

That's a fairly complex SQL statement to take in all at once, so let's take it apart piece by piece. Figure 7 shows the relationships between the main query and the subqueries.
The main part of the query returns a list of all candidates paired with all jobs by using a Cartesian product (no JOIN clause in the FROM specification).This provides us with every possible combination of candidate and job:

SELECT tblCandidates.CandidateName,


FROM tblCandidates, tblJobs

The next Select statements are in correlated subqueries that restrict the output rowset to those where the count of matching job skills for the candidate exactly matches the count of skills required for the job. The subqueries are termed correlated because data (CandidateId and JobId) from the outer query (the Cartesian product) is being used to drive the subqueries.
To find out which candidates have any of the skills, a simple INNER JOIN subquery will do:

(SELECT tblCandidateSkills.CandidateID

  FROM tblCandidateSkills INNER JOIN tblJobSkills

   ON tblCandidateSkills.SkillID =


  WHERE tblJobSkills.JobID = tblJobs.JobID

    AND tblCandidateSkills.CandidateId =


The trick is to turn that into a Totals subquery by adding a GROUP BY clause on the Candidate ID so that you can add a HAVING clause to identify the candidates who have all the skills. The HAVING clause uses a second subquery to obtain the total count of skills for the current job:

(SELECT tblCandidateSkills.CandidateID

.  As above

 GROUP BY tblCandidateSkills.CandidateID

  HAVING Count(tblCandidateSkills.SkillID) =

  (SELECT Count(J2.SkillID)

    FROM tblJobSkills As J2

    WHERE J2.JobID = tblJobs.JobID));

This technique of comparing counts works because we know from the table design that no candidate has any duplicate skill listings, and no job has any duplicates, either. You should note that because this uses not one but two correlated subqueries, it'll probably run pretty slowly for all candidates and jobs. It should run reasonably fast if you look for the qualified candidates for only one job at a time by adding a filter on JobID to the outer WHERE clause.
I've included a copy of the Skills database and query in download file MVP9710.EXE. The query is called qryJV_SkillMatch.
Read about how to get the download MVP9710.EXE   on this page