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

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

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

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

Quick Tip – Conditional Formatting on Ms Access Reports

This is one little annoying problem that there is not enough obvious documentation for within Ms Access 2007. While it might seem obvious enough, at the time, it wasn’t to me (so who is to blame, my lack of knowledge, the software interface, or the help documentation?).

I was developing a report in Ms Access and setting all these conditional formatting expressions and values. When I was running the reports, the formatting conditions were seemingly not working. I ran my mouse over some of the textboxes, and noticed that the background colour was changing as per my formatting rules when … Read the rest

Access 2007 After One Hour – Initial Highlights

I’ve been using Access 2007 for about an hour now, quickly developing a small database to store information on what periodicals I’ve been reading for my thesis, and when I have accessed them. Quick review – it is fairly impressive! Highlights are:

  • The Ribbon Gui common to all Office 2007 products is used very well in Access 2007
  • Graphical Calendar Lookup for Date Fields.
  • Split View Forms – Graphical Form at top, Datasheet at the bottom.
  • Ability to standardised formatting of controls on a form, meaning left, top, width and height values are consistent for groups of form controls! This
Read the rest