Now, Where Did THAT Come From?

I recently started thinking about setting application properties a little differently than I had been. Well, actually, it's been a while since I really gave it much thought at all, to be honest. A long time ago, I created a module with default constants like "MBTitle" and "AppTitle", and started importing it into new accdbs or mdbs. Then I'd edit the values for the new database. It works, but it never really made me completely happy. Something about hard-coding values like the support phone number seemed less than appropriate. But I couldn't really think of a better approach, and just stopped thinking about it.

Well, for some reason, it recently occurred to me that it might be better to make this a table-driven function. Actually, I'd thought of that once before, but my implementation wasn't all that creative at the time and I had let it slide again.

This time, though, it dawned on me that I could create a Hidden User System Table for my application parameters and retrieve them as needed with a simple little function. If I distributed the app as an mde it would be easier to update values, such as Application Version, when necessary, with having to redo a module. There is still no guarantee a knowledgeable user won't figure out how to get to my hidden table, of course. But if you’re going to work with Access-built applications, you have to learn to live with that.

So, here’s a screen shot of my USysValueList table with some typical properties and values.

Here's the function that retrieves values from the Hidden User System Table:

Public Function UTV(strField As String) As String
'Retrieves values for various database properties from a User System Table
On Error GoTo errHandler
UTV = DLookup("UsysValue", "USysValueList", "USysValueList.UsysField ='" & strField & "'")
ExitProc:
Exit Function
errHandler:
MsgBox "Error in UTV():" & Err.Number & ":" & Err.Description
Resume ExitProc
End Function

Here's an example of a VBA procedure that would use properties from the table to populate controls on the Main Menu of the application when it opens:

Private Sub Form_Open()
...
Me.Caption = UTV("AppTitle") & " - [Main Menu]"
Me![lblTitle] = UTV("AppTitle")
Me![lblVersion].Caption = UTV("AppVersion") & " D" 'D for "demo"
.....
End Sub

The next step is to create a new function so that I can start saving user preferences into this table. I'm thinking of things like storing preferred date formats ("mm/dd/yyyy" or "dd/mm/yyyy"), for example.

Here's a query that displays a list of dates in two formats, using a stored date format preference value:

SELECT tblWork.WorkDate, Format([workdate],utv("DateFormat")) AS YYYYMMDDFormatted
FROM tblWork;

To support it, we added a new row to USysValueList:




I'm starting now to develop a start up form in which we'll be able to save one or more user specified values for properties that the database will then call up as needed to personalize the database.

Fortune Cookie Blues

Family, Good Friends, Old Friends, New Friends . The cookie knows. All you have to do is find the right cookie. I think maybe we've got it this time. Thanks, YoYo. It was a very good Thanksgiving, all in all.

Access on SharePoint

Are We There Yet?

Actually, I think so. I really do. We still have to unload the car and carry a few bags and boxes into the house, but we're certainly sitting in the driveway.

I've done up a small demo with MS Access 2010 (beta) running on the SharePoint 2010 (beta) and shown it to a few folks. It runs on my employer's site, so I can't publish the URL publically, unfortunately. Still waiting for a few "spare minutes" to float by so I can set up my own SharePoint site. However, from first responses, I'd say this is the real thing.

What Have You Done for Me Lately?

Here's a scenario where I think this new Access/SharePoint technology makes great sense (and dollars, if you'll pardon the pun). Consider a centralized sales organization which operates a Home Office in a large West Coast City. They have a few local agents in several other states (Michigan, Florida, Tennessee). Each agent is exclusively assigned to follow up on leads that originate in his or her territory. This organization obtains leads via a very sophisticated Affinity Program direct mail program. The Access database (with a SQL Server backend) which manages this program sits on a workstation PC in their Home Office. All potential customers, mailings, leads, assigned agents, and sales are tracked "cradle to grave" in this database.

One factor has always been a challenge for them: lead distribution and reporting is mostly manual.

Someone in the Home Office has to rubber-band a stack of mailed-in lead cards and stuff them in an envelope to resend to the assigned agent. Sometimes they photocopy and email them as PDFs. Getting results back from Agents? Well, yes, they are good about reporting results, especially sales, but leads can, and do, end up in a local agent's hands for quite some time before they are eventually disposed of.

Feedback from agents is hardest to manage. Management wants to know what's happening with all those "In Process" leads.

Sometimes a Great Notion

So, tracking leads and lead status is where this new Access/SharePoint capability comes in. Here's how I picture it:

The lead card comes in and the Office Manager updates that mailing record to indicate a response was received. Then, he assigns that lead to the agent nearest to the customer's address by selecting from a drop down list of agents. All of this happens now, by the way. The next step is where Access on SharePoint can really improve their process. Instead of getting up to photocopy the lead card, or sticking it on a pile to mail to the agent at the end of the day (or tomorrow if she gets too busy), the Office Manager pushes a button on the screen that says "Post to Lead Tracker". Access uploads that new lead record, along with any others that have been added recently, to the SharePoint site and sends an email to the assigned agent to notify them a new lead is ready.

The agent gets the email, goes to the URL for his or her leads and sees a list of "In Process" leads in his lead bank, with the new one sitting at the top. A good agent won't even be waiting for that email--he'll be checking his Lead Bank a couple of times a day anyway.

And when the agent disposes of leads from her lead bank, all she needs to do is pop open her Lead Bank form on the SharePoint site and select the new status from a drop-down list. If it's a sale, of course, the paper work now has to be completed, the same as it is done now. The main difference, though, is that the Office Manager back in the Home Office knows, by opening her desktop Access FE, that the lead has been disposed of, and she can set a flag to watch for the paperwork from the agent if necessary. (There is one unfortunate consequence for the procrastinators among us: "It's in the mail" is no longer a valid excuse.)

No more snail mail. Not no more photocopies. And everyone knows what is happening at both ends of the process in real time.

Wanna Buy a Good Buggy Whip? Cheap?

Access on SharePoint just might help to make the US Postal Service even less relevant than it already is. There's still email though.

.