title: Trying to Make an Efficient Calendar in Microsoft Access
body: <p>I'm working on an equipment management system using a MS Access .mdb file for the front end, and SQL Server 2008 for the back end. If needed I can convert the front end to a MS Access 2010 file.</p>
  
  <p>I created a calendar form, where the users can see what equipment is booked, signed out, or over due. It looks like this:</p>
  
  <p><img src="https://i.stack.imgur.com/IOtqx.png" alt="enter image description here"></p>
  
  <p>I made this using 42 subforms, which is unfortunately slow. With the data shown above, it only takes about 5 seconds to load, but as soon as I use real data, it starts to really bog down unacceptably. I tried to make this more efficient by keeping the source object of the subforms blank until they are shown, as well as not loading the recordsource until this time. This helped enough to make the example seen above run passably fast, but it still isn't enough for real data.</p>
  
  <p>So what I would like to do, is either find a way to make this efficient while still using subforms, find another control that works in place of subforms, or to switch the subforms out with listboxes, but somehow still be able to format the colours of the rows. I understand this is impossible with listboxes as is, but I am a programmer, and am willing to try subclassing listboxes to do this if it won't waste too much of my time. Unfortunately I have never done any vba subclassing, so I would need to be pointed to some good resources in order to do so.</p>
  
  <p>The code to set the recordsource of each day subform is as follows:</p>
  
  <pre><code>f("sub" &amp; X &amp; Y).Form.RecordSource = "SELECT * " &amp; _
                                       "FROM QRY_Calendar " &amp; _
                                       "WHERE CDate(StartDate) &lt;= #" &amp; curDate &amp; "# " &amp; _
                                       "AND ((EndDate IS NULL OR CDate(EndDate) &gt;= #" &amp; curDate &amp; "#)" &amp; _
                                       IIf(CDate(curDate) &lt;= Date, " OR ((Date_In IS NULL OR CDate(Date_In) &gt;= #" &amp; curDate &amp; "#) AND Date_Out IS NOT NULL)", "") &amp; ") " &amp; _
                                       "ORDER BY IIF(Date_Out Is Not Null And (Date_In Is Null Or CDate2(Date_In)&gt;=#" &amp; curDate &amp; "#) And CDate2(EndDate)&lt;#" &amp; curDate &amp; "#,0,iif(CDate2(Date_Out)&lt;=#" &amp; curDate &amp; "# And (Date_In Is Null Or CDate2(Date_In)&gt;=#" &amp; curDate &amp; "#),1,2)), ID"
  </code></pre>
  
  <p>QRY_Calendar looks like this:</p>
  
  <pre><code>SELECT B.ID, Person, Initials, ProjectNum &amp; '-' &amp; ProjectYear &amp; '-' &amp; Format(TaskNum,'000') AS Project, Sign_Out_Code, Value AS Type, StartDate, EndDate, Date_Out, Date_In
  FROM (((TBL_Booking AS B INNER JOIN TBL_Person AS P ON B.PersonID = P.ID) INNER JOIN LKUP_List AS T ON B.EquipTypeID = T.ID) LEFT JOIN TBL_Usage AS U ON B.ID = U.BookingID) LEFT JOIN TBL_Equipment AS E ON U.Equipment_ID = E.ID;
  </code></pre>
  
  <p>StartDate and EndDate in the table TBL_Booking are the beginning and end of a booking, and Date_Out and Date_In in the table TBL_Usage are the beginning and end of a sign out.
  Each sign out is linked to a booking through the foreign key BookingID. If Date_In is NULL, that means that the equipment is currently signed out.</p>
  
  <p>LKUP_List is a poorly named table from before I started working on this years ago that I never bothered to change. It contains a list (among other things) of equipment types. Bookings are for equipment types and not specific items, and when a user signs out their equipment, a record in TBL_Usage is created which is linked to a specific piece of equipment.</p>
  
  <p>If anyone has ideas on which direction I should take with this and where I can look for guidance it would be much appreciated.</p>