The New MacroMan

Commenter MacroMan was considering a new line of work recently. It appears he’s made the leap to a career in VBA programming. Good for you MacroMan; I know you’ll do well.

A point of concern for him is that in addition to Excel VBA, he must program with Access VBA. I’ve made no secret of my distaste for non-Excel object models. Nevertheless, once you learn the idiosyncrasies of Access, you’ll have no problems. Here’s some advice:

If an intuitive object/method can’t be found (like Forms.Open), it’s probably under the DoCmd object.
You can’t change the properties of a form’s controls unless that control has the focus.
Learn what DAO and ADO are, and use DAO inside Access.
Learn the .FindFirst method, you’ll use it a lot.
Always split your database between a front end and a back end.
Get used to this:

Sub SomeProc()
   
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sqlMyQuery As String
   
    sqlMyQuery = “SELECT * FROM tblMyTable”
   
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sqlMyQuery)
   
    With rs
        If Not (.BOF And .EOF) Then
            .MoveFirst
            Do
                ‘do something with the current record
               Me.tbxMyTextBox = .Fields(“MyField”).Value
                .MoveNext ‘For the love, don’t forget this line
           Loop Until .EOF
        End If
    End With
   
    rs.Close
    Set rs = Nothing
    Set db = Nothing
   
End Sub

Bookmark these site:
The Access Web
Allen Browne’s Site
Granite Consulting
Utter Access

The Access MVPs are great, so don’t be shy about posting your questions on the Access newsgroups. Everyone help a brother out and leave your Access tips and links to your favorite Access sites.

Posted in Uncategorized

