Another ‘gotcha!’ in Access that I need to remember

I’m working on two sets of menu actions that are almost, but not quite, identical.  The first set works against all of the students assigned to a specific teacher.  The second set pulls against a subset: it’s a caseload system where the teacher can say that ‘I only work with THESE kids’.  We have two districts with the potential for a hundred or more blind students, far more than one teacher should work with to provide good service.  So there will be multiple teachers with certain students assigned to them.

The solution was a Caseload table consisting of two fields: TeacherID and StudentID, those two fields are also the clustered PK.  I’m debating whether StudentID should also have a unique constraint, that’s a topic for a future meeting of the work group (which hasn’t happened since April).  If a student should only be assigned one teacher, then I could theoretically add the assigned teacher to the student record and use a filtered index which might give me better performance.

So the menu options are Edit Students, View Missing or Late (eye) Evaluations, Edit APH Status (an annual function that gives us more funding to get the student’s more stuff to help with their education, depending on severity of their visual impairment), and a browser that lets the teacher see if a specific student ID is assigned to what district.  The first three are duplicated for the caseload system, the fourth is replaced with a button for adding/removing students to the teacher’s caseload records.

Thus those three caseload functions join against the teacher’s records.  I got the add/remove code working late last week, today I started working on the other three functions, and I finally got them working.

Except for one problem — the second and third programs didn’t work.  They worked fine when I opened them from the Access Object Browser window, but not when I tried to open them from the menus.  I’d get a parameter box asking for a value that shouldn’t be needed.  I’d click Debug and go in to the code, and it didn’t make sense as the parameter being requested wasn’t there!

It finally occurred to me that it might be a macro parameter!  So I clicked on the builder for the On Open event, and sure enough, there it was.  Delete it, and the code works fine.  My real mistake was being lazy and using the Command Button Wizard to create a macro to open the forms rather than going to the fairly trivial effort of putting VBA code behind it.  It would probably be a good idea to go through my system and change all the buttons from macros to VBA, but I have two more major dragons to slay before I can go to internal testing, and the superintendent wants to get this out to the districts, so I think I’ll slay some dragons before I go back to that part of the code.


Data Cleanup

I’m working on a project to randomize name and address data for a project.  Basically I want to be able to take live data and do a one-way (non-reversible) scramble for showing to the public and for training purposes.  I’ve downloaded a lot of records from, a site that I really like for the purpose, but it’s a little too ‘international’ for my needs.  So I’ve grabbed data from a number of places.

One places that I took was my iPhone contact list.  I generated a PDF of all records with just the name and address.  All I wanted was street addresses, but I very quickly ran in to a major problem after I did a Select All and pasted it in to Excel for cleanup before loading it in to a database.

The data looked like this:

Zeffiro's Pizzaria                                                    135 N. Water St

The problem was that there was an unpredictable and inconsistent number of spaces between the company name and the address.  Incidentally, if you ever find yourself in Las Cruces, NM, Zeffiro’s is fantastic pizza from a wood-fired oven.  And yes, that’s their address.

I figured out a simple Excel formula to handle the extraction:


It wasn’t 100% effective, but it was pretty darn good for extracting just that part of the address.  Grab the rightmost X columns, and the TRIM() function removes both leading and trailing spaces, and Robert is your parent’s brother.

The process was a little more complicated than that, but ultimately I ended up with what I needed.

Good luck if you have a similar need!

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.

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

How to drive yourself insane with 64-bit Office and Access Runtime

I have been going nuts for some weeks now, though some would argue that I’ve been nuts for multiple decades.  Not long after I started this job I asked for the 64-bit versions of Windows 7 and Office so that I could use the full 16 gig of RAM on my desktop for SQL Server.

And it was good.

We finally got everything done to get us a VM, which was also 64-bit.  So I installed Access Runtime x64.

And it was not good.

When I got my SQL Server installed and configured on the VM and ran my Access app, with LOTS of VBA code behind the scene, I got the following error:

The database cannot be opened because the VBA project contained in it cannot be read. The database can be opened only if the VBA project is first deleted.  Deleting the VBA project removes all code from modules, forms and reports.

Delete all my code.  RIGHT.  That’s a total non-starter.  After MUCH digging I came to the conclusion that it was the x64 Office that was the root of my problems.  So I ripped out my x64 Office from my desktop and laptop, installed x32, tore out the x64 Access runtime from the server, installed the x32, and all was well.

