Darragh – Page 10 – Darragh Murray

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

October 27, 2008January 12, 2010DarraghMs Access Tips, VBALeave a Reply

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

September 18, 2008January 3, 2017DarraghMs Access Tips

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

February 3, 2008January 3, 2017DarraghMs Access TipsLeave a Reply

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

January 10, 2008January 3, 2017DarraghMs Access Tips, ReportsLeave a Reply

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

Ms Access 2003 Bug – Crash after Compact/Repair & Autonumber Resetting

October 30, 2007January 3, 2017DarraghMs Access Tips3 Replies

Two little tidbits of information today. Firstly, a bug that occurs in Access 2003 after you try run the Compact/Repair function. This is a really annoying bug that seems to occur randomly in Access 2003. What happens is that for one reason or another your database corrupts, and when you try open it, Access tells you it must be repaired, you hit OK, then Access tells you that an error occured (abliet with very little detail other) and must be close. You close, try reopen, and you run into the same stumbling block – the database must be recovered … Read the rest

Ms Access – Cannot make MDE file error

October 17, 2007January 3, 2017DarraghMs Access Tips

Well, as usual, I published Access tips in response to problems that I come across, and get around, during my day-to-day Ms Access development.

As we know, Ms Access ain’t exactly the most stable product ever made and there are many bugs which often occur with badly explained non-sensical error messages.

Today, I kept getting an error that went something like “Cannot make MDE, ACCDE file”. This was strange. I could make the MDE about 5 minutes before I had made some minor edits to control names on a report. Why could it suddenley not make an MDE (with no … Read the rest