Visual Basic (VB and VBA)

Copyright 1999-2001 Christopher Greaves. All rights reserved. Home Page and email to [email protected]

If in doubt, record a macro and inspect the entrails!

Here is an INDEX to all the procedures.

You will probably need one copy of my GLOBAL DECLARATIONS.

Public Function strGetTableRow(strPath As String, strFile As String, strTable As String, strKey As String, strKeyValue As String) As String
' Procedure :   strGetTableRow
' Description:  Returns the first string row that matches a key in an Acess97 database table.
' Copyright: Chris Greaves
' Inputs:       Database path, file and table name, field name and value, all as strings.
' Returns:      The specified row of the table.
' Assumes:      None.
' Side Effects: None.
' Tested:       By the calls shown below.
    Dim wrk As Workspace
    Set wrk = wrkCreateWorkspace("", "admin", "")
    Dim dbs As Database
    Set dbs = dbsOpenDatabase(wrk, strPath, strFile) ' given path and file
    Dim rst As Recordset
    Set rst = rstOpenRecordset(dbs, "SELECT * from " & strTable) ' given table
    Dim lngRows As Long
    rst.MoveLast:     rst.MoveFirst
    lngRows = rst.RecordCount ' this many records/rows.
    Dim strArray
    strArray = rst.GetRows(lngRows)
    ' Now find the strKey in the field list, then examine that column for the key value
    Dim intFld As Integer
    For intFld = 0 To rst.Fields.Count - 1
        If rst.Fields(intFld).Name = strKey Then
            ' Now find the key value in that column
            For lngRows = 0 To rst.RecordCount - 1
                If strArray(intFld, lngRows) = strKeyValue Then
                    strGetTableRow = strArray(0, lngRows) & strArray(1, lngRows) & strArray(2, lngRows)
                    Exit For
                Else
                End If
            Next lngRows
        Else
        End If
    Next intFld
    Call CloseRecordset(rst)
    Call CloseDatabase(dbs)
    Call wrkCloseWorkspace(wrk)
'Sub TESTstrGetTableRow()
'    MsgBox strGetTableRow(strTemplatePath(), "utilities.mdb", "Utilities", "Module", "DAOAccess")
'    MsgBox strGetTableRow(strTemplatePath(), "Utilities.mdb", "Utilities", "Module", "MathStats")
'End Sub
End Function

We all knew nothing when we started …

Home Page and Contact Information Send email to [email protected]. This page was last updated Thursday, November 15, 2001

Visual Basic (VB and VBA)

Copyright 1999-2001 Christopher Greaves. All rights reserved. Home Page and email to [email protected]

If in doubt, record a macro and inspect the entrails!

Here is an INDEX to all the procedures.

You will probably need one copy of my GLOBAL DECLARATIONS.

Public Function strGetTableRow(strPath As String, strFile As String, strTable As String, strKey As String, strKeyValue As String) As String
' Procedure :   strGetTableRow
' Description:  Returns the first string row that matches a key in an Acess97 database table.
' Copyright: Chris Greaves
' Inputs:       Database path, file and table name, field name and value, all as strings.
' Returns:      The specified row of the table.
' Assumes:      None.
' Side Effects: None.
' Tested:       By the calls shown below.
    Dim wrk As Workspace
    Set wrk = wrkCreateWorkspace("", "admin", "")
    Dim dbs As Database
    Set dbs = dbsOpenDatabase(wrk, strPath, strFile) ' given path and file
    Dim rst As Recordset
    Set rst = rstOpenRecordset(dbs, "SELECT * from " & strTable) ' given table
    Dim lngRows As Long
    rst.MoveLast:     rst.MoveFirst
    lngRows = rst.RecordCount ' this many records/rows.
    Dim strArray
    strArray = rst.GetRows(lngRows)
    ' Now find the strKey in the field list, then examine that column for the key value
    Dim intFld As Integer
    For intFld = 0 To rst.Fields.Count - 1
        If rst.Fields(intFld).Name = strKey Then
            ' Now find the key value in that column
            For lngRows = 0 To rst.RecordCount - 1
                If strArray(intFld, lngRows) = strKeyValue Then
                    strGetTableRow = strArray(0, lngRows) & strArray(1, lngRows) & strArray(2, lngRows)
                    Exit For
                Else
                End If
            Next lngRows
        Else
        End If
    Next intFld
    Call CloseRecordset(rst)
    Call CloseDatabase(dbs)
    Call wrkCloseWorkspace(wrk)
'Sub TESTstrGetTableRow()
'    MsgBox strGetTableRow(strTemplatePath(), "utilities.mdb", "Utilities", "Module", "DAOAccess")
'    MsgBox strGetTableRow(strTemplatePath(), "Utilities.mdb", "Utilities", "Module", "MathStats")
'End Sub
End Function

We all knew nothing when we started …

Home Page and Contact Information Send email to [email protected]. This page was last updated Thursday, November 15, 2001