Category Archives: MS Access Forms

MS Access Forms

MS Access – Sliding/Shutter Subform Example

I was looking for a simple way to reproduce a web style expandable sidebar (accordian subform, expandable subform, sliding subform, shutter subform, or whatever name you’d like to use to describe it in MS Access), instead of merely making a subform visible/invisible. The attached file, does exactly that and with a single change to a constant variable you can control the speed of the shutter/slider.

This is a very crude example to illustrate the principle.  You can use any subform you like, within any form you’d like.  There are no requirements to make this work and all the code is native MS Access code, no external references, or ActiveX Controls.  The button used to expand and retract the subform can also be changed as you see fit, it is just a question of placing the brief code behind whatever button you select to use in your design.

Continue reading →

MS Access – Button Picture – doesn’t support this format … or the file size is too big

I came across the following post and wanted to see what the issue was since I routinely use Irfanview myself (it is a great, free no strings attached piece of software).

So I contacted the creator of Irfanview directly regarding the error, and he was able to pinpoint the exact nature of the problem.  As it turns out, the ico files created by Irfanview are just fine.  The issue is that MS Access would seem to expect a specific type of ico file (and doesn’t tell anyone this).

Irfanview creates a 24BPP (with no alpha layer) ico file while MS Access seems (based on my testing and returned information from Irfan) to expect a 32BPP with an alpha layer ico file.

So for anyone else ever seeing this this of error.  An ico is not an ico in the world of MS Access.  It is picky and requires a specific type of ico file.  So just be sure of the type of file your are creating is a 32BPP with an alpha layer ico and you should have no problems using them as a picture for a button.  Alternately, instead of having such issues using a ico file, use BMPs instead.  I personally don’t like BMPs as they aren’t used in any other programming (always jpg, gif, png, ico) so I will stay with ico so I can reuse them easily where I please.  It would be nice if MS would integrate common image files into the application, but that is out of my hands (if it were up to me many issues like this would have been resolve a long time ago!).

Hopefully this will help someone out.

MS Access – VBA – Select the Treeview Root Node

When using the a Treeview control and an expand control the Treeview sets’ the focus at the end/bottom node. So how can you return the focus, in this case ‘select’ the root node? To do so you merely need to use the following bit of code.

Me!TreeviewControlName.Nodes(1).Selected = True

That said, selecting Nodes(1) may not always yield the desireed result. The reason being that if sorting is accomplished after nodes are added the Nodes(1) may no longer be positioned at the top. So what do we do now? There is a solution, but I’m not going to reinvent the wheel on this one. UtterAccess has a sample database with a procedure named GotoFirstNode() that works around the above mentioned little problem. The sample database in question can be found at Treeview Sample With Drag And Drop. It also, covers many other functionalities and is greatly worth checking out.

MS Access – VBA – Determine a Control’s Associated Label

It can become necessary to need to determine the associated label to a given control. It actually is, yet again, very simple to do. Not what I’d call intuitive, but easy once you are aware of the proper syntax to use.

To reference a control’s associated label you use the following syntax

Me.ControlName.Controls(0)

Or

Forms!FormName.Form.ControlName.Controls(0)

Continue reading →

Microsoft Access – Creating a Triple State Check Box or Option Group

One of my long standing frustrations with Microsoft Access is the way it handles the “Yes/No” data type. On the surface, it seems perfect for building a Yes/No option group on a form. And to be fair, it works well as long as you are comfortable with always having a value selected.

The problem appears when you need a third state: “Null”. In many real world scenarios, you do not want to assume Yes or No by default. Instead, you want the field to remain blank until the user makes a conscious choice. This triple state requirement, meaning Yes, No, or Not Yet Answered, is surprisingly common.
 
Continue reading →

MS Access – VBA – Retrieve a Random Record

Another interesting question I was once asked on an Access forum was how can one retrieve a random record in a form?

I was actually perplexed as to how to approach this request, but it really isn’t that complicated at the end of the day. The code below demonstrates one possible method.

'---------------------------------------------------------------------------------------
' Procedure : GetRndRec
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Goto/retrieve a random record
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2008-Dec-21             Initial Release
'---------------------------------------------------------------------------------------
Function GetRndRec()
On Error GoTo Error_Handler
    Dim db          As DAO.Database
    Dim rs          As DAO.Recordset
    Dim tblName     As String   'Table to pull random record from
    Dim iRecCount   As Long     'Number of record in the table
    Dim iRndRecNum  As Integer
    
    tblName = "YourTableName"
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(tblName, dbOpenSnapshot, dbReadOnly, dbReadOnly)
    
    If rs.RecordCount <> 0 Then 'ensure there are records in the table before proceeding
        With rs
            rs.MoveLast   'move to the end to ensure accurate recordcount value
            iRecCount = rs.RecordCount
            iRndRecNum = Int((iRecCount - 1 + 1) * Rnd + 1) 'Get Random Rec Number to use
            rs.MoveFirst
            .Move CLng(iRndRecNum)
            GetRndRec = ![YourFieldName]
        End With
    End If

Resume Error_Handler_Exit
    On Error Resume Next
    'Cleanup
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: GetRndRec" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

MS Access – Mandatory Fields, Mandatory Entries

Here is a common question: “How can I make a field mandatory to my users?”

Well, as always, there are different techniques that can be employed. I will breifly covert 2: using Table Field Properties and using Form Events.

 

Table Setup

The easiest method is to merely set the table’s Required field property to Yes.

That said, this approach does present limitations. Mainly the fact that the message returned to the user references the Field Name (see the example below) and not its’ Caption or Description or Form Control Name, so the message in fact ends up confusing most users! This is why, using form event can come in handy.