21 thoughts on “The New MacroMan

  1. Dick wrote: “.MoveNext ‘For the love, don’t forget this line”

    He, he, he! Ain’t that the truth. {grin}

    And, yeah, there’s something about the object models for other Office products that is somehow “not quite right.” Of course, it wouldn’t surprise me if those familiar with them feel the same way about the Excel OM. ;-)

  2. Macro Man – in a previous comment you said “there’s no Macro Recorder in Access to learn from?!?”

    Whilst this is correct, you can however, convert Access Macros to VB Macros. Simple Access Macros can be created by choosing a few actions (open form, query etc) and then converted by going to Tools, Macro, Convert Macros to Visual Basic.

    This also automatically adds error handling and copies any comments you made. For example, an Access macro to open a query is converted to this:

    Function Macro1()
    On Error GoTo Macro1_Err
        DoCmd.OpenQuery “Query1”, acViewNormal, acEdit
    Macro1_Exit:
        Exit Function
    Macro1_Err:
        MsgBox Error$
        Resume Macro1_Exit
    End Function

    This code can then be modified, added to etc

    Hope this helps…

  3. Probably by far the most maddening aspect of Access vb is the one mentioned by Dick: “You can’t change the properties of a form’s controls unless that control has the focus”; which is an absolutely absurd concept. Even when using MyControl.SetFocus then MyControl.Text = “SomeText” oftentimes will produce the cannot update without focus error. It’ll make ya say ‘shucky darn’ :-(

    Hoping this goes away in v12.

  4. Tushar –

    “And, yeah, there’s something about the object models for other Office products that is somehow ‘not quite right.’ Of course, it wouldn’t surprise me if those familiar with them feel the same way about the Excel OM.”

    The OM I have the most trouble with is Word’s. I think it was designed by monkeys on crack. It seems there are hundreds of workarounds to get the code to do what the documentation and the syntax imply it should already do. Excel only has dozens of these workarounds. I’ve never written VBA for Access, well, I did write VBA to control Excel charting from Access, but never anything to control Access. This whole DoCmd sounds like a hoot. Of course, Word has WordBasic to do what VBA can’t, and Excel has XLM.

    Then there’s VSTO…

  5. Hey Dick,

    Great tips, the “DoCmd” object will be used a lot. The “Me” object will come in handy too. So I guess DAO is only used when my db is in Access and I’m creating a UI for it in Access also. So to me “.SetFocus” is analogous to Excel’s “.Select”.

    Simon,

    Yes you’re correct, I saw that.

    The help files are great also, but it sure is nice to get these tips. Saves me from a lot of reading. Thanks bros.

    Maybe this site should be “Daily Dose of Excel/Access”? Seems like you guys are knowledgeable about all Office object models.

  6. When I program in Access, the more complex forms are unbound. That is, the data is written to the database via code, not by some built in Access method. Is that the way most people do it?

    Because of that, every time I write in Access I think I might as well be using Excel forms instead. I like Excel forms better and the code is almost the same. How do those of you who write in Access do it? Bound or unbound, primarily?

  7. MacroMan,
    IMHO, John Walkenbach’s ‘Excel xxxx Power Programming with VBA’ has an equivalent in Access. For me THAT is high praise!
    I use ‘Mastering Microsoft Access 2000 Development – The Authoritative Solution’ by Alison Balter, SAMS Publishing. I just checked on BarnesAndNoble.com. The book goes for $49.99. The 2003 version (which I have not seen) is $59.99 (Alison Balter’s Mastering Microsoft Access 2003).
    Good Luck.
    Gary Brown

  8. A different opinion from JKP about joining an organization that’s aimed at “advocating our kind,” I’d be interested in knowing what that means.

    Or, for that matter, how many people actually belong to that organization.

    I know a little about some of those who made the initial push to get it going. Given how they treated me in a public forum (one of the moderators wrote me a note telling me how they were out of line and then locked the discussion), there is no way I would ever consider becoming a member.

  9. I find that building a department-level application is often easier and safer in Access. Because, the underlying schemas are restricted and “structured”, you have a better chance of avoiding errors. That is to say, you spend less time writing error handling for the 100 different ways something can go wrong on a spreadsheet (moved data, wrong data types, multiple open workbooks, etc….)

    By the way, I have a rant.

    Why the hell does an Excel textbox control not come with a Format property? You know…Currency, Number, Date, etc. Textbox controls are, by default, set to general number, leaving you with no easy way to add formatting.

    Apparently, you’ve got to come up with some convoluted work around that adds formatting at run-time (using a form event). Does anyone have a clever way to do this? I can never get it to work.

  10. > Why the hell does an Excel textbox control not come with a Format property?

    That sure would be a nice feature and I think it’s a great idea. But as to ‘why’.. it is just a TEXTbox I suppose…

    Cheers,

    Remco

  11. Dick,
    Personally I always bound controls for almost everything in Access. I normally always use a Subform for every screen even if it is just a form with a simple block of data on it. I use lots of subforms within subforms and subreports on reports, they can really add functionality and make it much easier to navigate. The best thing is the Child and Master links. They can make your life a breeze. Bear in mind that the more code YOU write the more errors your users get. It is possible to make a fairly ok (if some what bland) interface using no code what so ever. This sort of form could be a combo box with some sub form that would allow you to select something that shows a sorted and filtered list. I like to build the interface with menus rather than using some funny main screen with huge buttons, and the more tabs the better.

    Top tips are

    Design the tables first.
    Don’t ever use the multi valued cell in 2007 it is the work of the devil.
    Add descriptions and captions to everything during design of the table.
    Add all the relationships and indexes at the start.
    Go back and add in all the combo box lookups for all the fields that are related in the Relationships.
    Now build…..
    (don’t start the VBA until you have sorted ALL your fields and tables etc.)
    It gets out of date so quickly.

  12. Hi Tushar,

    What I meant is that PODA is a community of professional developers committed to helping each other, with a focus on principles of teamwork.

    As such, PODA aims to nurture collaboration between members to further enhance their knowledge, and to encourage high standards.

    All MS Office professionals are invited to participate, joining over 150 other members world-wide.

  13. Quick Comment I re-read my last post and it sounded far too much like a lecture at Dick.
    Appologies as clearly Dick already has a sound grasp of Access.

  14. Jan, I appreciated your previous comment – no lecture inferred. In fact, I tried to apply it to an Access project I’m working on now. I’m using a temporary table to hold some values while a form is being completed, which eventually is written to a permanent table. It’s a lot of work and is prone to errors. “How can I make this a subform directly from the permanent table?”, I asked. I wasn’t able to do it. I’m thinking about putting an mdb together that demonstrates my situation and seeing who can come up with something. There’s probably a better way than I’m doing, but I don’t have an Access expert looking over my shoulder, so I end up doing what works.

    One problem I foresee is usability limitations. I won’t sacrifice my ideal of usability for anything. If I have to change the way the user interacts to suit the tool, then I deem the tool to be broken and I need to find a better one. So someone could come up with a solution that “works”, but it would be unacceptable to me because it would be asking the user to adapt to the program.

  15. You quite right Dick. Make it work intuitively for the user, don’t make the system drive the users habits.

    You could try writing a full VB app. It is quite easy to get into and you can store your data wherever you like.

    I am keen to see it, so that at least I can try to force it into a nice subform and keep the interface identical to the user. Generally speaking there have been very few cases where I had to resort to loading data by hand.

    One thing I had added to lots of Access apps that the users really enjoy are Rightclick menus
    Access does them very well and I have setup things like a MRU list on ids and search boxes.

  16. Dick: I can’t be sure of your exact circumstances yet, but as you have discovered, temp tables are a mixed blessing and I tend to view them as a very last resort. Depending on your project configuration, you may find “disconnected recordsets” useful to your needs
    http://www.4guysfromrolla.com/webtech/080101-1.shtml
    This technique allows users to carry on editing as normal, while still retaining system control over how/when the persistent data is updated.

    As you may know, Access forms have a ‘Recordset’ property which can be manipulated at run-time. This together with the ‘Continuous Forms’ setting, make programming data-intensive GUI’s much easier in Access forms IMO :-)


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.