Microsoft Access





Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Share tech news, updates, or what's on your mind.

Sign up to Post

In one of my projects I needed to display several forms of the same type beside each other. It was a kind of a dashboard showing information about one project per form. One approach could have been to display several subforms on one main form -- but the problem was that the output should be shown on a video projector with as much as possible usage of the whole form and the number of projects to display should be variable, from one to five.

So, my first idea was to work with what Access does use itself: A function to tile windows horizontally or vertically which is possible with simply calling these functions:

    DoCmd.RunCommand acCmdTileVertically 
    DoCmd.RunCommand acCmdTileHorizontally

Open in new window

works fine and one could be satisfactory -- but there are some issues with these functions:

it tiles in the desired direction only up to three windows; with more it can happen that Access decides itself if the next window will be tiled vertically or horizontally

the border setting of the forms must be "sizeable"; otherwise these functions doesn't work correctly and with "none" they doesn't work at all

the form mustn't be a popup form; such forms are not affected by these functions.
So in the end I needed to write an own procedure to tile windows horizontally or vertically and additionaly use the maximum window size.

The attached demo database file shows the result. The form …
LVL 62

Expert Comment

by:Jim Dettman (EE MVE)
<<English is not my native language so sometimes my sentences may be a little bit wrong...:-)>>

 Never need to apologize for knowing more then one language I always say!

<<The forms were opened on my second screen as I  mostly work with two screens.>>

 Ah, that explains the blank screen; never looked at the top and left properties to see where it was set to display.   So it was there, just off screen.

<<I also attached a version here which now is in the format A2000-A2007 so it should be possible to open it with all Access versions. It would be nice if you could delete the original attachment and exchange it with this one.>>

 I don't see an attachment on your comment.  Once we get that squared away, I do not see any reason why we cannot go ahead and publish this.

LVL 15

Administrative Comment

by:Eric AKA Netminder
Congratulations, Christian!
Level -- Beginner

Check boxes on Access forms are used to indicate discrete true/false information such as "is Employee", "is active", "Send Newsletter", etc.  As such, they are bound to Yes/No (boolean) fields in the underlying tables, which are displayed as True/False, Yes/No, Checked/Unchecked or -1 (true)/ 0 (false).

Occasionally however, users are interested in a third state in addition to true/false values, representing non-commited/don't know/don't care values.  For example, in collecting voting information, in addtion to Yes or No votes, the fact that a participant has not voted yet may be important.  In such a case, preserving that state as Null is important. Triple-state check boxes are designed for this type of data entry.

Access checkboxes by default are two-state (true/false).  However, there is a triple-state property located under the Data Tab in the checkbox's property sheet (see Figure 1).   With the triple-state set to YES, a checkbox can accommodate true/false and NULL data. True appears checked, False appears clear and NULL appears 'grey'.
Figure 1: Triple state property setting
Piece of cake, right?  

There is one small catch, however.  The Yes/No datatype which we typically bind Checkboxes to in Access does not allow NULLs.  It is a special case of a numeric data type which only allows 0s and -1's.   In Access 2003, you can set the 'Required' property of your Yes/No field to NO - but your triple-state checkbox will still only toggle between …
LVL 61

Author Comment


The columns in the last figure simply illustrate table data generated from a two state checkbox (which saves binary data with no nulls), versus data generated from a separate three state check box (integer with Null, -1, 0, 1).  Despite the column headings being the same (confusing, in retrospect), the controls and columns are independent.
LVL 61

Author Comment

I could have sworn the sample database from which those screenshots were taken was originally attached to this article.  There was a bug in EE articles many moons ago which caused file uploads to go missing.  I wonder if that happened here?
If you are using DoCmd.TransferSpreadsheet to link to a file, you may encounter problems with the data types that Access 'guesses' once it has linked the file as an attached table.

