Hi,

Me again. Now the requirement is slightly changed now.

Requiremetn:
When I enter a employee id in a text box and click "Show calender", the code will run a query to fetch Allocation date and release date of the employee for a project and the dates between Allocation date and release date in the monthview calender should display in bold. Below is my code snippet

Private Sub BTShow_Click()
Dim State() As Boolean
Dim StartDate As Date
CuntDts = 42
StartDate = MonthView1.VisibleDays(1)
MonthView1_GetDayBold StartDate, CuntDts, State()

    If Trim(ResAvail.TBEmpCode.Text) = "" Then
        MsgBox "Please fill valid entry", vbOKOnly, "Alert"
        Exit Sub
    End If

MonthView1_GetDayBold StartDate, CuntDts, State()

End Sub


Sub MonthView1_GetDayBold(ByVal StartDate As Date, ByVal CuntDts As Integer, State() As Boolean)
Dim Allo_On As Date
Dim Rel_date As Date
Dim i As Integer
Dim MyConn As ADODB.Connection
Dim MyRecSet1 As New ADODB.Recordset
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=DIVAKAR-PC\LOCALHOST;Initial Catalog=RMG;User Id=RMG;Password=Thinksoft"
MyConn.Open
       
    Emp_Id = ResAvail.TBEmpCode.Text
    MyRecSet1.CursorLocation = adUseClient
    Set MyRecSet1 = MyConn.Execute("Select Allocated_On, Release_Date from dbo.Res_Allocation As R Left Join dbo.Employee As E on R.Emp_Id = E.Emp_Id Where " & _
        "R.Emp_Id = '" & Emp_Id & "' and E.Status <> 'Exit' and R.Last_Update_Date = (Select Max(Last_Update_Date) from dbo.Res_Allocation)")
    
    If MyRecSet1.BOF = True Or MyRecSet1.EOF = True Then
    Exit Sub
    End If
    
    Allo_On = MyRecSet1![Allocated_On]
    Rel_date = MyRecSet1![Release_date]
    
    For i = 0 To CuntDts - 1
        If StartDate >= Allo_On And StartDate <= Rel_date Then
            'MonthView1.DayBold(StartDate) = True           ' Bold all blocked days.
            'State(MonthView1.DayBold(StartDate)) = True
            State(i) = True
            MonthView1.Refresh
        End If
        StartDate = StartDate + 1
    Next
End Sub

Problem:

When the code reading the below line

State(i) = True

Its throwing an error saying "Script is out of range". I dont know what should be corrected. Kindly help me.

I can not see a value given for CuntDts, although you are calling a value against it, hence the out of range script. Something can't be true if it does not exist...

Add a value to CuntDts and try your code again.:)

No.. I did gave the value. Please refer the code


Private Sub BTShow_Click()
Dim State() As Boolean
Dim StartDate As Date
CuntDts = 42

You have set a value to it under a command button click event which becomes null and void once the button is clicked, yet you call it under the monthview event with no value. Set a value to it under the monthview event as well.

I found an alternative logic to meet the requirement now. Hence I dont want to waste your time. I am closign this thread

This question has already been answered. Start a new discussion instead.