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

By daz | Published: October 27, 2008

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 website – I was explicitly closing the workspace. This is apparently a no-no. I deleted the .close line of code, and voila, it all worked!

Post to Twitter Tweet This Post

Posted in Ms Access Tips, VBA | Leave a comment

DCount – “You canceled the Previous Operation” error

By daz | Published: September 18, 2008

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.

Post to Twitter Tweet This Post

Posted in General | Comments closed

Argh! Wordpress post.php returns blank page…solution.

By daz | Published: July 8, 2008

Yes, wordpress is being annoying.

I was having a problem with publishing new posts after upgrading to the latest wordpress. I would write a post, hit publish, and the post.php would appear, BLANK. No real explanation was given. I did notice that the post I had selected to publish turned up as a draft. Trying to turn these ‘drafts’ into ‘published’ articled did the exact same thing.

After a bit of an extensive search for this problem, I came across a solution on this wordpress forum discussion. I thought I’d repost what solution worked for me (indeed it was one written in the preceding link, but I’ll summarise). It seems to be either a) a database problem or b) a plugin problem. Try the following (** though I take no responsibility if you suddenley destroy your wordpress install and/or database **)

1. Back up your wordpress database before doing anything. I repeat…BACKUP.
2. Hopefully, you are using phpmysqlAdmin (it make this easier). Locate your wp_settings table.
3. Find an entry called active_plugin field, with a value similar to “a:1:{s:5:./././././././././././././././././././././tmp/upl52653.jpg” or something.
4. Delete it.
5. Get out, save, and try make a post.

Not a great explanation, but read through this forum post

Post to Twitter Tweet This Post

Posted in General | Comments closed

A useful fashion to code multiple filters on an Access form

By daz | Published: February 3, 2008

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.

Post to Twitter Tweet This Post

Posted in General | Leave a comment

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

By daz | Published: January 10, 2008

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.

Post to Twitter Tweet This Post

Posted in General | Leave a comment

Windows cannot access the specified device, path or file. You may not have the appropriate permissions

By daz | Published: November 14, 2007

Argh! At work today a computer took ill. An XP SP2 Box, which had been working faithfully for the past 3 years, had suddenley decided to disallow its users from accessing any program executables through the desktop and via the control panel. This problem started occuring while I was away, so I wasn’t actually present to diagnose straight away. I could access some (generally non-microsoft) applications by forcing the program to run as an administrator, despite the fact that I was already logged on as an administrator.

It seems this error is quite common in XP land (as evidenced here), and I spent about two and half hours trying to solve it by following various suggestions posted around the internet.

Turns out, it was the virus protection – Trend Micro PC Cillan 2007. Something occured during an update of this application, and suddenley it messed with all the permission settings. So if you’re stuck on this error, try give this a go first.

Post to Twitter Tweet This Post

Posted in General | 2 Comments