Tag Archives: MS Access

MS Access – VBA – The Save Operation Failed

I was recently working on a database of mine which has been in production for over 4 years now and all of a sudden it started giving me the error: “The Save Operation Failed”. Nothing like a nice obscure error message from the VBA/VBE!!!

I tried the normal approaches: Compact and repair, Decompile/Recompile, … None worked for me.

I took a look at Microsoft’s Knowledge Base and found an article, ACC97: Error “The Save operation failed” When Saving a Module, for Access 97 (as a lot of the information can be used in future version, I read it over). Sadly, the article is pretty much useless.

In the end, the solution, for me, was to start a new database and import all of the database objects (File -> Get External Data -> Import) from the database giving me the error message. Setup the startup properties… and everything was in working order again!

I hope this helps someone else in the same predicament!

Microsoft Access Database Add-ins, Tools, Etc.

One thing any good worker will tell you is that you must have the right tools to do the job. Computer programming, MS Access database development, is no exception to this rule!

I thought I’d try and list a few add-ins and other useful tools that I have come across, or heard of, in the hopes it could prove useful to other developers.

This is an unbiased listing and I have no financial gain from any of the tools listed below. I am simply trying to regroup them to help you find them, and you can judge their usefulness on your own. In a future post, I will examine the 2 or 3 that I use myself and find very useful.
Continue reading →

MS Access – VBA – Loop Through All The Controls on a Form

Below is come sample VBA which illustrates how one can easily loop through all the controls within a form to identify and work with them.

Dim ctl As Control
For Each ctl In Me.Controls
    ctl.Name 'Get the name of the control
    ctl.Value 'Get or set the value of the control
    ctl.Visible = False 'Control the visibility of the control
Next ctl

Now how can this be put to good use? Well one use for such code would be to setup a Select All, or Select None button for a series of check boxes on a form. Below is what the code could look like for each command button:

'Select All
Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acCheckBox Then
        If ctl.Value <> True Then
            ctl.Value = True
        End If
    End If
Next ctl

'Select None
Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acCheckBox Then
        If ctl.Value <> False Then
            ctl.Value = False
        End If
    End If
Next ctl

Lastly, you could easily adapt the general form specific code and transform it into a generic procedure to which you will supply the form name to loop through the control, rather than working with the current form, you could do something along the lines of:

Function YourProcedureName(ControlName As String, frm As Access.Form)
    Dim ctl As Access.Control
    For Each ctl In frm.Controls
        

    Next ctl
End Function

MS Access Sample – Change Password

If you are using user-level security (ULS), the following is a simple demonstration of a form that can be added to any database to permit the user the ability to change their password. Useful when you create an mde or disable the standard toolbars for increased security but still want to give the user the ability to rotate their own password.

Download the sample database: User-Level Security Change of Password Form Demonstration.

Special Note & Warning
Be very careful using this sample.  If you run it on a database which does not have a designated mdw security database assigned, it will alter the system.mdw, thus affecting ALL your databases!  It is only meant to be run on secured databases using ULS security.  Any other applications can have disastrous results on your databases.  I mention this because I recently was e-mail by someone who made this mistake.  Below is the solution to fix such a mistake:

So if I understand the situation properly, you ran the chgPwd.mdb on your computer, in a db, or on its own in a db that wasn’t already secured?  I would assume that by doing so you inadvertantly applied a password against your system mdw database.  So in fact you secured the master system.mdw database, thus imposing a password against all databases running on your computer.  The fix, you’d need to either reinstall Access, or copy over a system.mdw from another clean PC over to your.

MS Access Sample – Colors Converter

Microsoft Access Color Picker & Converter Utility

This straightforward Access database offers a user-friendly form that makes converting colors effortless. Whether you’re working with OLE Color values, RGB components, or HEX codes, this utility lets you seamlessly switch between all three formats.

MS Access Color Utility

As you can see by the above you can enter a OLE Color, RGB Color, HEX color value, move sliders (if you were trying to find a color) and they get converted back and forth between all the formats in real-time and you get a visual of the color in question at the top of the form.
 
Continue reading →

MS Access Sample – Common Issues

This database demonstrates common programming concepts, such as: basing one combo box’s list on another combobox’s value, changing a control’s background color, changing a form’s background color. This sample database will be updated as I have time to add to it.

Download the sample database: Common MS Access Issues Sample Database.