Solved Counting Records and populating a text box

[Cliff67]

Hi All,

I know this sounds easy to do but let me fill you in on this.

So on my tech support DB I have a calendar form, gratefully taken from another (sorry can't remember who).

I want to populate the small Green text boxes with the number of Tech support calls we have had in the particular day. the code is below

Public Sub FillTextBoxes(frm As Access.Form, theYear As Integer)
Dim db As Database
Dim rs As Recordset
Dim ctl As Access.TextBox
Dim strMonth As String
Dim intMonth As Integer
Dim intDay As Integer
Dim FirstDayOfMonth As Date
Dim intOffSet As Integer
Dim IntRec As Integer
Dim ActualDate As String
On Error GoTo ErrHand

Set db = CurrentDb
clearTextBoxes frm
IntRec = 0
If Me.CmbView = 1 Then 'TSR
' Open a recordset on the entries for This year
clearTextBoxes frm
Set rs = db.OpenRecordset("SELECT * FROM Tbl_Tickets " & _
"WHERE [DateOpened] BETWEEN #01/01/" & CmbYear & _
"# AND #31/12/" & CmbYear & "#", dbOpenDynaset)
rs.MoveFirst
' Loop through all the rows and set the TSR in the calendar
Do Until rs.EOF
' Calculate the offset
intDay = Day(rs!DateOpened)
intMonth = Month(rs!DateOpened)
strMonth = Format(rs!DateOpened, "mmm")
FirstDayOfMonth = getFirstOfMonth(CmbYear, intMonth) 'First of month
intOffSet = getOffset(CmbYear, intMonth, vbSaturday) 'Offset to first label for month.
ActualDate = intDay & "/" & intMonth & "/" & CmbYear
' Set in the TSR in the appropriate day
Set ctl = frm.Controls("txt" & strMonth & intDay + intOffSet)
ctl.BackColor = 52582 'Green
ctl.ForeColor = vbBlack
IntRec = DCount("Ticket_Number", "Tbl_Tickets", "DateOpened = " & ActualDate)
ctl.Value = IntRec
' Get the next row
rs.MoveNext

Loop
Else ' If Me.CmbView = 2 Then 'RMAs
clearTextBoxes frm
Set rs = db.OpenRecordset("SELECT * FROM Tbl_Repair_Main " & _
"WHERE [DateRaised] BETWEEN #01/01/" & CmbYear & _
"# AND #31/12/" & CmbYear & "#", dbOpenDynaset)
rs.MoveFirst
' Loop through all the rows and set the RMA in the calendar
Do Until rs.EOF
intDay = Day(rs!DateRaised)
intMonth = Month(rs!DateRaised)
strMonth = Format(rs!DateRaised, "mmm")
FirstDayOfMonth = getFirstOfMonth(CmbYear, intMonth) 'First of month
intOffSet = getOffset(CmbYear, intMonth, vbSaturday) 'Offset to first label for month.
' Set in the RMA in the appropriate day
Set ctl = frm.Controls("txt" & strMonth & intDay + intOffSet)
ctl.BackColor = vbRed
ctl.ForeColor = vbWhite
ctl.Value = rs.RecordCount
rs.MoveNext
Loop
End If

rs.Close
db.Close

On Error GoTo 0
Exit Sub

ErrHand:

If Err.Number <> 0 Then
Err_Hand ("FillTextBoxes")
Err.Clear
Exit Sub
Else
Err.Clear
Exit Sub
End If
End Sub

As you can see as it goes through the year I get a green box where I have a call in that day. I was originally only interested if any calls had been received in that day.

Now I need to count the number of calls and put it in the box. Once I've got it for the Tech support calls I'll do it for the RMAs

It seems to be going wrong at the red bold place as IntRec returns 0 every time.

Can anyone suggest where or what I'm doing wrong as I'm stuffed If I can find it.

I did do ctl.value = rs.RecordCount but that gave all records in the selected year in each box

Many thanks in advance

 

[Gasman]

No code tags?

 

[Cliff67]

Sorry Gas Man the tags were there when I sent it.
I'll re-do it

 

[CJ_London]

because ActualDate is a string, not a date so you need to surrount with # characters

further your value need to be in the format mm/dd/yyyy
Suggest define actualdate as

ActualDate = intMonth & "/" & intDay & "/" & CmbYear

and your dcount

intRec = DCount("Ticket_Number", "Tbl_Tickets", "DateOpened = #" & ActualDate & "#")

 

[Cliff67]

Public Sub FillTextBoxes(frm As Access.Form, theYear As Integer)
Dim db As Database
Dim rs As Recordset
Dim ctl As Access.TextBox
Dim strMonth As String
Dim intMonth As Integer
Dim intDay As Integer
Dim FirstDayOfMonth As Date
Dim intOffSet As Integer
Dim IntRec As Integer
Dim ActualDate As String
On Error GoTo ErrHand

Set db = CurrentDb
clearTextBoxes frm
IntRec = 0
    If Me.CmbView = 1 Then 'TSR
    ' Open a recordset on the entries for This year
    clearTextBoxes frm
        Set rs = db.OpenRecordset("SELECT * FROM Tbl_Tickets " & _
        "WHERE [DateOpened] BETWEEN #01/01/" & CmbYear & _
        "# AND #31/12/" & CmbYear & "#", dbOpenDynaset)
        rs.MoveFirst
        ' Loop through all the rows and set the TSR in the calendar
        Do Until rs.EOF
            ' Calculate the offset
                intDay = Day(rs!DateOpened)
                intMonth = Month(rs!DateOpened)
                strMonth = Format(rs!DateOpened, "mmm")
                FirstDayOfMonth = getFirstOfMonth(CmbYear, intMonth) 'First of month
                intOffSet = getOffset(CmbYear, intMonth, vbSaturday) 'Offset to first label for month.
                ActualDate = intDay & "/" & intMonth & "/" & CmbYear
                ' Set in the TSR in the appropriate day
                Set ctl = frm.Controls("txt" & strMonth & intDay + intOffSet)
                ctl.BackColor = 52582 'Green
                ctl.ForeColor = vbBlack
                IntRec = DCount("Ticket_Number", "Tbl_Tickets", "DateOpened = " & ActualDate)
                ctl.Value = IntRec
                ' Get the next row
             rs.MoveNext
          
        Loop
    Else ' If Me.CmbView = 2 Then 'RMAs
    clearTextBoxes frm
        Set rs = db.OpenRecordset("SELECT * FROM Tbl_Repair_Main " & _
        "WHERE [DateRaised] BETWEEN #01/01/" & CmbYear & _
        "# AND #31/12/" & CmbYear & "#", dbOpenDynaset)
        rs.MoveFirst
        ' Loop through all the rows and set the RMA in the calendar
        Do Until rs.EOF
                intDay = Day(rs!DateRaised)
                intMonth = Month(rs!DateRaised)
                strMonth = Format(rs!DateRaised, "mmm")
                FirstDayOfMonth = getFirstOfMonth(CmbYear, intMonth) 'First of month
                intOffSet = getOffset(CmbYear, intMonth, vbSaturday) 'Offset to first label for month.
                ActualDate = intDay & "/" & intMonth & "/" & CmbYear
                ' Set in the RMA in the appropriate day
                Set ctl = frm.Controls("txt" & strMonth & intDay + intOffSet)
                ctl.BackColor = vbRed
                ctl.ForeColor = vbWhite
                IntRec = DCount("RMANo", "Tbl_Repair_Main", "DateRaised = " & ActualDate)
                ctl.Value = IntRec
            rs.MoveNext
        Loop
    End If
 
rs.Close
db.Close

On Error GoTo 0
Exit Sub

 

[Cliff67]

Got it

 

[Cliff67]

CJ

because ActualDate is a string, not a date so you need to surrount with # characters

further your value need to be in the format mm/dd/yyyy
Suggest define actualdate as

ActualDate = intMonth & "/" & intDay & "/" & CmbYear

and your dcount

intRec = DCount("Ticket_Number", "Tbl_Tickets", "DateOpened = #" & ActualDate & "#")

CJ

Thanks for that, It mainly works however, I get a few days with 0 in it at the beginning of the year and one in November. I seem to remember it was something to do with the date format yet again..oops missed the format thing in your post

 

[Cliff67]

@CJ_London that has worked a treat thank you very much, I knew I was missing something but couldn't see the wood for the trees

 

[CJ_London]

happy to help - but do use code tags in the future, it saves us time

 

[Cliff67]

@CJ_London I used the wrong button, I used the [ ] button then couldn't figure why it didn't post correctly.

Once I saw the </> button I realised my mistake. I will make sure in future that I post correctly.

once again many thanks

 

[Gasman]

@CJ_London I used the wrong button, I used the [ ] button then couldn't figure why it didn't post correctly.

Once I saw the </> button I realised my mistake. I will make sure in future that I post correctly.

once again many thanks

I just tend to write [ code ] and then [/ code ] (without the spaces) to surround the code

 

[Cliff67]

I just tend to write [ code ] and then [/ code ] (without the spaces) to surround the code

good idea, thanks

 

[Pat Hartman]

Also dim your variables correctly. If you want a date, dim it as a date!

Dates dim'd as strings act like strings. So 11/30/2021 is < 12/01/2020 because 11 is less than 12 and strings are evaluated character by character, left to right.

 

[Cliff67]

Thanks Pat

 

[Pat Hartman]

You're welcome