Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Calendar (A2k)

  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calendar (A2k)

    <pre>intCount = intCounter
    strTemp = ""
    Do
    Me("label" & intCount).Caption = intCount - intCounter + 1
    Me("label" & intCount).ControlTipText = strMonth & " " & intCount - intCounter
    + 1 & ", " & intYear
    Me("label" & intCount).Visible = True
    Me("text" & intCount).Visible = True

    tmpText = ""
    tmpText2 = ""

    'Fixed Events
    If intCount - intCounter + 1 = 25 And strMonth = "December" Then tmpText =
    Chr(13) + Chr(10) + "Xmas Day": Me("text" & intCount).BackColor = 16777215


    If intCount - intCounter + 1 = 26 And strMonth = "December" Then tmpText =
    Chr(13) + Chr(10) + "Boxing Day": Me("text" & intCount).BackColor = 16777215


    If intCount - intCounter + 1 = 1 And strMonth = "January" Then tmpText =
    Chr(13) + Chr(10) + "New Years Day": Me("text" & intCount).BackColor = 16777215


    If intCount - intCounter + 1 = 25 And strMonth = "December" Then tmpText =
    Chr(13) + Chr(10) + "Xmas Day": Me("text" & intCount).BackColor = 16777215


    If intCount - intCounter + 1 = 26 And strMonth = "December" Then tmpText =
    Chr(13) + Chr(10) + "Boxing Day": Me("text" & intCount).BackColor = 16777215


    If intCount - intCounter + 1 = 1 And strMonth = "January" Then tmpText =
    Chr(13) + Chr(10) + "New Years Day": Me("text" & intCount).BackColor = 16777215


    For I = 1 To events
    If intCount - intCounter + 1 = Int(Format(eventDate(I), "d")) _
    And strMonth = Format(eventDate(I), "mmmm") _
    And intYear = Int(Format(eventDate(I), "yyyy")) _
    Then
    tmpText = tmpText + Chr(13) + Chr(10) + eventTitle(I)
    tmpText2 = eventID(I) + " or [ID] = " + tmpText2
    Me("text" & intCount).BackColor = 16777215 '16763080 14474460
    End If</pre>


    The above code from my Calendar sets fixed events, ie Xmas Day etc.
    I already hold fixed holidays in a "tblHolidays" Holiday table.
    Concidering different companies hold different dates, is it possible to change the code to
    pull the dates from the table
    Instead ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calendar (A2k)

    What is the structure of your tblHolidays table? Do you have a date field, or separate day, month and year fields? And are fixed holidays like Christmas Day repeated for each year (25 December 2001, 25 December 2002, 25 December 2003, ...), or are day listed only once?

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calendar (A2k)

    Hans
    I've attached the relevant Form and tables (2).
    We like to enter Holiday's manually to give th freedom of juggling days to suit the seasonal differences.
    Although Christmas,New Years & Boxing day are constant, the mid year Bank Holidays are not.

    BTW

    Beware of Text83 problem, the calendar likes UK regional settings.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calendar (A2k)

    By far the easiest solution is to add the holidays to tbl_Calendar. You can then remove the code that checks for fixed holidays.

    This append query will add the holidays to tbl_Calendar:

    INSERT INTO tbl_Calendar ( Title, StartDate )
    SELECT HolidayName, HolidayDate
    FROM tblHoliday;

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calendar (A2k)

    Hans
    I never thought of that !
    Before I do so, I think I would use this on lets say the open event of the form, but, will the dates be duplicated ?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calendar (A2k)

    >> I would use this on lets say the open event of the form

    Sorry, I don't understand. Do you want to run the append query in the On Open event? That is not a good idea, the append query should be run only once. Or do you mean something else?

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calendar (A2k)

    Hans
    Using the query is fine but if the holiday table is updated and the query run again, it will populate the tbl_Calendar
    with duplicate dates, (I don't want to remove the old dates).
    So I would run a SQL when the form starts up to only push dates which are not in the table.
    This way tbl_Calendar is always up to date with new entries.

    I'll try a bit of air code here which can be dangerous !!!!



    Dim strSQL as String
    Dim DateCheck As Integer

    strSQL = INSERT INTO tbl_Calendar ( Title, StartDate ) SELECT HolidayName, HolidayDate FROM tblHoliday;

    DoCmd.SetWarnings False
    ' Omit Append msgs, they are not needed
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    DateCheck = DCount("*", "tbl_Calendar", "Title=" & Title & " And " & StartDate ("StartDate",???
    If DateCheck > 0 Then

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calendar (A2k)

    If you decide to include holidays in tbl_Calendar, you should not use the holidays table any more after adding its records to tbl_Calendar. If you want to add or modify holidays, do it in (a form based on) tbl_Calendar.
    If that is not desirable, you should *not* include the holidays in tbl_Calendar at all; in that case, the code in Form_Current will have to be adapted.

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calendar (A2k)

    Hans
    I understand. I thought this would be more efficient to me as tblHolidays is used elsewhere within the database.
    I use it for Staff Attendance, Staff Holidays and Courtesy Car management on a form basis.

    I have a maintenance form for tblHolidays where as the dates are finalised, the Administrator updates the one table,
    which runs the dates throughout.

    tblHolidays is also used for Start - End date calculations also, so you see my need to update the one table instead
    of douple input.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calendar (A2k)

    Adding new records to tblHoliday wouldn't be the problem. But if you remove a record from tblHoliday, it wouldn't be removed from tbl_Calendar automatically, and if you edit a record in tblHoliday, the modifications wouldn't be transported to tbl_Calendar automatically.

    I'll have to think about this, but I can't get the darn form to work correctly on a non-English system...

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calendar (A2k)

    <post#=267776>post 267776</post#>

    Hans
    You had this problem once before.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calendar (A2k)

    Yes, I know, and there is an easy workaround - set my regional settings to English, but I don't have the time for that now.

  13. #13
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calendar (A2k)

    No problem.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calendar (A2k)

    Here you go. The following will use the tblHoliday table to display holidays in the table in much the same way the tbl_Calendar table is used to display events.

    In the declarations section at the start of Form_Current, add the following declarations:<pre> Dim holDate(10000) As Date
    Dim holTitle(10000) As String
    Dim hols As Long</pre>

    Add the following after the code that reads in qryCalendar and qryCalendar2 into an array:<pre> 'Read in tblHoliday to Array
    Set myset = CurrentDb.OpenRecordset("tblHoliday", DB_OPEN_SNAPSHOT)
    Do Until myset.EOF
    hols = hols + 1
    holDate(hols) = myset![HolidayDate]
    holTitle(hols) = myset![HolidayName]
    myset.MoveNext
    Loop
    myset.Close
    Set myset = Nothing</pre>

    Finally, replace the code for fixed events by:<pre> 'Fixed Events
    For I = 1 To hols
    If intCount - intCounter + 1 = Int(Format(holDate(I), "d")) _
    And strMonth = Format(holDate(I), "mmmm") _
    And intYear = Int(Format(holDate(I), "yyyy")) Then
    tmpText = Chr(13) + Chr(10) + holTitle(I)
    Me("text" & intCount).BackColor = vbWhite
    End If
    Next I</pre>

    This way, you can keep tblHoliday as it is.

  15. #15
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calendar (A2k)

    Hans
    Thats amazing, no need for a query either.

    Works like a dream <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •