This month, Andy addresses some Access 97 quirks and how to work around them; he dredges up an old error-handling technique from VBA's deep, dark past; and he shows you a couple of cool tricks you can use with combo boxes.
I have an Access 97 subform that acts in a very irritating way. When the user clicks on a record, that row jumps to the top of the form, hiding all the rows above it. This doesn't happen on all my subforms. Is there any way to stop it?
Angela J.R. Jones answered a similar question in the July 1998 Access Answers column ("Right-Clicks, Subforms, and Numbers"). This is, however, just one of several problems in Access 97 forms that seem to be related to problems with the Link Child Fields and Link Master Fields properties that join a form to its subform. For example, if you change the source object of a subform, this synchronization is ignored and all records in the subform record source are shown. I've even seen subforms that weren't even updateable until the link fields were eliminated.
If you have one of these other problems (or if Angela's elegant solution doesn't fit your needs), you can try removing the link fields altogether. If you do, the question then becomes, "What must you do to replace the functionality that the link fields provided?"
When you set the link child and link master fields properties for a subform, there are three functions that Access performs for you automatically:
1. It filters the subform to display only matching records.
2. It fills in the link field values when you save a new record.
3. It requeries the subform when a link master field changes.
Fortunately, each of these is relatively easy to achieve without relying on the link field property settings.
To filter the subform, you simply need to assign it a recordsource query with a Where clause that uses what would have been the link child and master fields. For example, an order details subform query might have a Where clause of "Where OrderID = Forms!frmOrders!OrderID." Notice that the reference to the link master field is to the value on the parent form.
To ensure that the values from what would have been the link master fields get filled in for the corresponding child fields on new records, use the BeforeUpdate event procedure of the subform. In the order details subform that I used in my previous example, the procedure would look like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!OrderID) Then
Me!OrderID = [Forms]![frmOrders]![OrderID]
To keep the subform in sync with the main form, requery the subform when any changes occur to the fields or controls on the main form that would have been the link master fields. On a bound main form, this also means that you need to perform a requery of the subform in the Current event procedure of the main form. If one subform is being used to provide values for a second subform, then your requery code also needs to go in the Current event of the first subform. If the would-be link master field is an unbound control on the main form, then you need to perform a requery of the subform in the AfterUpdate event procedure of the control. In all these cases, the one line of code that you need is the same:
By handling those three tasks, you can eliminate any problems caused by link child/master fields -- just leave those properties blank and "roll your own." The sample application available in the accompanying Download file includes a form, frmEmployeeAssignments, that has a subform that displays the problem behavior you described in fsubEmployees. A second form, frmEmployeeAssignments2, solves the problem by eliminating the link child and link master fields and shows how to replace their functionality by using the techniques I've described.
I'd like my error handler to report the line or at least the section of code that contained the error. Is there anything in VBA that would help me do that?
You could change the value of a variable each time you enter a new section of code and then pass that variable to the error handler, but VBA does have a seldom-used feature that you might prefer using. Back in the old days of Basic, all lines of code were numbered, and you could use the ERL function to get the number of the line that caused an error. Well, even though it doesn't appear in the Help file or the object browser, ERL lives!
The catch is that ERL will only return a line number if you put those line numbers in yourself. Otherwise, it just returns 0. So, if you want line-level granularity, you can put an integer followed by a colon (for example, "10:") at the start of each line of code and use ERL to get the line number. But you don't have to go to that extreme. ERL actually gives you the number of the last numbered line that executed when the error occurred, so you could just number the line that begins a section of code and use ERL to find out which section you're in.
I've got a situation where my Access 97 application won't save the compile state, no matter what I do. I can CompileAndSave the modules, and the IsCompiled property changes from False to True, but the menu option continues to show as enabled. If I close and then re-open the database, the compiled state seems to have returned to False, and all three menu compile options are enabled. I've compacted, repaired, and prayed, all to no avail. Am I missing something?
There's a chance that you have a compile error somewhere that's not being reported when you do a Compile and Save All Modules (that's been known to happen). The first thing to do is to try individually compiling your modules to see if you find such an error.
If that doesn't work, there's an undocumented command line switch that will probably fix your database, and that's helpful in handling a number of intractable VBA problems. Using a shortcut file or the Windows Start menu's Run dialog box, open your database with a command line that ends with /Decompile. For example, the full command to open a database called Nwind might look like this (all on one line):
This will cause your VBA project to be completely decompiled when the database opens. You'll get a message box telling you that your code has been converted to the current version of Visual Basic. The message will also advise you to open a module in design view and select Compile and Save All Modules from the Debug menu. I've heard that it's safest to close the database first in this uncompiled state, then re-open it without using the /Decompile switch, and only then do the recompile. There's a very good chance that this will give you a more stable compiled state, and it's also possible that your mdb file will get 30 - 40 percent smaller!
This /Decompile option was apparently created to allow Microsoft's own internal developers to upgrade projects to the latest version of VBA, and the technique isn't supported for use by the general public. However, I've never known it to cause any problems, and it definitely can solve some.
I have an Access 97 database that won't close. When I try to close it, Access just minimizes to the task bar. I heard that I could fix this by being sure to set all my DAO object variables to Nothing at the end of my procedures, and I did that, but the problem is still there. Even creating a new database and importing everything into it won't help. Is there anything else I should look for?
This problem is, unfortunately, pretty common. As you mentioned, stray DAO object references have been blamed for it -- see Michael Kaplan's article in the August 1998 issue on the PrivDBEngine object ("The PrivDBEngine Object") for a DAO solution. DAO is definitely not all that can cause this problem. Here's a simple scenario that could be the cause of your problem:
1. Create a new mdb.
2. Create a table with one text field, Field1.
3. Create a form, Form1, with a check box, Check1.
4. Give Check1 a default value of 0. (This isn't crucial, but it avoids an Invalid Use of Null error later.)
5. Create a second form, Form2, with a text box, Text1, bound to Field1.
6. Put the following code behind the text box:
Private Sub Text1_BeforeUpdate(Cancel As Integer)
If Me.Parent!Check1 Then
Cancel = True
7. Save and close Form2. It doesn't matter if you explicitly compile the code or not.
8. Drag Form2 from the database container window and onto Form1 to create a subform.
9. Save and close Form1.
10. Run Form1, enter text in Text1, and press Tab.
11. Close the form and the database.
12. Try to close Access. It remains open, minimized.
You can fix this in a number of different ways. Here are the ones I know:
|•||If you replace 'Me.parent' with 'Forms!Form1', Access closes.|
|•||If you replace 'Cancel = True' with 'MsgBox "Checked"', Access closes.|
|•||If you replace 'Me.Parent!Check1' with 'Me.Parent!Check1=True', Access closes.|
|•||If you replace 'Me.Parent!Check1' with '(Me.Parent!Check1)', Access closes.|
As you can see from this scenario, DAO isn't the only culprit. The underlying problem is complex, but, in this case, the crux of it seems to be that there's something in the evaluation of certain Boolean (True/False) expressions that's causing this weird behavior. This particular Boolean expression involves a check box on a parent form, but that's not always the case. To be safe, do a search on "If" and make sure that all your Boolean expressions are enclosed in parentheses or end with "= True".
In my application, the users can have many different forms open at once. If they add new records to a table in one form, how can I make sure that all the combo boxes based on that table get requeried so that the new item will show up? Do I have to keep track of all those combo box names and which form they're on to requery them individually?
It's true that once a form is open, the combo boxes on the form contain static snapshots that won't reflect changes to the database. So, it would seem that the only way to keep them current is to individually requery each one. But I recently discovered an undocumented Access feature that makes this task a whole lot easier.
If you refresh a form using the Refresh method, all the combo boxes on the form are automatically requeried. This isn't the documented behavior for the Refresh method. According to the Help file, Forms!FormName.Refresh should do no more than update the records in the underlying record source of the form (which happens anyway every minute or so, depending on what your refresh interval is set to). But, in fact, calling the Refresh method of a form has the added effect of requerying any combo or list boxes on that form. So all you need to do is Refresh the open forms, and you don't need to requery the individual combo boxes.
I'm using the NotInList event procedure of a combo box to bring up a dialog form that allows the user to add a new item. When I open the dialog form, I use the OpenArgs parameter to send in the NewData value that the user had entered in the combo box, and in the load event of the dialog form I put that data into the appropriate control on the form. But what if the user edits that new entry on the dialog form to be something different from what they originally typed into the combo box? The new value gets added to the list, but the old value is what's left showing in the combo box. How can I get the new value not only to be added to the list, but also to appear in the combo box?
The combo box has a text property that you can set as long as the combo box is the active control, which it is when your NotInList code is running. So, you can get the new value into the combo box by setting its Text property. That's the easy part.
The tricky part is that when you set the Text property of the combo box from within the NotInList event procedure, it will recursively trigger the NotInList event to start running again, before the first run of the procedure has finished. This is a problem, because the combo box is only requeried after the NotInList procedure has ended (assuming you set the Response property to acDataErrAdded). You can very easily get stuck in an infinite loop that prevents the procedure from ever ending.
You can avoid this problem by using a static variable in the procedure (a static variable will retain its value through multiple runs of the procedure). By setting and checking the value of that static variable, you can tell whether the procedure is being run recursively for the second time. If the procedure is on its second time around, you can just set Response to acDataErrContinue and move on.
In the sample application, in frmOfficeAssignments, I've implemented this technique for cboBuilding. If you type in a new building name, you're given a chance to add the new building to the database using a dialog form. Even if you change the name of the building on the form, the correct new value ends up in the combo box. Here's the code:
Private Sub cboBuilding_NotInList( _
NewData As String, Response As Integer)
On Error GoTo Handle_Err
'Use a static variable to handle
' cases where the procedure is
' called recursively from within itself.
Static slngResponse As Long
'Allow new items to be added, using
' a dialog form.
If slngResponse = 0 Then
If MsgBox("Do you want to add " _
& NewData & " as a new building?", _
NewData & " is not in the database") _
= vbYes Then
'Open dialog form for data entry.
'Assume form has:
' code to handle OpenArgs,
' cycle set to CurrentRecord,
' and code to hide/close based on OK/Cancel.
DoCmd.OpenForm "fdlgNewBuilding", _
If SysCmd(acSysCmdGetObjectState, _
acForm, "fdlgNewBuilding") _
= acObjStateOpen Then
'The user clicked OK in the dialog form.
'Set NewData to
' the actual new value entered
' (in case the user changed it)
NewData = _
Response = acDataErrAdded
DoCmd.Close acForm, "fdlgNewBuilding"
'The user Canceled data entry
Response = acDataErrContinue
'The user chose not to add new data
Response = acDataErrContinue
'set the static variable
slngResponse = Response
'NewData may now hold a different value,
' if the user edited it on the form.
'Recursively trigger the procedure again.
Me!cboBuilding.Text = NewData
'Prevent an extra NotInList message.
'When the code gets here, the
' combo will already have the
' new data.
Response = acDataErrContinue
'Second time through the procedure
Response = slngResponse
'The user added data, so
' tab to the next control.
'Using setfocus here causes errors,
' but Sendkeys works fine.
'If you don't want to tab,
' then just raise the combo list:
slngResponse = 0
Read about the download SA9809AA.ZIP on this page