[AccessD] Two questions about datasheet forms

    You are most welcome Arthur! If at any stage you happen to need further assistance, just let me know. 

    The suggested solution for 12 column display of calendar months is implementable even when the source data for column headings is of date type.

A.D.Tejpal
--------------

  ----- Original Message ----- 
  From: Arthur Fuller 
  To: 'Access Developers discussion and problem solving' 
  Sent: Monday, August 30, 2004 21:23
  Subject: RE: [AccessD] Two questions about datasheet forms


  Thanks for the tips!

  In my case, since the column names are generated from the dates, I'll
  need to revise your code a bit to make it work, but thanks for the
  pointer. It won't be difficult now. As the to the problem of an unwieldy
  number of columns, I don't think that will be a problem, particularly if
  I start them on the Now() column. They might even think of it as a
  feature! (You can also press left-arrow to see previous months :)

  A.

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
  Sent: Sunday, August 29, 2004 11:20 PM
  To: Access Developers discussion and problem solving
  Subject: Re: [AccessD] Two questions about datasheet forms


  Arthur,

      Two alternatives could be considered -

      (a) No limit to the maximum number of columns to be displayed in the
  form. Additional columns keep getting added dynamically at run time as
  required.

      (b) Refine the cross-tab query so as to have fixed number of 12
  column headings ("Jan" to "Dec" in column headings property). In such a
  case, the year field should be set as the dominant row heading (if any
  additional field is to be set as supplementary row heading, it is
  preferably put to the right of year field - in query design grid).

      From your post, you appear to be aiming at an arrangement similar to
  (a) above. In such a case, the following implications need to be
  considered -
      (i) The form would tend to get progressively unwieldy, with ever
  increasing number of columns, requiring excessive horizontal scrolling.
      (ii) Creation of new controls at run time requires transitory
  switching to design view.
      (iii) Such a db would not be amenable to conversion as mde file.

      On the other hand, alternative (b) above is free from the handicaps
  just mentioned, at the same time affording the convenience of two
  pronged scanning of data as follows -
      (i)  Horizontally in a row - for data across various months in a
  given year and 
      (ii) Vertically in a column - for data across various years in a
  given month.

      Coming to second part of your posting, automatic setting of focus to
  the first matching cell in datasheet, corresponding to the current date
  (whenever the form is opened) can be effected by putting the code given
  below, in form's load event.

  Regards,
  A.D.Tejpal
  --------------
  ===================================
  Private Sub Form_Load()
      Dim Mn As String
      
      SYear.SetFocus
      DoCmd.FindRecord Year(Date)
      Mn = Format(Date, "mmm")
      Me(Mn).SetFocus
  End Sub

  Note - This code is based upon the name of year field being SYear and
  names of column headings being "Jan" to "Dec" 
  ===================================

    ----- Original Message ----- 
    From: Arthur Fuller 
    To: AccessD 
    Sent: Saturday, August 28, 2004 15:58
    Subject: [AccessD] Two questions about datasheet forms


    1. I built a datasheet form from a crosstab... And no simple crosstab
  either, it's actually 28 crosstabs UNIONed. No problem insofar as I'm
  dealing with the current data. The problem will emerge when the users
  add new rows to the data source, which will result in additional columns
  that won't be dealt with in the datasheet form. So the question is, can
  I programmatically generate a datasheet form based on the underlying
  query, and if so how so? Just to be clear, the queries will generate
  columns from 1/1/2003 to as far as the users care to go. I need to
    determine the number of columns in the current form, and if that
  differs from the number of columns delivered by the underlying query,
  then programmatically add enough columns to handle it gracefully.

    2. Given such a form with, say, 50 columns, all of which represent
  successive month-end-dates (i.e. 2/29/2004), how can I open the form and
  move the cursor to the month corresponding to Now()?

    If I can just get this to work the way I see it on the back of my
    eyelids, the users are going to have spiritual orgasms :)

    TIA,
    Arthur

  -- 
  _______________________________________________
  AccessD mailing list
  AccessD at databaseadvisors.com
  http://databaseadvisors.com/mailman/listinfo/accessd
  Website: http://www.databaseadvisors.com

  -- 
  _______________________________________________
  AccessD mailing list
  AccessD at databaseadvisors.com
  http://databaseadvisors.com/mailman/listinfo/accessd
  Website: http://www.databaseadvisors.com