Defaulting Form to VBA

Topic

New Reply

I think I could do this in previous versions.

When I add a control; say a button to a form, I want the code to default to VBA and not a macro. When I tell Access to convert macros to VBA, it blows up.

I cannot find this in the help.

Reply | Quote

Replies

I finally found it. Go to Access Options | Object Designers and scroll to Forms Control and choose “Aways Use Event Procedures”

Reply | Quote

I thought this was going to solve a problem that’s been bugging me since 2007!

However, it doesn’t change the fact that, unlike, say 2000, the button wizard still creates macro code rather than VBA.

Any thoughts?

Cheers

Alison C

Reply | Quote

The code created by the wizards was junk anyway, mostly because of the lousy error handling. Except for maybe the find record in the combo box wizard.

I’ve started using MZ Tools, a free utility for the VBE. Easy to install, let’s you setup error handler code the way you want and inserts it in a procedure with one click even if there is already code in it.

Also lets you customise Procedure headers that insert in a click, code templates and more.

Way better than the code generating wizards.

And certainly better than using macros.

Reply | Quote

I think I need to dig deeper. I hate macros. VBA is the way to go.

Reply | Quote

I have done some research on this issue. From what I have experienced and read, I have drawn the following conclusions about this issue:

1) There is no way to have Access 2007/2010 using an accdb file, create Event Procedures instead of Embedded Macros. It will still work if you are working with an MDB File.

2) Access 2010 has a bug in it which does not allow the “Convert Form’s Macros to VBA” to work so you can’t create Embedded Macros and Convert them. There is no current time frame to fix this.

3) Microsoft made a conscious decision to switch over to embedded macros only in favor of the “Information Worker” rather than the developer.

Soapbox Rant follows: I have been an Access deloper since it release in 1992. Access has always been, and obviously continues to be, the stepchild. Microsoft has never completely debugged Access or made it solid enough for professional development. They have never really supported the Access developer. Remember, it was originally marketed as a tools where you could create a database solution “Without Programming”. Microsoft constantly wants to push other solutions for desktop database (MSDE/SQL Server Lite along with a VB or.NET front end) provided by “Real” developers. They don’t seem to realize that Access and Relational Database development is WAY above the capabilities and pay grade of the typical Information Worker (IW) that uses their other Office products.

The typical Information Worker uses Excel for any simple list management because they have it on their desks and the IW understands Excel. Until Microsoft realizes that a relational database solution falls into the domain of a developer and not an IW, and they continue to reales a buggy product, Access will continue to be unable to find its rightful place on the desktop as a powerful database tool that can be used to create effective database applications for small/medium businesses and departmental solutions for big business. Right now they are straddling the line between IW and Developer and disappointing both. If they released SQL Server and TSQL with bugs like these, they wouldn’t be in business very long.

Anyway…the solution you are looking for does not exist and I wouldn’t hold my breath for a fix or enhancement any time soon.

Reply | Quote

Bob,

here my comments on your comments and rant.

1) surely you could write and add in to give this functionality even if someone hasn’t already. I did a quick search but came up empty handed.

In any case I’m much happier using MZ tools to do all this for me.

2) never tried to use it, I’ve never (well not in 15 years) had any macros to convert.

3) Very true, not to mention the impementation of Access Services in 2010. 2010 macros are designed to convert to Java script when they are uploaded to the web. With no interface for a serious developer to modify them.

My add to your rant –

I’ll admit that in my early days I did use the wizards to create code and this did help me go in the directions of code rather than macros. These days I often come across code generated by others less experienced using those wizards, say yuk and clean it up while thinking back to when I did the same.

I love that Access is designed for the information worker, I get most of my work from people like this whos application has grown to be so big and important to their business that they need to get in a real developer. While 95% of the apps started in house will die, there are those few that become mission critical. ANd as long as some of those come to me to cleaup and build on then I have a business.

Access is the best tool for a real developer to create a desktop application but as you point out Microsoft’s commitment to this is sometimes questionable.

It’s OK that information workers use macros, when they are getting an application off the ground, but at some stage the limitations will be reached and VBA is the only answer. (the converter

The fact that Access Services does not have a coding interface eliminates it from serious consideration as a web application. Even if I do manage to create a useable application using macros, at some point, my client will request a feature that is simply not possible using macros. Unhappy client.

Access 2007 was a considerable improvement for the developer trying to distribute an application. It almost got there. Pity MS’s focus was distracted by the sparkly lights of the web when they started on 2010!

SageKey’s installer does fill in all the holes but it’s very expensive. MZ Tools however is free. Here is an example of what two button clicks can insert into your procedure –

Code:

On Error GoTo Error_Handler
Dim strFormName As String, strCriteria As String, strArg As String

    strFormName = ""
    strCriteria = ""
    strArg = ""
    DoCmd.OpenForm strFormName, , , strCriteria, , acWindowNormal, strArg

Exit_Procedure:
    On Error Resume Next
    Exit Sub
Error_Handler:
    Select Case Err.Number
        Case 0

        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in Procedure: mzTest in Module: ModTest", vbCritical, DBEngine(0)(0).Properties("AppTitle")
    End Select
    Resume Exit_Procedure
    Resume ' For Debugging

Now isn’t that better than the old wizards?

Reply | Quote

Oh yes…I certainly agree about your source of business. Quite a number of my clients start with Excel, think they can use Access but never learn how to use it, bringing their Excel paradigm to Access. They dig themselves a large hole from which there is no internal escape so the come to me!

I have never really had to use the Macro converter either but I tried it because I teach this stuff and need to at least know it is there. When I tried the Macro Converter in 2010…it just fails. Dug a little deeper and found the admission of the bug that no one seems to be planning to fix.

A LONG time ago, I used the code wizards to help me start learning about code and Access. I could write an addin but there are others who have more time to do that type of stuff! I gave up on using purchased tools a while ago. I used FMS Access tools which were good but It was just another application to keep updated. I did like them though. I’ll have to check out the MZ Tools.

I guess we’ll just keep helping people out of the holes they find themselves in, eh?

Thanks for your comments.

Reply | Quote

Nice to chat Bob,

Do try out MZ tools – and remember – the best things in life are free, but please don’t tell my clients…

Kent

Reply | Quote

I re-installed MZ Tools. Had it before I rebuilt my PC.

I haven’t used wizards in years. I sometimes go into a system that someone else has built and see the evidence all over the place. Looks like wasted cycles to me.

Reply | Quote