LEARN MS-ACCESS TIPS AND TRICKS

Opening Specific Page of Pdf File

a.p.r. pillaiMarch 30, 2012pdf files, Utilities No comments

Introduction.

Opening an external file (such as Word, Excel, or Adobe PDF) from Microsoft Access is straightforward. You can use the Hyperlink tool (Ctrl+K) to browse for a file on disk and assign it as a hyperlink on a form. Another option is to open the Hyperlink tool from the Hyperlink Address property of a label, then browse for and select the desired file.

Once the hyperlink is set, clicking on it will open the file (Word, Excel, or PDF) starting with the first page, according to the document’s default view settings.

The Shell Command.

Another method used to open an external file is a DOS Command (Microsoft Disk Operating System) Shell in VBA. The Shell() command needs mainly two parameters (actually three values), as the syntax is shown below:

Call Shell(“<Parent Application> <file pathname>”, <window mode>)

The first parameter of the Shell() command has two segments, separated by a space.

A. First Parameter

The parent Application Path Name(C:\Program Files (x86)\adobe\Reader 9.0\Reader\AcroRd32.exe).
The pathname of the PDF file to open (C:\aprpillai\Documents\dosa.pdf).

B. Second Parameter

open window mode

The Sample Trial Run.

Let us try an example to open a PDF file: C:\aprpillai\Documents\dosa.pdf using the Shell() Command:

Open any one of your databases or create a new one.
Create a new Form with a Command Button on it.
Select the Command Button and open its Property Sheet (F4).
Change the Name property value to cmdRun and change the Caption Property value to Open PDF File.
Select the Event Tab of the Property Sheet and click on the On Click Property.
Click on the build (. . .) button at the right end of the property to open the VBA Module.

Copy and paste the following VBA Code overwriting the existing lines:

Private Sub cmdRun_Click() 
Dim strApplication As String 
Dim strFilePath As String 

strApplication = "C:\Program Files (x86)\adobe\Reader 9.0\Reader\AcroRd32.exe" 
strFilePath = "C:\aprpillai\Documents\dosa.pdf" 

Call Shell(strApplication & " " & strFilePath, vbNormalFocus) 

End Sub

Change the pathname of the PDF file to select a file from your disk with a few pages.
Save the Form with the name PDF_Open_Example.
Open the form in normal view and click on the Command Button to open the PDF file.

The above Sub-Routine opens the file selected with Normal Focus.

After opening the PDF file with page 1 on the top, type a different page number in the navigation control at the bottom of the document to jump to that page.

However, if we already know the page number we want to open, we can pass it as a parameter to the AcroRd32.exe program. For example:

..\AcroRd32.exe /A page=25 ..\dosa.pdf

This command will open the PDF file directly to page 25 instead of starting from the first page.

To demonstrate, let’s modify our earlier program by adding the page parameter (/A page=25) so that the PDF opens at the 5th page. The updated version of the program is shown below:

Private Sub cmdRun_Click()
Dim strApplication As String
Dim strFilePath As String

strApplication = "C:\Program Files (x86)\adobe\Reader 9.0\Reader\AcroRd32.exe /A page=25"
strFilePath = "C:\aprpillai\Documents\dosa.pdf"

Call Shell(strApplication & " " & strFilePath, vbNormalFocus)

End Sub

Note: Do not include spaces on either side of the equal sign in the parameter page=25. The /A switch must immediately follow the program name (AcroRd32.exe), with a space before specifying page=25.

In our example, the file dosa.pdf contains several recipes. To make navigation easier, we should be able to jump directly to a specific recipe with a single click. For this purpose, we can create a Combo Box on the form that lists all recipes, along with their corresponding page numbers and descriptions. By selecting a recipe from the list, the program can pass the correct page number to Acrobat Reader, allowing us to quickly display the chosen recipe.

A Sample Form.

An image of a sample form with the list of Dosa Recipes in a Combo Box is given below:

Right-click to open Large Image in New Tab/Window.

I will explain the second Combo box (Zoom Percentage) a little later.

Open the Form in Design View.
Select the Control Wizard tool to launch when you select the Combobox Tool.
Select the Combobox Tool and draw a Combobox on the Form.
Select the Radio Button on the Control Wizard, with the caption ‘I will type the values that I want and click Next.
Type 2 in the ‘Number of Columns’ control and press the Tab Key.
Type a similar list of topics, shown in the image above, from your PDF file with Page Number in the first column and Description in the second column. When finished, click Next.
Select the first column and click Next.
Type a suitable caption for the child label and click Finish.
Select the Combo Box; if it is deselected, then display the Property Sheet (F4).
Change the Name Property value to cboPage.
Display the VBA Module of the Form (Design -> Tools -> View Code or press ALT+F11).

Copy and paste the following VBA Code into the Module, overwriting the existing code:

Private Sub cmdRun_Click()
Dim ReaderPath As String
Dim pdfFilePath As String
Dim PageNumber As Integer
Dim strOpenPDF As String

PageNumber = Nz(Me![cboPage], 1)' Get user selected page number, if empty then take 1 as default

ReaderPath = "C:\Program Files (x86)\adobe\Reader 9.0\Reader\AcroRd32.exe /A " & "page=" & PageNumber 
pdfFilePath = "C:\aprpillai\Documents\dosa.pdf" 'change the path to match your file location

strOpenPDF = ReaderPath & " " & pdfFilePath
Call Shell(strOpenPDF, vbNormalFocus)

End Sub

Close the VBA Module, save the Form, and open it in normal view.
Select one of the items from the Combobox with a larger page number.
Click on the Command Button to open the PDF file displaying the selected page. Check the following image for a sample view of the dosa.pdf file:

The Zoom Parameter.
From the header toolbar, you can see that the current view shows page 7 of 51, with the document opened at about 60% zoom of its actual size. This zoom level can also be controlled programmatically. By specifying the Zoom parameter immediately after the Page parameter, we can open the PDF document to both the desired page and zoom percentage.
To demonstrate this, I created a second Combo Box control on the form, named cboZoom, which contains a list of zoom percentage values: 50, 60, 70, 80, 90, 100, and 120. By selecting one of these values along with the page number, the PDF document can be opened not only at the correct page but also at the preferred zoom level for easier viewing.

The modified Code with the addition of Zoom Parameter is given below:

Private Sub cmdRun_Click()
Dim ReaderPath As String
Dim pdfFilePath As String
Dim PageNumber As Integer
Dim intZoom As Integer
Dim strOpenPDF As String

PageNumber = Nz(Me![cboPage], 1)
intZoom = Nz(Me![cboZoom], 100)

ReaderPath = "C:\Program Files (x86)\adobe\Reader 9.0\Reader\AcroRd32.exe /A " & quot;page=" & PageNumber & "&zoom=" & intZoom
pdfFilePath = "C:\aprpillai\hostgator\dosa.pdf"

strOpenPDF = ReaderPath & " " & pdfFilePath
Call Shell(strOpenPDF, vbNormalFocus)
End Sub

The Page parameter and Zoom parameter values must be joined with an & symbol, and there should not be any spaces on either side of the equal (=) sign:

..\AcroRd32.exe /A page=7&zoom=60 C:\aprpillai\Documents\dosa.pdf

When you combine the parameter key names (page, zoom) with the control values (page number and zoom percentage), the result should be the sample value shown earlier.

To test this, you can place a Text Box on the form with the name cboZoom. Enter a zoom percentage value in this control (note: do not include the % symbol), then run the code to confirm that it works.

Important: If both controls—cboPage and cboZoom—are left empty, the PDF file will open by default with the first page on top and at 100% zoom.

Technorati Tags: Microsoft

IIF vs Switch function in MS-Access
Preparing Rank List
Restoring Disabled Full Menus in Access 2007
Running Workgroup Admin in Access 2007
Apply the Filter to the Table directly

Calculating Time Difference

a.p.r. pillaiMarch 21, 2012Functions, Time Difference No comments

Introduction.

How do we calculate the difference in time in Hours, Minutes, and Seconds between two time periods? The time period can be within the same date or across different dates.

Example-1:

Start Time: 21-03-2012 12:30:45

End Time: 21-03-2012 13:30:15

Example-2:

Start Time: 21-03-2012 12:30:45

End Time: 24-03-2012 15:15:15

In the first example, the date portion of the Start Time and End Time is irrelevant when calculating the difference between them. For instance, subtracting the start time of 12:30:45 from the end time of 13:30:15 gives a result of 00:59:30. However, if the end time occurs past midnight, such as 01:30:15, how should we calculate the time difference?

00:00:00 Time.

First, let’s consider how time resets after 23:59:59 at night and returns to 00:00:00. Understanding how this transition is stored in a computer’s memory makes it much easier to work with date and time values.

We all know the basic units: a day has 24 hours, an hour has 60 minutes, a minute has 60 seconds, and a second can be further divided into 1000 milliseconds.

If you’re curious about how February gets 29 days, why every 100th year (such as 1700, 1800, 1900, or 2100) is not a leap year, why every 400th year is a leap year, and why every 4000th year is not a leap year, [click here to find out].

One Day in Seconds.

Setting aside milliseconds for the moment, one day equals 86,400 seconds (24 × 60 × 60). Put another way, one second equals 1/86,400, or 0.0000115740740741 days. Exactly one second after midnight, the computer’s internal time value becomes 0.0000115740740741, and this value increments with every passing second.

If we divide this value by 1000, we get the fraction that represents one millisecond, meaning the time value can also be incremented at millisecond intervals. At 12:00 noon, the internal time value is 0.5 (half a day), and at 23:59:59, it reaches 0.999988425926 (in days). When the clock rolls over to 00:00:00 midnight, the value resets to 0.

Date and Time Together.

To calculate the time difference between values that span different dates, the date component must be considered along with the time value, as shown in Example 2 above. In the computer’s memory, the date is stored as a continuous serial number beginning with 1 for 31-12-1899. For instance, if you create a Date/Time field in a table with a default value of 0, the field will display the date as 30-12-1899 until an actual date is entered.

Date and Time Number.

The date 21-03-2012 equals the date number 40989, and the Start Time (Example-2) in memory will be 40989.521354166700; the number after the decimal point is the time value. Since the Date and Time values held in memory are real numbers, it is easy to calculate the difference between them. All you have to do is subtract one number from another. This works with Date/Time Values on the same day or on different dates.

Let us find out the time difference between the date and time values in Example 2:

StartTime = #21-03-2012 12:30:45# = 40989.521354166700

This is the kind of value (Current Date and Time) returned with the function Now(). You can format this value as date alone (dd-mm-yyyy) or time alone ("hh:nn:ss") or both combined - format(now(),”dd-mm-yyyy hh:nn:ss”).

Use ? format(StartTime,”0.000000000000”) to display the Date/Time value as a real number from the StartTime Variable in the Debug Window.

EndTime = #24-03-2012 15:15:15# = 40992.635590277800

Difference = (EndTime - StartTime) = 3.114236111112 days

So, the result value we get in days doesn’t matter if the date is the same or a different date. If the date is the same, you will get the result in days, like 0.9999999. All we have to do is convert these days into Hours, Minutes, and Seconds.

Total_Seconds = Difference x 86400 (difference in days converted into total seconds) = 269070.000000077 Seconds

Hours = Int(Total_Seconds / 3600) = 74 Hrs.

Minutes = int((Total_Second MOD 3600)/60) = 44 Min.

Seconds = Total_Seconds MOD 60 = 30 sec.

If we assemble all this information into a small function, we can easily find the time difference in Hours, Minutes, and Seconds by using the Start and End Date/Time Values in the Function as parameters.

The Hours/Minutes Function.

Copy and paste the following Function Code into the Standard Module and save it:

Public Function HrsMin(ByVal startDate As Date, ByVal endDate As Date) As String
Dim diff As Double
Dim difHrs As Integer
Dim difMin As Integer
Dim difSec As Integer
diff = (endDate - startDate) * 86400
difHrs = Int(diff / 3600)
difMin = Int((diff Mod 3600) / 60)
difSec = (diff Mod 60)
HrsMin = Format(difHrs, "00") & ":" & Format(difMin, "00") & ":" & Format(difSec, "00")
End Function

You may call the Function HrsMin() directly from the Debug Window, as shown below, to test the code:

