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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s