[5分] 查找取值

两种方式，

一：按钮命令方式

sub genxingA()
    Dim rst As ADODB.Recordset, i As Long, fld As ADODB.Field, str As String
    Set rst = New ADODB.Recordset
    rst.Open "SELECT * FROM 更新表 ", CurrentProject.Connection, adOpenStatic, adLockOptimistic
    For i = 1 To rst.RecordCount
        str = ""
        For Each fld In rst.Fields
            If Nz(fld.Value, 0) <> 0 And fld.Name <> "ID" And fld.Name <> "取值" Then
                str = str & DLookup("代号", "参数", "连接字段='" & fld.Name & "'")
            End If
        Next
        rst.Fields("取值") = str
        rst.Update
        rst.MoveNext
    Next i
    rst.Close
    Set rst = Nothing
End sub

二、查询方式

创建一个函数如下

Function genxingB(THEID As Long) As String
    Dim rst As ADODB.Recordset, fld As ADODB.Field, str As String
    Set rst = New ADODB.Recordset
    rst.Open "SELECT * FROM 更新表 WHERE ID=" & THEID, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    str = ""
    For Each fld In rst.Fields
        If Nz(fld.Value, 0) <> 0 And fld.Name <> "ID" And fld.Name <> "取值" Then
            str = str & DLookup("代号", "参数", "连接字段='" & fld.Name & "'")
        End If
    Next
    genxingB = str
    rst.Close
    Set rst = Nothing
End Function

创建一个查询如下：

UPDATE 更新表 SET 更新表.取值 = genxingB([ID]);