[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