Sequential database table record navigation

Move sequentially through the records of a Microsoft Access database table by clicking on [First], [Previous], [Next], and [Last] buttons.

ADVERTISEMENTS

Problem

You have a database table for which you would like to display all of the data, one record record at a time, by sequentially moving forward and backward through the table.

Solution

Use rules on buttons to move forward and backward through the database table and apply a filter on a repeating section based on the current record position in the table.

Discussion

You can accomplish this functionality as follows:

  1. Create a Player table in a Microsoft Access database (Figure 1).
  2. Create a new InfoPath form: Use New from Data Connection... and bind it to the Player table.
  3. Add a field called CurrentRecordNumber of type Whole Number (integer) to the Main data source of the form.
    Your Main data source should now resemble the following figure:

    Figure 2. Main data source of the InfoPath form.
  4. Add a table to the InfoPath form.
  5. Drag the CurrentRecordNumber field from the Main data source, and drop it below the table.
  6. Drag the Player repeating group from the dataFields node in the Main data source and drop it into the table; use a Repeating Section with Controls to display the records.
  7. Remove the border from the Repeating Section and disable the possibility to insert and delete sections in its Properties dialog box.
  8. Add 4 buttons below the repeating section in the table and label them First, Previous, Next, and Last.
  9. Modify the table and controls to resemble the following figure:

    Figure 3. The InfoPath form in design mode.
  10. Add the following rule on the First button:
    Set a field's value: CurrentRecordNumber = 1
    This button will move the record pointer to the first record in the database table.
  11. Add the following rule on the Last button:
    Set a field's value: CurrentRecordNumber = count(Player)
    This button will move the record pointer to the last record in the database table by counting the total amount of records in the Player table.
  12. Add the following rule on the Previous button:
    Set a field's value: CurrentRecordNumber = CurrentRecordNumber - 1
    This button will move the record pointer one position backwards in the table.
    Add the following condition for this rule:
    CurrentRecordNumber > 1
    This will prevent the record pointer to go backwards beyond the first record.
  13. Add the following rule on the Next button:
    Set a field's value: CurrentRecordNumber = CurrentRecordNumber + 1
    This button will move the record pointer one position forward in the table.
    Add the following condition for this rule:
    CurrentRecordNumber < count(Player)
    This will prevent the record pointer to go forward beyond the last record.
  14. Since you want to populate the fields on the InfoPath form with data from the player at the current record position, you need to filter the data being shown by the repeating section as follows: Double-click on the repeating section to open its Properties dialog box. Click on the Display tab. Click on the Filter Data... button. Add a Condition that says:
    position() = ../../my:CurrentRecordNumber
    Note: You will have to type in this expression yourself in the Specify Filter Conditions dialog box by choosing The expression from the first dropdown list box.
  15. To autofill the Main data source of the InfoPath form with data as soon as it is opened, do the following: Go to Tools > Form Options.... Click on the Open and Save tab. Click on the Rules... button. Add an Action that says:
    Query using a data connection: Main connection
  16. Set the Default Value of the CurrentRecordNumber field to 1, so that the first record is shown immediately when the form is first opened.

You should now be able to sequentially navigate through the database table and retrieve the data for each player.

 
 Subscribe for updates via RSS or Email

Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners
InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013
InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners
InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010
InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers
InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services


Related InfoPath Articles:

ADVERTISEMENTS

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services