Home
Excel
Access
| Feedback | VB code in Reports |

Snippets of VBA code for Access

It is possible in Access to close a form automatically after a specified period of time. This is useful for opening title screens and forms telling the user that a task has completed. The following code can be linked to a Timer event on a form.

Would you like to learn more?

Access 2002: The complete reference

Private Sub Form_Timer()
If Me.TimerInterval <> 0 Then
Me.TimerInterval = 0
End If
DoCmd.Close A_FORM, "MyOpenScreen"
End Sub

You may need to ask the user for a filename on your PC which is to be imported. This simple VBA function checks to see whether a particular filename exists.

Function DoesFileExist(FileName As String) As Byte
' returns 1 if FileName exists, otherwise 0
 If Dir(FileName, vbNormal) <> "" Then
DoesFileExist = 1
Else
DoesFileExist = 0
End If
End Function

In an application you may want (as a default) to open forms at their normal size rather than maximised to fill the screen - while reports may look much better maximised. This can simply be fixed using either a Macro or a VB Procedure to set their size each time they are opened. Without a procedure like this, forms and reports will inherit the normal or maximised condition of the previous window.

Create a new Macro containing a single command: Restore

Then save it with a name such as MyRestore

Within any form, attach this MyRestore Macro to the Activate event.

Would you like to learn more?

Access 2002 programming by example

Create a second similar macro containing the command Maximise which can be linked to the Activate event of a report.

Alternatively use the Code Builder linked to an Activate event to create the code DoCmd.Restore or DoCmd.Maximise

Access does not include a built in ROUND() function to remove unwanted decimal places from a value. You may therefore choose to write your own function to perform this task.

Function TwoDP(TAmt As Double) As Double
' Convert the number to text with 2 dec places and then convert it back to a number
TwoDP = Val(Format(TAmt, "#.00"))
End Function

Alternatively, if you simply wanted to calculate a rounded integer value (not simply a rounded down integer) in a query or program you could use the following formula:

= INT(AnyValue + 0.5)

file: avarious.htm © MeadInKent.co.uk 2004 Page last updated Sep06