Skip to content

Annual MS Access tasks – and my blood pressure

May 20, 2015

I have a customer who is using a 17 year old MS Access application.  I have been supporting it for a couple years now.  supporting meaning when something needs to be updated or when somethings does not work and Access gives error messages – I need to figure out why, fix it and move on.

Let me say that again…..

Support = Resolving a problem and moving on.

I am smart when I blog about the things I do, solve, figure out, work through etc.  Especially when that thing is a annual thing.

Yesterday, I got a message from my customer that said this

Hi James!
 I’m hoping you can help me rather quickly. I created the calendar for next year and something went wrong. The calendar generated fine. Then I went in to designate holiday weeks. So I chose the following:
12/21 – 12/25 and 12/28 – 1/1
2/15 – 2/19
3/28 – 4/1
 However when I went to book kits the weeks were messed up.  So the week of 12/21 is not there which is correct, but I think the system shifts the week numbers or dates somehow because all the other dates I chose 12/28, 2/15, and 3/28 are still there and the week after each of those is gone (designated as a holiday).  So I went in to fix the 12/28 but I cannot retrieve the week of 1/4. I did not try to fix the others. 
 So in summary, I need the above weeks designated as holiday weeks so the kits do not book on those dates and I need the following weeks back in the system: 1/4-8,  2/22-26, and 4/4-8.
 If you have questions let me know.
My first course of action is to figure out what she is talking about.  Then determine why this is happening.  Then set out to resolve the issue.  To my customer, its just one step “give james problem – james solve problem”.  I often need/require her to be more specific and provide details, lots of them.  She is better now about details, as you can see by her message.
I think the problem she is describing is due to how she completes the task – the steps she takes.  I solved this a year ago by going into the table and actually removing records generated – rather than using the application to do it.  Probably not the best solution, BUT, it works.  I had her regenerate the weeks of the year and then I went into the schoolyearWeeks table and manually removed the 4 weeks.

Once that was done, I got another message from her ”

thanks James. The dates look good BUT… when I book any kit the “return” date isn’t working correctly.  So if I book a kit ex. es115 for 1/4/16 it should automatically have a return date for the given number of weeks we designated it for. Instead the message comes up that “your return date is beyond the given time……”  So it seems the calendar is not catching the number of weeks each kit is allowed. hope that makes sense. Just try to book any kit and it should come up. Try Sodus, primary, bassage and book a kit.
 Thanks as always for all your help!

I went into the application and found where there is a hard coded value in a query.
” vWeeksPastEnd = DateDiff(“ww”, #8/31/2016#, vEndDate)”
Really, a hard coded value?  Well – its a simple fix, change the date.
I found the code by opening the form that generated the error (above), putting the DB into design mode, clicking the select control that generated the error, viewing the properties and going into the on-click event – which brought up the VB code window.  Then I searched for the error and found the code.
  If vWeeksPastEnd <= 0 Then
[vEndID] = DLookup(“[SchoolWeekID]”, “tblSchoolWeeks”, “[Week_Number] = ” & vEndWeek & ” and [SchoolYearID] = ” & [Forms]![frmBooking – District]![vSchoolYearID])
MsgBox “Your Return Date is beyond the last week for kit returns!” & Chr(13) & Chr(10) & “Your Kit Return Date has been shortened.”
[vEndID] = DLookup(“[SchoolWeekID]”, “tblSchoolWeeks”, “[Week_Number] = ” & vEndWeek – vWeeksPastEnd & ” and [SchoolYearID] = ” & [Forms]![frmBooking – District]![vSchoolYearID])
vWeeks = vWeeks – vWeeksPastEnd
End If
Once I had that, I was able to find the variable vWeeksPastEnd , and ……. find the hard coded date.
I should really figure out how to automatically change that date, but that is too much work (i am lazy) – plus I do not have a rule to determine when to change the date to the next year.  When my customer decides to do the bookings.  Rather, I blog it and make a note somewhere about annual maintenance tasks.  So the next time I see this error, I know just what to do and have to do very little thinking – but rather just executing the steps already thought out.  I do not mind doing that, what I do mind is having to solve the problem again.
Moral of the story?
Annual Access maintenance points
May 2016 (the next time I have to do this)
– Remove the *holiday weeks (4 of them) manually from the tblschoolWeeks (12/21,12/28, 2/15, 3/28)
– change the hard coded date in the Form_frmBooking form from 8/31/2016 to 8/31/2017

From → MS Access

Leave a Comment

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

%d bloggers like this: