返回记录集中的字段的索引 [英] Returning the index of a field in a recordset

问题描述

我有一个由以下代码生成的记录集对象.

I have a recordset object generated by the following code.

Private Sub GetID_Click()

'first find max id on sheet; used for if no ID is found on sheet
Dim myRange As Range
Dim maxIdOnSheet As Long
Dim clientSheet As Worksheet

Set clientSheet = Sheets("Client Codes")
Set myRange = clientSheet.Range("A1:A1048576")
maxIdOnSheet = WorksheetFunction.max(myRange) + 1

'set up connections with Nina's housing database
Dim cmd As New ADODB.Command
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim IDdb As Long
Dim IDwb As Long

'connection string
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\db\path\here\db.accdb; Persist Security Info=False"

'open connection database
conn.Open strConn

'sql statement
strSQL = "SELECT * FROM Clients WHERE (((Clients.FirstName)='" & FirstName.Value & "') AND ((Clients.LastName)='" & LastName.Value & "'));"

'open connection with the recordset
rs.Open strSQL, conn, adOpenDynamic, adLockOptimistic

'use the late-bound application match method to find out where the firstname and lastname values are in the worksheet, if found
Dim first As Long
Dim last As Long
Dim foundWB As Boolean
Dim foundDB As Boolean

foundWB = False
foundDB = False

Dim base As Long
Dim curRow As Long

base = 1

'First check to make sure if both values are in the worksheet
If Not IsError(Application.Match(FirstName.Value, Range("c" & base & ":c1048576"), False)) And Not IsError((Application.Match(LastName.Value, Range("b" & base & ":b1048576"), False))) Then
    'if it is in the worksheet, find where it is
    While found = False
        first = Application.Match(FirstName.Value, Range("c" & base & ":c1048576"), False)
        last = Application.Match(LastName.Value, Range("b" & base & ":b1048576"), False)
        If first = last Then
            foundWS = True
            curRow = curRow + first
            IDwb = Cells(curRow, 1)
        Else
            If first < last Then
                base = first + 1
                curRow = curRow + first
            ElseIf last < first Then
                base = last + 1
                curRow = curRow + last
            End If
        End If
    Wend
Else
    'if its not in the WS, it is now the highest +1
    IDwb = WorksheetFunction.max(Range("a1:a1048576")) + 1
End If

'find if its in the database
If rs.EOF Then
    'if its not in the database, find the highest number and add 1
    rs.Close
    strSQL = "SELECT MAX(Clients.[Client ID]) FROM Clients;"
    rs.Open strSQL, conn, adOpenDynamic, adLockOptimistic
    IDdb = rs.Fields(0) + 1
    MsgBox (rs.Properties.Item("Address"))
Else
    'if it is, find the first column
    IDdb = rs.Fields(0)
    foundDB = True
    MsgBox (rs.Properties.Item("Address"))
End If

If foundWB = True Then
    ClientID.Value = IDwb
ElseIf foundDB = True Then
    ClientID.Value = IDdb
Else
    If IDdb > IDwb Then
        ClientID.Value = IDdb
    ElseIf IDwb > IDdb Then
        ClientID.Value = IDwb
    Else
        ClientID.Value = IDwb
    End If
End If

End Sub

我有两个数据源-该宏位于Excel中的工作表和一个Access数据库.输入客户数据，并为其分配特定的代码.该代码成功完成了该操作.

I have two data sources - the worksheet this macro is located in Excel, and an Access database. I enter client data, and assign it a specific code. The code does that successfully.

我也想根据收到的代码填写用户表格.上面的代码成功查询了数据库并可以获得客户端ID.我还希望从查询中将诸如地址，城市，州，邮编，家庭收入之类的信息存储在数据库中.

I also want to fill out a userform based on that code which is received. The code above successfully queries the database and can get the client ID. I also want things like address, city, state, zip, household income, that is stored in the DB, from the query.

