Question Access 2010 VBA Code Doesn't Work with 2013

[NoSmoke]

Posted this on Mr Excel/Access forum a while ago with no response so hoping someone here can help.

My Access 2010 developed database has a problem when a particular form is switched to Design View from Form View when running under Access 2013. I get an "MS Access has Stopped Working" error message and Access closes & restarts. I have narrowed it down to some VBA code, part of which, if eliminated, removes the problem. Everything else in the dbase appears to work. The entire module is:

Option Compare Database
Option Explicit
Private WithEvents cTextBox As TextBox
Private WithEvents cComboBox As ComboBox
Private colFrmControls As Collection
Public Sub CFInitialize(Frm As Form)
Dim c As Control
Dim cCF As clsControlFocus
    Set colFrmControls = New Collection
    
    For Each c In Frm.Controls
        Select Case c.ControlType
            Case acTextBox, acComboBox
                    If c.Enabled And c.Visible Then
                         Set cCF = New clsControlFocus
                         Set cCF.ControlSetting = c
                              colFrmControls.Add cCF
                     End If
            End Select
     Next c
     
End Sub
Public Property Set ControlSetting(ByVal c As Control)
        
        Select Case c.ControlType
            Case acTextBox
                Set cTextBox = c
                    cTextBox.OnEnter = "[Event Procedure]"
                    cTextBox.OnLostFocus = "[Event Procedure]"
            Case acComboBox
                Set cComboBox = c
                    cComboBox.OnEnter = "[Event Procedure]"
                    cComboBox.OnLostFocus = "[Event Procedure]"
        End Select
        
End Property
Private Sub cTextBox_Enter()
     OnEnterSettingsTextBox cTextBox
End Sub
Private Sub cTextBox_LostFocus()
    OnLostFocusSettingsTextBox cTextBox
End Sub
Private Sub cComboBox_Enter()
     OnEnterSettingsComboBox cComboBox
End Sub
Private Sub cComboBox_LostFocus()
    OnLostFocusSettingsComboBox cComboBox
End Sub
Private Sub OnEnterSettingsTextBox(c As Control)
        With c
            .BackColor = RGB(255, 255, 255) 'while
            .ForeColor = RGB(0, 0, 0) 'black
        End With
End Sub
Private Sub OnLostFocusSettingsTextBox(c As Control)
        With c
            
            .BackColor = RGB(214, 223, 236) 'bluish
            .ForeColor = RGB(0, 0, 0)   'black
        End With
End Sub
Private Sub OnEnterSettingsComboBox(c As Control)
        With c
            .BackColor = RGB(255, 255, 255) 'white
            .ForeColor = RGB(0, 0, 0) 'black
        End With
End Sub
Private Sub OnLostFocusSettingsComboBox(c As Control)
        With c
            .BackColor = RGB(214, 223, 236) 'bluish
            .ForeColor = RGB(0, 0, 0)   'black
        End With
End Sub

By trial and error, I have found that commenting out the "Set cTextBox = c" and "Set cComboBox = C" statements removes the problem (I then get a VBA execution error message, not surprisingly I guess, but the Access Stopped Working error is gone).

I didn't write the code and I'm not sure what it is doing at some of the code level but it appears Access 2013 doesn't like it for some reason. Could someone please offer a suggestion as to how to get this to work with 2013? Please excuse the poor code formatting - the "

" tags seem to remove certain blank lines and add others.
TIA for any help...

 

[The_Doc_Man]

First thought: Every time I've changed versions of Office, I get burned by some dinky little VBA reference. From the Code window click Tools then References and see if the 2013 version shows a missing reference. (It is easy to find because it actually tells you it is "missing.") Invariably, SOME obscure .DLL file changes versions, say from 032 to 033, so you have to find the new version and check that reference (and uncheck the 032). The recompile and try the code. The DAO library was the biggest culprit of this back when AC97 moved to AC2K3 and I can't tell you how much hair I tore out before finding that one.

The second thought is that some of this code appears a little odd. It appears to be making a "shadow copy" of the controls on some form. I.e. your loop near the top of that module does a "For Each C in {the collection of all controls on the form Frm}" - i.e. it traverses all controls on the form. It then adds copies of all visibile, enabled TextBox and ComboBox controls to a new collection.