Look at the example below:
Example dataWhen this file is linked to Access, the SedolCode column is considered to be a number, and as a consequence there are #Num errors because it cannot process 'B051Y83' as a number:
Error importing dataThe registry folder My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel contains a value called TypeGuessRows. This indicates how many rows that Access will use in a linked file to determine a data type for a column:
Registry entry for TypeGuessRowsAs it is unlikely that you will be changing registry settings on a user's PC to overcome this issue, the alternative is to insert eight (or enough to match the registry setting) rows of dummy values that match the data type that you need:
Dummy values to 'fool' TransferSpreadsheetNow when you link the file, Access will use the datatype of the dummy values you have inserted:
Data successfully importedYou can then either delete the 'dummy' records or ensure that your queries exclude any 'dummy' values.
LVL 26

Expert Comment

Myself, what I do, is create a dummy file of just a single row that has 'correct' data in each column.
I link to that, so the link table is all good.
I then use FileSystemObject to replace the dummy file with the real one.

When Access goes to open the linked file, it's all good.
The problem in using the built-in Excel generator (TransferSpreadsheet) from Ms Access is that you cannot apply formatting to the cells in Excel during export. The workaround is to create a template first in Excel to use as filename parameter in TransferSpreadsheet. But what if you are generating a dynamic data specially from a crosstab query (variable column).

To solve that, here is a sub procedure I've created in VBA. This is an alternative way to generate dynamic data from MS Access to Excel with some cell formatting.

You can use a Table or non-action Query (such as Crosstab and Select) as your dataSource.

for example you have a crosstab query named as "tablex", with an output as shown below:
tablex - crosstab query
by calling this subprocedure in VBA or from any control's events (such as command button's click event):

call ExportDataToExcel("Tablex")

it will generate an excel file named as Tablex.xls (or Table.xlsx, depending on what version of Excel you are working on). When you open the excel file, the output should be similar to the figure below:
sample excel output

Now here's the code:

In VBA editor (Alt+F11), create a new module then copy the code below.

Sub ExportDataToExcel(dataSource As String)
    'Requires Reference to Microsoft Excel Object Library
    On Error GoTo errExportDataToExcel
    Dim rs As Recordset
    Dim Xrow, Xcol, rowCtr As Integer
    Dim ObjXL As Object
    Dim objWkb As Excel.Workbook
    Dim objSht 

Open in new window


Expert Comment

Nice Article!

The function (xlLastRow) at the link below is also very helpful for Excel automation.

Expert Comment

Great article. I was able to create my spreadsheet. I would like it to open each cell to maximum how do I do that? I have some wide date time fields. Also How do I use code to open the spreadsheet? Thanks for any help.
Very often you need to display images on continuous forms. It could be photos of employees or goods, or other images. You can use attached OLE objects, but they are very whimsical.

You can try the sample in this article:
but it is very dependent on your system settings and will not work consistently.

Access 2007 and 2010 give us a modified Image control, which is able to display external images from file paths saved in our records. Advantages of this method are obvious: DB will not grow with each new image, images could be edited and replaced independently.

Let us assume we have a table with a text field containing the full path to an image: tblImagesCreate a continuous form with this field: frmImagesAdd an Image Control to the detail part of your form: image controlWhen asked about the file name, press Cancel.
Open the Image Control's properties:
Image control propertiesYou should set the Control Source to the field with path to images:
Control SourceResult you can see immediately:
Now we can make some improvement.
It is a good idea not to store the full path to the image, only the relative path. Using the relative path we do not need to change every record when moving the DB to a new folder or network path. We can add field to our form with path to images (of course it could be field in a table with DB parameters).
If field's name is "path", we can modify control source of our Image Control:
LVL 10

Expert Comment

Can this procedure be used to display images from a website? (Note: I know the image paths won't change, because it's my website.)
LVL 41

Author Comment

You can't use http://.... path, only some local (d:\....) or network(\\server1\sharedfolder\....) paths. You always should download files from website before you can show it.
Level: Beginner-intermediate


We see the occasional question in the Access area which at its core is about the difference between what is seen in a control on a form and the underlying value in that control.  This article,  and the attached sample show the differences between the two.


Text ... Value ... OldValue

Unlike other platforms where textbox controls only have a Text property to get or set the data, Access textboxes and combo boxes have several such properties – the Text property, the Value property and the OldValue property.  

What's seen in the UI...

The Text property gets or sets the data as it is seen in a control.  This changes real-time, as the user enters data.  


Open in new window

