If statement to change caption in label (1 Viewer)

[dean_hurrell]

Hi

I am designing a holiday planner within my database which shows employee's holiday booked, cancelled and declined.

I found a very helpful demo database which I have slightly amended to suit what I need, but I am having problems changing the caption labels to value's within the recordset table.

Part of the code I am using at the moment is

 Dim strCaption As String
                     Dim strKey As String
                     
                     strKey = datStartDate
                     strCaption = ""
                     strCaption = colCalendarDates.Item(strKey)
                     colCalendarDates.Remove strKey
                                         
                     If strCaption = "H" Then
                         .Caption = [COLOR=Red]DLookup("Holiday Hours]", "[TBL_HOLIDAY_HOURS_FOR _IND_HOLIDAY_REPORT]")[/COLOR]
                         .Bold = False
                     Else
                         .Caption = strCaption
                         .Bold = True
                         Select Case strCaption
                             Case "DEC"
                              .BackColor = 255
                             Case "H"
                                 .BackColor = 16711680
                             Case "CAN"
                                 .BackColor = 0
                             Case "B"
                                 .BackColor = 5753088
                         End Select
                         .ForeColor = vbWhite
                     End If

I am wanting the report to change the caption to the holiday hours booked where the caption is H. The other codes, DEC, CAN & B can remain a they are.

I have played around with the code, but being a novice at this I have not had any luck, but I am pretty sure I am going wrong where I have highlighted the code when it comes to changing the caption.

Hope this makes sense, and someone may be able to let me know where I am going wrong, if not I can provide more information. Many Thanks

 

[pr2-eugin]

Dean, I am not sure if it is a Typo.. but your DLookUp's first argument is missing an opening Square bracket and DLookUp is lacking a Criteria.. Without a criteria, your result would be just the First record returned from the table/query..

 

[dean_hurrell]

I just noticed the typo the dlookup as your reply came through and have amended this. As for the criteria, not sure how this should look within the dlookup. Below is a snap shot of one of the months in the report.


There should be 7.5 in 28th & 29th, once I amended the typo in the dlookup this does bring this in, however only because it is finding the first record I presume and populating this.

I should say that behind each box on the HOL row is a date, however I have made this transparent. I do need to know how the dlookup should look in order to match the date on the report to the date in my table and bring back the correct values, if anyone can help?

 

[pr2-eugin]

Try THIS LINK.. It shows how to incorporate Criteria in the DLookUp statements..

 

[dean_hurrell]

 

[dean_hurrell]

Thank mate, I will have a look at that link and see if I can work it out.

 

[dean_hurrell]

I cannot seem to get this to work at all, so here goes..... I will post the whole code which I am using in the hope someone can help with my dlookup. Its not code I wrote myself as described below this is from a demo database which I found and slightly amended to suit my needs

