Kiribako

Thought I Found A Bug In MS Access

A major feature of my Mondays at Olsson’s is the Bestseller Reports. Since the earliest days of the website, we had a page of bestsellers for books and music.

Outside of Christmas, our CD sales have been dismal lately. It seems like everybody I know has an iPod, so CD stores are going the way of the dinosaur. Sales reps and publicists are still hungry for the data, but the less we sell the more futile it is to spend time working on it. Specifically, rankings are nonsense. You can’t always find a sensible place to cut off the list. More data would help - so I recommend lots of customers come to the store and buy CDs, but that’s not likely.

A few weeks ago, I skipped the music report. Nobody complained. And I think that was the big week for R.E.M. - so it seems like a shame to skip, since I don’t hear about the new releases that we will actually sell a serious quantity of.

This week, I’m determined to change the format. It’s going to be a “What’s Hot” page. Not always the top ten items in each category. For that I need to attach the sales quantities so the PHP script can judge.

My five-year-old solution still essentially works: I run bestseller reports on the point-of-sales system, open up my Access database with the routines to parse the data into tables, a succession of forms to guide me (I originally thought somebody else would be doing this part, otherwise, I wouldn’t have bothered to make it user friendly - but it’s nice to have some automation to channel me away from easy mistakes…). The data is held in intermediate tables, which can be copied into the web server at the click of a button. There is also a report that I export to Word.

I go in to Access to trace the dependencies and carry sales quantities all the way from the initial report to the final pasting of records into the MySQL server. It all went quite smoothly: I didn’t make any mistakes. Until I get to a form with a sub-form that shows you the final preview of the database records to copy onto the server. I start getting a really weird mismatch.

The subform exists as an object in the Access forms list. It has the right data, and all the same properties that I used before, except it now includes a column of sales numbers. I update the link, save the outer form, and I get… One record. Instead of 78. Huh?

I already knew that I hated the Access form editor. It’s unforgiving with clicks - you wind up with some other object selected, the editing modes aren’t intuitive to me, the select handles for objects don’t do what I expect them to do…

So I click “show subform in separate window”. It pops up in the correct view, with 78 records. Awesome. I put that away, save the main form, click form view, and it shows me one record. This doesn’t make any sense to me. Clearly these two views are not the same, even though it looks like they purport to be the same. I figure I must be going insane. I go around in circles double-checking what I’ve done - looking for evidence of the contradiction - and I can’t find any. I delete the subform and start over with a new one - I’ll play along and let the wizard guide me. I click everything that always made sense before, pull the boundaries of the subform back into shape, save the main form, toggle back to form view, and…

No it still doesn’t work. Very frustrating. I have apparently exhausted my avenues of attack.

Philosophically, I know something is askew: I don’t like the idea of embedding one form inside another. I suspect there is some way to attach buttons to a datasheet, but I never learned how. I’m just using what I know, and I have proof that it worked well for years.

This is the point at which I question the interface. Because, you know, maybe I’m not getting the objects I think I’m selecting in the form editor. If that were true, then all the edits I’ve made are to some object I do not subsequently see in the form view. And yet, somehow I managed to damage what I could see when I added the quantity field to the data.

I should have figured this out by now: Maybe all that mess with the ADSL modem actually helped me. Maybe I’m not stubborn for as long in the face of this kind of problem now.

These ‘forms’ I’m using to enforce the process flow are not benign: Access assumes they are all linked to some data set. And when you include a subform, Access wants to enforce a relationship between the ‘inner’ and ‘outer’ data sets. Since I know I’m not using any data in the outer form, it doesn’t occur to me to check this relationship. The inner form, shown in table format, is being filtered based on some criteria that I still cannot actually explain. But that’s why it shows only one record as a subform, while showing all 78 records in a separate window. I can’t even remember how I hit upon this as an issue. I was ready to give up. My eyes hurt from staring at the screen. And yet, somehow I tried the subform wizard again from scratch. It’s the assumption of a relationship between the datasets, and the non-option I missed every other time: “none”.

None is the correct answer. It is an afterthought in the list, but in this context it is the only choice that would make sense, since the data set of the outer form has no records. I still can’t figure out why the thing showed one record, and not zero records. That would have probably tipped me off sooner. One record makes even less sense to me now in retrospect.

So, A Graphical Interface Actually Made Things Harder?

Yeah, how about that. There were long lists of properties for the form objects. Those don’t actually require graphics, but so what. When clicking on the right object is one pixel away from trouble, I think I’d rather just take it form the list. The only trouble with the list is that there are lots of auto-generated items like form labels with really generic names.

Okay, I’d better stop complaining and write the new PHP scripts…

UPDATE: I Helped A Stranger

You might not see the comments in your current view, but ‘Svendson’ got some use out of my rambling explanation.

When I got a spare copy of MS Office, I took it seriously. I taught myself everything I could. O’Reily has a good book on Access [I’ll find the link…], taking you from the basics of relational tables, through the quirks of Access’s SQL version, the Jet DB Engine, and some API flavors like ADO - It really helped me understand how code pages functioned in the Office suite. I thought I knew how macros worked - they didn’t seem mysterious, but practical matters can be a little different. Some VBA tricks in hand and I was off, linking up tables, queries, (hardly ever reports - doing all that with web server scripting), forms, macros and code.

And then I had to take a class… I naively thought I would learn something. This was over-priced adult education with an emphasis on following the workbooks. I might have taught the professor a few things he didn’t know. I got as far as I did on the humility of assuming I could always learn more. I showed off my O’Reily book to some of my classmates.

I never saw anything in a textbook about how hard it is to select the right objects. I couldn’t figure out who these people were, writing as if there could be no misunderstanding. For all the user-friendly features of the interface, it’s often deviantly wrong. I was already interested in user interfaces, and now I’m completely hooked.

For a lot of jobs, you can’t beat the convenience of Access… But that makes these little problems even more galling.

Creating and editing forms is the most painful part, and when I am not inflicting the design on anybody else, I will happily skip it. To me, table view *IS* a kind of form.

Let’s hear it for helping anonymous strangers out on the Internet!

Posted by Evan Bittner Tue, 06 May 2008 17:38:00 GMT