Item cannot be found in the collection corresponding to the requested name or ordinal

[sanderson]

Hello:



And thanks for all the help this week. As I mentioned, it's been several years since I have done VBA, in favor of .NET applications. I think this question is hopefully easy??

The title says it all, re, error 3265. It appears to be happening at string creation time, which really puzzles me.

    sql = "SELECT * FROM tblocalApprovalLog " & _
          "WHERE empid = '" & rs.Fields("empid") & "' " & _
          "AND dayDate = '" & Forms!frmTimeCard.txtDate.Value & "' " & _
          "AND weekDate = '" & Forms!frmTimeCard.txtPeriodStart.Value & " - " & Forms!frmTimeCard.txtPeriodEnd.Value & "' "

Here is the entirety of the sub...

Private Sub chkApprove_Click()
    Dim rs As adodb.recordset
    Set rs = New adodb.recordset


    Dim sql As String
    sql = "SELECT * FROM tblocalApprovalLog " & _
          "WHERE empid = '" & rs.Fields("empid") & "' " & _
          "AND dayDate = '" & Forms!frmTimeCard.txtDate.Value & "' " & _
          "AND weekDate = '" & Forms!frmTimeCard.txtPeriodStart.Value & " - " & Forms!frmTimeCard.txtPeriodEnd.Value & "' "
    
    Debug.Print ("sql: " & sql)
      
    rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    
    If Not rs.BOF Then
        rs.MoveFirst
        
        If Me.chkApproved = False Then
            rs.Fields("approved") = True
        
        End If
          
    End If
    
    rs.Close
    Set rs = Nothing
               
End Sub

Again, much thanks for all the help!

[ranman256]

you dont need the ampersand AND the continuation underscore

just use 1 of them:

sql = "SELECT * FROM tblocalApprovalLog "  _
          "WHERE empid = '" & rs.Fields("empid") & "' " _
          "AND dayDate = '" & Forms!frmTimeCard.txtDate.Value & "' " _
          "AND weekDate = '" & Forms!frmTimeCard.txtPeriodStart.Value & " - " & Forms!frmTimeCard.txtPeriodEnd.Value & "' "

[sanderson]

When I remove the &, the whole thing turns red! Is this my problem? Because it is how I have coded all the other sql statements...

[ranman256]

if it doesnt like the underscores, you can use a concatinate...

sql = "SELECT * FROM tblocalApprovalLog " 
sql = sql & "WHERE empid = '" & rs.Fields("empid") & "' " 
sql = sql & "AND dayDate = '" & Forms!frmTimeCard.txtDate.Value & "' " 
sql = sql & "AND weekDate = '" & Forms!frmTimeCard.txtPeriodStart.Value & " - " & Forms!frmTimeCard.txtPeriodEnd.Value & "' "

[sanderson]

So here's the problem...

This line refers to itself...

    sql = sql & "WHERE empid = '" & rs.Fields("empid") & "' " & _

The problem is, if I remove it everything happens on the first field. The goal is to have a recordset of 1 and change a second checkbox when the first one is selected. I do not know hoe wlse to isolate the data, as empid and the two dates must all be equal.

[rpeare]

one other note, if empid is a number (autonumber) field your string won't work you'll hve to remove the ' marks around it

"WHERE empid = " & rs.Fields("empid") & " "

[sanderson]

empid is a string

[ItsMe]

The error is because of this

"WHERE empid = '" & rs.Fields("empid") & "' " & _

The rs is not SET to anything at the time you use your SQL. You need to place another variable in there. Your WHERE clause does not make any sense.

[June7]

The & and _ are needed in the original code. The issue is as ItsMe states - this concatenation references an ADODB recordset (rs) that has not been opened. Need to pull empID from somewhere other than recordset.

Also can't change value in recordset field without first setting the recordset to EditMode and then have to Update.

An UPDATE action would be simpler.

Much of this is shown in your other thread https://www.accessforums.net/program...set-54473.html.