Parsing First and Last Names out of one field

I don’t know if you do a lot of string parsing, like getting First/Middle/Last name in one field. It’s something that I do fairly regularly. I got a spreadsheet of kids that I need to add to my system, and it has their full name in one field so I have to break it apart. Shouldn’t be a big deal.

Famous last words.

I actually couldn’t do it in Access, which was a first for me and quite got my knickers in a twist. Normally I’d do an update query with the Update From, but it didn’t work. I thought maybe the space between the first and last name was actually something weird in hex, but it wasn’t.  So I copied the data from Access back in to Excel (it was fewer than 500 records, and only eight fields or so). The Excel code is as follows:

=LEFT(B2,FIND(" ",B2))

And that should give you the first name. But it didn’t. Turned out that the field had a leading space! So I used the following code:


And it worked. Then I had a clever thought for getting the last name:


(I2 being the first name)  Subtract the length of the first name from the full string length to give you the length of the last name, then a right() gives you the rest.

I was mildly proud of myself. After getting the data back in to an Access table in preparation of uploading it to SQL Server, I went ahead and trimmed leading and trailing spaces.

It’s not perfect. If they have a middle name, or a non-hyphenated compound last name, it’ll plug it all in to the last name and you’ll have to go back and do some manual cleanup. But it’ll get you most of the way there.


One form, two data sources

As a part of the system that I’m developing, I needed to copy a form.  The two copies were identical, and the data set was the same, the difference was in filtering.  The RecordSource for each was a different query, one connected to a view on my server, the other to a query in Access.  I thought this was a ridiculous thing to have two copies with such a trivial difference, so I went after it and came up with a pretty cool solution.

First, I added a hidden text box called txtRecordSource to the menu form.  I made it visible for testing.  Then I changed the buttons that the user clicked from macros to open the form to the following:

Private Sub cmdEditStudents_Click()
    txtRecordSource = "dbo_vwStudentsFiltered"
    DoCmd.OpenForm "frmStudents"
End Sub
Private Sub cmdCaseloadEditStudents_Click()
    txtRecordSource = "qryCaseloadStudents"
    DoCmd.OpenForm "frmStudents"
End Sub

I could have used the FilterName or WhereCondition parameters but decided not to.  I wanted the student filtering to happen at a lower level so that filter could be used in other places and changed in only one.

Within frmStudents, I added the following code:

Private Sub Form_Open(Cancel As Integer)
    Me.RecordSource = Forms!frmMenu!txtRecordSource.Value
End Sub

That’s all there is to it.  Whenever you change the RecordSource of a form or report, it automatically requeries the underlying data.  I’m considering applying the technique in some other places throughout my system, it will require a little more work but might be worth it for a long-term maintenance perspective.

If you’re in the United States, I hope you enjoy your Thanksgiving holiday.  If you’re not in the USA, have a good remainder of the week and a wonderful weekend!

Invalid character value for cast specification (#0)

I ran in to this rather odious SQL Server Native Client 11.0 error last week, getting a message box saying “ODBC–update on a linked table failed.”  Actually it was an insert, but that’s OK, I never expect software to have particularly good error messages.  (but why, oh why, can’t Microsoft make their error messages so that we can copy the error message out with our mouse to make it easier to look up?)  I’ve since been banging my head against the wall trying to solve it.  It didn’t seem like anything complicated going on: I had a table that I was accessing directly through a subform and adding a record.  The insert was consistently blowing up.

Well, I found my problem.  Your mileage may vary.

First, the SQL Server table:

CREATE TABLE [dbo].[NotesStudents](
    [NoteID] [int] IDENTITY(1,1) NOT NULL,
    [StudentID] [int] NOT NULL,
    [NoteDate] [date] NOT NULL CONSTRAINT [DF_NotesStudents_NoteDate] DEFAULT (getdate()),
    [NoteTime] [time](3) NOT NULL CONSTRAINT [DF_NotesStudents_NoteTime] DEFAULT (getdate()),
    [UserID] [int] NOT NULL,
    [NoteText] [varchar](1000) NOT NULL,
    [RowVer] [timestamp] NOT NULL);

I have the date and time as two separate fields as I want my form to display the records sorted by date descending, time ascending.  I needed to populate the date, time, student ID, and user ID, and the following code on the BeforeInsert did that:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.txtNoteLen.Visible = True
    Forms!frmStudentsSPEDs!fsubStudentsNotes.Form!SeekerID = Forms!frmStudentsSPEDs.StudentID
    Forms!frmStudentsSPEDs!fsubStudentsNotes.Form!UserID = Forms!frmMenuSPEDs.UserID
    Forms!frmStudentsSPEDs!fsubStudentsNotes.Form!NoteDate = Date
    Forms!frmStudentsSPEDs!fsubStudentsNotes.Form!NoteTime = CStr(Time)
End Sub

(the txtNotLen field counts how many characters are in the note since they’re limited to 1,000)

So the above code produced the #0 error message.  I could add records through SSMS, so I knew the table was functional.  Today I get back from lunch and decide to manually add a record to the table via Access, using Ctrl-; to add the date and Ctrl-: to add the time.

And it blew up.

Turns out that when you use Ctrl-: to enter the time, you get 12:57:41 PM.  The PM was the problem.  SQL Server was expecting just a time value, so I changed the NoteTime line to say Format(Time, “h:m:s”): the Format “h:m:s” converts the current time to 24 hour format, so 4:49pm becomes 16:49.

So if you’re getting this error and you’re dealing the VBA and a Time(3) field on SQL Server, this might be the cause.

A clean way to handle a report’s NODATA event

This was annoying me.  I wanted to display a report showing all of the notes for a student, but it was entirely possible that there would be no notes.  Seems simple: put a MSGBOX in the VBA behind the NODATA event of the report.  Except the report then tries to display.  So I came up with this code:

Private Sub cmdNotesReportPreview_Click()
    Dim snum As Integer   'student ID
    Dim sncnt As Integer  'num of notes for this student
    snum = Forms!frmStudentsSPEDs.StudentID
    sncnt = DCount("[NoteID]", "dbo_NotesStudents", "StudentID=" & snum)
    If sncnt > 0 Then
        DoCmd.OpenReport "rptStudentsNotes", acViewPreview, , "StudentID=" & snum
        MsgBox "This student has no notes to display."
    End If
 End Sub

Does the trick for me quite nicely.

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.

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