Posts tagged ‘MS Access Forms’

February 14th, 2012

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.

CategoryMS Access Forms, MS Access General Information | CommentNo Comments »

Tags:
November 9th, 2011

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.

CategoryMS Access Forms, MS Access VBA Programming | CommentNo Comments »

Tags:
November 8th, 2011

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 synthax to use.

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

Me.ControlName.Controls(0)

Or

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

So let’s say we wanted to determine a control’s associated label’s caption, we do something along the lines of:

Me.ControlName.Controls(0).Caption

One note of caution however, you need to ensure you implement error handling to trap possible errors (Error number: 2467 – The expression you entered refers to an object that is closed or doesn’t exist) that may arise with control’s that do not have associated labels.

CategoryMS Access, MS Access Forms, MS Access VBA Programming | CommentNo Comments »

Tags:
November 4th, 2011

MS Access – Forms – Yes/No option Group with Null State

One of my greatest annoyances with Access is the fact that Microsoft in their infinite wisdom created a great field data type ‘Yes/No’ which you would think would be great for creating a Yes/No Option Group with. This is true if you want to have a default value, but what happens if you want to allow for a Null state, what is commonly referred to as a triple state field/Option Group, to allow for the case (a common situation in my experience) where you would like a Yes/No Option Group but leave it blank until the user actually makes a selection then the Yes/No data type no longer works?! You cannot have a Null value with a standard Yes/No Field.

So what is one to do?
Well, thankfully the solution is actually very simple. Change the data type from Yes/No to Number and change the Field Size to suit your particular needs. For instance, if like me, you wanted to assign -1 to Yes and 0 to No then you would have to change the Field Size to Integer.

CategoryMS Access Forms | CommentNo Comments »

Tags:
August 19th, 2011

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 occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: GetRndRec" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

CategoryMS Access Forms, MS Access Tables, MS Access VBA Programming | CommentNo Comments »

Tags:
May 25th, 2011

MS Access – VBA – Hide Object Pane

Here is a simple bit of code that permits you to hide the MS Access’ main object browser, to stop nosy users from accessing tables, queries, etc…

DoCmd.SelectObject acTable, , True
DoCmd.RunCommand acCmdWindowHide

In conjunction with the code to determine whether the user is running the runtime or full version of Access (see MS Access – Determine if Runtime or Full Version
) you could insert a section of code such as:

If SysCmd(acSysCmdRuntime) = False Then
    DoCmd.SelectObject acTable, , True
    DoCmd.RunCommand acCmdWindowHide
End If

This would also be a good place to enable any custom command bars/ribbons and/or disable any built-in command bars/ribbons…

CategoryMS Access Forms, MS Access Queries, MS Access Reports, MS Access Tables, MS Access VBA Programming | CommentNo Comments »

Tags:
May 3rd, 2011

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…

CategoryMS Access Forms, MS Access Tables | CommentNo Comments »

Tags:
June 22nd, 2010

MS Access – Calculate the Age

If you have ever needed to calculate the age between two dates, then the function below is for you. Often people mistakenly believe that the age calculation can simply be accomplished using the DateDiff() function, but this is not the case and a slightly more complexe function is required to do the job.

To merely calculate the age of an individual in years, you can simply use the DateDiff().

=DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") > Format(Date(), "mmdd"), 1, 0)

However, if you would like a little more detail (Years, Months, Days) you can use a function such as the one presented below.

Function fAge(dteStart As Variant, dteEnd As Variant) As Variant
'*******************************************
'Purpose:   Accurately return the difference
'           between two dates, expressed as
'           years.months.days
'Coded by:  raskew (from MS Access forum)
'Inputs:    From debug (immediate) window
'           1) ? fAge(#12/1/1950#, #8/31/2006#) 'Calculate btw 2 specific dates
'           2) ? fAge(#12/30/2005#, Date()) ' Calculate as of today's date
'*******************************************
Dim intHold   As Integer
Dim dayhold   As Integer
 
   'correctly return number of whole months difference
   'the (Day(dteEnd) < Day(dteStart)) is a Boolean statement
   'that returns -1 if true, 0 if false
   intHold = DateDiff("m", dteStart, dteEnd) + (Day(dteEnd) < Day(dteStart))
 
   'correctly return number of days difference
   If Day(dteEnd) < Day(dteStart) Then
      dayhold = DateDiff("d", dteStart, DateSerial(Year(dteStart), Month(dteStart) + 1, 0)) + Day(dteEnd)
   Else
      dayhold = Day(dteEnd) - Day(dteStart)
   End If
 
   fAge = LTrim(Str(intHold \ 12)) & " years " & LTrim(Str(intHold Mod 12)) & " months " & LTrim(Str(dayhold)) & " days"
 
End Function

CategoryMS Access, MS Access VBA Programming | Comment1 Comment »

Tags: