The trouble with the Docmd function

The second in an occasional series of rants by Chris Shepherd

When chatting to other developers and users I often find myself starting sentences with "The trouble with Access is.....". The purpose of this column is to get some of these problems out in to the open and to offer some possible solutions.

For my Second column I’d like to take a look at coding the access user interface. What on earth were they thinking about?

I can imagine the discussion between the Jet and Access teams at Microsoft. First the jet team outline how the database engine will be a hierarchy of objects each with properties and methods. Then the Access team stand up and say "we thought we would just base it on some existing dodgy macro code".

The trouble with Access is the DoCmd function. I challenge anyone to come up with a satisfactory explanation for why this exists rather than a proper object model for the Access UI. I’ll explain what I mean:

As a naive new user you run a wizard which handily generates code for you, but you’d have real trouble understanding what it’s done. For example the wizard (not very whizzy if you ask me) generates this mess if you want it to open a form:

Urgh!

First: Notice that even the Access developers couldn’t be bothered with there own macro system – which should be ideal for Novice users (if it was any good).

Secondly: 9 Lines to open a form! This is VB not machine code.

For how it should be we need to turn to a product that the developers of Access are obviously not familiar with its called Visual Basic and it’s from Microsoft. In this product all we need to do is:

Formname.show

Now that is just a little bit neater. Ask yourself if you were debugging a system which one would be easier to understand.

After this had driven me up the wall for hours on end I finally decided to write code that gets round it. Unfortunately my coding skills weren’t up to producing it in the OBJECT.METHOD style shown above so instead I opted for:

ShowForm "FormName"

This can be achieved by secreting the following code in a module:

Note that I have wrapped this in an Hourglass and switched warnings off. This code is not particularly necessary but it makes the application more professional and using this global sub means I only have to add those Lines once.

In order to save others having to put up with ugly code I have made the following subs available as part of UsefullSubs – available from this web site by clicking the relevant link

Access 97 usefull97.mdb      or Access 2000 usefull2000.mdb

ShowForm(FormName As String) - Open formname

ShowTable(FormName As String) - Open formname in datasheet mode

ShowFilteredForm(FormName As String, Filtertext As String) - Open formname filtered on filtertext

RunReport(ReportName As String, Optional ViewMode) - Open a report in Previewmode filtered on filtertext (or optionally ViewMode)

RunFilteredReport(ReportName As String, Filtertext As String, Optional ViewMode) - Open a report in Previewmode (or optionally ViewMode)

RunQuery(QueryName As String) - Open Query QueryName

ClearTable(TableName As String) - Delete all data from TableName

RecordAdd() - add a record to current Form

RecordDelete() - delete a record from current form

RecordSave() - save record on current form

FormFilter(TargetForm As Form, Filtertext As String) - Filter Target form with filtertext

FormClose() - close current form

JumpToControlValue(SearchField As String, cbobox As ComboBox) - Move to the first record where Searchfield matches the value of cbobox

Hopefully this will make your databases easier to understand and debug.

If you have any comments about this article or would like to suggest other niggles with Access please drop me a line at Mail Chris

Chris Shepherd is a Technical Consultant working for SCC in the UK. He is an MCP in Access, VB, Excel and is available to discuss the joys of Access at Weddings Funerals and Christenings.