运行时错误"424"需要对象 - 移动第一个错误

Question

我有一个登录屏幕，其中包含表 tTbl_LoginUsers 中的 MSID 下拉列表MSID 对应于 PKUserId（这是一个整数），如下所示

PKUserID    MSID
1           jjenki02
2           trensc01 

我需要下面的代码来记下用于 LngLongID 的 PKUserID，它将 1 或 2 个对应于 MSID 的 PKUSERID 放入 fldUser Name 列中的tTbl_LoginSessions中，但执行此操作会给我运行时错误"424"对象需要。我一辈子都想不通为什么？有什么想法吗？

Private Sub CboUser_Click()
Dim MyCon As ADODB.Connection
Dim MyRS As ADODB.Recordset
    Set MyCon = New ADODB.Connection
    MyCon.Open "DRIVER=SQL Server;SERVER=dbswd****;UID=*****;PWD=*****;DATABASE=Regulatory;"
    Set MyRS = New ADODB.Recordset
'/The following code focuses on the DropDown field that the User selects their MSID
'/if MyNum = MSID, then MoveFirst to get the PKUserID(Column 1)from tTbl_LoginUsers
CboUser.SetFocus
MyNum = CboUser.Text
MyRS.Open "Select * from dbo.tTbl_LoginUsers Where MSID = '" & MyNum & "'", MyCon
Debug.Print strSQL
NewRecordRS.MoveFirst
LngLoginId = NewRecordRS!fldUserName

End Sub

新调整代码：tTbl_LoginSessions具有以下字段：fldloginkey，fldUserName，fldLoginEvent，fld LogoutEvent，fldComputerName。.移动优先触发它

Private Sub CboUser_Click()
Dim MyCon As ADODB.Connection
Dim MyRS As ADODB.Recordset
    Set MyCon = New ADODB.Connection
    MyCon.Open "DRIVER=SQL Server;SERVER=dbswd0027;UID=Mickey01;PWD=Mouse02;DATABASE=Regulatory;"
    Set MyRS = New ADODB.Recordset
With MyRS
'/The following code focuses on the DropDown field that the User selects their MSID
'/if that MyNum = MSID, then MoveFirst to get the PKUserID(Column 1)from tTbl_LoginUsers
     CboUser.SetFocus
     MyNum = CboUser.Text
     MyRS.Open "Select * from dbo.tTbl_LoginUsers Where MSID = '" & MyNum & "'", MyCon
     Debug.Print strSQL
   If Not (.BOF And .EOF) Then
        .MoveFirst
        LngLoginId = !fldUserName
    End If
End With
Debug.Print strSQL
'NewRecordRS.MoveFirst
'LngLoginId = NewRecordRS!fldUserName

End Sub

这是将该 PKUSERID 放入登录会话的代码：

Function CreateSession()
'/This function records the details regarding the login details of the person
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim strSQL As String
Dim WhoAmI As Long
Set NewIdentRS = New ADODB.Recordset
Dim LngLoginId As String, StrComputerName As String

'passing variables
StrMSID = LngLoginId
'old StrMSID = StrLoginName
StrComputerName = FindComputerName
   'Declaring what table you are passing the variables to
    strSQL = "Insert into dbo.tTbl_LoginSessions(fldUserName, fldLoginEvent, fldComputerName) Values ('" & StrMSID & "','" & Now() & "','" & StrComputerName & "')"
     Debug.Print strSQL
    'connect to SQL Server
    Set con = New ADODB.Connection
    With con
        .ConnectionString = cSQLConn
        .Open
    End With
    'write back
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = con
        .CommandText = strSQL
        .CommandType = adCmdText
        .Execute

    End With
    con.Close
    Set cmd = Nothing
    Set con = Nothing

End Function

我也有这个：

Public Function GrabMSID(frmObject)
GrabMSID = LngUserID
End Function
'Call it this way:
MyMSID = GrabMSID(Me)

Option Compare Database
Public LngLoginId As Long

Answer 1

.MoveFirst行引发错误，因为您没有名为 NewRecordRS 的变量。 看起来你需要这个代替...

'NewRecordRS.MoveFirst
MyRS.MoveFirst

或者考虑在With块中使用一次记录集对象变量名称...

With MyRS
    .Open "Select * from dbo.tTbl_LoginUsers Where MSID = '" & _
        MyNum & "'", MyCon
    If Not (.BOF And .EOF) Then
        .MoveFirst
        LngLoginId = !fldUserName
    End If
End With

注意：如果tTbl_LoginUsers不包含名为 fldUserName 的字段，则该代码将在以下行抛出错误（"在与请求的名称或序号对应的集合中找不到项目"）：

        LngLoginId = !fldUserName

但是，我试图解决"需要对象"错误，这是您问题的原始主题。 如果随后遇到错误，因为 fld用户名未在记录集中退出，则这是另一个错误。