It appears to be trying to do something object-oriented but it is not clear from this viewpoint just what is going on. I might suggest, though, that Access isn't truly object-oriented for its intrinsic structures. You can build your own object-oriented structures and perhaps that is what the original author was trying to do - but some parts appear to be missing. Specifically, the parts that would link the object-oriented structures to the visible controls - unless I missed it entirely.

The thing is, if the control structure isn't correctly bound to its underlying source, it might not work correctly. I'm going to suggest that the forms must also have class-module code that calls some of these routines because without an actual form to be bound, some of the controls are going to be lost. For instance, text boxes typically draw data from a bound form's recordset. A combo box can have its own rowsource, but again there can be issues with the rowsource if it uses "cascading combo box" concepts and one of the cascade members isn't there yet to provide required data.

In essence, this looks like only half the code, and the half we see would probably leave controls dangling without some sort of support from the class modules for the associated forms. I think you will have to take a "wholistic" view as opposed to a "reductionist" view on this one. Try to see the forms and that supporting module as a gestalt.

 

[NoSmoke]

Gizmo, thanks for the assistance. I'm not sure I understand what you are suggesting but I created a blank dbase in 2010 and imported the relevant table, form and vba module into it. I then created a blank dbase in 2013 and performed the same procedure from the just created 2010 dbase. The problem with 2013 remained. I don't have "access" to another machine to try it on.

Recalling I had solved a problem with the same form in converting it from 2003 to 2010, I attempted to export the form in text mode with the intent of importing it into a blank 2013 dbase. That too was unsuccessful as the export text function simply hung in an endless loop.

My 2010 platform is 64 bit and I tried both 64 and 32 bit versions of 2013 with the same results.

 

[NoSmoke]

Doc Man, thanks for the comments. As I mentioned in my first post, I didn't write the code (IIRC someone on this forum wrote it for me) and being a VBA novice, I don't understand how some of it works. Functionally, its purpose is to simply change the background & foreground colours in text and combo boxes when the box has or looses focus (the idea to make it easier to see at a glance where the cursor is on a rather cluttered form). This code BTW has caused me problems before in that it would stop working for some unknown reason and restart seemingly spontaneously or after a recompilation.

The only other code associated with this class module code (called "dsControlFocus") lies in the form's VBA as follows:

Dim ControlFocus As New clsControlFocus

and:

Private Sub Form_Load()
ControlFocus.CFInitialize Me.Form
End Sub

Don't know if that sheds any light but thanks for any further assistance...

 

[spikepl]

You could consider a pragmatic apporach: throw the code out and replace it with the well-documented code of Allen Browne's: http://allenbrowne.com/highlight.html

 

[NoSmoke]

You could consider a pragmatic apporach: throw the code out and replace it with the well-documented code of Allen Browne's: http://allenbrowne.com/highlight.html

Hey, now that looks good! Being the pragmatic type, I will try it (esp since the code I have now has caused other problems). THANKS!

 

[The_Doc_Man]

I always do this via simple GotFocus/LostFocus code and I just write a subroutine that does the attribute/color mods. Each GotFocus/LostFocus passes in the control (as a control object) and the subroutine has a Case statement based on ControlType so that it knows what it CAN do with the control. Then another parameter to the subroutine tells whether it was "Got" or "Lost" - and you don't need fancy property assignments and elegant but obscure code. Put some effort in the subroutine, but then you might find that each GotFocus and LostFocus event might be three lines of code:

Private Sub xxxx_GotFocus:

SetControlAttr  xxxx, True

End Sub

Of course, it is the code within SetControlAttr(control, t/f) where you put the effort.

