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!

Choose Two

There’s an old IT adage that is pretty much applicable to any endeavor:

For any project, you can have it fast, accurate, or cheap.  Choose two.  In far too many cases, management/owners wants all three, and that just doesn’t work.  If you want fast and accurate, it won’t be cheap.  If you want fast and cheap, the results probably won’t be accurate.  Etc.  Wanting all three is a perpetual “do more with less”.

Someone on Slashdot posted a great reply: “When you choose all three you can’t say with any certainty which you’ll actually get when all is said and done – if indeed you get any of them at all.”


Another phrase that comes to mind is the “We the unwilling, led by the unknowing, have done so much with so little for so long that we are now qualified to do anything with nothing.”

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.

Premature Stupidity

Did you ever get ahead of yourself, thinking about how to implementsteps M, N, O when you hadn’t finished the code for F, G, H?

I just had that happen.

I have some code on my main student form tied to _CURRENT.  When the record opens, it tests several dates to see if an eye exam or whatever needs to be performed.  If it’s missing, expired, or about to expire, a text box is made visible and populated.  If everything is OK, you never see the box on the screen.  Easy enough, works great.

I needed to test a new condition, but it wasn’t directly related to the record that I was on.  The objective was similar: check to see if the student is enrolled in a specific program.  So I performed a DLOOKUP to get a count, if the count was greater than zero, they were in the program.  To do a quick and dirty check, I popped up a message box saying ‘Student is enrolled’.

The problem was that the message box would pop up before the form rendered.  I stared researching what event fired after _CURRENT that I could trap and was getting frustrated when I realized it did not matter.

The end goal was that a locked text box would display with the name of the teacher of the class that I was checking for (which could be changed elsewhere in the system).  Which was exactly what my code was already doing.  Look up the teacher name from a join with another table, make the box visible, else make the box invisible.  No problem.

I would give myself a dope slap but last week I smacked myself in the forehead with my hatchback door, so a repeated head injury is probably best avoided right now.