Check for recordset

**sanderson** · 08-17-2015, 01:32 PM

Hello again:

This simple question is really getting quite complicated, and I thought it was simple.

I am writing a query (sql3 / rs3) for the sole purpose of checking for data. If there is nothing, then there is nothing to add. Otherwise, I want to add the date. I believe there is confusion when there is nothing, because I need to open and close the recordset. Even if there is nothing, there is apparently an open recordset that must be closed. But then again, I get errors when trying to close it.

The code is below:

Code:

Private Sub PopulateApprovalLog()
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 rs3 As adodb.Recordset
Set rs3 = New adodb.Recordset
sql = "SELECT dbo_empbasic.name, dbo_empbasic.empid, * " & _
"FROM dbo_empbasic " & _
"WHERE (((dbo_empbasic.character06) Like " & Forms!frmTimeCard.txtEmpID.Value & ") AND ((dbo_empbasic.empstatus)= " & Chr$(34) & "A" & Chr$(34) & ")) " & _
"ORDER BY dbo_empbasic.name; "
Debug.Print ("sql: " & sql)
sql2 = "SELECT * FROM tblocalApprovalLog"
Debug.Print ("sql2: " & sql2)
rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If Not rs.BOF Then
    rs.MoveFirst
    
    Do While Not rs.EOF Or rs.BOF
        rs2.Open sql2, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    
        Do While Not rs.EOF
            sql3 = "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 ("sql3: " & sql3)
            
            On Error GoTo nxt
            rs3.Open sql3, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
            
            If rs3.RecordCount > 0 Then
               rs2.AddNew
               rs2.Fields("empid") = rs.Fields("empid")
               rs2.Fields("dayDate") = txtDayDate.Value
               rs2.Fields("weekDate") = txtWeekDate.Value
               rs2.Update
            End If
        
nxt:
            If rs3.RecordCount = -1 Then
                Debug.Print ("No Recordcount")
            End If
            
            rs3.Close
            Set rs3 = Nothing
    
        Loop
        rs.MoveNext
    
    Loop
    rs2.Close
    Set rs2 = Nothing
End If
rs.Close
Set rs = Nothing
End Sub

**June7** · 08-17-2015, 01:56 PM

Post code between code tags to retain indentation and be more readable - I modified your post.

empid, daydate, weekdate are text fields in table or are they number and date types?

Code:

sql2 = "SELECT * FROM tblocalApprovalLog"
Debug.Print ("sql2: " & sql2)
rs2.Open sql2, CurrentProject.Connection, adOpenDynamic, adLockOptimisticsql = "SELECT dbo_empbasic.name, dbo_empbasic.empid, * " & _
"FROM dbo_empbasic " & _
"WHERE (((dbo_empbasic.character06) Like " & Forms!frmTimeCard.txtEmpID & ") AND ((dbo_empbasic.empstatus)= " & Chr$(34) & "A" & Chr$(34) & ")) " & _
"ORDER BY dbo_empbasic.name; "
Debug.Print ("sql: " & sql)
rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs.MoveFirst
While Not rs.EOF
    sql3 = "SELECT * FROM tblocalApprovalLog " & _
    "WHERE empid = '" & rs!empid & "' " & _
    "AND dayDate = #" & Forms!frmTimeCard.txtDate & "# " & _
    "AND weekDate = #" & Forms!frmTimeCard.txtPeriodStart & "# AND #" & Forms!frmTimeCard.txtPeriodEnd & "#;"
    Debug.Print ("sql3: " & sql3)
    rs3.Open sql3, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    If rs3.RecordCount > 0 Then
       rs2.AddNew
       rs2!empid = rs.empid
       rs2!dayDate = Me.txtDayDate
       rs2!weekDate = Me.txtWeekDate
       rs2.Update
    Else
        Debug.Print ("No Recordcount")
    End If
    rs3.Close
    rs.MoveNext
Wend
rs.Close
rs2.Close

Or

Code:

