recordset question

[sanderson]

Hello:



I am using Access 2013 with VBA.

I have the following code...

    Dim rs As adodb.recordset
    Set rs = New adodb.recordset
    Dim sql As String

    sql = "SELECT * FROM tblocalApprovalLog "
    sql = sql & "WHERE approve = True "
    sql = sql & "AND approved = False "
    sql = sql & "AND dayDate = '" & Forms!frmTimeCard.txtDate.Value & "' "
    sql = sql & "AND weekDate = '" & Forms!frmTimeCard.txtPeriodStart.Value & " - " & Forms!frmTimeCard.txtPeriodEnd.Value & "' "
    sql = sql & "AND supid = '" & Forms!frmTimeCard.txtEmpID.Value & "' "
    sql = sql & "AND shortchar02 = 'Salaried' "
    sql = sql & "OR approve = True "
    sql = sql & "AND approved = False "
    sql = sql & "AND dayDate = '" & Forms!frmTimeCard.txtDate.Value & "' "
    sql = sql & "AND weekDate = '" & Forms!frmTimeCard.txtPeriodStart.Value & " - " & Forms!frmTimeCard.txtPeriodEnd.Value & "' "
    sql = sql & "AND empid = '" & Forms!frmTimeCard.txtEmpID.Value & "' "
    sql = sql & "AND shortchar02 = 'Salaried' "

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

    Dim cl As Integer
    rs.MoveLast
    cl = rs.RecordCount

    rs.MoveFirst
    Dim c As Integer
    c = 0

    For c = 1 To cl
        c = c + 1
        txtEmpID = rs.Fields("empid")
        rs.Fields("approved") = True
                            
        ' Send EMail
        GenerateEmailContent_Weekly (txtEmpID)
        Debug.Print ("Email # " & c & " has been sent.")
            
        rs.MoveNext
        
    Next c

In the above example, c=0 and cl=-1


If I use this logic...

    Do While Not rs.BOF Or rs.EOF
        c = c + 1
        txtEmpID = rs.Fields("empid")
        rs.Fields("approved") = True
                            
        ' Send EMail
        GenerateEmailContent_Weekly (txtEmpID)
        Debug.Print ("Email # " & c & " has been sent.")
            
        rs.MoveNext
        
    Loop

the c variable counts appropriately.

My dilemma, is I need to do this manually, so the loop runs one additional time, but if cl is -1 as the recordset length, I will never get there.

Thanks for the help.

[June7]

You are mixing AND and OR operators in your SQL. Parenthesizing is critical to get the desired dataset. You don't have any. Also, number fields do not use delimiters on parameters and date/time fields need # delimiter. So if ID fields are number type and to force unbound textbox values to be recognized as date/time:

sql = sql & "WHERE (approve = True "
sql = sql & "AND approved = False "
sql = sql & "AND dayDate = #" & Forms!frmTimeCard.txtDate.Value & "# "
sql = sql & "AND weekDate = #" & Forms!frmTimeCard.txtPeriodStart.Value & " - " & Forms!frmTimeCard.txtPeriodEnd.Value & "# "
sql = sql & "AND supid = " & Forms!frmTimeCard.txtEmpID.Value " "
sql = sql & "AND shortchar02 = 'Salaried')"
sql = sql & "OR (approve = True "
sql = sql & "AND approved = False "
sql = sql & "AND dayDate = #" & Forms!frmTimeCard.txtDate.Value & "# "
sql = sql & "AND weekDate = #" & Forms!frmTimeCard.txtPeriodStart.Value & #" - "# & Forms!frmTimeCard.txtPeriodEnd.Value & "# "
sql = sql & "AND empid = " & Forms!frmTimeCard.txtEmpID.Value & " "
sql = sql & "AND shortchar02 = 'Salaried')"

Or maybe:

sql = sql & "WHERE (empid = " & Forms!frmTimeCard.txtEmpID.Value & " OR supid = " & Forms!frmTimeCard.txtEmpID.Value) & " "
sql = sql & "AND approve = True "
sql = sql & "AND approved = False "
sql = sql & "AND dayDate = #" & Forms!frmTimeCard.txtDate.Value & "# "
sql = sql & "AND weekDate = #" & Forms!frmTimeCard.txtPeriodStart.Value & "# - #" & Forms!frmTimeCard.txtPeriodEnd.Value & "# "
sql = sql & "AND shortchar02 = 'Salaried'"

What are dayDate and weekDate - date/time fields? What are the txtPeriodStart and txtPeriodEnd values - numbers or dates? Subtracting two date values will return a number (default unit is days), not a date.

[pbaldy]

I don't normally use ADO recordsets, but from MSDN:

The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.

Can you just do the extra bit you need done before or after the EOF loop?

[sanderson]

I tried to make this work:

    sql = "SELECT * FROM tblocalApprovalLog "
    sql = sql & "WHERE (empid = '" & Forms!frmTimeCard.txtEmpID.Value & "' OR supid = '" & Forms!frmTimeCard.txtEmpID.Value & ")' "
    sql = sql & "AND approve = True "
    sql = sql & "AND approved = False "
    sql = sql & "AND dayDate = '" & Forms!frmTimeCard.txtDate.Value & "' "
    sql = sql & "AND weekDate = '" & Forms!frmTimeCard.txtPeriodStart.Value & " - " & Forms!frmTimeCard.txtPeriodEnd.Value & "' "
    sql = sql & "AND shortchar02 = 'Salaried'"

I also added the two parens as you showed in the first option with no change in results...

What is puzzling is that the do while not .bof or .eof works. The only problem is, on the first click it takes all but one record, and on the last it takes the last. I am looking for it to take them all on one click, which is why I am trying to manually count this.

Let me know if you have further thoughts.

Also, the dates are not dates, but text strings representing date values.

Thanks,

[sanderson]

I am using Dynamic

[ssanfu]

In the WHERE line, you have the single quote outside of the parenthesis, instead of inside the parenthesis. Shold be:

    SQL = SQL & "WHERE (empid = '" & Forms!frmTimeCard.txtEmpID.Value & "' OR supid = '" & Forms!frmTimeCard.txtEmpID.Value & "') "

I would put a "Debug.Print SQL" line right after the last SQL line to see if the query is formed correctly.


Just curious, are you storing a range of dates in the "weekdate" field? (example - 1/1/2015 - 2/1/2015)

[June7]

Ooops, that misplaced apostrophe was me. Sorry. I edited my post while you were reading. Might look at it again.

The SQL structure edits is not a fix for your primary question but is important to the procedure.

[sanderson]

Okay, I moved the apostrophe. Both queries work the same, but still produce a recordset length of -1. Again, if I use the do while loop, everything works, and I have to select my button twice to get all the data through.

I was hoping by doing the loop manually, I could find out why this was happening.

So again, any thoughts are appreciated. I am using Dynamic, and moving last first to get the recordcount.

Thanks,

[June7]

What do you really want to do? Do you want to loop however many times there are records in the recordset?

So why does the recordcount show empty (-1)? No records meet the filter criteria?

https://support.microsoft.com/en-us/kb/194973

I use adOpenStatic, adLockPessimistic and I get correct recordcount and without MoveLast.

[pbaldy]

What is your complete code with the Do While Not rs.EOF loop? I've never had a problem with it missing a record.

[sanderson]

Here is the complete code (below)...

It takes all but one record on the first click, and the last one on the second. When there is only one item, it works great.

The endloop is there in case of an error, and I would imagine this is the problem. However, there should not be an error! Four counts, four records... When I remove it, I do get an error of:



Perhaps running the 'GenerateEmailContent_Weekly (txtEmpID)' is knocking somwething out of whack??

Thanks again!

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


    Dim sql As String
   
    sql = "SELECT * FROM tblocalApprovalLog "
    sql = sql & "WHERE (empid = '" & Forms!frmTimeCard.txtEmpID.Value & "' OR supid = '" & Forms!frmTimeCard.txtEmpID.Value & "') "
    sql = sql & "AND approve = True "
    sql = sql & "AND approved = False "
    sql = sql & "AND dayDate = '" & Forms!frmTimeCard.txtDate.Value & "' "
    sql = sql & "AND weekDate = '" & Forms!frmTimeCard.txtPeriodStart.Value & " - " & Forms!frmTimeCard.txtPeriodEnd.Value & "' "
    sql = sql & "AND shortchar02 = 'Salaried'"


    Debug.Print ("sql: " & sql)
      
    rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    On Error GoTo endloop


    Dim c As Integer
    c = 0
        
    Do While Not rs.BOF Or rs.EOF
        c = c + 1
        Debug.Print ("RecordCount: " & CStr(c))


        ' On Error GoTo endloop
        txtEmpID = rs.Fields("empid")
        rs.Fields("approved") = True
                            
        ' Send EMail
        GenerateEmailContent_Weekly (txtEmpID)
        Debug.Print ("Email # " & c & " has been sent.")
            
        rs.MoveNext
        
    Loop


endloop:
    Me.Requery
    rs.Close
    Set rs = Nothing


End Sub

Private Sub GenerateEmailContent_Weekly(txtEmpID As String)
    Debug.Print ("Running Weekly...")
    PopulateDates
        
    ' Create the queries
    sqlReportMain = "SELECT TimeCards.*, dbo_empbasic.name, dbo_empbasic.shortchar02, #" & datPeriodStart & "# As periodstart, #" & datPeriodEnd & "# As periodend, " & _
                    "iif(TimeCards.BillType='P' or TimeCards.BillType='S',TimeCards.laborhours,0) As Burden, " & _
                    "iif(TimeCards.BillType='P' or TimeCards.BillType='S',TimeCards.OpCode,'9999') As ResourceGroup " & _
                    "FROM TimeCards " & _
                    "LEFT JOIN dbo_empbasic ON TimeCards.empid = dbo_empbasic.empid " & _
                    "WHERE (TimeCards.empid = '" & txtEmpID & "') " & _
                    "AND (TimeCards.Date Between #" & datPeriodStart & "# And #" & datPeriodEnd & "#) " & _
                    "AND Len(nz(TimeCards.billtype,'')) > 0 " & _
                    "AND dbo_empbasic.shortchar02 = 'Salaried' "


    Debug.Print ("sqlReportMain: " & sqlReportMain)
   
    sqlReportSub = "SELECT qryTime.billtype, qryTime.indirectcode, qryTime.jobnum, qryTime.oprseq, qryTime.opcode, " & _
                       "Sum(qryTime.laborhours) AS SumOflaborhours, Sum(qryTime.qtycompleted) AS SumOfqtycompleted, " & _
                       "Sum(qryTime.qtyscrap) AS SumOfqtyscrap, Sum(qryTime.Burden) AS SumOfBurden, qryTime.ResourceGroup " & _
                   "FROM (" & _
                       sqlReportMain & _
                   ") As qryTime " & _
                   "GROUP BY qryTime.billtype, qryTime.indirectcode, qryTime.jobnum, qryTime.oprseq, qryTime.opcode, qryTime.ResourceGroup "
    
    Debug.Print ("sqlReportSub: " & sqlReportSub)
    
    ' DoCmd.OpenReport "rptweekly", acViewPreview
    ' DoCmd.SendObject acSendReport, "rptweekly", acFormatPDF, "TimeCards@metromachine.com", , , "Time Card"
    
    Dim FileSavePath As String
    FileSavePath = "c:\temp\" & txtEmpID & ".pdf"
    DoCmd.OutputTo acOutputReport, "rptWeekly", acFormatPDF, FileSavePath, , , , acExportQualityPrint
       
    Debug.Print ("FileSavePath: " & FileSavePath)
       
    SendMessage (FileSavePath)
            
End Sub

Private Sub SendMessage(FileSavePath As String)
    Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem


    Dim txtEmpName As String
    ' txtEmpName = Nz(DLookup("name", "dbo_empbasic", "empid = '" & txtEmpID & "' "), "")
    ' txtEmpName = GetUserName(empid)
   
    Dim sql As String
    sql = "SELECT dbo_empbasic.* FROM dbo_empbasic WHERE dbo_empbasic.empid = '" & txtEmpID & "' "
    
    Dim rs As adodb.recordset
    Set rs = New adodb.recordset
    
    rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    rs.MoveFirst
    
    txtEmpName = rs.Fields("name")
    
    Dim salaried As String
    salaried = rs.Fields("shortchar02")
    Debug.Print ("salaried: " & salaried)
    
    rs.Close
    Set rs = Nothing
       
    Debug.Print ("txtEmpID: " & txtEmpID)
    Debug.Print ("txtempname: " & txtEmpName)
    
    Dim SD As String
    Dim ED As String
    
    If salaried = "Salaried" Then
        SD = datPeriodStart
        ED = datPeriodEnd
        
    Else
        SD = Forms!frmTimeCard.txtDate.Value
        ED = Forms!frmTimeCard.txtDate.Value
        
    End If
    
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)


    With MailOutLook
        .BodyFormat = olFormatRichText
        .To = "sanderson@metromachine.com"
        ''.cc = ""
        ''.bcc = ""
        .Subject = "Timecard for " & txtEmpName & ""
        .HTMLBody = "Attached is the timecard for " & txtEmpName & ", employee number " & txtEmpID & " for the period of " & CStr(SD) & " through " & CStr(ED) & "."
        .Attachments.Add (FileSavePath)
        .Send
        '.Display    'Used during testing without sending (Comment out .Send if using this line)
            
    End With
  
End Sub

[pbaldy]

Try simply

Do While Not rs.EOF

And I would expect when setting a recordset value

rs.Edit
rs.Fields("approved") = True
rs.Update

[sanderson]

Adding rs.Edit produces a compile error: Method or data member not found.

[pbaldy]

Maybe that's only for DAO recordsets. I don't use ADO much. I was hoping changing the Do line would help. Yours actually doesn't do what you think. This:

Do While Not rs.BOF Or rs.EOF

says "do while the recordset is NOT BOF or the recordset IS EOF". I've never included a test for BOF.

[June7]

Did you see post 9?

I also never have used BOF test.

While Not rs.EOF