SQL Saturday!

Tomorrow morning, at a ridiculously early hour, I’m heading for El Paso to attend my first SQL Saturday!

These events are put on by local PASS chapters and are free.  Typically they ask for $10 to provide lunch and also feature pre-conference sessions on Friday covering a variety of subjects, these are normally about $100, which is dirt cheap compared to some of the pre-con sessions that I’ve seen at some trade shows!  Tomorrow features Tune Like A Guru by Kevin Boles, I’m really looking forward to it.

And in 3 weeks, SQL Saturday in Denver!  I have an excellent boss: he’s letting me attend free training and giving me time off to drive up to Denver for it.  Fortunately I have a friend in the area that I can stay with, so the school doesn’t need to pay for a hotel.

Check them out at http://www.SQLSaturday.com, you definitely can’t go wrong with a price of free!  And there’s an excellent chance that there might be one in your area.


Changing the appearance of Access fields when they’re entered and exited

I came across this code in Access Hacks by Ken Bluttman (O’Reilly, 2005, pg 83) and decided to experiment with it.  The concept is quite simple: two short subroutines to define what to do to a field when it is entered and exited using With Me.ActiveControl to set numerous parameters, followed by an _Enter and _Exit sub for every control that you want so manipulated.  Also note that if the Special Effects of the text box is other than Flat, it might not work correctly.

You’ll notice the IF in the _Enter and _Exit subs.  I prefix the names of non-text fields with ck for checkboxes and cbo for combo boxes.  In the case of the checkbox, they don’t support the FontItalic and FontWeight options, so I skip them.  There may be other controls that I don’t use that have similar limitations.

Sub Active_Control_Enter()
    With Me.ActiveControl
        .Properties("BorderWidth") = 2
        .Properties("BorderColor") = vbRed
        If Left(Me.ActiveControl.Name, 2) <> "ck" Then
            .Properties("FontItalic") = 1
            .Properties("FontWeight") = 700       'bold
        End If
    End With
End Sub
Sub Active_Control_Exit()
    With Me.ActiveControl
        .Properties("BorderWidth") = 0
        .Properties("BorderColor") = vbBlack
        If Left(Me.ActiveControl.Name, 2) <> "ck" Then
            .Properties("FontItalic") = 0
            .Properties("FontWeight") = 400       'normal
        End If
    End With
End Sub
Private Sub APHGrade_Enter()
End Sub
Private Sub APHGrade_Exit(cancel As Integer)
End Sub

Assuming that you’re dealing with linked tables, you can use the T-SQL code that I wrote a few months ago called GetTableFieldNames to get a list of all of your table’s field names, it would be pretty simple to modify the code to produce all of the _Enter and _Exit subs.

Windows 7 can really drive you nuts at times, ya know?

I’ve had a problem for months that defied easy diagnosis and good keywords for Google searches.  Simply put, some windows insisted in being in the foreground and anything you opened was forced to the background and you had to minimize or move the ill-behaving programs in order to use the other programs.  Didn’t matter if you clicked on the title bar to give it focus, it would have focus but it would still be covered by other programs, likewise if you did an Alt-Tab to switch it.

I FINALLY found a solution!

Since this is Windows, it requires some registry editing.  Simply put, look for HKEY_CURRENT_USER\Control Panel\Desktop.  There are two keys that we’re interested in: Foreground Flash Count and Foreground Lock timeout.  Set the first one to 3 (mine was 7), the second to zero (mine was 200,000 in hex).  Then remember to reboot your computer!

This fixed the problem, at least for me.


There’s another suggestion on the page to go in to Control Panel/Ease of Access Center and checking a mouse option that says “Prevent Windows from being automatically arranged when moved to the edge of screen”.  When unchecked, in Windows 7, moving a program window in certain ways can result in it maximizing and taking the entire screen.  My work system has dual monitors, a 19″ secondary for email and timeclock, and a 24″ primary.  I HATE THIS FUNCTIONALITY.  I turned it off long ago, and while I appreciate the option, it had nothing to do with my primary problem.

EDIT: this did work briefly, but only briefly. A few days later the z-layer behavior of my windows reverted to its previous idiotic state.

Preventing record deletion in Access through VBA

My current system has a business rule that students cannot be deleted.  They might age-out, die, move out of state, whatever.  We keep the record.  They’re no longer available for viewing but the information is retained for auditing and study.  On the SQL Server side, where the data is stored, no one has delete permissions on any table to maintain the validity of the audits.  I wanted to provide a message to any user trying to delete a student along with a very lurid color notification.

Canceling the Delete action is the simple ‘cancel = True’ line, that’s all there is to it.  We then save the current color of the form header and detail to a long variable, set the colors to green, pop up the messagebox, then return the colors to normal.  If you have a form footer, obviously you’ll need a line for that if you want to twist the entire screen.

Private Sub Form_Delete(cancel As Integer)
    Dim headercolor As Long
    cancel = True
    headercolor = Me.FormHeader.BackColor
    Me.FormHeader.BackColor = RGB(0, 255, 0)
    Me.Detail.BackColor = RGB(0, 255, 0)
    MsgBox "You cannot delete students from this system."
    Me.FormHeader.BackColor = headercolor
    Me.Detail.BackColor = headercolor
End Sub