Private Sub PopulateApprovalLog()
Dim sql As String
Dim sql3 As String
Dim rs As adodb.Recordset
Set rs = New adodb.Recordset
Dim rs3 As adodb.Recordset
Set rs3 = New adodb.Recordset
sql = "SELECT dbo_empbasic.name, dbo_empbasic.empid, * " & _
"FROM dbo_empbasic " & _
"WHERE (((dbo_empbasic.character06) Like " & Forms!frmTimeCard.txtEmpID & ") AND ((dbo_empbasic.empstatus)= " & Chr$(34) & "A" & Chr$(34) & ")) " & _
"ORDER BY dbo_empbasic.name; "
Debug.Print ("sql: " & sql)
rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs.MoveFirst
While Not rs.EOF
    sql3 = "SELECT * FROM tblocalApprovalLog " & _
    "WHERE empid = '" & rs!empid & "' " & _
    "AND dayDate = #" & Forms!frmTimeCard.txtDate & "# " & _
    "AND weekDate BETWEEN #" & Forms!frmTimeCard.txtPeriodStart & #" AND #" & Forms!frmTimeCard.txtPeriodEnd & "#;"
    Debug.Print ("sql3: " & sql3)
    rs3.Open sql3, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    If rs3.RecordCount > 0 Then
        CurrentDb.Execute "INSERT INTO tblocalApprovalLog(empid, daydate, weekdate) VALUES('" & rs!empid & "', #" & Me.txtDayDate & "#, #" & Me.txtWeekDate & "#)"
    Else
        Debug.Print ("No Recordcount")
    End If
    rs3.Close
    rs.MoveNext
Wend
rs.Close
End Sub