? HrsMin(#21-03-2012 12:30:45#,#24-03-2012 15:15:15#)

Result: 74:44:30

You may call the function from a Textbox in the Form like:

= HrsMin([StartDate],[EndDate])

Or from the Query Column like:

HMS:HrsMin([StartDate],[EndDate])

Or you may run it from VBA Code:

strHMS = HrsMin(dtSDate,dtEDate)

Technorati Tags: Functions

Earlier Post Link References:

Centralized Error Handler and Error Log

a.p.r. pillaiMarch 11, 2012Error Handler, Utilities No comments

Introduction.

In an earlier article on the VBA Utility program, we explored how to scan through a VBA module—whether a standard module or a form/report class module—and automatically insert missing error-handling lines. You can find the link to that article here.

This utility program can save you considerable time that would otherwise be spent typing, copying and pasting, and modifying hundreds of lines of error-trapping code in your subroutines and functions. The main purpose of an error handler is to manage unexpected errors and, when necessary, report them to the developer so that the underlying issue can be permanently fixed. At the same time, the program should not terminate abruptly. If it is a minor issue, the user should be able to dismiss the error and continue working without interruption.

A typical project may contain hundreds of subroutines and functions across standard modules and Form/Report modules. When an error occurs, the message typically includes the error number and description. If the procedure name is included in the MsgBox() function’s title parameter, it will also appear in the message box title. However, users often overlook this important detail, which could otherwise help the developer quickly locate the exact procedure where the error occurred and resolve it efficiently.

A Common Error Handler.

A more effective approach to handling such issues is to create a centralized error handler and maintain an error log Text File on disk. Whenever an error occurs in a function or subroutine, the common error handler can be called with the necessary parameters, like the Error number, Description, procedure name, module name, and database name. The handler will both display the error message to the user and record the details in a log file.

If several Microsoft Access applications are running on a Local Area Network, all of their error log information can be saved to a single shared text file on the Server. Each log entry will include details like the date, time, module name, and database name, along with the usual error number and description, creating a consolidated and traceable error history.

A Text file image with sample error log entries is given below:

Right-click to open Large Image in New Tab/Window.

Error Message Info.

Each error log entry contains all the essential details—such as the date and time of the error, database path, module name, and procedure name—to precisely identify where the error occurred. Even if users choose not to report problems, the administrator can periodically review the log file to monitor the application’s overall health and address issues proactively.

The Trial Run.

The following sample data processing program, DataProcess(), attempts to open the input table Table_1, but the table doesn’t exist (got deleted or renamed by mistake), and the program runs into an error:

Public Function DataProcess()
Dim db As Database, rst As Recordset, x
On Error GoTo DataProcess_Error

Set db = CurrentDb
Set rst = db.OpenRecordset("Table_1", dbOpenDynaset)

Do While Not rst.EOF
 x = rst.Fields(0).Value
Loop
rst.Close

DataProcess_Exit:
Exit Function

DataProcess_Error:
BugHandler Err, Err.Description, "DataProcess()", "Module4", CurrentDb.Name
Resume DataProcess_Exit
End Function

Common Error Handler Info and Log File.

When the above program runs into an error, it calls the BugHandler() Program and passes the Module Name and Database Path as the last two parameters in addition to Error Number, Error Description, and Program name. The VBA Code of BugHandler() main program is given below:

Public Function BugHandler(ByVal erNo As Long, _
                           ByVal erDesc As String, _
                           ByVal procName As String, _
                           ByVal moduleName As String, _
                           ByVal dbName As String)
On Error GoTo BugHandler_Error
Dim logFile As String
Dim msg As String

'Error Log text file pathname, change it to the correct path
'on your Local Drive or Server Location
logFile = "c:\mdbs\bugtrack\acclog.txt"

'Open log file to add the new error log entry
Open logFile For Append As #1
  'write the log details to log file
  Print #1, Now() & vbCr
  Print #1, "Database : " & dbName & vbCr
  Print #1, "Module   : " & moduleName & vbCr
  Print #1, "Procedure: " & procName & vbCr
  Print #1, "Error No.: " & erNo & vbCr
  Print #1, "Desc.    : " & erDesc & vbCr
  Print #1, String(80, "=") & vbCr
  Close #1

msg = "Procedure Name: " & procName & vbCr & "Error : " & erNo & " : " & erDesc
  MsgBox msg, , "BugHandler()"

BugHandler_Exit:
Exit Function

BugHandler_Error:
MsgBox Err & " : " & Err.Description, , "BugHandler()"
Resume BugHandler_Exit
End Function

The Library Database.

You can save the above code in a common Library Database, where you have saved your own common library functions, so that they can be attached to your Projects.

This method will write out the details of errors from your databases into a commonplace, accessible to you all the time. When an error is reported by the User, you can directly check the details of it without asking the user to spell it out.

Technorati Tags: Databases, Error Handler

Changing Font Color Conditional Formatting

a.p.r. pillaiFebruary 29, 2012Conditional Formatting, How Tos 1 comment

Introduction.

With the conditional formatting feature of Microsoft Access, we can apply up to three colors to the font or background of a Textbox, because only three sets of conditions can be set on a field at one time. If we need more than that, then what? We can do that ourselves by checking for the specific condition and applying the color to the font.

The Color Table.

For example, we have a Category Code Combo Box Field on the Form having values ranging from A to F. When the user selects one of these codes from the Combo box, the item Description Field’s Font Color should change as per the following color table:

Color Table
Category	Color	Decimal	Hex
A	GREEN	32768	#008000
B	BLUE	10485760	#000080
C	LIGHT BLUE	16711680	#0000FF
D	LIGHT GREEN	65280	#00FF00
E	RED	128	#800000
F	LIGHT RED	255	#FF0000

Design a Form.

Let us try this out on a sample Form.

Open a new Form in Design View.
Click on the Control Wizards button on the Toolbox to enable it.
Select the Combo box Tool and draw a Combo box in the Detail Section of the Form.
Select the Radio Button with the Caption: I will type in the Values that I want, then click Next.
Type A, B, C, D, E & F in separate rows under Col1 and click on Finish Command Button.
While the Combo box is still in the selected state, display the Property Sheet (F4).
Click on the Other tab of the Property Sheet.
Change the Name Property Value to cboCat.
Select the Event Tab on the Property Sheet.
Select [Event Procedure] from the After Update Event Property.
Click on the Build (. . .) button at the right edge of the After Update Event property to open the VBA Module of the Form.

Form Module Code.

Copy and paste the following Code overwriting the empty lines of the After Update Event Procedure:

Private Sub cboCat_AfterUpdate()
Dim strtxt, num As Integer
Dim colr As Long

strtxt = Nz(Me![cboCat], "")
If Len(strtxt) > 0 Then
  num = Asc(strtxt) - 64

  colr = Choose(num, 32768, 10485760, 16711680, 65280, 128, 255)
  With Me!Desc
     .ForeColor = colr
  End With
  
End If
End Sub

Close the VBA Editing Window to come back to the Form Design.
Create a Textbox to the right of the Combobox.
Display its Property Sheet (F4) and select the Other Tab.
Change the Name Property Value to Desc.
Save and close the Form with the name Sample.

Try out the Form.
Open the Sample Form in Normal View.
Type your name in the Text box.
Try out our creation by selecting the Category Code (A, B, C, D, E, F) one after the other or in random order, and watch the color of your name change.

Technorati Tags: Microsoft

Earlier Post Link References:

Duplicating fields with Conditional Formatting

a.p.r. pillaiFebruary 12, 2012Forms 4 comments

Introduction.

To make data entry tasks faster in Microsoft Access, we duplicate certain field values (Ctrl+") to bring them forward to the current record from the previous record field, wherever it becomes necessary. If there is more than one field and needs to duplicate the same into more than one record, then repeating Ctrl+” everywhere is asking for more manual action, even though it is quicker than typing all the information literally.

For example, let us assume that we are creating a Mailing List of all Family members of all the residents in our locality, and the sample data entry Form looks like the image given below:

If there are five members in a family, then the data fields Family Code and Address lines must be repeated for each member of the family in the next four records, as shown in the image below. Only the unique information needs to be keyed in.

A Sample Trial Run - Duplicating Field Values.

Create a new Table with the following structure and save it with the name FamilyDetails.

Table: FamilyDetails
Srl	Field Name	Data Type	Field Size
1	ID	AutoNumber
2	FamilyID	Integer
3	Title	Text	15
4	FName	Text	25
5	LName	Text	25
6	Add1	Text	50
7	Add2	Text	50
8	City	Text	50
9	State	Text	50
10	Country	Text	50
11	PIN	Text	10

Use the Form Wizard and create a Form in Column format, as shown in the image above, using the FamilyDetails Table and save the Form with the name frmFamilyDetails.
Create a Textbox (wide enough to type a list of field names separated with commas) on the header of the Form as shown in the image above.
Click on the Text Box to select it and display its Property Sheet (F4).
Change the Name Property Value to FieldList.
Create a Command Button below the Textbox, change its Name Property Value to cmdDup, and change the Caption Property Value to Set Carry Forward.

Display the Form’s VBA Module (Design -> Tools -> View Code). Copy and paste the following Code into the Form

Module and save the form:

The Form Module Code.

Private Sub cmdDup_Click()
'Set 'CarryForward' Text in the submitted field's TAG property
SetTagValue Me, "FieldList"

End Sub

Private Sub Form_AfterUpdate()
'Set Default Property Value
SetDefaultValue Me

End Sub

Private Sub Form_Current()
'Change color of Duplicated field to Red.
SetColorChange Me

End Sub

Save and close the Form.
Open the VBA Editing Window (ALT+F11).
Create a new Standard Module (Insert -> Module).

Functions in the Standard Module.

Copy and paste the following VBA Code (consisting of three Functions) into the new Module and save it:

Public Function SetTagValue(ByVal frm As Form, ByVal fldList As String)
'---------------------------------------------------------------------
'Author: a.p.r. pillai
'Date  : Feb 2012
'Rights: All Rights Rserved by www.msaccesstips.com
'---------------------------------------------------------------------
Dim txt, splt, ctl As Control
Dim ctlName As String, ctlType As Integer, j As Integer
Dim resetFlag As Boolean, ctrl_name As String

resetFlag = False
txt = Nz(frm.Controls(fldList).Value, "")
ctrl_name = frm.Controls(fldList).Name
If Len(txt) = 0 Then
   resetFlag = True
Else
   'split the field list sepparate with commas
   'and load them into the Array: splt()
   splt = Split(txt, ",")
End If
'   Set frm = Me
   'initialize all Textboxes and Combobox Tag & Default Value
   'Properties, except the FieldList textbox.
   'and change all field's Forecolor to black. 
   For Each ctl In frm.Controls
      ctlType = ctl.ControlType
      ctlName = ctl.Name
      If ctlName = ctrl_name Then GoTo nextitem
      
      If ctlType = 109 Or ctlType = 111 Then
         ctl.Tag = ""
         ctl.DefaultValue = ""
         ctl.ForeColor = vbBlack
      End If
nextitem:
   Next

   frm.Repaint 'show the change color on the form
   If resetFlag Then 'if the fieldlist textbox was empty then exit
     Exit Function
   End If

'Compare each field's name on the form with the field names 
'selected and stored in the 'FieldList' Array.
   For Each ctl In frm.Controls
      ctlName = ctl.Name
      ctlType = ctl.ControlType
      'control-type 109 is Textbox and 111 is combobox
      'only these controls are duplicated
      If ctlType = 109 Or ctlType = 111 Then
         For j = 0 To UBound(splt)
            'if a match found then change it's Tag Property Value to 'CarryForward'.
            If Trim(splt(j)) = ctlName Then
               ctl.Tag = "CarryForward"
            End If
         Next

      End If
   Next

End Function

Public Function SetDefaultValue(ByVal frm As Form)
'---------------------------------------------------------------------
'Author: a.p.r. pillai
'Date  : Feb 2012
'Rights: All Rights Rserved by www.msaccesstips.com
'Run this Procedure when the Form_Update() Event Procedure fires.
'---------------------------------------------------------------------
Dim ctl As Control
For Each ctl In frm.Controls
If ctl.ControlType = 109 Or ctl.ControlType = 111 Then
      'if a control found with it's Tag Property set with the text 'CarryForward'
      'copy the current field value into the Default Property.
      'When the new record is created the Default Value will automatically
      'appear in the new record.
      If ctl.Tag = "CarryForward" Then
          ctl.DefaultValue = Chr$(34) & ctl.Value & Chr$(34)

      End If
           'change the color of the data to black
           ctl.ForeColor = vbBlack
End If

Next ctl
frm.Repaint

End Function

Public Function SetColorChange(ByVal frm As Form)
'---------------------------------------------------------------------
'Author: a.p.r. pillai
'Date  : Feb 2012
'Rights: All Rights Rserved by www.msaccesstips.com
'Note  : This Function is run from the Form_Current() Event Procedure.
'---------------------------------------------------------------------

Dim ctl As Control
If frm.NewRecord Then
     For Each ctl In frm.Controls
         If ctl.ControlType = 109 Or ctl.ControlType = 111 Then
            'Format the copied values with Red Color.
            If ctl.Tag = "CarryForward" Then
               ctl.ForeColor = vbRed
            End If
         End If
     Next
frm.Repaint
End If

End Function

Data Entry.

Open the frmFamilyDetails Form in Design View.
Key in your own personal information (or the sample data from the first image above), but don't move to the next new record now.
Key in the following data field names in the text box, in the header of the Form, separated with commas.
```
FamilyID,Add1,Add2,City,State,Country,PIN
```
Set the Child Labels of TextBoxes with the actual field names (as shown in the above image) so that Users can type the required field names correctly from these labels.
Click on the Command Button.

Note: The Command Button’s Click event procedure calls the SetTagValue() function, passing the Form object and the Textbox name (FieldList) as parameters. This function reads the field list and assigns the text "CarryForward" to each field’s Tag property. Remember, the current record has not yet been updated in the table.
Press Ctrl+S to update the current record, and run the Form_AfterUpdate() procedure runs the SetDefaultValue() function.
The current record field (having its Tag Property set with the text 'CarryForward') values are copied into the Default Value Property of that field. The default values will appear in those fields in a new record.
Note: The values assigned to the Tag and Default Value properties are stored only while the form is open in Form View, not in Design View. Once the form is closed, these property values are cleared. Therefore, when you reopen the form, you must re-enter the list of fields in the text box and click the command button again to set the text "CarryForward" in the Tag property.
However, if you want certain fields to permanently use the duplication feature for new records, open the form in Design View, enter the text "CarryForward" in the Tag property of those fields, and then save the form. In this case, you no longer need the text box for the field list or the command button on the form header.
Press Ctrl++ to create a new record.
The values we have saved in the Default Value Property (through the Form_AfterUpdate() event procedure) appear in the new record. The Form_Current() event procedure runs too, and the color of the duplicated text changes to red. Now, all that's to do is to key in the rest of the information.
Press Ctrl+S first to update the current record, and then Ctrl+Plus is used to create a new record.
After keying in the data on a record, the User can directly press Ctrl+Plus (click on the New (Blank) Record control on the navigation control at the bottom of the form) to create a new record. This action will also fire the Form_AfterUpdate() event first and the Form_Current() event next.

Technorati Tags: Microsoft

How to Use Form Filter on Report

a.p.r. pillaiJanuary 31, 2012Reports No comments

Introduction.

Normally, we use a query or a processed data table as the record source for a report when printing selected items, such as customer invoices or customer ledger statements.

But what if you want to print only the current record displayed on a form?

Let’s begin with a simple method before moving on to using the form filter with the report. For this, we’ll need some sample data, a form, and a report to work with.

Sample Trial Run.

Import the Order details and Products Table from the Northwind sample database.
Use the Report Wizard and design a Report using the Order Details Table as Record Source (sample report view is given below) and save the Report with the name Rpt_OrderDetails.

Right-click to open Large Image in New Tab/Window.
Use the Form Wizard and design a multiple-item (continuous) Form using the Order Details Table (sample data view image is given below) and save it with the name Frm_OrderDetails.

Right-click to open Large Image in New Tab/Window.
Open the Form Frm_OrderDetails in the design view.
Select the Command Button Tool from the Toolbox and draw a Command Button on the Header Section of the Form.
While the Command Button is still in the selected state, display the Property Sheet (Design ->Tools -> Property Sheet or use F4) and select the All Tab on the Property Sheet.
Change the Name Property Value to cmdRpt and change the Caption Property Value to Run Report (see the Form image above).
Select the Event Tab of the Property Sheet.
Select [Event Procedure] from the drop-down list of the On Click event property and click on the Build (...) button at the right edge of the property to open the VBA window with the empty Subroutine stub of the Command Button Click Event Procedure.
Copy and paste the middle line of Code between the empty Sub-Routine Stub of the Command Button Click event procedure:
```
Private Sub cmdRpt_Click()

   DoCmd.OpenReport "Rpt_OrderDetails", acViewPreview, , "[order id] = " & Me![order id]

End Sub
```
Check the middle line of the above code. The last parameter to the 'DoCmd.OpenReport' command is a Filter condition to select the current record's Order ID number to filter all records with the same Order ID number. We have inserted one extra comma between the printing option and the filter condition to skip the choice of using the name of a Query as the Report Source Data.
The filter condition "[Order Id] = " & Me![Order ID] states that "take all the records with the current record Order ID Number as source data" for the Report.
Save the form Frm_OrderDetails and open it in normal view.
Click on any record with the same Order ID in more than one record or any record you like.
Click on the Run Report Command Button to open the Rpt_OrderDetails with the selected record.
Close the Report, and you may try it again after selecting some other record on the Form.

The above example uses only a single record or several records with the same Order ID to print the Report.

Several Items Selection.

Next, we’ll add more flexibility by using the Filter property of the form. This allows us to filter the data on the form—such as by selecting one or more Order IDs—before opening the report for printing.

Instead of hardcoding the criteria, for example:
```
"[Order ID] = " & Me![Order ID]
```
in the DoCmd.OpenReport command, we can reference the form’s Filter property directly.

With this approach, the user can filter records based on any column value on the form (such as Order ID, Quantity, or Unit Price) and then use the resulting dataset to print the report.
Make a copy of the form Frm_OrderDetails and paste it with the name Frm_OrderDetails2.
Open the form Frm_Orderdetails2 in the design view.
Click on the Command Button to select and display its property sheet (F4).
Select the Event Tab and select the On Click property.
Click on the Build (. . .) button to open the VBA window.

Copy the following code and paste replacing the existing lines of code in the Form Module:

Private Sub cmdRpt_Click()
    If Me.Filter = "" Then
        MsgBox "Apply a filter to the Form first."
    Else
        DoCmd.OpenReport "Rpt_OrderDetails", acViewPreview, , Me.Filter
    End If
End Sub

Check the last parameter setting in the DoCmd.OpenReport statement. We are asking the Report to use whatever criteria setting is available in the current Form’s (Me) Filter Property Value (like ('[Order Details].[Order ID] In (30,31,32)') to pick records for the Report. Save the Form and open it in its normal view.
Click on the Command Button to open the Report.
When you click the command button, you may see a message prompting you to “Apply a filter to the form first.” This happens because the program checks whether the form’s Filter property contains a filter condition—similar to the example we used earlier, or the sample shown in Step 7.
Note: Once a filter is applied, the filter condition remains stored in the form’s Filter property, even after the filter action is turned off. The actual filtering is controlled by another property: FilterOn, which can be set to either True or False. When you toggle the filter action on the form, the FilterOn property changes accordingly, but the filter criteria text in the Filter property is not cleared. You will only see the above message if the Filter property is completely empty.
Click on the Order ID column on any record.
Click on the Filter Toolbar button (see the image below) to display the Filter selection control.
Right-click to open Large Image in New Tab/Window.
A list of the selected field values (Order ID numbers) is displayed, and shows all the values are check-marked, indicating that all the values are in the selected state.
Click on the Select All Option to deselect all items.
Now, put check marks on the Order ID Numbers 30, 31 & 32, and click OK to close the Filter Control.
The form now shows only records of Order ID numbers 30, 31 & 32.
Click the Run Report Command Button on the Header Section of the Form to open the Report in Print Preview with the records filtered on the Form.
Close the Report.

Try it with a different Field.
Place the cursor on the Quantity Field on any record on the Form.
Click on the Filter Toolbar Button to display the Filter Control.
Select the items with Quantity values 100, 200 & 300 and click the OK button to close the control and filter the selected records.
Open the Report by clicking on the Run Report Command Button and check the report contents.
Close the Report.
Click on the Toggle Filter Toolbar button. The Filter action is reversed, and all the records are back on the Form (or the FilterOn Property Value is set as False now).
Now, click on the Run Report Command Button to preview the Report.

The Report shows the last filtered records only, rather than all the records from the Form. When we toggle the filter, Microsoft Access sets the FilterOn Property Value to False, nullifying the effect of the filter action on the form, without removing the filter condition string inserted in the Filter Property, because the User may click the Toggle Filter button again to bring back data filtered by the last set filter condition. Our program keeps repeatedly using the filter setting because the Filter Property value is not empty, and we are not checking the status of the FilterOn Property setting.

But we can solve this issue with a few changes in our program as follows:

Private Sub cmdRpt_Click()
    If Me.FilterOn Then
        DoCmd.OpenReport "Rpt_OrderDetails", acViewPreview, , Me.Filter
    Else
        DoCmd.OpenReport "Rpt_OrderDetails", acViewPreview
    End If
End Sub

Change the program as shown above. Try the effect of the Filter and Toggle Filter on the Form and Report.

Technorati Tags: Reports, Microsoft

Saving Report Pages as separate PDF Files

a.p.r. pillaiJanuary 20, 2012Utilities 2 comments

Introduction.

When printing multiple customer invoices as a single report, you often face the challenge of separating them for physical mailing.

A more efficient solution is to save each invoice as a separate PDF file on disk. This makes it simple to send invoices directly to customers via email. The benefits are immediate: reduced stationery costs, faster delivery straight to the customer’s inbox, and greater convenience for customers, who can view the invoices on their devices or print them if needed.

In short, part of the workload shifts to the customer—saving both time and money on your end.

Prepare for a Sample Run.

Let us try this with one or two Tables from the Northwind.mdb sample database.

Import the following Tables from the Northwind.mdb (or Access2007 Northwind) sample database:
- Order Details
- Products
Note: The examples use tables from the Northwind.mdb database. However, the queries, reports, and code will be executed in Access 2007. The Products table is not used directly in the query or report, but the ProductID combo box in the Order Details table references it to display product descriptions.
Open a new Query in SQL View without selecting any Table/Query from the displayed list.

Data Preparation Queries.

Copy and paste the following SQL string into the new Query’s SQL editing window and save the Query with the name Invoice_Orders_0:

SELECT [Order Details].OrderID,
 [Order Details].ProductID,
 [Order Details].Quantity,
 [Order Details].UnitPrice,
 [Order Details].Discount,
 [Quantity]*((1-[Discount])*[UnitPrice]) AS TotalValue
FROM [Order Details];

After saving and closing the above Query, create another Query, using Invoice_Orders_0 as the source, with the following SQL:
```
SELECT Invoice_Orders_0.*
FROM Invoice_Orders_0
WHERE (((Invoice_Orders_0.OrderID)=10258));
```
Save the new Query with the name Invoice_Orders_1.

Design Sample Report.

Design a Report to print Sales invoices using the Invoice_Orders_1 Query as the Record Source.
Sample Report Design Image is given below:
Right-click to open Large Image in New Tab/Window.
Save the Report with the name Rpt_Invoice. Sample Report Preview Image:
Right-click to open Large Image in New Tab/Window.

The Create_PDF() Function.

Copy and paste the following VBA Code into a Standard Module and save it:

Public Function Create_PDF(ByVal OrderStart As Integer, ByVal OrderEnd As Integer, ByVal strPath As String)
'--------------------------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : January 2012
'Rights : All Rights(c) Reserved by www.msaccesstips.com
'--------------------------------------------------------------------------------
'Function Parameters:
' 1. - OrderID Start Number
' 2. - OrderID End Number
' 3. - Target Folder Address, sample: C:\My Documents
'--------------------------------------------------------------------------------
Dim strsql_1 As String, strsql As String, criteria As String
Dim db As Database, rst As Recordset, QryDef As QueryDef
Dim int_Order As Integer, outFile As String, T As Date
Dim SQLParam As String, i As Integer, msg As String

'Invoice Query Definition, Order Number must be added at the end as criteria
strsql_1 = "SELECT Invoice_Orders_0.*  FROM Invoice_Orders_0 "
strsql_1 = strsql_1 & " WHERE (((Invoice_Orders_0.OrderID)="

'Query definition for Order Numbers between OrderStart and OrderEnd numbers
SQLParam = "SELECT DISTINCT [Order Details].OrderID FROM [Order Details] "
SQLParam = SQLParam & "WHERE ((([Order Details].OrderID) Between " & OrderStart & " And " & OrderEnd & ")) "
SQLParam = SQLParam & " ORDER BY [Order Details].OrderID;"

Set db = CurrentDb
'open the OrderIDs parameter list to process one by one
Set rst = db.OpenRecordset(SQLParam, dbOpenDynaset)
'open the Report Query definition to incorporate OrderID criteria
Set QryDef = db.QueryDefs("Invoice_Orders_1")

i = 0 'take a count of invoices printed
Do While Not rst.EOF 'cycle through the parameter list
  'get the order number
  int_Order = Nz(rst!OrderID, 0)
  If int_Order > 0 Then 'if any blank record ignore
     i = i + 1
     'create the criteria part for the Invoice Query
     criteria = int_Order & "));"
     'complete the Invoice SQL by adding the criteria.
     strsql = strsql_1 & criteria
     'Redefine the Invoice Query to print the Invoice
     QryDef.sql = strsql
     db.QueryDefs.Refresh
  
     'PDF file's target path and Order Number is the file name.
     outFile = strPath & "\" & int_Order & ".PDF"

     'Save the report as pdf file.
     DoCmd.OutputTo acOutputReport, "Rpt_Invoice", "PDFFormat(*.pdf)", outFile, False, "", 0, acExportQualityPrint
  
  '2 seconds delay loop to give enough time for Access to create the file on disk.
  T = Timer
  Do While Timer < T + 2
    DoEvents
  Loop
 End If
  rst.MoveNext
Loop
rst.Close

msg = "Order Start Number: " & OrderStart & vbCr & vbCr
msg = msg & "Order End Number: " & OrderEnd & vbCr & vbCr
msg = msg & "Invoices Printed: " & i & vbCr & vbCr
msg = msg & "Target Folder: " & strPath

MsgBox msg, , "Create_PDF()"

Set rst = Nothing
Set db = Nothing
Set QryDef = Nothing

End Function

How it Works.

Now, let us take a look at what preparations we have made so far:

The first query (Invoice_Orders_0) selects the required fields from the Order Details table for the Customer Invoice Report. It also calculates the total value of each record after applying the discount. However, this query does not include any criteria for selecting a specific OrderID or range of OrderIDs for invoice printing.

The second query (Invoice_Orders_1) is based on Invoice_Orders_0 and applies criteria to select a specific OrderID. Using a two-step query structure keeps the SQL simpler. Later, we incorporate the SQL into VBA code to dynamically update the criteria with different OrderIDs, so that each invoice can be generated and saved as a separate PDF file.

In addition, we use a third query—defined as a SQL string variable (SQLParam) within the Create_PDF() function. This query is executed through the statement:


Set rst = db.OpenRecordset(SQLParam, dbOpenDynaset)

When calling the Create_PDF() function, you must provide three parameters: the Order Start Number, the Order End Number, and the target folder path where the PDF files should be saved. The third query retrieves all order numbers within the specified range, and each order is processed individually to generate separate invoice files.

NB: To make this sample exercise simple, we are using only the transaction file to print the Invoices. As you can see from the Report specimen shown above, it doesn’t have any Customer Address printed on it. If this is required, we must consider setting relationships with the Customer Address Table in the Report Query and include the address fields also. The main idea behind this whole exercise is to save the report of individual Invoices in separate PDF files, rather than going into their details or refinement.

Let us keep that point in mind, and we will continue to review what we are doing in the VBA Code lines. You must call the Function using the following:

Syntax:

Create_PDF Start_Number, End_Number, ”PDF Files Target Folder”)

Example-1:

Create_PDF 10248,10265,”C:\My Documents”
Example-2:
x = Create_PDF(10248,10265,”C:\My Documents”)

You may call the function from a Command Button Click Event Procedure, after setting the Parameter values in Text Boxes on the Form

Example-3:
Create_PDF Me![txtSNumber], Me![txtENumber], Me![txtPathName]

With the Start Number and End Number values, the Parameter Query is redefined to extract all the Order Numbers between those two numbers from the Order Details table so that they can be used for extracting Order-wise items for printing individual Invoices. The SELECT DISTINCT clause suppresses duplicates from the parameter list.

The data source of the Rpt_Invoice Report is Invoice_Orders_1 Query. This is redefined for each Order-Id as criteria for printing the Rpt_Invoice in PDF format. The PDF files are saved in the location specified as the third parameter, C:\My Documents.

Each line in the VBA Code is commented at each step. Please go through them to understand the code.

Technorati Tags: Utility

AutoNumber with Date and Sequence Number

a.p.r. pillaiJanuary 10, 2012Auto-Number, How Tos 17 comments

Introduction

In most cases, we use the AutoNumber feature of tables to create a unique ID field. It is easy to set up, starts at 1, and increments by 1 for each new record (unless the New Values property is set to Random instead of the default Increment).

But what if we need different sequence numbers for each day’s records?

For example, in a hospital project, each patient registered on a given day must be assigned a unique Registration Card Number in the format:

yyyymmdd000

Here, the prefix is the current date, and the last three digits represent a daily sequence number starting at 001 and resetting whenever the date changes.

This approach is useful if the hospital keeps physical patient records organized by date, month, and year, since the registration number itself makes it effortless to locate any file.

We can create this unique number for each record using a custom VBA function, demonstrated below with a sample Table and Form.

The Autonum() Function

Before that, copy and paste the following Function Code into a Standard VBA Module and save it:

Public Function Autonum(ByVal strField As String, ByVal strTable As String) As String
Dim dmval As String, dt1 As String, dt2 As String, Seq As Integer, dv As String

'get the highest existing value from the table
dmval = Nz(DMax(strField, strTable), 0)

'if returned value is 0 then the table is new and empty
'create autonumber with current date and sequence 001
If Val(dmval) = 0 Then
   dv = Format(Now(), "yyyymmdd") * 1000 + 1
   Autonum = dv
   Exit Function
End If

'format the number as an 11 digit number
dv = Format(dmval, "00000000000")
'take the 3 digit sequence number separately
Seq = Val(Right(dv, 3))
'take the date value separately
dt1 = Left(dv, 8)
'get today's date
dt2 = Format(Now(), "yyyymmdd")
'compare the latest date taken from the table
'with today's date
If dt1 = dt2 Then 'if both dates are same
   Seq = Seq + 1 'increment the sequence number
   'add the sequence number to the date and return
   Autonum = Format(Val(dt1) * 1000 + Seq)
   Exit Function
Else 'the dates are different
   'take today's date and start the sequence with 1
   Autonum = Format(Val(dt2) * 1000 + 1)
End If

End Function

The Sample Table and Form

Create a sample table with the following structure, as shown in the image given below:

Right-click to open Large Image in New Tab/Window.
The Cardno Field is a text type with 11 characters in length. Both the second and third fields are also text fields with sizes of 10 and 50 characters, respectively.
Save the Table with the name Patients.
Use the Form Wizard to design a Datasheet Form for the Patient's Table and name the Form as frm_Patients.
Open the Form in Design View.
Click on the CardNo Field to select it.
Display the Property Sheet (F4). If you are using Access 2007, then you can select CardNo from the Selection Type drop-down list.
Select the Data Tab and set the following Property Values as shown below:
- Enabled = Yes
- Locked = Yes
Access 2007 users select Form from the Selection Type drop-down control. Earlier version users click on the top left corner of the Form (in the intersection where a black rectangle is shown) to ensure that the Property Sheet belongs to the Form and not to any other control on the Form.
Select the Event Tab on the Property Sheet.
Select the Before Insert event property and select Event Procedure from the drop-down list.
Click on the Build (...) Button to open the VBA Module of the Form.
Copy the middle line of the following procedure and paste it in the middle of the empty Form_BeforeInsert() lines of code in the Form module.
```
Private Sub Form_BeforeInsert(Cancel As Integer)
  Me![CARDNO] = Autonum("CardNo", "Patients")
End Sub
```
Save the Form frm_Patients with the changes made.
Open the form in normal view and type Mr., in the Title Field, and type some name in the Patient Name field. You can see that the first field is filled with the current date in yyyymmdd format and the sequence number 001 as the suffix.
Type a few more records. Since we have locked the CardNo field, Users cannot edit this field’s content. A sample image is given below:

Right-click to open Large Image in New Tab/Window.

Test Run of the Code

Now, we will test whether the sequence number resets to 001 or not when the date changes. To do that, close the frm_Patients Form.
Open the Patients' Table directly in Datasheet View.
Change the 7th and 8th digits from left (the dd digits of the date) to the previous date in all the records that you have entered so far.
For example, if the date displayed is 20120109001, then change it to the previous day, like 20120108001.
When you have completed changing all the records, close the Table.
Open the Form frm_Patients in normal view and try adding a few more records on the Form.

Tip: If you prefer to test it on different dates in the next few days, you may do so rather than changing the dates and trying it now.

You can see that the Sequence number at the end of the CardNo resets to 001 with the current date, and subsequent records’ last three digits will be incremented automatically.

The user cannot change the CardNo manually because we have set the Locked Property Value of the field to Yes. Since the Enabled Property Value is also set to Yes, the User can select this field and search for a specific CardNo, if needed.

Displaying the Number Segment Separately

If you would like to display the sequence number part separately from the date with a dash (like 20120109-005), we can do that by changing the Input Mask Property of the field, without affecting how it is recorded on the table.

Open the frm_Patients in Design View.
Click on the CardNo field to select it.
Display the Property Sheet (F4) of the Field.
Type 99999999-999;;_ in the Input Mask property.
Tip: When you set the input mask this way, the dash character between the date and sequence number is used for display only—it is not stored in the table. However, if you insert a 0 between the two semicolons, for example: 99999999-999;0;_ then the dash will also be stored in the CardNo field of the table. It’s generally better to avoid this, since the dash is meant only for readability and not for storage.
Save the Form and open it in a normal view. Now you can distinguish the date and sequence number.

Finding Patient Record.

Assume a patient arrives at the registration desk with her Registration Card. The staff member can use the CardNo to look up her record, retrieve her history, locate her physical file, and check which doctor she last consulted. When the search control is displayed, two options are available for performing the search.

Try the following:

Click on the CardNo field to select it.
Press Ctrl+F to display the search control (the search control image).

Right-click to open Large Image in New Tab/Window.

As shown in the image above, you can search for the CardNo without the dash if you remove the checkmark from the search options Search Field as Formatted. Turn the check mark on when searching with the dash separating the date and sequence number.

Download Demo Database.

DateAndSeq.zip

Technorati Tags: Autonumber, Forms, Property

Introduction.

The Sample Trial Run.

The Zoom Parameter.

Introduction.

Example-2:

One Day in Seconds.

Date and Time Number.

The Hours/Minutes Function.

Earlier Post Link References:

Introduction.

Error Message Info.

The Trial Run.

Common Error Handler Info and Log File.

The Library Database.

Introduction.

The Color Table.

Design a Form.

Form Module Code.

Try out the Form.

Earlier Post Link References:

Introduction.

A Sample Trial Run - Duplicating Field Values.

The Form Module Code.

Functions in the Standard Module.

Data Entry.

Introduction.

Sample Trial Run.

Several Items Selection.

Try it with a different Field.

Introduction.

Prepare for a Sample Run.

Data Preparation Queries.

Design Sample Report.

The Create_PDF() Function.

How it Works.

Example-1:

Example-2:

Example-3:

Introduction

The Autonum() Function

The Sample Table and Form

Test Run of the Code

Displaying the Number Segment Separately

Finding Patient Record.

Download Demo Database.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

Post Feed

Get new posts by email:

Search

USEFUL LINKS

Popular Posts

About Me

Blog Archive

Labels

Statcounter