Option Compare Database
 Option Explicit
  
 Private m_strCTLLabel  As String
 Private m_strCTLLabelHeader As String
 Private colCalendarDates As Collection
  
 Public Sub loadReportYearCalendarHOL(theReport As Report)
 Dim i As Integer
 Dim datStart As Date
 Dim rptControl As Report
  
     m_strCTLLabel = "labelHOL"
     m_strCTLLabelHeader = "labelDAY"
     
     '// load dates into our collection
     Call getCalendarData
     
     With theReport
         '// get the first month of the year
         datStart = "1/1/" & Year(Date)
         '// add the year to the report's label
        For i = 1 To 12
        
             '// set pointer to subreport control hosting the mini-calendar
             Set rptControl = .Controls("childCalendarMonth" & i).Report
             '// run procedure to populate control with it's respective year
             Call loadReportCalendar(rptControl, datStart)
             '// reset and obtain first day of the following month
             datStart = DateAdd("m", 1, datStart)
         Next i
     End With
     '// clean up
     Set colCalendarDates = Nothing
     Set rptControl = Nothing
 End Sub
  
  
 Function getCalendarData() As Boolean
 Dim rs As DAO.Recordset
 Dim strDate As String
 Dim strCode As String
 Dim i As Integer
     Set rs = CurrentDb.OpenRecordset("TBL_HOLIDAY_HOURS_FOR _IND_HOLIDAY_REPORT", dbOpenDynaset)
     Set colCalendarDates = New Collection
     With rs
         If (Not .BOF) Or (Not .EOF) Then
             .MoveLast
             .MoveFirst
         End If
         If .RecordCount > 0 Then
             For i = 1 To .RecordCount
                 strDate = .Fields("Date")
                 strCode = .Fields("Holiday Absence Code")
                 colCalendarDates.Add strCode, strDate
                 .MoveNext
             Next i
         End If
         .Close
     End With
     '// return date collection
     Set rs = Nothing
 End Function
  
  
 Public Sub loadReportCalendar(theReport As Report, Optional StartDate As Date, Optional theHeaderColor As Variant)
 Dim i As Integer
 Dim intCalDay As Integer
 Dim datStartDate As Date
 Dim intWeekDay As Integer
     
     datStartDate = StartDate
     intWeekDay = Weekday(datStartDate)
  
     
     With theReport
         
         .Controls("labelMONTH") = Format(StartDate, "MMMM")
         
         '// change the day label's backcolor if necessary
        
  
         For i = 1 To 42
             With .Controls(m_strCTLLabel & i)
                 If (i >= intWeekDay) And (Month(StartDate) = Month(datStartDate)) Then
                     If (datStartDate = Date) Then
                         .BackColor = vbYellow
                     End If
                     
                     '// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                     '// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                     
                     '// nevermind on how this dirty code has been setup; then objective is getting
                     '// the values ;) being elegant is your design and how you'd like to approach it
                 
                     On Error Resume Next
                     
                     Dim strCaption As String
                     Dim strKey As String
                     
                     strKey = datStartDate
                     strCaption = ""
                     strCaption = colCalendarDates.Item(strKey)
                     colCalendarDates.Remove strKey
                                         
                                        
                   
                     If strCaption = "H" Then
                         .Caption = [COLOR=Red]DLookup("[Holiday Hours]", "[TBL_HOLIDAY_HOURS_FOR _IND_HOLIDAY_REPORT]")[/COLOR]
                         .Bold = False
                         .BackColor = 16711680
                         .ForeColor = vbWhite
                     Else
                     .Caption = strCaption
                         .Bold = True
                         Select Case strCaption
                             Case "0"
                              .BackColor = 255
                             Case "7.5"
                                .BackColor = 16711680
                             Case "CAN"
                                 .BackColor = 0
                             Case "B"
                                 .BackColor = 5753088
                         End Select
                         .ForeColor = vbWhite
                     End If
                     '// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                     '// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                     
                     datStartDate = DateAdd("d", 1, datStartDate)
                 Else
                     .Caption = ""
                 End If
             End With
         Next i
     End With
     
 End Sub

As mentioned in an earlier post I am looking for help with the dlookup to change the label caption in accordance with another table taking into account the dates in my calendar as per the earlier image.

All help and guidance greatly appreciated.

 

[MSAccessRookie]

I have some questions about the line DLookup("[Holiday Hours]", "[TBL_HOLIDAY_HOURS_FOR _IND_HOLIDAY_REPORT]")

1. It the space between the R and the _ supposed to be there, or is that a type-o?
2. What happens if the DLookup returns a NULL Value? I believe that a NULL Value would not be allowed in the Caption of a Label.
3. What happens if there is more than one value and you do not want the first? Without any criteria, only the first record will be returned.

-- Rookie

 

[dean_hurrell]

1. The space is a typo, however this was done in the original naming of the table and therefore is correct in the code.

2. When I have played about with the code to try and add criteria to the Dlookup, it does seem to return a NULL value but the label is displayed as black and on the the fomating, of the label can be seen i.e blue background etc.

3.This is my problem, there should be the hours which an employee has taken or booked holiday so there could be 7.5, 3.75, 2 etc. As it stands, this does only bring back the 7.5 as this is the first value in the table, but I need to add some criteria to the dlookup so that the actual hours on the report pull through on the correct date, as the other labels do eg DEC, CAN and B (see the image posted earlier)

In case this helps, my table consists of a Name, Emp, ID, Date, Holiday Hours, HR Code (which is DEC, CAN, B, H).

UPDATE - If I do the following

.Caption = DLookup("[Holiday Hours]", "[TBL_HOLIDAY_HOURS_FOR _IND_HOLIDAY_REPORT]", "[Date] = ...........")

If I put a date in there it obviously puts in the correct value related to that date, what I need is the correct criteria to check through all the dates and put the correct value in.