But why do you even open rs3 - its data is not used. A DLookup() or DCount() could be used instead of opening and closing a recordset.
If IsNull(DLookup("empid", "tblocalApprovalLog", "empid='" & rs!empid & "' AND dayDate = '" & Forms!frmTimeCard.txtDate & "' " & _
"AND weekDate BETWEEN #" & Forms!frmTimeCard.txtPeriodStart & # AND #" & Forms!frmTimeCard.txtPeriodEnd & "#")) Then

**sanderson** · 08-17-2015, 03:17 PM

I am so confused, sorry to say. The purpose of rs3 is to check for data each time. If it already exists, it should simply do nothing so as not to duplicate the information. Because there is no data, the rs3 does not work.

You are right, perhaps a dLookup would be better. I just thought it would be easy to use a single query statetment, because we are looking at three fields. If there were a way to keep it, I would much prefer to do it this way.

However, rs3 cannot happen because there is no data in the approval log (which means it needs to be added).

I do prefer to do a select query over an insert query. I just feel more in control. The code you provided for insert also did not put any information in the table.

Note, there can be multiple rows of data generated off of sql, which needs to be added via rs2 after checking that it does not already exist in rs3.

I hope this makes sense and brings more clarity. I have been doing a lot of .NET in recent years, and quite honestly have forgotten this stuff in VBA. This is an old program we are modifying, and I was hoping it would have come sooner that later. Thanks for your patience with me, and my apologies for not wrapping the code, as the syntax is a little different here than in other forums.

Code:

Private Sub PopulateApprovalLog()
    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 rs3 As adodb.recordset
    Set rs3 = New adodb.recordset


    sql = "SELECT dbo_empbasic.name, dbo_empbasic.empid, * " & _
          "FROM dbo_empbasic " & _
          "WHERE (((dbo_empbasic.character06) Like " & Forms!frmTimeCard.txtEmpID.Value & ") AND ((dbo_empbasic.empstatus)= " & Chr$(34) & "A" & Chr$(34) & ")) " & _
          "ORDER BY dbo_empbasic.name; "
    Debug.Print ("sql: " & sql)
    
    sql2 = "SELECT * FROM tblocalApprovalLog"
    Debug.Print ("sql2: " & sql2)
    
    rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    
    If Not rs.BOF Then
        rs.MoveFirst
   
        Do While Not rs.EOF Or rs.BOF
            rs2.Open sql2, CurrentProject.Connection, adOpenDynamic, adLockOptimistic


            While Not rs.EOF
                sql3 = "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 ("sql3: " & sql3)
                
                rs3.Open sql3, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
                                
                If rs3.RecordCount > 0 Then
                    rs2.AddNew
                    rs2.Fields("empid") = rs.Fields("empid")
                    rs2.Fields("dayDate") = txtDayDate.Value
                    rs2.Fields("weekDate") = txtWeekDate.Value
                    rs2.Update
                    
                Else
                    Debug.Print ("No RecordCount")
                
                End If
                
                rs3.Close
                Set rs3 = Nothing
                
            Wend
            rs.MoveNext
            
        Loop
        rs2.Close
        Set rs2 = Nothing
        
    End If
          
    rs.Close
    Set rs = Nothing
        
End Sub

**June7** · 08-17-2015, 03:28 PM

Did not answer question about field types.

I think you have too much nesting loops.

**sanderson** · 08-18-2015, 07:51 AM

The field types are strings.

There are basically two loops. I have to next through the first set of information in order to write it to the table.

**sanderson** · 08-18-2015, 09:04 AM

Here lies the answer!

Code:

Private Sub PopulateApprovalLog()
    Dim sql As String
    Dim sql2 As String
    
    Dim rs As adodb.recordset
    Set rs = New adodb.recordset
    
    Dim rs2 As adodb.recordset
    Set rs2 = New adodb.recordset


    sql = "SELECT dbo_empbasic.name, dbo_empbasic.empid, * " & _
          "FROM dbo_empbasic " & _
          "WHERE (((dbo_empbasic.character06) Like " & Forms!frmTimeCard.txtEmpID.Value & ") AND ((dbo_empbasic.empstatus)= " & Chr$(34) & "A" & Chr$(34) & ")) " & _
          "ORDER BY dbo_empbasic.name; "
    Debug.Print ("sql: " & sql)
    
    sql2 = "SELECT * FROM tblocalApprovalLog"
    Debug.Print ("sql2: " & sql2)
    
    rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    
    If Not rs.BOF Then
        rs.MoveFirst
   
        Do While Not rs.EOF Or rs.BOF
            rs2.Open sql2, CurrentProject.Connection, adOpenDynamic, adLockOptimistic


            While Not rs.EOF
                Dim criteria As String
                criteria = "empid = '" & rs.Fields("empid") & "' " & _
                           "AND dayDate = '" & Forms!frmTimeCard.txtDate.Value & "' " & _
                           "AND weekDate = '" & Forms!frmTimeCard.txtPeriodStart.Value & " - " & Forms!frmTimeCard.txtPeriodEnd.Value & "' "
                
                Debug.Print ("criteria: " & criteria)
                Debug.Print ("DCount: " & DCount("*", "tblocalApprovalLog", criteria))
                
                If DCount("*", "tblocalApprovalLog", criteria) > 0 Then
                    Debug.Print ("Do Nothing...")
                    
                Else
                    rs2.AddNew
                    rs2.Fields("empid") = rs.Fields("empid")
                    rs2.Fields("dayDate") = txtDayDate.Value
                    rs2.Fields("weekDate") = txtWeekDate.Value
                    rs2.Update
            
                End If
                
                rs.MoveNext
                
            Wend
            
        Loop
        rs2.Close
        Set rs2 = Nothing
        
    End If
          
    rs.Close
    Set rs = Nothing
        
End Sub

Check for recordset

Thread Tools

Check for recordset

Similar Threads

How to check recordset working properly or not?

how to filter a date range and check another field is true with check box syntax err

Theory I hope to work with recordset and filtering another recordset

Form Recordset Property (Want ADODB.Recordset for MDB Forms)

get data from one recordset with the field name being in another recordset

Posting Permissions