[access查询]对竹笛先生的[示例]查询中自动编号的修改

时　间:2010-09-01 00:00:00
作　者:wqm099   ID：5203  城市：郑州
摘　要:对竹笛先生的“[示例]查询中自动编号”的修改，修改了序号的函数，增加了两个参数，用于确定那个字段进行了排序和使用了条件，这样不管排序不排序，也不管有没有条件，都能实现自动加序号功能。

正　文:

对竹笛先生的“[示例]查询中自动编号”的修改。下面的链接为原竹笛先生的示例

http://www.accessoft.com/article-show.asp?id=2196

本示例见附件：

点击下载此附件

图   示:

修改了序号的函数，增加了两个参数，用于确定那个字段进行了排序和使用了条件，这样不管排序不排序，也不管有没有条件，都能实现自动加序号功能。

Function SerializeAdo(qryname As String, KeyName As String, KeyValue, Optional orderneme As String = "", Optional Condition As String = "") As Long
' On Error GoTo Err_Serialize
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Dim px, tj
    If Condition <> "" Then
     tj = "select * from " & qryname & " where " & Condition
    Else
     tj = "select * from " & qryname
    End If
    If orderneme <> "" Then
     px = tj & " orDER BY " & orderneme
    Else
     px = tj
    End If
    rs.ActiveConnection = CurrentProject.Connection
     rs.Open px, , adOpenStatic, adLockReadOnly, adCmdTableDirect
     'Find the current record with key value
         Select Case rs.Fields(KeyName).Type
            ' Find data type key value
            Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, DB_DOUBLE, DB_BYTE
               rs.Find "[" & KeyName & "] = " & KeyValue
            ' Find date type key value
            Case DB_DATE
               rs.Find "[" & KeyName & "] = #" & KeyValue & "#"
            ' Find text type key value
            Case DB_TEXT
               rs.Find "[" & KeyName & "] = '" & KeyValue & "'"
            Case Else
               MsgBox "Errer ;   Invalid KeyName or KeyValue  "
         End Select

        SerializeAdo = Nz(rs.AbsolutePosition, 0)
        rs.Close
        Set rs = Nothing
       Exit Function
Err_Serialize:
         MsgBox "QueryName ", 64, " is wrong "
        Exit Function
End Function