The point is, this simple approach will be easy for you to debug two or three years from now (after you've been immersed in some other problem and have to come back to this code) because there will only be one place to debug. The Get/Lost Focus code is too simple to warrant debugging so you'll only ever have to touch the subroutine that does the work.

This is an example of a really good strategy called "Divide and Conquer." You make the problem into two parts, one of which is trivial. Then just solve the other part of the problem ONCE and you are done.

 

[NoSmoke]

Doc Man, if I understand you correctly, the suggested GotFocus/LostFocus code would have to be separately entered for each text or combo box on the form. The issue I would have there is that I have about 100 boxes on the form in question so it was v advantageous to have a single code module that performs the function for all boxes.

BTW, "Conditional Formatting" for a control also provides a method of doing it w/o VBA but again it has to be done separately for each control (as I understand it anyhow) this also making it laborious if one wishes to change the background or foreground colours at some later time.

 

[CJ_London]

BTW, "Conditional Formatting" for a control also provides a method of doing it w/o VBA but again it has to be done separately for each control (as I understand it anyhow) this also making it laborious if one wishes to change the background or foreground colours at some later time. Today 04:39 AM

BTW, "Conditional Formatting" for a control also provides a method of doing it w/o VBA but again it has to be done separately for each control (as I understand it anyhow) this also making it laborious if one wishes to change the background or foreground colours at some later time.

Ah yes but you can highlight all the controls (not labels) and do the conditional formatting for all the controls just once and it will update all the controls

 

[spikepl]

I see suggestions are pouring in, so: you could also buy a whip and engage in a half-hour session of autoflagellation.

 

[NoSmoke]

BTW, "Conditional Formatting" for a control also provides a method of doing it w/o VBA but again it has to be done separately for each control (as I understand it anyhow) this also making it laborious if one wishes to change the background or foreground colours at some later time. Today 04:39 AM Ah yes but you can highlight all the controls (not labels) and do the conditional formatting for all the controls just once and it will update all the controls

That would be a good solution (and one I would have used from the outset) but if you highlight more than one control, Conditional Formatting no longer appears as an option (at least in 2010). Don't know why that should be...

 

[NoSmoke]

I see suggestions are pouring in, so: you could also buy a whip and engage in a half-hour session of autoflagellation.

I sometimes find that just programming Access is autoflagellation in itself.

 

[CJ_London]

That would be a good solution (and one I would have used from the outset) but if you highlight more than one control, Conditional Formatting no longer appears as an option (at least in 2010). Don't know why that should be...

Probably because you are selecting one or more controls which cannot have conditional formating such as labels, buttons, option groups, listboxes etc

 

[NoSmoke]

Nope, select two combo boxes or two text boxes or a combo box & a text box etc. and no Conditional Formatting.

 

[CJ_London]

I have just tested in 2010 and I do get conditional formating - you can see below I have selected two controls, the conditional formatting button is not greyed out and the rules manager is open. Perhaps it is something to do with your setup?

 

[NoSmoke]

OK, I see why the confusion. If I select Conditional Formatting from the ribbon as you show above, multiple controls can be formatted at once. What I was doing was selecting multiple controls and then right-clicking on one of them. When you do that, the Conditional Formatting choice in the pop-up list disappears.

So then, looks like a good solution but there is a slight complication. All the text in my 100 or so controls has the Font Weight property set to "Semi-bold". When I use the Conditional Formatting "Has Focus" rule, the Font Weight property gets reset to "Normal" for some reason so it is also necessary to reset the property back to semi-bold (not that difficult though since all the controls are selected anyhow).

I think this is the best solution to my problem. Let me thank again all who assisted!

 

[CJ_London]

As part of the conditional formatting, you can set both background and foreground so when the control 'has focus' you can set the font weight as well

 

[NoSmoke]

Yes, used that as well (to set font weight when control has focus).

 

[CJ_London]

Not sure why you are having a problem - If I set the control to be bold if it has focus it works OK. Perhaps it is because your are using semi-bold as the default - semi bold is not an option in conditional formatting and is larger than bold (which I've never uderstood why!) so when it has focus the text appears smaller

 

[NoSmoke]

No problem really. Conditional formatting can set to bold when "has focus" which is what I want but it also resets the font weight property to normal for some reason (so font appears as "normal" when hasn't got focus which I don't want). Therefore had to set font weight property(s) back to bold after using Conditional formatting.

Conditional formatting, at least for font weight, sounds like something that may have been added for 2010 or there would have been no need for VBA code to do it in previous versions(?).