The field ‘YourFieldName’ cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field.

 

Form Event

Happily, we can add a lot more functionality and user-friendliness to our form by using its’ BeforeUpdate event to perform our validation. By inserting a little VBA code, for instance, something along the lines of:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.[YourControlName]) Then
        Cancel = True
        MsgBox "You must enter a value for 'YourControlNameOrYourDescription'. Please make a valid entry or press ESC to Cancel"
        'You could set the focus on the specific control if your wish, change the background color, ...
    End If
End Sub

As you can see, both are easy to implement, but the second method offers much more control and you can explain to your users in plain English to problem. Furthermore, you could also perform more advance data validation to not only ensure they have made an entry, but that it meet the require format…

MS Access – VBA – Determine Where a Field Is Used

I recently had to make a minor change to a poorly designed, but large database and had to determine where certain fields were being used so I could go make the necessary changes. In this case, I had to review hundreds of MS Access objects, so a manual approach was just not acceptable. As such, I created a VBA to let the computer do the checking for me and report back. Below is the fruits of my labor.

'---------------------------------------------------------------------------------------
' Procedure : FindFieldUsedWhere
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Locate where a field is used within queries, forms and reports
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFieldName    : Field Name to search for in the various Access objects
'
' Usage:
' ~~~~~~
' FindFieldUsedWhere("Type A")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-04-08                 Initial Release
'---------------------------------------------------------------------------------------
Function FindFieldUsedWhere(sFieldName As String)
On Error GoTo Error_Handler
    Dim db               As DAO.Database
    Dim qdf              As DAO.QueryDef
    Dim sSQL             As String
    Dim ctl              As Control
    Dim frm              As AccessObject
    Dim DbO              As AccessObject
    Dim DbP              As Object

    Set db = CurrentDb
    Debug.Print "FindFieldUsedWhere Begin"
    Debug.Print "Searching for '" & sFieldName & "'"
    Debug.Print "================================================================================"

    'Check Queries
    For Each qdf In db.QueryDefs
        'qdf.Name    'The current query's name
        'qdf.SQL     'The current query's SQL statement
        sSQL = qdf.SQL
        If InStr(sSQL, sFieldName) Then
            'The Query is a Make Table Query and has our TableName we are looking for
            Debug.Print "Query: " & qdf.Name
        End If
    Next

    'Check Forms
    For Each frm In CurrentProject.AllForms
        DoCmd.OpenForm frm.Name, acDesign
        If InStr(Forms(frm.Name).RecordSource, sFieldName) Then
            'The Query is a Make Table Query and has our TableName we are looking for
            Debug.Print "Form: " & frm.Name
        End If
        'Loop throught the Form Controls
        For Each ctl In Forms(frm.Name).Form.Controls
            Select Case ctl.ControlType
                Case acComboBox
                    If Len(ctl.Tag) > 0 Then
                        If InStr(ctl.Tag, sFieldName) Then
                            'The Query is a Make Table Query and has our TableName we are looking for
                            Debug.Print "Form: " & frm.Name & " :: Control: " & ctl.Name
                        End If
                        If InStr(ctl.ControlSource, sFieldName) Then
                            'The Query is a Make Table Query and has our TableName we are looking for
                            Debug.Print "Form: " & frm.Name & " :: Control: " & ctl.Name
                        End If
                    End If
                Case acTextBox, acCheckBox
                    If InStr(ctl.ControlSource, sFieldName) Then
                        'The Query is a Make Table Query and has our TableName we are looking for
                        Debug.Print "Form: " & frm.Name & " :: Control: " & ctl.Name
                    End If
            End Select
        Next ctl
        DoCmd.Close acForm, frm.Name, acSaveNo
    Next frm

    'Check Reports
    Set DbP = Application.CurrentProject
    For Each DbO In DbP.AllReports
        DoCmd.OpenReport DbO.Name, acDesign
        If InStr(Reports(DbO.Name).RecordSource, sFieldName) Then
            'The Query is a Make Table Query and has our TableName we are looking for
            Debug.Print "Report: " & DbO.Name
        End If
        'Loop throught the Report Controls
        For Each ctl In Reports(DbO.Name).Report.Controls
            Select Case ctl.ControlType
                Case acComboBox
                    If Len(ctl.Tag) > 0 Then
                        If InStr(ctl.Tag, sFieldName) Then
                            'The Query is a Make Table Query and has our TableName we are looking for
                            Debug.Print "Report: " & DbO.Name & " :: Control: " & ctl.Name
                        End If
                        If InStr(ctl.ControlSource, sFieldName) Then
                            'The Query is a Make Table Query and has our TableName we are looking for
                            Debug.Print "Report: " & DbO.Name & " :: Control: " & ctl.Name
                        End If
                    End If
                Case acTextBox, acCheckBox
                    If InStr(ctl.ControlSource, sFieldName) Then
                        'The Query is a Make Table Query and has our TableName we are looking for
                        Debug.Print "Report: " & DbO.Name & " :: Control: " & ctl.Name
                    End If
            End Select
        Next ctl
        DoCmd.Close acReport, DbO.Name, acSaveNo
    Next DbO

    Debug.Print "================================================================================"
    Debug.Print "FindFieldUsedWhere End"

Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Set ctl = Nothing
    Set frm = Nothing
    Set DbP = Nothing
    Set DbO = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FindFieldUsedWhere" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, "An Error has Occurred
    Resume Error_Handler_Exit
End Function

This is my first draft and I haven’t looked at optimizing my code. It was more about getting results than developing a procedure to be reused often. Hopefully it can help someone else in the same boat as I found myself! Simply execute the procedure and it will return a listing of what Access objects use the specified field in the immediate window of the VBE console.

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 – 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 →