Except it wasn’t.

The 32-bit ACCDE of my project still got the same error message using the runtime.  But the full version did not.  So it looks like users will be running the full ACCDB version when it goes in to testing.

I cannot count how many reboots that I’ve done today between the three systems.  I hate rebooting systems as a matter of principle.

Apparently there’s an issue with the 64-bit, and sometimes with 32-bit, that trips the error message.  The best thing to do is run 32-bit and apparently don’t apply any service packs to office, which I definitely do not like.  But at least everything looks like it is running now.  For licensing purposes I want to have users running the ACCDB through the runtime so I don’t have to have a hundred Microsoft Access licenses, we’ll see how I pull that off.  Hopefully it should be easy enough.

BTW, here’s a link to the Google search results on the specific error message and some more detailed explanations as to exactly what the heck was going on.

Forcing Confirmation For Fields That Probably Ought Not Change

The database that I’m developing in Microsoft Access and SQL Server is a student tracking system, and not surprisingly, the main table is called Students.  After much consideration, I decided not to use the student ID as the primary key of the table, instead I’m using an integer identity(1,1).  The main reason is that the number might change.  Normally, when a student is assigned an ID by the State Department of Education, that number will never change.  But if they’re enrolled in a private school, of which there are two or three types, they will have a number assigned by that organization, which could theoretically collide with a number assigned by the State.  My solution was to add a char(1) field that discriminates between the different types of organizations that can assign an ID, and I have an index on that combination of ID# and Org.

The main reason why I didn’t want this number to change was that I didn’t want to cascade changes to two child tables, plus, I couldn’t cluster on it because there’s no telling what order they’ll be entered in, which would cause endless page splits and I/O hits.

Access has a .Dirty event to indicate changed data, but it only operates at the record level, not at the field level.  (Or row and column level, if you want to be pedantic about it)  But Access does save the previous value of a changed field in .OldValue, so we can recover from an accidental field change without cancelling all edits on a record!

I don’t want the ID# to be accidentally changed, so I developed the following code tied to the LostFocus event that pops up a message box warning the user and forcing them to click Yes to change the number.

Private Sub StudentID_LostFocus()
    'can't directly check for Dirty, must compare to OldValue
    With StudentID
        If .Value <> .OldValue Then
            retval = MsgBox("You have changed the "_
                & "Student ID. Did you mean to do this? " _
                & "Selecting No will reset it to the " _
                & "original value.", vbYesNo)
            If retval = vbNo Then .Value = .OldValue
        End If
    End With
End Sub

Yes, I was supposed to write about how I do DBCCs without doing maintenance plans, I’ve had to push that a bit.

The Importance of VBA’s On Error With Reports, and Run-Time Error 2427

So I’m happily cruising along testing the functionality of my system for a presentation to the board next week, and *BOOM!* a report crashes. It was working fine recently, what the [blank] happened?!

I got the oh so informative VB dialog box saying:

Run-time error '2427'
You entered an expression that has no value.

The Help button was, of course, no help at all. Searching online didn’t help much either. The report is fairly straightforward: dump a table, but it has one serious twist: conditional formatting. I’m calculating if an evaluation is overdue in the view that provides the data, and if it is, I draw a box around the date of the previous eval and the date it is due. The box also goes up if the initial eval is blank.

Well, actually, it’s the reverse: the code in the _Format actually turns on all the boxes and turns them off if they’re current, but you get the gist. I’m not sure why I did it like that, I really should reevaluate that as I don’t like reversed logic.

Anyway, here’s the code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me!boxIEP.Visible = True     'the box around the fields

    If Me!IEPLate < 1 Then Me!boxIEP.Visible = False
End Sub

This system is sort of a student tracking system for a district (the ‘sort of’ is important: it’s a complicated explanation and very complicated system).  I remembered that I’d been playing with the data, and I’d created a school with perfect data: every record validated, every eval was current.

And that was the problem: technically there was no data, and that’s what the error 2427 was trying to tell me.

The solution was in two parts.  First, adding an On Error Resume Next to the top of the code.  The next was adding a MsgBox to the On No Data of the report as follows:

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "Your report is blank because all of your student" _
       & " evaluations are current." _
       & "  Press the <Escape> key to close the form."
End Sub

Staff will still see a blank report, but at least they get an explanation that it is a good thing.