This property can only be used when a control has the focus (as in the Change Event), and will cause errors when used at other times.
Must have focus...
... Is not necessarily what is currently in the tables

In a bound control, the Value property gets or sets what is stored in the underlying field (Control Source) in the table or query that defines the Record Source of the form or report.   The Value property is the default property for data entry controls, and can be referenced either implicitly or explicitly:

Me.txtMyTextbox   '<- Implicit reference to the value property (this is more common)
Me.txtMyTextbox.Value  '<- Explicit reference, meaning the same thing

Open in new window


Expert Comment

by:Patrick O'Dea
I just got around to reading this article now (5:40 am!).

Very useful stuff.  It addresses many problems that I have had in the past.

I will re-read .... often!
LVL 61

Author Comment

Thanks for the feedback - I'm glad you found it useful!
There are many things a user does with an application that need to be preserved either during processing, between screens, between sessions, or between application updates/versions. When designing a system, it's important to consider what needs to be kept and where/how to do this. If designed properly, the data should also support multi-user environments.

Users are commonly annoyed to be forced to re-enter their last specifications when the application should start with that as its default. After all, a computer is supposed to be good at remembering things, right?

Keeping Selections in Memory for the Current Session
At the simplest level, the user's settings and can be stored in memory as global variables in VBA. These are temporary and will disappear when the application closes. However, while it's open, the program can default to those values if they should be used again.

Temporary variables, TempVars, introduced in Microsoft Access 2007 can also be used and referenced via macros.

Using the Registry to Store User Information Between Sessions
Another way to save user preferences is to store it in the user's Windows registry. This lets you store data on a machine specific to the user for your application. It's not appropriate for saving large amounts of data that you would expect in a table but helpful for user selections. VBA offers a few simple commands to manage registry settings:

GetSetting(appname, section, key[, default])
This article is part 3 of a 3-part series:

Introduction to VBA: Part 1  went over the basics of the VBA language and development environment.
Introduction to VBA: Part 2 took the VBA language to the next level.

This article, Introduction to VBA: Part 3 covers the more advanced topics not included in the previous articles including a discussion of the DoCmd object, built-in functions, practical applications, and more.

The DoCmd Object: Performing Macro Actions

The Access environment is rich with objects that have built-in properties and methods. By using VBA code, you can modify the properties and execute the methods. One of the objects available in Access is the DoCmd object, used to execute macro actions in Visual Basic procedures. The macro actions are executed as methods of the DoCmd object. The syntax looks like this:

DoCmd.ActionName [arguments]

Open in new window

Here’s a practical example:

DoCmd.OpenReport strReportName, acViewPreview

Open in new window

The OpenReport method is a method of the DoCmd object; it runs a report. The first two parameters that the OpenReport method receives are the name of the report you want to run and the view in which you want the report to appear (Preview, …
LVL 61

Administrative Comment

An enthusiastic 'yes' vote for this article - this and its companion articles are a great series for anyone getting started in VBA programming, and also a good read for proficient programmers.

EE Page Editor
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open on the left monitor, and the main application window open on the right monitor, and you now open the app on a single monitor machine, the resulting popup form will be 'smushed' to a width of zero--and therefore be invisible

What to do?

Well, you can first put some code in the Form_Open event to move the form to the upper left (some arbitrary position (0,0) ) of the work area and set a minimum size to get around any 'smushing'

Private Sub Form_Open(Cancel As Integer)

With DoCmd
    .SelectObject acForm, "myformname"
    .MoveSize 0, 0
End With

Me.Width = 6.5 * 1440 '6.5 inches wide for the window
Me.InsideHeight = 2.125 * 1440 '2.125 inches high for the window working area
Me.InsideWidth = 6.25 * 1440 '6.25 inches wide for the window working area

end sub

Open in new window

Which is okay as far as it goes.  But what if the boss has a triple monitor setup, and opens the app on the right-hand monitor.  Then that popup form goes waaaaaaaaaaaay left,  which is annoying.  Ideally, you'd like to be able to position the popup form in relation to the main application window.  But how?

Enter some Windows API code!
Put this snippet in a code module

Option Compare Database
Option Explicit
Public Declare Function 

Open in new window


Overview: This article:

      (a) explains one principle method to cross-reference invoice items in Quickbooks®
      (b) explores the reasons one might need to cross-reference invoice items
      (c) provides a sample process for creating a Microsft® Access cross-referencing combo box using QODBC

What is QODBC?

QODBC is an ODBC (Open Database Connection) driver for reading and writing QuickBooks® data using SQL (Structured Query Language). Anyone with fundamental VBA knowledge should find it easy. Copy and paste codes exist on and other sites for beginners

Do I Need QODBC and Microsoft® Access to Cross Reference Invoice Items?

No. The article explains a princple. Any QuickBooks® connection and database may use this principle to produce the same results. QODBC is only one way to import data into the database. Microsoft Access is only one database to import into.

Previous Related Article

A previous article, Invoice Extensibility with QuickBooks®, Microsoft® Access and QODBC, explained how customized invoice programs can:

      1. speed invoice entry
      2. reduce user input error
      3. facilitate cross-referencing of items among customers

Why Cross Reference?

Cross referencing items among customers is generally required by contractors who:

      1. invoice the same items for all customers and;
      2. work for different customers that;
      3. use …
This process can be altered to use any database and ODBC because it is a principle and process. However, the sample code in this article uses:

      1. Microsoft Access and DAO
      2. QODBC
      3. QuickBooks

The sample code demonstrates the process and lets you see the it at work.

Problem #1:  Insertion

Not all InvoiceLine fields are insertable. To confirm this for yourself:

      1. open QuickBooks
      2. open VBDemo that came with QODBC
      3. establish a connection to QuickBooks in VBDemo
      4. type:
            sp_columns invoiceline      
      5. click the Query button
      6. scroll down to the customfields
      7. use the horizontal scroll bar to scroll across to the Queryable, Updateable and Insertable columns.
      8. note that some of the custom fields are updateable but not insertable

Question: How do you insert data into non-insertable fields?

Answer: Insert followed by update using the following process:

      1. retrieve the TxnID of the Invoice you want to add lines to
      2. use the Invoice TxnID to insert data to insertable fields
      3. insert only one invoice line at a time
      3. before inserting the next invoice line, use the same TxnID to retrieve all the invoicelines into a query
      4. retrieve the last record from the query--this is the last inserted InvoiceLine
      6. use the InvoiceLineTxnLineID from that last record to update the non-insertable yet updateable fields of the
In the article Introduction to VBA: Part 1, you learned many of the basics of the VBA language. We began by discussing why the VBA language is important to you as an Access developer. You then learned all about the development environment and how to create event procedures. You also learned how to create and work with user defined procedures. In this article we take your use of the VBA language to the next level. You first learn how to work with variables. You then learn how to add comments and line continuation characters to your code. Finally, you will explore how the various control structures available in the VBA language help you to effectively branch through your code.

Working with Variables

You must consider many issues when creating VBA variables. The way that you declare a variable determines its scope, its lifetime, and more. The following topics will help you better understand declaring variables in VBA.

Declaring Variables

There are several ways to declare variables in VBA. For example, you could simply declare x=10. With this method of variable declaration, you really aren’t declaring your variables at all; you’re essentially declaring them as you use them. This method is quite dangerous. It lends itself to typos and other problems. If you follow the practice recommended previously, of always using the Option Explicit statement, Access will not allow you to declare variables in this manner.

You could also type Dim intCounter; the …
LVL 38

Expert Comment

Another great Article from this author.
Her work is always worth reading.

"Yes" vote above.
LVL 61

Administrative Comment

Awarded Editor's Choice

EE Page Editor
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic.

I don’t intend to describe all of the uses of temporary tables in this article, but I would like to offer up a function I wrote several years ago as an efficient way to create temporary tables (either in the application front-end or in an external database, linked to the application front end).

Reasons for temporary tables

There are a number of good reasons for using temporary tables in your application, but the five that were important to me at the time I wrote this function were:

1.  Speed up queries.  This also includes cases where I wanted to replace the use of the inherently slow IN ( ) clause in a SQL query.

2.  Reduce network traffic. Pulling information that rarely changes across the network multiple times during a session just slows things down.  I do this for lookup tables that contain data that rarely change but which I use in combo boxes and lists.

3.  Stage data generated by long running queries. When you have multiple operations that use the same or similar information, and the query that generates that data takes a while to run, it is useful to stage that data in a local table.  You can update this information at regular intervals if need be.
4.  Provide user specific options for selecting various form and report display options

Expert Comment

I was able to get around it by increasing the record lock limit on the windows 10 laptop.  I inserted this line of code right after the On Error statement in your function:

DAO.DBEngine.SetOption dbMaxLocksPerFile, 50000

Apparently that temporarily ups the limit from 9500 just during the execution of the function.  There are several tables made, and the process runs about 1/2 the speed it does on my laptop.  We are both using Lenovo T450 laptops - same processor etc. just different Windows versions.  Well it least I got it to work.  Let me know if you discover anything.
LVL 62

Expert Comment

by:Jim Dettman (EE MVE)

  Bump that to 500,000....even 50,000 is way too low.


The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to the VBA language. It serves as a foundation for all of the development that you do. After reading the article, you will be familiar with the development environment. You will know how to declare and work with variables, create procedures, and determine the scope and lifetime of the procedures that you build.

VBA Explained

VBA is the development language for Microsoft Access. It offers a consistent language for application development in the Microsoft Office suite. The core language, its constructs, and the environment are the same in Microsoft Access, Microsoft Visual Basic 6.0 and earlier, Microsoft Excel, Microsoft Word, Microsoft Outlook (for application-wide programming), and Microsoft Project. What differs among these environments are the built-in objects specific to each application. For example, Access has a CurrentProject object, but Excel has a Workbook object. Each application’s objects have appropriate properties (attributes) and methods (actions), and, in some cases, events associated with them. This article gives you an overview of the VBA language and its constructs.

Unlike macros in Word or Excel, Access macros are not subprocedures in modules; instead, they are a different type of database …
LVL 61

Administrative Comment

Awarded Editor's Choice, along with "Part 2":

These are both great articles for novice Access programmers - and also good reads for more experienced programmers.  There are some valuable tips here.

EE Page Editor
LVL 11

Expert Comment

Ive been using VBA for over ten years and picked up a couple of small tips I never realised I could do! Fantastic article, very well written. Kudos!
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpful in getting your questions answered.

Posting samples however, comes with responsibility and risks.  Your employer and clients have entrusted you with their databases and their data, and it is up to you to use care and respect when sharing applications with others.

So what kind of data is safe to share?

As a rule, you should never, ever post Personally Identifiable Information (PII).  In a nutshell, this is any information that can be used by itself or in conjunction with other collected data to identify or locate another person.   This article lists specific types of information that fall into this category, and discusses the potential hazards of sharing it:

Conversely, a database of recipes may (*see note) be okay to share, as it does not compromise sensitive information about other people.

In addition to PII, you should take into account whether your database contains:
-      Classified information (enough said)
-      Proprietary or competition sensitive information
-      Anything else your employer or clients do not want shared

Also keep in mind that it is not just data in your tables that …
LVL 62

Expert Comment

by:Jim Dettman (EE MVE)
Very nice....something long overdue!

  Now we only need a "sticky" feature so we could pin stuff like this to the top of the zones.

LVL 28

Expert Comment

by:Glenn Ray
"Yes" vote from me as well.

Unfortunately in the MS Office zones, a lot of users don't check the properties of the sample files they attach.  Often,
their real name and company names are embedded in the data.  This article probably doesn't reach them.

When I post example file - either in questions or in reply to questions, I usually strip this information out as a precaution.
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the Page Footer so that the bottom of my report would always be the same design. Here is an example of what my printed report would come out as:

 Broken report
In the end, this is what I wanted to end up with;

 Finished report
Another issue was that I wanted my horizontal gridlines to run all the way to the bottom of the report regardless of whether there was enough data on the page to fill the detail section to the bottom or not.

I finally came to a function called MoveLayout. When you set movelayout to false, the section basically becomes suppressed and your other sections will print on top of that section just as they do in Crystal Reports.

In my example the best section for me to underlay to accomplish my goal was the page header section. By creating the page header almost the size of the entire page and then setting the On Format = to Me.MoveLayout = False, I could design the section which acts as the backround image for the entire page. In other words, I could draw up all of my borders and gridlines which would then display through the entire page without any breaks.

Most likely it will take a little time tweaking the exact alignment you are looking for as in my case I needed some white space at …
LVL 62

Expert Comment

by:Jim Dettman (EE MVE)
I think the major thing to walk away with from this is the fact that you can address the entire page in a header event and with .movelayout false, continue on down the page overlaying anything you've placed on it.

That's never been obvious to anyone before and opens up the door for a wide range of things.  For example, placing a water mark on the page.

I believe everyone up till now has always visualized this as the report engine walking down the page and not thinking you could do anything below the current layout spot you were at.

 If you were trying to do anything with the page, you always used OnPage, but now that's not the only option.

Definitely something new that was brought to light and one to keep in the bag of Access tricks.

LVL 74

Expert Comment

by:Jeffrey Coachman
No problem Buddy, I understand.

And to be fair, I do realize that you perhaps signed up here so that you could get "targeted" help, (not just Google Hits...)

The bottom line is that unless you are doing something fairly unique, they fastest way to an answer may be via Google.

Looping files in a folder, is like Filtering records by a form. is a fairly standard question.

Now,  ...if I wanted to do something really unique like "underlay a section in Microsoft Access Report"
Then I would post a question here, and hopefully someone may have some insight...

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®.

Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain unit names, quantities and prices. However:

1.  customers do not always use the same names as RUS nor the same names as other customers
2.  engineers do not always use RUS names or customer names on work plans
3.  foremen often use field terms that do not match either RUS, contract, customer or engineer

We are bound by contract to use the unit names in the contract.

Data entry technicians were responsible for cross-referencing. This was accomplished from:

1.  memorization
2.  manual cross-referencing

To add to the confusion, RUS changed their unit names in 2001. Not all customers adopted the new names and some of the customers adoped some of the new names but not all of them.

Relying on the data entry technician was no longer an option.

Using QODBC , a new invoice entry program was developed in Microsoft® Access that:

   1.  allows cross-referencing at unit-entry time and on-the-fly

   2.  calculates quantities by a user-input multiplier on-the-fly (Used for wire which is entered on the work plan as a number of feet that must be multiplied by the number of wires in a span.)

   3.  verbally warns the user of possible errors using typical error scenario algorithms

   4.  automatically fills in …


When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened.

If you wanted to inspect/iterate the Forms or Reports collection to determine of a form existed, you would only see those forms which had been opened.

Easy and Reliable Solution

The objects are listed in the MsysObjects table.  We can query the table, looking for a name and object type match.  For simplicity, I used a DLookUp() function.
Option Explicit

Public Enum ObjectType
    ot_Table = 1
    ot_AttachedTable = 6
    ot_Form = -32768
    ot_Query = 5
    ot_Report = -32764
    ot_Module = -32761
    ot_Macro = -32766
    ot_Relationship = 8
End Enum

Public Function ObjectExists(ByVal parmName, parmType As ObjectType) As Boolean
    If IsNull(DLookup("Name", "Msysobjects", "Name='" & Replace(parmName, "'", "''") & "' And Type=" & parmType)) Then
        ObjectExists = False
        ObjectExists = True
    End If
End Function

Open in new window

* Since an Access object name might contain an apostrophe, I have to use the Replace() function to double-up any apostrophe characters in the object name parameter.
* Since DLookUp() returns Null in a not-found condition, I have to use the IsNull() function.

Using the ObjectExists() function
As you will see in the example below, the function returns a boolean…
LVL 26

Expert Comment


I didn't grasp this sentence in your article the first two or three times I looked it over.
(That apostrophe is invisible unless you are looking for it, or know it is there.  The eye just misses it)
<<In the following trials, I know that a [BigTable] table object exists, but a [Big'Table] table object does not exist.>>
<<In the following trials, I know that a [BigTable] table object exists, but a [Big'Table] (with an apostrophe between Big and Table) table object does not exist.>>
Would be a useful edit.

Also, with the Enum's you show in the code, you don't explicitly state where those values come from, which make them a bit like magic.
Having looked in MsysObjects myself, I know that in the first code sample you made MsysObjects visible and then figured out what the numbers in [MsysObjects].[Type] mean.
In the container code, how did you derive the Enum values you show there?
LVL 48

Author Comment


Thanks for the feedback.  Maybe I should have used an Irish name so that readers would expect to see an apostrophe.

I found a partial list of MsysObjects type values during a web search.  I deduced the others by creating objects, such as a macro, in my test database and then seeing what appeared in the MsysObjects table.

I iterated the Container collections to determine the Enum values for Containers.
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  When you get a workable but not very efficient form with a lot of comboboxes working you can hear the muttering. "Did I pick all the items from that category already?  Did I skip a category?  Grr, this is a LOT of clicking.  And this job is ALMOST EXACTLY like the one last week!  Why do I have to go through the drudgery of this again and again?  Why can't I just reload this stuff from last week and change these two columns and delete that row?"  Designing a form to do such a job is not as simple as it seems on the surface.

You have data in tables, but you don't want to edit that data.  You want to present it to the user for consideration, editing and finally appending to tables as new records.  That seems like a job for unbound controls!  Unbound controls are the go-to solution for when you want to present data, but not edit it.  Bind a RowSource to a combo or list box, but don't bind a ControlSource.  Use VBA in events to pre-load textboxes.  Navigation and filtering controls get done like this all the time.  One big problem, though.  MS Access does not play nicely with unbound controls on continuous forms.  MS Access does not have control arrays like …

Expert Comment

Ah I see.  Thanks so much for clearing that up for me!   Great article!

Expert Comment

I have another question about this.  Is it possible to create a command button that would select all hazards listed in a category?  I'm guessing the command button would have to use a recordset but I'm still murky on how those work.
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can use the debugger to interact with your code as it is executing.

Invoking the Debugger

You can invoke the Access debugger in several ways:

     Place a breakpoint in your code.
     Place a watch in your code.
     Press Ctrl+Break while the code is running.
     Insert a Stop statement in your code.

A breakpoint is an unconditional point at which you want to suspend code execution. It is temporary because it is in effect only while the database is open. In other words, Access does not save breakpoints with the database.

A watch is a condition under which you want to suspend code execution. You might want to suspend code execution when a counter variable reaches a specific value, for example. A watch also is temporary; Access removes it after you close the database.

A Stop statement is permanent. In fact, if you forget to remove Stop statements from your code, your application stops execution while the user is running it.

Using Breakpoints to Troubleshoot

As mentioned, a breakpoint is a point at which Access will unconditionally halt the execution of code. You can set multiple breakpoints in your code. You can add and remove breakpoints as …
In the article entitled Working with Objects – Part 1, you learned the basics of working with objects, properties, methods, and events. In Working with Objects – Part 2, you learned how to work with object variables, collections of objects, and how to pass objects to subroutines and functions. As with the techniques covered in Parts 1 and 2, the techniques that you learn in this article make you much more powerful as a VBA programmer.

Understanding Access’s Object Model

Now that you’ve learned the concept of objects, properties, methods, and events in a general sense, I’m going to switch the discussion to the objects that are natively part of Microsoft Access. Databases are composed of objects, such as the tables, queries, forms, reports, macros, and modules that appear in the Navigation Pane. They also include the controls (text boxes, list boxes, and so on) on a form or report. The key to successful programming lies in your ability to manipulate the database objects using VBA code at runtime. It’s also very useful to be able to add, modify, and remove application objects at runtime.

The Application Object

At the top of the Access Object Model, you will find the Application object, which refers to the active Access …
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from your application?

Naturally, the unnamed search engine applies very advanced technology, using massive pre-indexed tables with complex relationships, and search statistics. This isn't the topic of this article. The search used here is deliberately not optimized and is only fitting for very small tables (let's say below 10'000 records).

Instead, the present article focuses on the user interface. A combo box is used to show the results of the current search, and is updated “on the fly”, while the focus remains in the control. This allows to narrow down the search until the desired information is located. Note that this is different from the built-in auto-complete feature, which works only if the user always types the first letters of the information displayed in the combo.

The specific “while you type” aspect might not be suitable for every application. However, if that option is turned off, what remains is a more classical search box, but using the combo's drop-down section as search result window. This technique is very well received by users, even if they have to press Tab in order to see the result of the search. So, even if that aspect isn't what you are looking for, you might still be interested in the …

Expert Comment

S U P E R B !!!!!!!!!!!!!!!!!!

you're amazing harfang!!!!! = )))

Expert Comment

very, very usefull ....
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather which page to read? At the time, there was no “intellisense”, so it could take me a couple of minutes to find for example “.ItemData()”, even if I had used it before.

Another more obscure problem was that I could never trust myself to use the right indexing in the right property. I would type “column(2)” with hesitation and an urge to check if shouldn't be 1 or 3 instead. I learned the cause much later. Row indices are zero-based or one-based depending on the property used; column indices are also inconsistent when column headers are involved. Before I became full aware of this fact, I had simply learned intuitively that, whatever indexing I used, it was wrong every other time.

In this article, I dissect a combo box and show when each indexing is used, and for what reasons. Properties are presented by theme, making them easier to remember. They are then used to perform some typical tasks, in a “how to” section.

Anatomy of a List

In the text below a list is simply the full content of a combo or list box, including any hidden columns. I will use list box if I want to designate specifically that type of control.
combo box and data tableThe innocent looking combo box at the left can contain an entire table

Expert Comment

thanks harfang for this fantastic article! this is my favourite article after Understanding the ComboBox because it clarifies so many things and allows us to FINALLY use VBA with ComboBoxes. to build reuseable function to handle header and non-header situations.

the 1st picture you showed showing how Access numbers the rows and columns is FANTASTIC. that can already sum up so much. really, really, fantastic.

thanks so much harfang!! = ))
This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event.

A form does not expose it's calculation dependencies chain. It exists — you can see calculated fields and controls being updated automatically — but this doesn't trigger any event. When we want to run some code (for example conditionally enabling a command button), we often need to trap one or several `after update´ events.

This article shows that a user defined function can be called automatically whenever Access decides to recalculate a control, in effect hooking into the dependencies chain, and thus creating an “on recalculate” event handler.

The Dependencies Chain

When a form is opened, Access will examine all calculated controls and build a hierarchy, specifying how controls depend on one another. This way, when you change the value of Text1, it will know that the control with “=Text1*2” needs to be updated, and possibly others depending in turn on that control.

It is relatively easy to obfuscate an expression so that Access will not catch the dependency. If you create a simple form (called Form1) with three text boxes (the first being named Text0) try these expressions in the next two text boxes:

Notice that the second control will be updated immediately, while the third requires you …
LVL 12

Expert Comment

Good stuff! I'll be using this today in a client's application.

Expert Comment

Very nice article! Thanks a lot.

Unfortunately the trigger does not work at all when the form is switched to the datasheet view.
When switching back to form view it does not work also any more (or lets say not always (it's wired)). Thats really strange...
Do you have any explanation for that behaviour or maybe a solution?
I often find myself explaining how a combo box works. Naturally, every book on Access has a chapter dedicated to them, and many good resources exist on the Web. One more article about them is probably redundant, but hopefully not entirely useless! However, I do not intend to cover all the basics here.

The help file installed with Access has always been quite complete, and easily accessible directly from each property. For example, if you want to know what the number in the bound column property means, please press [F1] and read that page first. I will not assume that you have read the pages of all properties, but at least that these pages are available when my explanations are lacking or not detailed enough.

In this article, I will try to explain the basic mechanics of a combo box, warn about some tricky problems one might encounter, show a few special tricks to enhance the user interface, and sketch out an overview placing each aspect of the combo into a better perspective. It serves as introduction to a series of articles covering various aspects and uses of the combo box, listed at the end.

Why is it called a ComboBox?

The control combines two features. It is a drop-down list presenting a list of available choices to the user and also a text box allowing regular editing. If the user can only select something, the editing feature is still useful for the auto-complete feature allowing a record to be found by typing …

Expert Comment

Great article Markus! Especially the explanation of how the combo boxes work behind the scenes for conversions! = )

Microsoft Access





Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.