Results 1 to 9 of 9
  1. #1
    sanderson is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    64

    Runtime 424 error... Object required.

    This piece of code produces a Runtime 424 error... Object required.


    sql2 = "SELECT * FROM tblocalApprovalLog " & _


    "INNER JOIN dbo_empbasic ON tblocalApprovalLog.empid = dbo_empbasic.empid " & _
    "WHERE (((tblocalApprovalLog.empid) <> '" & dbo_empbasic.empid & "') " & _
    "AND ((tbLocalApprovalLog.dayDate) <> '" & Forms!frmApproval.txtDayDate.Value & "') " & _
    "AND ((tblocalApprovalLog.weekDate) <> '" & Forms!frmApproval.txtWeekDate.Value & "')) "


    Please let me know what I need to do differently, and thanks in advance.

    In context:

    Private Sub UpdateApprovals()
    Dim sql As String
    Dim sql2 As String
    Dim sql3 As String

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    Dim rs2 As ADODB.Recordset
    Set rs2 = New ADODB.Recordset

    Dim empid As String
    empid = Forms!frmTimeCard.txtEmpID.Value

    sql = "SELECT dbo_empbasic.empid FROM dbo_empbasic WHERE dbo_empbasic.character06 Like '" & empid & "' And dbo_empbasic.empstatus= " & "'A'" & " ORDER BY dbo_empbasic.name"

    sql2 = "SELECT * FROM tblocalApprovalLog " & _
    "INNER JOIN dbo_empbasic ON tblocalApprovalLog.empid = dbo_empbasic.empid " & _
    "WHERE (((tblocalApprovalLog.empid) <> '" & dbo_empbasic.empid & "') " & _
    "AND ((tbLocalApprovalLog.dayDate) <> '" & Forms!frmApproval.txtDayDate.Value & "') " & _
    "AND ((tblocalApprovalLog.weekDate) <> '" & Forms!frmApproval.txtWeekDate.Value & "')) "

    Debug.Print ("sql: " & sql)
    Debug.Print ("sql2: " & sql2)

    rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

    rs.MoveFirst

    rs2.Open sql2, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

    rs2.MoveFirst

    Do While Not rs.EOF
    rs2.AddNew
    rs2.Fields("empid") = rs.Fields("empid")
    rs2.Fields("dayDate") = Forms!frmApproval.txtDayDate.Value
    rs2.Fields("weekDate") = Forms!frmApproval.txtWeekDate.Value
    rs2.Update

    rs.MoveNext

    Loop

    rs2.Close
    Set rs2 = Nothing

    rs.Close
    Set rs = Nothing


    End Sub

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I don't understand this WHERE clause
    WHERE (((tblocalApprovalLog.empid) <> '" & dbo_empbasic.empid & "') "

    What is dbo_empbasic.empid and do you get a value in your Debug.Print for it?

  3. #3
    sanderson is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    64
    The goal behind the WHERE clause is to not include records that already exist, when all three items (empid, dayDate and weekDate) are identical. empid is looking at another table.

    The data derived in sql2 will be used to populate another table for future use.

    Let me know if there is a better technique.

    Thanks!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why populate table and not just filter records?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by sanderson View Post
    ...

    Let me know if there is a better technique.

    Thanks!
    You mentioned the SQL statement was failing. The variable inserted seems like a pain point. That is why I asked what that variable looks like within your Immediate Window.

  6. #6
    sanderson is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    64
    The problem is, sql2 is not being set so I cannot view the syntax. Normally, one would not expect to get a 424 error on string creation, but perhaps when the query tries to run.

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    That is true. But you are using WHERE criteria to compare a single field with an entire Domain. In other words, you are not providing a valid object. Access is looking for something it can equate to a value. Perhaps you need to create a JOIN and not use WHERE criteria for that field.

    I did not get any errors with the following.

    Code:
    tttt = "ddddd"
    rrrr = "rrrrrrr"
    fffff = "gggg"
    
    Dim sql2 As String
    sql2 = "SELECT * FROM tblocalApprovalLog " & _
     "INNER JOIN dbo_empbasic ON tblocalApprovalLog.empid = dbo_empbasic.empid " & _
     "WHERE (((tblocalApprovalLog.empid) <> '" & fffff & "') " & _
     "AND ((tbLocalApprovalLog.dayDate) <> '" & rrrr & "') " & _
     "AND ((tblocalApprovalLog.weekDate) <> '" & tttt & "')) "
     
    
    Debug.Print sql2

    So you should be able to replace dbo_empbasic.empid with a variable that is a simple data type and be able to get something printed to the immediate window.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    On what line does the error ocurr? The error usually means an object has not been dim'd or set when trying to use it.
    I'm thinking this is the problem: Set rs = New ADODB.Recordset.
    You've already declared the variable in memory with Dim rs As ADODB.Recordset, so now set it to reference a table or query.
    After setting up the required variables, I use Set rst = db.OpenRecordset(svSql, dbOpenSnapshot) for example. However, I've only used DAO recordsets (even when adding records to a recordset), so maybe I'm way off base here. The fact that everyone else is focussed on the sql statement probably means I missed the mark (again!).

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Never mind me. I see that if I remove the period between dbo_empbasic.empid, I can get it to debug.print the portion before the forms reference. I tried to fix it with ()'s around the value, but that's not it. I believe the statement is being compiled as it's processed, because when I remove the period, it chokes because it can't find the form. I did not know a string statement would be evaluated/compiled like this. Anyway, looks like ItsMe is on the right track when asking what is dbo_empbasic.empid? Now I think it's being interpreted as an object without a reference. If it's a table.field reference, shouldn't dbo_empbasic.empid be an actual string/date/number etc, OR variable that contains a value OR a forms!frm.control type of reference?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Runtime Error 424 Object Required
    By Maltheo2005 in forum Programming
    Replies: 9
    Last Post: 06-13-2013, 04:52 PM
  2. Object Required Error
    By sgp667 in forum Programming
    Replies: 1
    Last Post: 11-06-2012, 03:15 AM
  3. Replies: 13
    Last Post: 06-12-2012, 09:52 PM
  4. Error: Object Required
    By compooper in forum Programming
    Replies: 6
    Last Post: 06-22-2011, 07:52 AM
  5. Object Required Error.
    By Robeen in forum Forms
    Replies: 1
    Last Post: 03-28-2011, 10:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums