Access – VBA – Close All Open Reports

Similar to my previous couple of posts:

Access - VBA - Close All Open Tables
Have you ever needed to close any and all open tables? Ensure that no tables are still open? It is remarkably easy to do!  
Access – VBA – Close All Open Queries
Continuing down the same path as my previous post: I thought I’d share a simple function to close any and all open queries. The concept is the same in that we loop through all the queries (CurrentData.AllQueries), check to see if each one is loaded (IsLoaded), and if so, close them (DoCmd.Close).

I thought I’d present a function to close all the open reports within a database.

The code is slightly simpler that the 2 previous posts as we can use the Reports property which returns a list of only the open reports.  Thus we don’t need to validate whether the report is loaded or not.

Continue reading →

Access – VBA – Close All Open Queries

Continuing down the same path as my previous post:

Access - VBA - Close All Open Tables
Have you ever needed to close any and all open tables? Ensure that no tables are still open? It is remarkably easy to do!  

I thought I’d share a simple function to close any and all open queries.

The concept is the same in that we loop through all the queries (CurrentData.AllQueries), check to see if each one is loaded (IsLoaded), and if so, close them (DoCmd.Close).

Continue reading →

Access – VBA – Convert ControlType to Name

If ever you created code to loop over controls to identify their type (.ControlType), if you’re like me, at times, you wanted to report the data in English, not as numbers.

It’s nice to know the ControlType is 104, but what the hell does that truly mean?!

So then you go looking through the Object Browser and eventually review the AcControlType Enum to find out that 104 = “Command Button”.

Now, while it is pleasant to do this manually every time, we need to automate this for our sanity.

Need not worry, we can simply create a simple function like:

Continue reading →

Access – VBA – Find Images In Forms and Reports

Experienced developers know that we always want to minimize the file size of a database so it loads faster.  We always want to squeeze the most performance out of every element and also ensure we never near Microsoft Access’ 2GB file size limit.

To that end, whenever I am asked to look over a database, one aspect I always review is the use of images.  Novice developers often insert images without first resizing them for their use within the database.

For instance, I’ve shrunk a db by over 250MB just by resizing 3 images! In that specific instance, I reduced the database by 1.2GB once I had properly optimized all the images within the database. So this can have huge implications.

Continue reading →

VBA – Determine Your PC’s IP Address

A while back I posted regarding retrieving the IP address of a host/domain:

VBA - Get Host IP Address
Once again, trying to help out in a forum discussion in which the user was wanting to retrieve the IP Address of a host name. The trick here is to know that there exists an ancient DOS (oh yes, that ancient beast) command that enables one to easily retrieve such information, the nslookup command. So…

Continue reading →

 

Today, I thought I’d share a couple procedures to get information regarding the local PC’s IP addresses:

  • Local Network IP Addresses
  • Public IP Address

 
Continue reading →

Microsoft Access – Table Field Captions

In this article I want to touch upon Field Captions. Why you should Love them and Hate them!

 

What Is A Caption?

The first thing we need to do is understand what the caption property is exactly:

The label text that is displayed for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Microsoft

Continue reading →

When A Macro Is NOT A Macro!?

I ask you:

When Is A Macro NOT A Macro!?

Always, and Never!

The first thing we need to ask is:

What is a Macro?

And that is a loaded questions, as it depends!  It depends on the application we are talking about!
 
Continue reading →

VBA – Storing Information In The Registry

How can we store information:

  • Settings
  • Preferences
  • Object States and positions
  • …

for a VBA solution, whether that be an Access database, Excel workbook, Word Document …?

What about storing the information in the registry!

That’s the subject I would like to broach in this article.

Continue reading →

Microsoft Access – Get Object Created Or Modified Date/Time

Microsoft Access - Object - Created and Modified Dates

Getting the Creation Date/Time

So have you ever tried to retrieve the Created Date/Time. Pretty easy right as there are a couple ways to attack this!

You can simply query the MSysObjects table:

SELECT MSysObjects.DateCreate
FROM MSysObjects
WHERE (([Name] = 'Form1') AND ([Type] = -32768));

Or perhaps use DAO Containers and Documents:

CurrentDb.Containers("Forms").Documents("Form1").Properties("DateCreated").Value

 

Getting the Last Modified Date/Time

But what about the Modified Date/Time?

So you would think that we could simply modify the above query of the MSysObjects table to:

SELECT MSysObjects.DateUpdate
FROM MSysObjects
WHERE (([Name] = 'Form1') AND ([Type] = -32768));

Or the DAO Containers and Documents to:

CurrentDb.Containers("Forms").Documents("Form1").Properties("LastUpdated").Value

But those are not reliable and don’t always work properly! (especially for Forms and Reports). Instead, they seem to return the Created Date/Time again.
 
Continue reading →