Darragh Murray

Category: Ms Access Tips

  • Testing a MS Access subreport for records

    I haven’t done one of these in a long, long time, but I happen to be doing a bit of coding for a friend, and wanted to make a subreport control on an Access report invisible should it have no data. Of course, since I’ve been mostly out of the Access game for a while now, I was racking my brain for a bit, trying to figure it out and stumbling through a whole bunch of non-working solutions on the net.

    But I eventually figured it out….

    Assuming you have a subreport control on a parent report called MySubReport and … Read the rest

  • Ever wanted to retreive the label information of a textbox control in Ms Access?

    Been a while since I’ve written one of these things. Truth is, I haven’t been doing much Access databases in some time. However, I’m back in the fold, writing a small Access 2007 app and reacquianting myself with its nuances and idiosynchroses. In the process, I stumbled across a small little feature that, while obvious once you think about it, is not immediately apparent to the casual user.

    Often on Access forms you’ll use a textbox control. Usually, unless you delete them, the textbox control comes with an associated label control. Lets call my example “txtGenericTextBox” with a label called … Read the rest

  • Troubleshooting Potential Errors when Using Transactions in Ms Access – Do NOT Close the Workspace!

    I was doing some recordset juggling in vba and out of habit I usually close every object I open up explicit using the .close method on that object.

    For example if I open up a database using something like this

    Dim db as dao.database
    Set db = currentDb

    I will close it at the end of the function or procedure

    Db.close
    Set db = nothing

    When using transactions, you make use of the workspace collection and you open it along the lines of

    Dim myWrk as dao.workspace
    Set myWrk = DBEngine.Workspaces(0)

    As usual, I want to close this at the … Read the rest

  • DCount – “You canceled the Previous Operation” error

    Really really quick tip

    It seems that if you use dashes (-) in your field names for any table, and try to run functions on them in VBA like “DCount()”, you’ll keep encountering a Run-Time Error ‘2001’ “You canceled the previous operation”.

    My recommendation: get rid of dashes and underscores in your table field names.

    Edit: You will also get this error if you request incorrect fields names in the table. For instance, you are trying to ask for a count of customerIds and write the code as “DCount(“customerId”, “tblCustomers”)”, when the field in the actual table is ‘intCustomerId’ … Read the rest

  • A useful fashion to code multiple filters on an Access form

    Programming an form filter in Ms Access is simple enough. Assume you have a combo box with two values – TRUE or FALSE – on some sort of boolean control. You want the filter to set this value to TRUE when the combo box reads TRUE, and vice versa. Usually you would simply put some code in the afterUpdate() event of the combo box which would read something like this:

    sub cbxSomeBox_AfterUpdate()
    dim strFilter as string
    if me.cbxSomeBox.value “” then
    strFilter = me.cbxSomeBox.value
    me.filter = strFilter
    me.filterOn = true
    end if
    end sub

    This works fine if you simply have … Read the rest

  • Access Report Design – Cluttered Controls – Can Grow & Can Shrink

    Here’s a quick tip with regards to report, and to a lesser extent, form design. Many times when you’re design Ms Access reports, often you will come across spatial problems, where you simply trying to fit too many controls (usually textboxes) on a report – even when you’re designing in landscape mode.

    Two extremely handy TEXT-BOX properties are the CAN GROW and CAN SHRINK property. By setting these properties to YES on a text-box, these will tell a text book to grow and shrink vertically with regards to the the size of the data that they are displaying. This means … Read the rest