need help with vertical (columnar) datasheets

[ti33m]

Hi All,

I'd like to include a datasheet on my user interface but since I'm
starting to run tight on space, I'd like to have a vertically-oriented
datasheet (column 1 has labels, column 2 has values), i.e. a
transposed datasheet or datasheet in column format. A vertical
datasheet will look cleaner, eliminate the need to scroll across (for
miles) and lay out bit more efficiently since I have some long labels.

It seems like Access only allows for horizontal datasheets. I can't
figure out how to make a datasheet orient in the vertical direction,
as in columns.

If a solution can maintain bound fields and controls that would be
great so that my users can use "find in form" feature rather than
trying to teach them query builder. At this point, I'm open to
hearing any and all suggestions.

Can anybody help?
Thanks,
Frances

[DFS]

You won't be able to create a datasheet form that automatically reorients
your data. You'll need to write some code to transpose it from it's present
"normal" state into a two-column work table (or you could probably write a
UNION query to get it into the format you want - and base the datasheet on
the query).

It would help if you posted your table structure.


"ti33m" <ti***@yahoo.com> wrote in message
news:91**************************@posting.google.c om...

Hi All,

I'd like to include a datasheet on my user interface but since I'm
starting to run tight on space, I'd like to have a vertically-oriented
datasheet (column 1 has labels, column 2 has values), i.e. a
transposed datasheet or datasheet in column format. A vertical
datasheet will look cleaner, eliminate the need to scroll across (for
miles) and lay out bit more efficiently since I have some long labels.

It seems like Access only allows for horizontal datasheets. I can't
figure out how to make a datasheet orient in the vertical direction,
as in columns.

If a solution can maintain bound fields and controls that would be
great so that my users can use "find in form" feature rather than
trying to teach them query builder. At this point, I'm open to
hearing any and all suggestions.

Can anybody help?
Thanks,
Frances

[ti33m]

"DFS" <no****@nospam.com> wrote in message news:<vu************@corp.supernews.com>...

You won't be able to create a datasheet form that automatically reorients
your data. You'll need to write some code to transpose it from it's present
"normal" state into a two-column work table (or you could probably write a
UNION query to get it into the format you want - and base the datasheet on
the query).

It would help if you posted your table structure.

Hi All,

The user interface is divided into 2 halves, a customer info section
and customer preference section. I have 2 tables, a tblCustomer and
tblPreference. tblPreference is linked to tblCustomer by way of a
fkey. The customer section is your usual fare: name, dob, contact
info, etc. The items in tblPreferences are all set to yes/no in the
design and on the form, they are represented by checkboxes in a
datasheet.

I am using the datasheet in an unconventional way and choose it mainly
for ease of maintenance and presentation. When Preferences are added
(and they will be in the future), once I add the field to
tblPreference, the datasheet will automatically put the new item on
the form when the form references the record source. I can do it the
harder way and draw out each individual checkbox.

As for writing code to transpose, how will that affect my users using
"Find in Form?" I use the same form for searching as for data entry
(they like that). If I write code to transpose the table, will I have
to write code when my users use "Find in Form" to feed arguments to
Access' Find In Form feature? To search, they want to do click and
fill in the blanks rather than use Query Builder.
Thanks,
Frances

[DFS]

Frances,

Maybe you can try to mimic a "vertical" datasheet by creating a form, adding
the fields to it and aligning them vertically, and setting form view to
continuous.

"ti33m" <ti***@yahoo.com> wrote in message
news:91**************************@posting.google.c om...

"DFS" <no****@nospam.com> wrote in message

news:<vu************@corp.supernews.com>...

You won't be able to create a datasheet form that automatically reorients your data. You'll need to write some code to transpose it from it's present "normal" state into a two-column work table (or you could probably write a UNION query to get it into the format you want - and base the datasheet on the query).

It would help if you posted your table structure.

Hi All,

The user interface is divided into 2 halves, a customer info section
and customer preference section. I have 2 tables, a tblCustomer and
tblPreference. tblPreference is linked to tblCustomer by way of a
fkey. The customer section is your usual fare: name, dob, contact
info, etc. The items in tblPreferences are all set to yes/no in the
design and on the form, they are represented by checkboxes in a
datasheet.

I am using the datasheet in an unconventional way and choose it mainly
for ease of maintenance and presentation. When Preferences are added
(and they will be in the future), once I add the field to
tblPreference, the datasheet will automatically put the new item on
the form when the form references the record source. I can do it the
harder way and draw out each individual checkbox.

As for writing code to transpose, how will that affect my users using
"Find in Form?" I use the same form for searching as for data entry
(they like that). If I write code to transpose the table, will I have
to write code when my users use "Find in Form" to feed arguments to
Access' Find In Form feature? To search, they want to do click and
fill in the blanks rather than use Query Builder.
Thanks,
Frances

[Larry Linson]

If this is an application you are distributing to users, it would be a much
better idea to give them a form. There's just too much that a user can
accidentally do wrong with datasheet view, even if they aren't doing it
maliciously.

If you need to transform your data for it to be intelligibly displayed, then
chances are that you might need to redesign your tables to make it easier to
work with. The design of your tables sounds as if it may not adhere to good
relational database design principles, and those aren't just arbitrary --
they really do make it easier to work with the data.

Larry Linson
Microsoft Access MVP

"ti33m" <ti***@yahoo.com> wrote in message
news:91**************************@posting.google.c om...

Hi All,

I'd like to include a datasheet on my user interface but since I'm
starting to run tight on space, I'd like to have a vertically-oriented
datasheet (column 1 has labels, column 2 has values), i.e. a
transposed datasheet or datasheet in column format. A vertical
datasheet will look cleaner, eliminate the need to scroll across (for
miles) and lay out bit more efficiently since I have some long labels.

It seems like Access only allows for horizontal datasheets. I can't
figure out how to make a datasheet orient in the vertical direction,
as in columns.

If a solution can maintain bound fields and controls that would be
great so that my users can use "find in form" feature rather than
trying to teach them query builder. At this point, I'm open to
hearing any and all suggestions.

Can anybody help?
Thanks,
Frances

[ti33m]

Hi All,

Thank you all for your suggestions. I was already going to use
checkboxes on my form but thought it might be better to use datasheets
because I'm trying to find a better way. I'm hoping to find a way to
display datasheets vertically rather than the default horizontal
arrangement.

As for my tables, they are normalized. The datasheet view is one
section of my form and for the following:

1. entering and displaying a small portion of the database (one
table);
2. "find in form" Access function is available without me having to
write code (one interface for data entry and searching so that users
see a consistent GUI that's intuitive); and
3. ease of maintenance (add a field in the table and viola! the field
shows up in the datasheet without further action on my part.

Is there a way to do to this short of writing code...? I'm thinking
that if I need to write code to transpose the table to make vertical
datasheets, Access' "find in form" function" will not work as is.
Thanks,
Frances

"Larry Linson" <bo*****@localhost.not> wrote in message news:<8J****************@nwrddc02.gnilink.net>...

If this is an application you are distributing to users, it would be a much
better idea to give them a form. There's just too much that a user can
accidentally do wrong with datasheet view, even if they aren't doing it
maliciously.

If you need to transform your data for it to be intelligibly displayed, then
chances are that you might need to redesign your tables to make it easier to
work with. The design of your tables sounds as if it may not adhere to good
relational database design principles, and those aren't just arbitrary --
they really do make it easier to work with the data.

Larry Linson
Microsoft Access MVP

[ti33m]

Hi All,

I got an email message asking what "find in form" was. I meant to
write "FILTER BY FORM" (Form view > Records > Filter > Filter by Form)
in my email messages to the newsgroup. "Find in form" is my
user-friendlier button name on the database form.

My apologies for any confusion this may have caused.
Thanks,
Frances