Run-Time Error '424' Object Required - MoveFirst Error

Question

I have a login screen with a dropdown list that has their MSID from Table tTbl_LoginUsers The MSID corresponds to the PKUserId (which is an integer) like below

PKUserID    MSID
1           jjenki02
2           trensc01

I need the code below to note the PKUserID to be used for LngLongID, which places the 1 or 2 corresponding PKUSERID to the MSID into my tTbl_LoginSessions in the column fldUserName, but executing this gives me the Run-Time Error '424' Object Required. I cannot for the life of me figure out why? Any ideas?

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

NEW ADJUSTED CODE: tTbl_LoginSessions has the following fields: fldloginkey, fldUserName, fldLoginEvent, fld LogoutEvent, fldComputerName. .MoveFirst triggers it

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

THIS IS THE CODE that places that PKUSERID into the Login Sessions:

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

I also have this:

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


Option Compare Database
Public LngLoginId As Long

Insert into dbo.tTbl_LoginSessions(fldUserName, fldLoginEvent, fldComputerName) Values ('','11/4/2013 9:38:26 AM','LH7U0CNU139430Z') The code works great to put the date/time and Users machine, but the first quotes should have the integer that we are trying to get through. — T-Rex

HansUp HansUp · Accepted Answer · 2013-11-01T21:42:47

The .MoveFirst line throws an error because you have no variable named NewRecordRS. Looks like you need this instead ...

'NewRecordRS.MoveFirst
MyRS.MoveFirst

Or consider using the recordset object variable name once in a With block ...

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

Note: If tTbl_LoginUsers does not include a field named fldUserName, that code will throw an error ("Item cannot be found in the collection corresponding to the requested name or ordinal") at this line:

        LngLoginId = !fldUserName

However, I attempted to solve the "object required" error which was the original subject of your question. If you then encounter an error because fldUserName does not exit in the recordset, that is a different error.

Run-Time Error '424' Object Required - MoveFirst Error

1 Answers