Results 1 to 10 of 10
  1. #1
    awurah is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2017
    Posts
    13

    Loading a text box Array for an Access Calendar

    I am currently building an Lease Database with access and need to keep track of the renewal and ending dates of multiple leases at once. For that purpose, I am trying to maintain a calendar inside the database that would track the information.

    It shows month and year through a combo box and every day of the month through text boxes. Within each text box, the goal is to have a clickable entry that leads to a form that states which event is due that day such as "Lease Renewal" or "End of Lease". The entries will load through an array.

    I was lucky enough to find the code for such a calendar through a youtuber named "Access All in One" who gave us a license to use and modify his code. FOr some reason, the array that I modified is not functional and I do not know why.

    This was the calendar array original code

    Code:
     Public Sub LoadArray()'This sub loads an array with the relevant variables from a query
    Dim db As Database
    Dim rs As Recordset
    Dim rsFiltered As Recordset
    Dim strQuery As String
    Dim i As Integer
    
    
    On Error GoTo ErrorHandler
    
    
    strQuery = "SELECT tblClass.ClassID, tblClass.ClassDate, tblLocations.ClassRoom, "
    strQuery = strQuery & "DLookUp('[LookUp24Hour]','tblTimes','[LookUpScheduleTime]=' & [tblclass].[StartTime]) AS StartTime, "
    strQuery = strQuery & "DLookUp('[LookUp24Hour]','tblTimes','[LookUpScheduleTime]=' & [tblclass].[EndTime]) AS EndTime, "
    strQuery = strQuery & "tblLevel.Level, tblLevel.Code, Left([tblTeachers].[FirstName],1) & Left([tblteachers].[LastName],1) AS Teacher "
    strQuery = strQuery & "FROM tblLocations INNER JOIN ((tblCourse INNER JOIN tblLevel ON tblCourse.Level = tblLevel.LevelID) "
    strQuery = strQuery & "INNER JOIN (tblClass INNER JOIN tblTeachers ON tblClass.TeacherID = tblTeachers.TeacherID) "
    strQuery = strQuery & "ON tblCourse.CourseID = tblClass.CourseID) ON tblLocations.LocationID = tblClass.LocationID "
    
    
    
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strQuery)
    
    
    With rs
        
        If Not rs.BOF And Not rs.EOF Then
        'Ensures the recordset contains records
         
            For i = 0 To UBound(MyArray)
            'Will loop through the array and use dates to filter down the query
            'It firsts checks that the second column has true for its visible property
                If MyArray(i, 1) = True Then
                    .Filter = "[ClassDate]=" & MyArray(i, 0)
                    'To filter you must open a secondary recordset and
                    'Use that as the basis for a query
                    'This makes sense as you are building a query on a query
                    Set rsFiltered = .OpenRecordset
                    If Not rsFiltered.BOF And Not rsFiltered.EOF Then
                        'If the recordset is not empty then you are able
                        'to extract the text from the values provided
                        Do While Not rsFiltered.EOF = True
                            
                            MyArray(i, 2) = MyArray(i, 2) & vbNewLine & rsFiltered!starttime
                            MyArray(i, 2) = MyArray(i, 2) & " - " & rsFiltered!endtime
                            MyArray(i, 2) = MyArray(i, 2) & " " & rsFiltered!code
                   
                            
                        rsFiltered.MoveNext
                        Loop
                    End If
                End If
         
            Next i
        
    End If
        .Close
    End With
    
    
    ExitSub:
        Set db = Nothing
        Set rs = Nothing
        Exit Sub
    ErrorHandler:
        MsgBox "There has been an error. Please reload the form.", , "Error"
        Resume ExitSub
    
    
    End Sub
    
    
    Public Sub PrintArray()
    
    
    Dim strTextBox As String
    Dim i As Integer
    
    
    On Error GoTo ErrorHandler
    
    
    For i = 0 To 41
        strTextBox = "txt" & CStr(i + 1)
        With Me
            Controls(strTextBox) = ""
            Controls(strTextBox).tag = i + 1
            Controls(strTextBox) = MyArray(i, 2)
        'Debug.Print strTextBox
        'MyArray(i, 2)
        End With
    Next i
    ExitSub:
        Exit Sub
    ErrorHandler:
        MsgBox "There has been an error. Please reload the form.", , "Error"
        Resume ExitSub
    End Sub
    This is my array:



    Code:
     Public Sub LoadArray()
    'This sub loads an array with the relevant variables from a query
    Dim db As Database
    Dim rs As Recordset
    Dim rsFiltered As Recordset
    Dim strQuery As String
    Dim i As Integer
    
    
    On Error GoTo ErrorHandler
    
    
    strQuery = "SELECT CalendarDatestbl.LeaseID, CalendarDatestbl.EventDate, CalendarDatestbl.Unit, CalendarDatestbl.Usage,"
    strQuery = strQuery & "DLookUp('[LookUp24Hour]','tblTimes','[LookUpScheduleTime]=' & [CalendarDatestbl].[StartTime]) AS StartTime, "
    strQuery = strQuery & "DLookUp('[LookUp24Hour]','tblTimes','[LookUpScheduleTime]=' & [CalendarDatestbl].[EndTime]) AS EndTime, "
    strQuery = strQuery & "Eventtbl.Event AS Event, Eventtbl.Code AS Code"
    strQuery = strQuery & "FROM CalendarDatestbl INNER JOIN Eventtbl ON CalendarDatestbl.Event = Eventtbl.Event"
    strQuery = strQuery & "ORDER BY CalendarDatestbl.EventDate, DLookUp('[LookUp24Hour]','tblTimes','[LookUpScheduleTime]=' & [CalendarDatestbl].[StartTime]);"
    
    
    
    
    
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strQuery)
    
    
    With rs
        
        If Not rs.BOF And Not rs.EOF Then
        'Ensures the recordset contains records
         
            For i = 0 To UBound(MyArray)
            'Will loop through the array and use dates to filter down the query
            'It firsts checks that the second column has true for its visible property
                If MyArray(i, 1) = True Then
                    .Filter = "[EventDate]=" & MyArray(i, 0)
                    'To filter you must open a secondary recordset and
                    'Use that as the basis for a query
                    'This makes sense as you are building a query on a query
                    Set rsFiltered = .OpenRecordset
                    If Not rsFiltered.BOF And Not rsFiltered.EOF Then
                        'If the recordset is not empty then you are able
                        'to extract the text from the values provided
                        Do While Not rsFiltered.EOF = True
                            
                            MyArray(i, 2) = MyArray(i, 2) & vbNewLine & rsFiltered!StartTime
                            MyArray(i, 2) = MyArray(i, 2) & " - " & rsFiltered!EndTime
                            MyArray(i, 2) = MyArray(i, 2) & " " & rsFiltered!code
                            MyArray(i, 2) = MyArray(i, 2) & " " & rsFiltered!leaseid
                            
                        rsFiltered.MoveNext
                        Loop
                    End If
                End If
         
            Next i
        
    End If
        .Close
    End With
    
    
    ExitSub:
        Set db = Nothing
        Set rs = Nothing
        Exit Sub
    ErrorHandler:
        MsgBox "There has been an error. Please reload the form.", , "Error"
        Resume ExitSub
    
    
    End Sub
    
    
    Public Sub PrintArray()
    
    
    Dim strTextBox As String
    Dim i As Integer
    
    
    On Error GoTo ErrorHandler
    
    
    For i = 0 To 41
        strTextBox = "txt" & CStr(i + 1)
        With Me
            Controls(strTextBox) = ""
            Controls(strTextBox).tag = i + 1
            Controls(strTextBox) = MyArray(i, 2)
        'Debug.Print strTextBox
        'MyArray(i, 2)
        End With
    Next i
    ExitSub:
        Exit Sub
    ErrorHandler:
        MsgBox "There has been an error. Please reload the form.", , "Error"
        Resume ExitSub
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    thats a lot of work to re-invent the wheel.
    Access already has a calendar.
    and a datasheet.

  3. #3
    awurah is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2017
    Posts
    13
    Thank you for answering!

    Sorry, I am new to Access.

    Do you mean the calendar control function? Or is there a calendar that can show events with the datasheet? If so, where can I find it?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    "Not functional" means what - error message, wrong results, nothing happens? Have you step debugged?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    awurah is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2017
    Posts
    13
    Quote Originally Posted by June7 View Post
    "Not functional" means what - error message, wrong results, nothing happens? Have you step debugged?

    Every time I try to open the form it says Error ;"There has been an error. Please reload the form." I have debugged, but I still get the same result over and over.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Your error handler is interfering. Comment out the On Error line then open form. Which line does the execution break on?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    awurah is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2017
    Posts
    13
    Thank you!

    Now it say

    "Run-time error '3141':

    The SELECT statement includes a reserved word or an argument name that is mispelled or missing or the punctuation is incorrect"

    Then the debugger highlights:

    ->Set rs = db.OpenRecordset(strQuery)

  8. #8
    awurah is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2017
    Posts
    13
    Quote Originally Posted by June7 View Post
    Your error handler is interfering. Comment out the On Error line then open form. Which line does the execution break on?
    Thank you!

    Now it say

    "Run-time error '3141':

    The SELECT statement includes a reserved word or an argument name that is mispelled or missing or the punctuation is incorrect"

    Then the debugger highlights:

    ->Set rs = db.OpenRecordset(strQuery)

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Need spaces at end of lines so the constructed SQL doesn't 'run together':

    strQuery = strQuery & "Eventtbl.Event AS Event, Eventtbl.Code AS Code "
    strQuery = strQuery & "FROM CalendarDatestbl INNER JOIN Eventtbl ON CalendarDatestbl.Event = Eventtbl.Event "

    Activate error handlers only when ready to publish and deploy product.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    awurah is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2017
    Posts
    13
    Quote Originally Posted by June7 View Post
    Need spaces at end of lines so the constructed SQL doesn't 'run together':

    strQuery = strQuery & "Eventtbl.Event AS Event, Eventtbl.Code AS Code "
    strQuery = strQuery & "FROM CalendarDatestbl INNER JOIN Eventtbl ON CalendarDatestbl.Event = Eventtbl.Event "

    Activate error handlers only when ready to publish and deploy product.
    Wow. It worked! I am extremely grateful, I spent so much time on this.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-30-2016, 10:53 AM
  2. Using a calendar in a text box on a form
    By crowegreg in forum Forms
    Replies: 3
    Last Post: 08-25-2015, 06:30 PM
  3. Replies: 35
    Last Post: 07-06-2015, 09:37 AM
  4. writing to text file (array set)
    By pradeep.sands in forum Forms
    Replies: 5
    Last Post: 08-02-2013, 02:37 PM
  5. dynamic array for calendar
    By workindan in forum Programming
    Replies: 7
    Last Post: 11-12-2010, 01:20 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums