By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,478 Members | 1,505 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,478 IT Pros & Developers. It's quick & easy.

Calendar bookings represented on a room booking form

100+
P: 121
Hi,

I am trying to represent on a form time slots I have re-created the look of an outlook calendar on the form I have a table with the calendar bookings start and end time and there could be any number of bookings in a day. The following code represents the column for the first day of the week (monday) and formats the text boxes accordingly if the room is booked.

This all works fine but I have to have this code again to represent the secound day (tuesday) third day, fourth day etc

I have got it working for Monday, Tuesday and Wednesday but if I go any further I am getting the error message "compile error procedure too large" I have to repeat this process for anywhere up to 20 calendars is there any way that I can cut this code down so that is not too large for access to handle

Any help is much appreciated

Regards Phill

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim dbsEquipBook As DAO.Database
  3. Dim rstCheck As DAO.Recordset
  4. Dim qdf As DAO.QueryDef
  5. Dim MyDate, MyWeekDay
  6. Dim FirstDay As Date
  7. Dim LastDay As Date
  8. Dim MondayForm As Date
  9. Dim TuesdayForm As Date
  10. Dim WednesdayForm As Date
  11. Dim ThursdayForm As Date
  12. Dim FridayForm As Date
  13. Dim SaturdayForm As Date
  14. Dim SundayForm As Date
  15. Dim BookedStartTimeDate As Date
  16. Dim BookedEndTimeDate As Date
  17. Dim BookedStartTime As Date
  18. Dim BookedEndTime As Date
  19. Dim txtMon0800 As Date
  20. Dim txtMon0830 As Date
  21. Dim txtMon0900 As Date
  22. Dim txtMon0930 As Date
  23. Dim txtMon1000 As Date
  24. Dim txtMon1030 As Date
  25. Dim txtMon1100 As Date
  26. Dim txtMon1130 As Date
  27. Dim txtMon1200 As Date
  28. Dim txtMon1230 As Date
  29. Dim txtMon1300 As Date
  30. Dim txtMon1330 As Date
  31. Dim txtMon1400 As Date
  32. Dim txtMon1430 As Date
  33. Dim txtMon1500 As Date
  34. Dim txtMon1530 As Date
  35. Dim txtMon1600 As Date
  36. Dim txtMon1630 As Date
  37. Dim txtMon1700 As Date
  38. Dim txtMon1730 As Date
  39. Dim txtMon1800 As Date
  40. Dim txtMon1830 As Date
  41. Dim txtMon1900 As Date
  42. Dim txtMon1930 As Date
  43. Dim txtMon2000 As Date
  44. Dim txtMon2030 As Date
  45. Dim txtMon2100 As Date
  46. Dim txtMon2130 As Date
  47. Dim txtMon2200 As Date
  48. Dim txtMon2230 As Date
  49. Dim txtMon2300 As Date
  50. Dim txtMon2330 As Date
  51. 'this checks to see if euipment is booked then populates sform with time info which is then picked up by conditional formatting
  52.  
  53.     Set dbsEquipBook = CurrentDb()
  54.     Set qdf = dbsEquipBook.QueryDefs("QSelS104calcheck")
  55.  
  56. qdf.Parameters(0) = [Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtMon]
  57. qdf.Parameters(1) = [Forms]![FrmCalendarMain]![SfrCalS104].[Form]![sun]
  58.  
  59.  
  60. 'works out the first and last day of the week acoording to the selected date from mydate which is a calendar control on the form that the user selects
  61. MyDate = [Forms]![FrmCalendarMain].[frmCalendar]![txtDate]
  62. MyWeekDay = Weekday(MyDate)
  63.  
  64.  
  65. FirstDay = MyDate - MyWeekDay + 2
  66. LastDay = MyDate - MyWeekDay + 8
  67.  
  68. 'writes the first and last date to form
  69.  
  70. [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon] = FirstDay
  71. [Forms]![FrmCalendarMain].[SfrCalS104]![sun] = LastDay
  72.  
  73. MondayForm = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon]
  74. TuesdayForm = [Forms]![FrmCalendarMain].[SfrCalS104]![tues]
  75. WednesdayForm = [Forms]![FrmCalendarMain].[SfrCalS104]![weds]
  76. ThursdayForm = [Forms]![FrmCalendarMain].[SfrCalS104]![thurs]
  77. FridayForm = [Forms]![FrmCalendarMain].[SfrCalS104]![fri]
  78. SaturdayForm = [Forms]![FrmCalendarMain].[SfrCalS104]![sat]
  79. SundayForm = [Forms]![FrmCalendarMain].[SfrCalS104]![sun]
  80.  
  81.  
  82.  
  83. Set rstCheck = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)
  84.  
  85. Do While Not rstCheck.EOF
  86.  
  87.  
  88.  
  89. BookedStartTimeDate = Format(rstCheck!StartDate, "dd mm yyyy")
  90. BookedEndTimeDate = Format(rstCheck!EndDate, "dd mm yyyy")
  91.  
  92.  
  93.  
  94. If BookedStartTimeDate = MondayForm Then
  95.  
  96.    BookedStartTime = Format(rstCheck!StartDate, "hh:nn")
  97.    BookedEndTime = Format(rstCheck!EndDate, "hh:nn")
  98. ''this is here to handle the midnight time issue
  99.  
  100.    If BookedEndTime = "00:00:00" Then
  101.  
  102.    BookedEndTime = "23:59:59"
  103.    End If
  104. 'this picks up the value of the text boxes on the form that represent the time slots
  105.  
  106.    txtMon0800 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon08:00]
  107.    txtMon0830 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon08:30]
  108.    txtMon0900 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon09:00]
  109.    txtMon0930 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon09:30]
  110.    txtMon1000 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon10:00]
  111.    txtMon1030 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon10:30]
  112.    txtMon1100 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon11:00]
  113.    txtMon1130 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon11:30]
  114.    txtMon1200 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon12:00]
  115.    txtMon1230 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon12:30]
  116.    txtMon1300 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon13:00]
  117.    txtMon1330 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon13:30]
  118.    txtMon1400 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon14:00]
  119.    txtMon1430 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon14:30]
  120.    txtMon1500 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon15:00]
  121.    txtMon1530 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon15:30]
  122.    txtMon1600 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon16:00]
  123.    txtMon1630 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon16:30]
  124.    txtMon1700 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon17:00]
  125.    txtMon1730 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon17:30]
  126.    txtMon1800 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon18:00]
  127.    txtMon1830 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon18:30]
  128.    txtMon1900 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon19:00]
  129.    txtMon1930 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon19:30]
  130.    txtMon2000 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon20:00]
  131.    txtMon2030 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon20:30]
  132.    txtMon2100 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon21:00]
  133.    txtMon2130 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon21:30]
  134.    txtMon2200 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon22:00]
  135.    txtMon2230 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon22:30]
  136.    txtMon2300 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon23:00]
  137.    txtMon2330 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon23:30]
  138.  
  139. 'this checks through to see if there is a calendar entry and formats the text boxes accordingly for the monday colum
  140.  
  141. If txtMon0800 >= [BookedStartTime] And txtMon0800 < [BookedEndTime] Then
  142.  
  143.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon08:00].BackColor = RGB(191, 191, 191)
  144.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon08:00].ForeColor = RGB(191, 191, 191)
  145.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon08:00].Enabled = False
  146. End If
  147.  
  148. If txtMon0830 >= [BookedStartTime] And txtMon0830 < [BookedEndTime] Then
  149.  
  150.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon08:30].BackColor = RGB(191, 191, 191)
  151.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon08:30].ForeColor = RGB(191, 191, 191)
  152.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon08:30].Enabled = False
  153.  
  154. End If
  155.  
  156. If txtMon0900 >= [BookedStartTime] And txtMon0900 < [BookedEndTime] Then
  157.  
  158.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon09:00].BackColor = RGB(191, 191, 191)
  159.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon09:00].ForeColor = RGB(191, 191, 191)
  160.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon09:00].Enabled = False
  161. End If
  162.  
  163. If txtMon0930 >= [BookedStartTime] And txtMon0930 < [BookedEndTime] Then
  164.  
  165.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon09:30].BackColor = RGB(191, 191, 191)
  166.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon09:30].ForeColor = RGB(191, 191, 191)
  167.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon09:30].Enabled = False
  168. End If
  169.  
  170. If txtMon1000 >= [BookedStartTime] And txtMon1000 < [BookedEndTime] Then
  171.  
  172.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon10:00].BackColor = RGB(191, 191, 191)
  173.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon10:00].ForeColor = RGB(191, 191, 191)
  174.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon10:00].Enabled = False
  175.  
  176. End If
  177.  
  178. If txtMon1030 >= [BookedStartTime] And txtMon1030 < [BookedEndTime] Then
  179.  
  180.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon10:30].BackColor = RGB(191, 191, 191)
  181.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon10:30].ForeColor = RGB(191, 191, 191)
  182.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon10:30].Enabled = False
  183.  
  184. End If
  185.  
  186. If txtMon1100 >= [BookedStartTime] And txtMon1100 < [BookedEndTime] Then
  187.  
  188.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon11:00].BackColor = RGB(191, 191, 191)
  189.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon11:00].ForeColor = RGB(191, 191, 191)
  190.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon11:00].Enabled = False
  191.  
  192. End If
  193.  
  194. If txtMon1130 >= [BookedStartTime] And txtMon1130 < [BookedEndTime] Then
  195.  
  196.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon11:30].BackColor = RGB(191, 191, 191)
  197.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon11:30].ForeColor = RGB(191, 191, 191)
  198.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon11:30].Enabled = False
  199.  
  200. End If
  201.  
  202. If txtMon1200 >= [BookedStartTime] And txtMon1200 < [BookedEndTime] Then
  203.  
  204.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon12:00].BackColor = RGB(191, 191, 191)
  205.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon12:00].ForeColor = RGB(191, 191, 191)
  206.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon12:00].Enabled = False
  207.  
  208. End If
  209.  
  210. If txtMon1230 >= [BookedStartTime] And txtMon1230 < [BookedEndTime] Then
  211.  
  212.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon12:30].BackColor = RGB(191, 191, 191)
  213.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon12:30].ForeColor = RGB(191, 191, 191)
  214.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon12:30].Enabled = False
  215.  
  216. End If
  217. If txtMon1300 >= [BookedStartTime] And txtMon1300 < [BookedEndTime] Then
  218.  
  219.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon13:00].BackColor = RGB(191, 191, 191)
  220.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon13:00].ForeColor = RGB(191, 191, 191)
  221.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon13:00].Enabled = False
  222.  
  223. End If
  224.  
  225. If txtMon1330 >= [BookedStartTime] And txtMon1330 < [BookedEndTime] Then
  226.  
  227.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon13:30].BackColor = RGB(191, 191, 191)
  228.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon13:30].ForeColor = RGB(191, 191, 191)
  229.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon13:30].Enabled = False
  230.  
  231. End If
  232. If txtMon1400 >= [BookedStartTime] And txtMon1400 < [BookedEndTime] Then
  233.  
  234.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon14:00].BackColor = RGB(191, 191, 191)
  235.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon14:00].ForeColor = RGB(191, 191, 191)
  236.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon14:00].Enabled = False
  237.  
  238. End If
  239.  
  240. If txtMon1430 >= [BookedStartTime] And txtMon1430 < [BookedEndTime] Then
  241.  
  242.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon14:30].BackColor = RGB(191, 191, 191)
  243.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon14:30].ForeColor = RGB(191, 191, 191)
  244.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon14:30].Enabled = False
  245.  
  246. End If
  247. If txtMon1500 >= [BookedStartTime] And txtMon1500 < [BookedEndTime] Then
  248.  
  249.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon15:00].BackColor = RGB(191, 191, 191)
  250.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon15:00].ForeColor = RGB(191, 191, 191)
  251.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon15:00].Enabled = False
  252.  
  253. End If
  254.  
  255. If txtMon1530 >= [BookedStartTime] And txtMon1530 < [BookedEndTime] Then
  256.  
  257.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon15:30].BackColor = RGB(191, 191, 191)
  258.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon15:30].ForeColor = RGB(191, 191, 191)
  259.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon15:30].Enabled = False
  260.  
  261. End If
  262.  
  263. If txtMon1600 >= [BookedStartTime] And txtMon1600 < [BookedEndTime] Then
  264.  
  265.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon16:00].BackColor = RGB(191, 191, 191)
  266.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon16:00].ForeColor = RGB(191, 191, 191)
  267.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon16:00].Enabled = False
  268.  
  269. End If
  270.  
  271. If txtMon1630 >= [BookedStartTime] And txtMon1630 < [BookedEndTime] Then
  272.  
  273.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon16:30].BackColor = RGB(191, 191, 191)
  274.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon16:30].ForeColor = RGB(191, 191, 191)
  275.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon16:30].Enabled = False
  276.  
  277. End If
  278.  
  279. If txtMon1700 >= [BookedStartTime] And txtMon1700 < [BookedEndTime] Then
  280.  
  281.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon17:00].BackColor = RGB(191, 191, 191)
  282.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon17:00].ForeColor = RGB(191, 191, 191)
  283.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon17:00].Enabled = False
  284.  
  285. End If
  286.  
  287. If txtMon1730 >= [BookedStartTime] And txtMon1730 < [BookedEndTime] Then
  288.  
  289.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon17:30].BackColor = RGB(191, 191, 191)
  290.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon17:30].ForeColor = RGB(191, 191, 191)
  291.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon17:30].Enabled = False
  292.  
  293. End If
  294. If txtMon1800 >= [BookedStartTime] And txtMon1800 < [BookedEndTime] Then
  295.  
  296.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon18:00].BackColor = RGB(191, 191, 191)
  297.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon18:00].ForeColor = RGB(191, 191, 191)
  298.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon18:00].Enabled = False
  299.  
  300. End If
  301.  
  302. If txtMon1830 >= [BookedStartTime] And txtMon1830 < [BookedEndTime] Then
  303.  
  304.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon18:30].BackColor = RGB(191, 191, 191)
  305.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon18:30].ForeColor = RGB(191, 191, 191)
  306.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon18:30].Enabled = False
  307.  
  308. End If
  309. If txtMon1900 >= [BookedStartTime] And txtMon1900 < [BookedEndTime] Then
  310.  
  311.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon19:00].BackColor = RGB(191, 191, 191)
  312.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon19:00].ForeColor = RGB(191, 191, 191)
  313.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon19:00].Enabled = False
  314.  
  315. End If
  316.  
  317. If txtMon1930 >= [BookedStartTime] And txtMon1930 < [BookedEndTime] Then
  318.  
  319.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon19:30].BackColor = RGB(191, 191, 191)
  320.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon19:30].ForeColor = RGB(191, 191, 191)
  321.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon19:30].Enabled = False
  322.  
  323. End If
  324. If txtMon2000 >= [BookedStartTime] And txtMon2000 < [BookedEndTime] Then
  325.  
  326.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon20:00].BackColor = RGB(191, 191, 191)
  327.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon20:00].ForeColor = RGB(191, 191, 191)
  328.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon20:00].Enabled = False
  329.  
  330. End If
  331.  
  332.  
  333. If txtMon2030 >= [BookedStartTime] And txtMon2030 < [BookedEndTime] Then
  334.  
  335.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon20:30].BackColor = RGB(191, 191, 191)
  336.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon20:30].ForeColor = RGB(191, 191, 191)
  337.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon20:30].Enabled = False
  338.  
  339. End If
  340.  
  341. If txtMon2100 >= [BookedStartTime] And txtMon2100 < [BookedEndTime] Then
  342.  
  343.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon21:00].BackColor = RGB(191, 191, 191)
  344.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon21:00].ForeColor = RGB(191, 191, 191)
  345.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon21:00].Enabled = False
  346.  
  347. End If
  348.  
  349. If txtMon2130 >= [BookedStartTime] And txtMon2130 < [BookedEndTime] Then
  350.  
  351.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon21:30].BackColor = RGB(191, 191, 191)
  352.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon21:30].ForeColor = RGB(191, 191, 191)
  353.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon21:30].Enabled = False
  354.  
  355. End If
  356. If txtMon2200 >= [BookedStartTime] And txtMon2200 < [BookedEndTime] Then
  357.  
  358.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon22:00].BackColor = RGB(191, 191, 191)
  359.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon22:00].ForeColor = RGB(191, 191, 191)
  360.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon22:00].Enabled = False
  361.  
  362. End If
  363.  
  364. If txtMon2230 >= [BookedStartTime] And txtMon2230 < [BookedEndTime] Then
  365.  
  366.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon22:30].BackColor = RGB(191, 191, 191)
  367.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon22:30].ForeColor = RGB(191, 191, 191)
  368.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon22:30].Enabled = False
  369.  
  370. End If
  371. If txtMon2300 >= [BookedStartTime] And txtMon2300 < [BookedEndTime] Then
  372.  
  373.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon23:00].BackColor = RGB(191, 191, 191)
  374.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon23:00].ForeColor = RGB(191, 191, 191)
  375.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon23:00].Enabled = False
  376.  
  377. End If
  378.  
  379. If txtMon2330 >= [BookedStartTime] And txtMon2330 < [BookedEndTime] Then
  380.  
  381.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon23:30].BackColor = RGB(191, 191, 191)
  382.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon23:30].ForeColor = RGB(191, 191, 191)
  383.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon23:30].Enabled = False
  384.  
  385. End If
  386. If txtMon0000 >= [BookedStartTime] And txtMon0000 < [BookedEndTime] Then
  387.  
  388.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon00:00].BackColor = RGB(191, 191, 191)
  389.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon00:00].ForeColor = RGB(191, 191, 191)
  390.         [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon00:00].Enabled = False
  391.  
  392. End If
  393.  
  394.  
  395. End If
  396.  
  397.  rstCheck.MoveNext
  398. Loop
  399.  
  400. rstCheck.Close
  401.  
  402.  
Jul 27 '09 #1
Share this Question
Share on Google+
4 Replies


puppydogbuddy
Expert 100+
P: 1,923
Hi Phil,
you can simplif things considerably by setting it up as a control array. see this thread starting with post on 10/26/2008 that has a download file.

http://bytes.com/topic/access/answer...ing-grid-dates
Jul 27 '09 #2

Expert 100+
P: 1,287
If this is code in the Form_Load or something, you can drop all the [Forms]![FrmCalendarMain]!.
Lines 106-137 do not seem to be necessary.
Try using a loop something like this:
Expand|Select|Wrap|Line Numbers
  1. Dim strControlName As String
  2. For i = 8 to 23
  3.   For j = 0 to 1
  4.     If i < 10 Then
  5.       strControlName = "txtMon0" & i & ":"
  6.     Else
  7.       strControlName = "txtMon" & i & ":"
  8.     End If
  9.     If j = 0 Then 
  10.       strControlName = strControlName & "00"
  11.     Else
  12.       strControlName = strControlName & "30"
  13.     End If
  14.     If Me.Controls(strControlName) >= BookedStartTime And Me.Controls(strControlName) < BookedEndTime
  15.       Me.Controls(strControlName).BackColor = 12566463
  16.       Me.Controls(strControlName).ForeColor = 12566463
  17.       Me.Controls(strControlName).Enabled = False
  18.   Next
  19. Next
But don't forget to do the midnight case, since it's not covered in the loop.
Jul 27 '09 #3

puppydogbuddy
Expert 100+
P: 1,923
Hi Phil,

In case you or anyone else is interested, here is the entire code for the daily booking form used in the application. The code is triggered on the click of a calendar control button that is used to select a booking date that denotes the beginning of the 14 day booking period for which the bookings are displayed.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Calendar4_Click()
  2.     Dim Db As DAO.Database
  3.     Dim rs As DAO.Recordset
  4.     Dim strSql As String
  5.     Dim ctl As Control
  6.     Dim zDateCtlName As String
  7.     Dim zCtl As Control
  8.     Dim zCtlName As Variant
  9.     Dim j As Integer
  10.     Dim k As Integer
  11.  
  12. ' Copy chosen date from calendar to originating combo box
  13.    StartDate.Value = Calendar4.Value
  14. ' Return the focus to the combo box and hide the calendar
  15.    StartDate.SetFocus
  16.    Calendar4.Visible = False
  17.  
  18. '---------------------------------------------------------------------------------------
  19.  
  20. 'set up calendar date array, Unbound textboxes, top row of grid, with code =DateAdd("d",0-7,[Startdate])
  21.      For k = 1 To 8
  22.        Me("Date" & (k)) = DateAdd("d", k, [StartDate])
  23.      Next k
  24.  
  25. 'dynamically assign the arrival dates and departure dates to the appropriate control on the grid based the data returned by the record source.
  26. '-----------------------------------------------------------------------------------------------
  27.  
  28. 'fetch arrival and departure data falling in 8 day block on the calendar.
  29. strSql = "Select RoomNumber,BookingGridIndex ,ArrivalDate, DepartureDate,"
  30. strSql = strSql & " DateDiff('d',ArrivalDate,DepartureDate) As numDays"
  31. strSql = strSql & " From qryDailyBookings"
  32. strSql = strSql & " WHERE ([RoomNumber] Between '1' And '9')"
  33. strSql = strSql & " AND ([ArrivalDate] Between " & "#" & [Forms]![frmDailyBookings]![Date1] & "#" & " And " & "#" & [Forms]![frmDailyBookings]![Date8] & "#)"
  34. strSql = strSql & " OR ([RoomNumber] Between '1' And '9') AND ([DepartureDate] Between "
  35. strSql = strSql & "#" & [Forms]![frmDailyBookings]![Date1] & "#" & " And #" & [Forms]![frmDailyBookings]![Date8] & "#)"
  36. strSql = strSql & " Order By RoomNumber, ArrivalDate, DepartureDate;"
  37.                     '  Debug.Print strSql
  38.  
  39. Set Db = CurrentDb()
  40. Set rs = Db.OpenRecordset(strSql)
  41.  
  42.  
  43. 'ok, now that the rooms and calendar dates have
  44. 'been populated on the grid, let's populate the
  45. 'grid with the arrival and departures for the period.
  46.   '_________________________________________
  47.  
  48. If Not (rs.EOF And rs.BOF) Then
  49. rs.MoveFirst
  50. Do Until rs.EOF
  51.      'make a loop around the grid to pick up and identify all arrivals, where the calendar
  52.        ' date is within the arrival and/or departure dates for each room.
  53.   On Error Resume Next
  54.   For Each ctl In Me.Controls
  55.     If InStr(1, ctl.Name, "Date") <> 0 Then
  56.         zDateCtlName = ctl.Name
  57.         zCtlName = CStr("Day" & rs!RoomNumber & Right(zDateCtlName, 1))
  58.         Set zCtl = Controls(CStr(zCtlName))
  59.  
  60.         If ctl.Value = rs!ArrivalDate Then
  61.                zCtl.Value = "Arr " & Format(TimeValue(rs!ArrivalDate), "hh:mm AMPM")
  62.                zCtl.BackColor = "16777088"
  63.         ElseIf ctl.Value = rs!DepartureDate Then
  64.                zCtl.Value = "Dpt " & Format(TimeValue(rs!DepartureDate), "hh:mm AMPM")
  65.                zCtl.BackColor = vbYellow
  66.         ElseIf ctl.Value > rs!ArrivalDate And ctl.Value < rs!DepartureDate Then
  67.                zCtl.Value = "Occupied"
  68.                zCtl.BackColor = "16777088"
  69.         End If
  70.  
  71.    End If
  72.  Next ctl
  73.  On Error GoTo 0
  74.  
  75.  rs.MoveNext
  76.  
  77. Loop
  78.  
  79.  
  80.   'make a final loop around the grid to pick up and identify all vacancies, where the calendar
  81.     ' date is prior to the arrival and where the calendar date is after the departure date
  82.     On Error Resume Next
  83.        For Each ctl In Me.Controls
  84.            If InStr(1, ctl.Name, "Day") <> 0 Then
  85.               If IsNull(ctl.Value) Then
  86.                  ctl.Value = "Vacant"
  87.               End If
  88.            End If
  89.        Next
  90.     On Error GoTo 0
  91.  
  92. Else
  93.     Exit Sub
  94. End If
  95.  
  96. rs.Close
  97. Set rs = Nothing
  98. Set Db = Nothing
  99.  
  100. End Sub
  101.  
  102. Private Sub Form_Current()
  103. '(This works)
  104. On Error Resume Next
  105.   For Each c In Me.Controls
  106.    If InStr(1, c.Name, "Day") <> 0 Then
  107.    If c.Value = DepartureDate.Value Then
  108.       c.ForeColor = vbRed
  109.    Else
  110.      c.ForeColor = vbBlack
  111.  
  112. End If
  113. End If
  114.  
  115. Next
  116.  
  117. On Error GoTo 0
  118. End Sub
  119.  
  120.  
Jul 27 '09 #4

100+
P: 121
Thats a great help thanks guys
Jul 27 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.