“Don’t Panic!” A FastForms/VBA project goes awry….

Hitchhikers Guide

The Hitchhiker’s Guide to the Galaxy by Douglas Adams, which was a radio show back in the 70’s, is near and dear to the hearts of many geeks. It branched out into many forms, including books and video games, over the years. What I remember most fondly is the Commodore 64 game released in the 80’s.

For the past few days I have been working on a FastForms customization of the SYCSL Customer Service Inquiry screen. The users need to track a set of 10 dates and various other pieces of data per inquiry and I wrote some VBA code to control the interaction of the controls. This is a time sensitive project, so I had to drop my other projects and complete this one as quickly as possible.

As I was nearing the end of the project, they requested that I make the screen change to the closed state when certain criteria are met with these controls. I’ve never done this before, but there’s always a first time for everything. So, I start experimenting with code that looks like this:

Public Function BEFORESAVE() As Boolean
  If conditions are true then
    DoCmd ("")
  end if
End Function 

This code will “ring the bell” on the Action Bar for you and I was working towards controlling the change status screen from the BEFORESAVE event on SYCSL.

However, I ran into a dead end there as the status window doesn’t have a mnemonic and I wasn’t sure that it could be manipulated. I began looking for a SQL solution, but this isn’t my first choice because of the risks associated with direct manipulation of the database.

I opened TOAD for SQL Server, and intended to use it to compare the database before and after using M2M to close an inquiry. With this comparison, I would know exactly which tables are changed in the process. I intend to blog about this tool later. I was in a hurry, and restored the last backup from my production server back to my test server over Data 01 and to Data 06. I intended to use Data 06 as my static database to compare changes. You are using a test server right?

Now, perhaps those of you experienced with FastForms have already spotted my mistake. For those who haven’t, let me take a second to explain the basics of how FastForms works. When you add controls to a screen, the changes are recorded in Visual Foxpro (VFP) tables called formxmaster and formxitems. The actual data is recorded on the SQL server in tables named in a systematic way. If you modify the SO (Sales Order) screen and you are adding fields regarding the master table, your FastForms table will be named Somast_ext. Somast is the sales order master table, and the FastForms table is an extension of that.

So, if you are working as quickly as possible, and dealing with the normal interruptions in your day, you can slip up and do something very stupid such as restore your pre-modified database right over the changes you spent around 13 hours of work creating. My VFP tables were fine as they are untouched in a SQL restore, but the SQL tables were gone. It’s important to note that I couldn’t open up SYCSL in M2M or in FastForms without forcing M2M to quit via the Task Manager. Even though this was all on a test server, I was in a bind as this was a rush job. This all happened right before lunch, so rather than panic I went home and spent some time relaxing with my bird.

While I was home, I planned in my mind what my game plan would be. First, I thought I would export the form changes and attempt to re-import them hoping that would re-create the tables. That didn’t work as the export program requires the SQL database structures to export as well.

At this point, I thought I may just have to remove the VFP information about my customization and recreate all of them as fast as I could. I would have missed my deadline doing so. So, I thought I would just manually recreate the tables in SQL, but I needed a template to start from. Since my Data 01 company was hosed, I opened my Data 02 and intended to recreate the _ext table with a sample date field and then use that as a guide to re-create the entire first table in Data 01.

I then realized that through a “feature” (bug) of FastForms, I could save myself. Let me explain. When you begin to customize a screen, M2M asks you if you want to customize it for only the current company or all companies. I always choose the current company and assumed that it only customized that company. This is not true. It only customizes the VFP for one company but all the structures and fields for SQL are customized in both companies. I had this revelation because when I opened Data 02 and proceeded to add a DateTime field, all of the fields I created for Data 01 were already there.

I opened Query Analyzer and used it to output a script to create that table and applied it to Data 01. I’ll discuss SQL programming for M2M Administrators and how to use the Query Analyzer to do this in a future post. After re-creating the table in Data 01, I tried to open the screen in FastForms and got an error which mentioned that it couldn’t find wp_SYCSLM_EXT. This is a stored procedure created by FastForms. I used Query Analyzer to output a script to recreate that in Data 01 as well.

At this time, I re-tested the SYCSL screen and got a more generic “Cannot Open SYCSL” error which is fairly common when working with FastForms. I believe it has to do with corruption of the VFP tables. The way to deal with it is to pack and reindex the formxmaster and formxitems tables and then run Createdbc. This is another reason that you need to use a test server as everyone has to be out of M2M to perform this maintenance.

I breathed a lot easier when everything came up as normal after that and I was able to do more work on the customization.

So, what did I learn from this? First, I’ll not make this particular mistake again. More often than not, a person becomes an expert through years of making mistakes and learning from them. Second, through this mistake I learned more about the behavior of FastForms which will prove valuable in the future. All in all, a very good day.

Leave a Reply




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>