如果严格在Excel中执行此操作，则将使用match语句，如果严格通过Access，则将使用SQL查询.我试图同时或以相同的代码在Excel工作表和Access数据库上运行此查询.这需要设置一个记录集对象:在这里可以找到文档 http://www.w3schools.com/asp/ado_ref_recordset.asp 以及此处 https://msdn.microsoft.com/en -us/library/ms675841(v = vs.85).aspx .

If I was doing this in strictly Excel, I would use a match statement, and if strictly through Access, a SQL query. I'm trying to run this query on both an Excel worksheet and an Access database at the same time, or in the same code. This requires setting up a recordset object: documentation is found here http://www.w3schools.com/asp/ado_ref_recordset.asp and here https://msdn.microsoft.com/en-us/library/ms675841(v=vs.85).aspx.

我知道我可以通过类似的方式获取信息

I know that I can get the information with something like

name = rs.fields(1)
address = rs.fields(4)
city = rs.fields(5)
'...

我宁愿动态获取索引.如果人们改变数据库，我希望公式稳定.

I'd rather get the index dynamically. If people change the database around I'd like for the formula to be stable.

让我们说地址"字段在任何时候是否都可以是索引4、5、6、7.

Lets say if the field "Address" could be index 4, 5, 6, 7, until whenever.

如何动态地找到记录集对象中特定字段的索引?

How do I dynamically find the index of a specific field in a recordset object?

推荐答案

那么，如何动态地找到记录集对象中特定字段的索引?假设直到任何时候，地址"字段是否可以是索引4,5,6,7.

So, how do I dynamically find the index of a specific field in a recordset object? Lets say if the field "Address" could be index 4,5,6,7, until whenever.

在ADO记录集中没有直接属性可以获取此信息，但是可以像在此函数中那样通过遍历字段并保持计数来找到它:

There isn't a direct property in an ADO recordset to get this, but you can find it by looping through the fields and keeping a tally like I do in this function:

Public Function GetRecordsetFieldIndexFromName(rs As adodb.Recordset, ColumnName As String) As Variant
' Pass in an ADODB recordset and a column name and return the column index.
' Returns index in base 0.
' Ben S. - 11/8/2019

    On Error GoTo ErrorHandler

    Dim i As Long

    If rs Is Nothing Then
        ' Recordset is not loaded
    Else
        For i = 0 To rs.Fields.count - 1
            'Debug.Print i, rs.Fields(i).Name
            If rs.Fields(i).Name = ColumnName Then
                GetRecordsetFieldIndexFromName = i
                Exit For
            End If
        Next
    End If

Exit_Function:
    Exit Function

ErrorHandler:
    MsgBox "Error #" & err.Number & " - " & err.Description & vbCrLf & "in procedure GetRecordsetFieldIndexFromName"
    GoTo Exit_Function
    Resume Next
    Resume
End Function

这是您可以在Access中尝试的简单测试.

Here is a simple test that you can try in Access.

Public Sub TestADOrs()
' BS 11/8/2019 - Test for GetRecordsetFieldIndexFromName

    Dim i           As Long
    Dim strSQL      As String
    Dim conn        As New adodb.Connection
    Dim rs          As New adodb.Recordset

    ' Set an object pointing to the current database connection
    Set conn = CurrentProject.Connection
    strSQL = "Select Top 1 * From MSysObjects"
    rs.Open strSQL, conn, adOpenStatic, adLockOptimistic

    Debug.Print GetRecordsetFieldIndexFromName(rs, "Flags") ' This should return 4

    Set rs = Nothing
    Set conn = Nothing

End Sub

我做了与列表框和组合框控件相似的功能.通过传递控件和列/字段名称，它们将使您从控件返回索引或值.
https://stackoverflow.com/a/58773219/1898524-按字段名称引用列表框列

I made similar functions to this that work with List Box and Combo Box controls. They will let you return the index or the value from the control by passing the control and the column/field name.
https://stackoverflow.com/a/58773219/1898524 - Reference List Box Column by Field Name

这篇关于返回记录集中的字段的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！