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 you want the subreport control to be not visible should it have no records, this is one option you can use.

Private Sub Report_Activate()
If Me.MySubReport.Report.HasData = False Then
Me.MySubReport.Visible = False
Else
Me.MySubReport.Visible = True
End If
End Sub

Report.hasData is the key!

Many solutions on the Internet talk about using code like this in the IF..THEN expression:

Me.SubReport.Form.RecordSet.RecordCount = 0

Of course, that won’t work (we’re working with reports not forms and Microsoft inform us that the recordset object isn’t available in reports. If you try use the above, Access will keep throwing you errors like:

This feature is not available in an MDB.

I’ve tested this in Access 2007 and it seems to work fine.

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 “lblGenericTextBoxLabel” with the string “THIS IS A LABEL” in the caption property of the label.

Now, what if you want to get certain values of that label in your VBA code? For instance, lets write the caption of the textbox label to the screen. Easy-peasy!

MsgBox Me.txtGenericTextBox.Controls(0).Caption

This will display the caption of a label associated with txtGenericTextBox. As such, you can access a host of individual attributes of the textbox’s label using similar methods.

How is this useful? Well, in my case, I’m developing some field validation for a data entry form for a beforeUpdate event. I’m looping through all the controls on a form that have the word “Required” in their TAG property. An example of similar code is shown over at DatabaseDev.co.uk (article link here).

But I want nice ‘user friendly’ error messages. I don’t want the user seeing ‘you haven’t entered proper data in the field txtGenericData’ – I want it to use the nice clean label caption which reads much better.

There may be many more easier ways to do what I’m doing, but this seems pretty straightforward. Hopefully it is of some assistance.

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 end of the procedure along the lines of

myWrk.close
set myWrk = nothing

This is were I went wrong

I was looping through a DAO recordset to check for conditions then make updates to a table. I wanted to make sure things didn’t screw up half way through so implemented some transactions, and then I started getting an error message

Run-Time Error ‘3420’:
Object Invalid or No Longer Set

Specifically when I tried to use the .moveNext method of my recordset.

I began to tear my hair out! Google talks about updating my jet version, but that wasn’t the problem at all – I knew that I had the most up to date Jet engine.

It took me some time and a bit of research but I figured out it was happening when I made a call to another procedure while inside the recordset. Looking through the code, and nothing was wrong. I even pasted the code of the sub-procedure into my main procedure (minus all the variable setting and clean up objects stuff) and it work. Something was up in my sub procedures.

It was eventually a comment in this article on transactions at Allen Browne’s excellent Ms Access tips websiteI was explicitly closing the workspace. This is apparently a no-no. I deleted the .close line of code, and voila, it all worked!

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’ or something along those lines. Sounds obvious but Jet gives you back a non-helpful error message.

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 one custom filter control on a form. Occasionally you may want to have multiple custom filter controls on a form, and then it gets slightly more complex. Using code similar to the preceding example will not preserve previous filtering choices – i.e you filter on one control, then select a new filter control and select a choice, the new filter control will filter the entire recordset and not the results of the previous filtered recordset (apologies for this explanation – it may sound rather confusing)

However, I’ve come across a useful solution to this dilemma that I wish to share with you.

This article at thescripts.com contains a sub-procedure called checkFilter() which, if you download the example mdb linked from this page, and have a quick examination of the code, you’ll realise that the checkFilter procedure is called during the ‘after update’ events of several custom filter controls based on a form. What this does is every time the user ‘does’ something with one of these custom filters, the procedure rebuilds the entire filter, and applies it to the form. This turns out to be REALLY useful.

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 that the report will add extra vertical space for the control on the detail section of report, without upsetting any other controls that may sit adjacent to the particular control.

In my experience, these two properties can save the developer a lot of time and grief when trying to set out a report in a neat and readable fashion.