Finding a Row in a Visio DataRecordset

A recent newsgroup post asked how to find a row in a Visio DataRecordset for a given criteria.  Well, I have used the IT Asset Management sample in Visio 2007 Professional to demonstrate how this can be done with a VBA function.

Of course, you may wish to improve the user interface because I limited myself to using an InputBox, and I have assumed that there is only one column defining the primary key of the DataRecordset.

The sub function, FindRow, listed below first checks whether the External Data window is open, and then gets the active DataRecordset.  The code then reads the columns of the DataRecordset and prompts for a selection of the number of the column that you wish to search for a value in.

image

The code will abort if an incorrect number is entered, otherwise you will be asked to enter the text to find in the column.  Note that you can enter a wildcard "*" character.

image

You will then be prompted to enter the row number of the row that you want to navigate to in the External Data window.  Note that you will not be prompted to select a row if there is only one matching row.

image

When you enter a row number, the code gets the related row ID, and uses this to select the row in the External Data window.

image

Here is a listing of the VBA code to find a row by criteria:

Public Sub FindRow()
‘David Parker : 11/11/2007
‘To find a row with a given string in a DataRecordset
Dim findString As String
Dim colString As String
Dim drs As DataRecordset
Dim col As DataColumn
Dim win As Visio.Window
Dim rowids() As Long
Dim i As Integer
Dim msg As String
Dim rowid As Long
Dim vData As Variant
Dim primKeys() As String
Dim primKey As Integer
Dim rowString As String

    ‘Check the active windows to find the window
    For Each win In Visio.ActiveWindow.Windows
        ‘The External Data window has ID = 2044
        If win.ID = 2044 Then
            Set drs = win.SelectedDataRecordset
            Exit For
        End If
    Next win
    If drs Is Nothing Then
        MsgBox "You do not have a recordset selected!"
        Exit Sub
    End If
    ‘Get the primary key (single assumed)
    drs.GetPrimaryKey visKeySingle, primKeys
    msg = "Enter the number of the column to search:"
    i = 1
    For Each col In drs.DataColumns
        msg = msg & vbCrLf & CStr(i) & vbTab & col.Name
        If primKeys(0) = col.Name Then
            primKey = i
        End If
        i = i + 1
    Next col
    colString = InputBox(msg, , "1")
    If Not IsNumeric(colString) Then
        MsgBox "You must enter a number between 1 and " & drs.DataColumns.Count
        Exit Sub
    ElseIf CInt(colString) < 1 Or CInt(colString) > (drs.DataColumns.Count) Then
        MsgBox "You must enter a number between 0 and " & drs.DataColumns.Count
        Exit Sub
    End If
    findString = InputBox("Enter text to find")
    If Len(findString) = 0 Then
        Exit Sub
    End If
    rowids = drs.GetDataRowIDs(drs.DataColumns.Item(CInt(colString)) & " LIKE ‘" & findString & "’")

    If UBound(rowids) > 0 Then
        ‘Need to ask which row
        msg = "Select a row to highlight"
        msg = msg & vbCrLf & "Row" & vbTab & drs.DataColumns(primKey).Name & _
            vbTab & drs.DataColumns(CInt(colString)).Name
        For i = 0 To UBound(rowids)
            rowid = rowids(i)
            vData = drs.GetRowData(rowid)
            msg = msg & vbCrLf & CStr(i + 1) & vbTab & vData(primKey – 1) & _
                vbTab & vData(CInt(colString) – 1)
        Next i
        rowString = InputBox(msg, , "1")
    Else
        ‘Only one row to select
        rowid = rowids(0)
        vData = drs.GetRowData(rowid)
        rowString = "1"
    End If
    If Not IsNumeric(rowString) Then
        MsgBox "You must enter a number between 1 and " & UBound(rowids) + 1
        Exit Sub
    ElseIf CInt(rowString) < 1 Or CInt(rowString) > (UBound(rowids) + 1) Then
        MsgBox "You must enter a number between 1 and " & UBound(rowids) + 1
        Exit Sub
    End If
    ‘Select the row in the External Data window
    win.SelectedDataRowID = rowids(CInt(rowString) – 1)
End Sub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Chris Webb's BI Blog

Microsoft Analysis Services, MDX, DAX, Power Pivot, Power Query and Power BI

davecra.wordpress.com/

Microsoft Office 365 Development, and more...

PowerShell.Amsterdam

Automate, Accelerate, Accurate

johnvisiomvp

Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

Nilsandrey's Weblog

Just another WordPress.com weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Shapes, Stencils, Drawings Templates, Tutorials, Tips & Developer Info for Microsoft Visio

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: