[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