November 9th, 2011
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 Forms, MS Access VBA Programming | 
No Comments »
November 8th, 2011
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.
MS Access, MS Access Forms, MS Access VBA Programming | No Comments »
November 4th, 2011
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.
MS Access Forms | No Comments »
August 19th, 2011
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
MS Access Forms, MS Access Tables, MS Access VBA Programming | No Comments »
May 25th, 2011
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 IfThis would also be a good place to enable any custom command bars/ribbons and/or disable any built-in command bars/ribbons…
MS Access Forms, MS Access Queries, MS Access Reports, MS Access Tables, MS Access VBA Programming | No Comments »
May 3rd, 2011
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 SubAs 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 Forms, MS Access Tables | No Comments »
April 8th, 2011
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 occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: FindFieldUsedWhere" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has Occured
Resume Error_Handler_Exit
End FunctionThis 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 Forms, MS Access Queries, MS Access Reports, MS Access VBA Programming | 1 Comment »
December 5th, 2010
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 ctlNow 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 ctlLastly, 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 Forms, MS Access VBA Programming | 4 Comments »
September 21st, 2010
Have you ever created a control on a form to enter percentage values and had your users complain because they enter whole numbers which get automatically multiplied by 100. So if the user enters 3, it will actually give 300%.
No worries anymore! I created a very simple procedure which will automatically readjust values entered by your users. 3 will automatically be updated to 0.03, which give 3%.
'---------------------------------------------------------------------------------------
' Procedure : ajustPercentage
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Automatically adjust whole number to percentage values
' 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 2010-Sep-21 Initial Release
'---------------------------------------------------------------------------------------
Function ajustPercentage(sValue As Variant) As Double
On Error GoTo Error_Handler
If IsNumeric(sValue) = True Then 'Only treat numeric values
If Right(sValue, 1) = "%" Then
sValue = Left(sValue, Len(sValue) - 1)
ajustPercentage = CDbl(sValue)
End If
If sValue > 1 Then
sValue = sValue / 100
ajustPercentage = sValue
Else
ajustPercentage = sValue
End If
Else 'Data passed is not of numeric type
ajustPercentage = 0
End If
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: ajustPercentage" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, _
"An Error has Occured!"
Resume Error_Handler_Exit
End FunctionThe one thing to note regarding this procedure is that it is meant for controls/fields that you always expect a percentage value between 0% and 100%. If you expect percentage above 100% this procedure will not help you in that case and you will need to build a custom procedure for that situation.
MS Access Forms, MS Access VBA Programming | No Comments »
June 12th, 2010
It can be useful to have a listing of all the objects in the database. For instance, a listing of all the table or queries… This can easily be achieved using a query which uses as its source a hidden system table named ‘MSysObjects’. The basic query SQL statment is as follows:
SELECT MsysObjects.Name AS [List OF TABLES]
FROM MsysObjects
WHERE (((MsysObjects.Name) NOT LIKE "~*" AND (MsysObjects.Name) NOT LIKE "MSys*") AND ((MsysObjects.TYPE)=1)) ORDER BY MsysObjects.Name;
You need only change the value of the (MsysObjects.Type)=1 part of the query expression to change what listing is returned. Below are the various values that can be used to return the various objects available in Access:
| Object Type |
Value |
| Tables (Local) |
1 |
| Tables (Linked using ODBC) |
4 |
| Tables (Linked) |
6 |
| Queries |
5 |
| Forms |
-32768 |
| Reports |
-32764 |
| Macros |
-32766 |
| Modules |
-32761 |
MS Access Forms, MS Access General Information, MS Access Queries, MS Access Reports, MS Access Tables | 3 Comments »
June 12th, 2010
The ‘Not In List Event’ occurs whenever a user tries to enter a vaule into a combobox that is not part of the existing list of choices. Below is a typical example of a ‘Not In List Event’ that will allow the user to add their new value to the exisitng list of choices for further use in the future, assuming you are using an underlying table as the list source.
Private Sub YourCboName_NotInList(NewData As String, Response As Integer)
'Requires that a reference to the Microsoft DAO 3.6 Object Library
On Error GoTo Error_Handler
Dim rst As DAO.Recordset
If MsgBox(NewData & "... not in list, add it?", vbOKCancel, "MessageBoxTitle") = vbOK Then
Set rst = CurrentDb.OpenRecordset("TableName") 'Table to add the new value to
With rst
.AddNew
.Fields("TableColumnName") = NewData 'Name of the table field to add the new value to
.Update
End With
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
Error_Handler_Exit:
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Sub
Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: YourCboName_NotInList" & vbCrLf & "Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Sub
MS Access Forms, MS Access VBA Programming | No Comments »
June 10th, 2010
The following simple little procedure can be used to check if a given form is already open.
'---------------------------------------------------------------------------------------
' Procedure : IsFrmOpen
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Determine whether a form is open or not
' 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:
' ~~~~~~~~~~~~~~~~
' sFrmName : Name of the form to check if it is open or not
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' IsFrmOpen("Form1")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2010-May-26 Initial Release
'---------------------------------------------------------------------------------------
Function IsFrmOpen(sFrmName As String) As Boolean
On Error GoTo Error_Handler
If Application.CurrentProject.AllForms(sFrmName).IsLoaded = True Then
IsFrmOpen = True
Else
IsFrmOpen = False
End If
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: IsFrmOpen" & vbCrLf & "Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
MS Access Forms, MS Access VBA Programming | No Comments »
June 10th, 2010
The following function will return the number of currently open forms.
'---------------------------------------------------------------------------------------
' Procedure : CountOpenFrms
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Returns a count of the number of loaded Forms (preview or design)
' 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.
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2009-Oct-30 Initial Release
'---------------------------------------------------------------------------------------
Function CountOpenFrms()
On Error GoTo Error_Handler
CountOpenFrms = Application.Forms.Count
Exit Function
Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: CountOpenFrms" & vbCrLf & "Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Exit Function
End Function
MS Access Forms, MS Access VBA Programming | No Comments »
June 10th, 2010
You can use the following function to retrieve a listing of all the currently open forms.
'---------------------------------------------------------------------------------------
' Procedure : ListOpenFrms
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Returns a list of all the loaded forms (preview or design)
' separated by ; (ie: Form1;Form2;Form3)
' 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.
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2009-Oct-31 Initial Release
'---------------------------------------------------------------------------------------
Function ListOpenFrms()
On Error GoTo Error_Handler
Dim DbF As Form
Dim DbO As Object
Dim Frms As Variant
Set DbO = Application.Forms 'Collection of all the open forms
For Each DbF In DbO 'Loop all the forms
Frms = Frms & ";" & DbF.Name
Next DbF
If Len(Frms) > 0 Then
Frms = Right(Frms, Len(Frms) - 1) 'Truncate initial ;
End If
ListOpenFrms = Frms
Exit Function
Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: ListOpenFrms" & vbCrLf & "Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Exit Function
End Function
MS Access Forms, MS Access VBA Programming | No Comments »
June 10th, 2010
There are a multitude of methods to password protect a form access to users should you not wish to implement Access’ built-in User-Level Security. The following Microsoft Knowledge Base article illustrates how you can achieve exactly this.
How to Create a Password Protected Form or Report
MS Access Forms | No Comments »
June 10th, 2010
Have you ever wanted to limit the number of records that could be input into a table through a form? Simply input the following Form Current Event!
Private Sub Form_Current()
Dim intMaxNumRecs as Integer
intMaxNumRecs = 5 'Max Number of Records to Allow
If Me.NewRecord Then
With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveLast: .MoveFirst
If .RecordCount >= intMaxNumRecs Then
MsgBox "Can't add more than " & intMaxNumRecs & " records in the demo database!"
.MoveLast
Me.Bookmark = .Bookmark
End If
End If
End With
End If
End Sub
MS Access Forms | No Comments »
June 10th, 2010
Depending on your needs, it can also be useful to have a color picker on your form. To this end check out
Calling Windows Choose Color Dialog
Leban’s Font and Color Dialog
MS Access Forms | 1 Comment »
June 10th, 2010
Another common need for a good number of application is a simple pop-up calculator. To my surprise, MS Access still does not come with any form of a calculator. No need to worry though! There are a great number of such MS Access calculators available for free online. Below are three examples of free calculators you can simply download and drop into your database application.
http://www.datastrat.com/Download/popCalc.zip
http://www.byerley.net/Access2kCalcDemo.zip
http://www.mvps.org/access/downloads/calc.zip
If you aren’t satisfied with those MS Access calculators listed above, why not create your own. Use the following link to learn how.
How to Build an MS Access Calculator
MS Access Forms | No Comments »
June 10th, 2010
A very common issue with any documents, not only access databases, is how data is entered by the users, most notably date entries. To ensure proper data entry it is very useful to give your users simple graphical tools and elimate keyboard entry. To this aim, the following are 2 excellent date pickers that can very easily be incorporated into any of your database applications.
Allen Browne’s Popup Calendar a very basic calendar easy to setup and use. This is a form based calendar.
Stephen Lebans Calendar a more advanced calendar (more options) equally easy to setup and use. This is an API based calendar.
Arvin Meyer’s Calendar another basic form based calendar which is easy to implement in any database.
It is also very important to note that whenever possible you should always avoid the use of ActiveX controls as they can lead to reference and versioning issues. The calendars listed above will not suffer from such issues.
Also, if you have develop an mdb application working in 2007 and have taken advantage of the pop-calendar included in Access 2007. If you wish you database to be backwards compatible then you must implement your own calendar, as earlier version do not have this functionality!
MS Access Forms | No Comments »
