Datasheets

[swas]

Hi,

A couple of questions on datasheets, if I may.

1. Is there any way to have column header names over two or more lines?

2. Is it possible when a cell is selected (Clicked, or double clicked for
example) to determine the cell position?

The second question is to superimpose a control over the selected cell to
allow edits.

I am displaying a crosstab query on a datasheet form, but need to edit cell
contents. My plan is to overlay a control on the selected cell, then
programatically make the necessary updates to the underlying table directly
from code. I'm happy enough with the general coding needed, but need access
to some more properties. As best I can see for the moment, I need to use
currentsectionleft, then add the columnwidths to get accross to the
me.selleft position, and similar for height.

Other thoughts to this end are appreciated. Thanks in advance


swas

 

[Allen Browne]

A1: No. You cannot have a column heading that spreads over 2 columns in a
datasheet.

A2: No. The datasheet of the query has no such events.

However if you create a form based on the query (showing the form in
Datasheet view if you wish), each control does have Click and DblClick
events, and the form's ActiveControl indicates which control has focus.

It is therefore possible to have an unbound text box on the form, and
SetFocus to this text box in the GotFocus of each control.

 

[swas]

Allen,

Thanks for the reply.

Q1A1: I mean to have two lines for the one column, ie.

Rather than 'Order Date', have

'Order
Date'

The data I am displaying is narrow, but the column headings force the widths
such that enough columns can't be displayed. I need to use form datasheet
layout as the datasheet has to be dynamically created (As opposed to
continuous form with labeled headings)

Q2A2: I did mean a datasheet on a form. Because it is a crosstab query the
recordsource it is not editable, but I need to accomplish this somehow. Hence
the thought to overlay a control (I have enough info available to update the
table directly).

Another possibility - I can have a combo box with a row source offering
multiple selections, even if the datasheet form is not updateable via the
crosstab query. Is there any way to detect a selected value on the cbo
dropdown before Access rejects the change attempt? None of the events seem to
fire.

Other suggestions appreciated. Thanks for the help.

swas

 

[Allen Browne]

#1. Okay: the datasheet can't have a heading 2 rows tall.
The nearest solution might be to use a form in Continuous View.
You can put a label into the Form Header section, and make it tall.

In general, I find Continuous better anyway: users can't mess up the columns
by confusing the order, freezing them, losing them (zero-width), etc.

#2. The unbound control overlaying the others in the continuous/datasheet
form should work. A bit messy: you will be writing to the table directly,
requerying, finding your place again, and clearing the unbound control.

Using a combo would be a bit more difficult. But since the combo is unbound,
you might be able to make the text value the bound column, which would allow
you to leave Limit To List as No, so you can enter values that are not in
the list.

 

[swas]

Allen,

Thanks for the comments. I'll have to play with some of these ways and see
what seems to work best. It will be a complex form regardless, so the easier
methods to solve editing the better.

Nice to see you working over Easter.


swas

 

[swas]

Allen,

Is my understanding correct that continuous form view cannot have frozen
columns (Or at lease a continuously viewable column to identify the rows)?

The form will have 30 or so columns, and 300 or so rows (Which unfortunately
can't be filtered), all combo boxes with the same rowsource. Are there any
recommended ways to manage network load for something like this? I thought of
loading the rowsource on combo selected, but in continuous form view this
will be the whole column.

At best presently from the below thread I will need to load a temporary
table from the crosstab to allow editing directly, then update main tables.

Thanks in advance.

swas

 

[Allen Browne]

Correct: one of the advantages of Continuous View is that users can get
themselves confused with frozen and hidden columns.

If all 30 combos have the same RowSource, you might want to load an array
(once) with the values, and then use a callback function to supply the
values to the combos. That should alleviate the network issue.

These callbacks are somewhat cryptic. There's an example here:
http://allenbrowne.com/func-02.html

 

[swas]

Allen,

Thanks for the thoughts. I am trying to work through building a datasheet
from a crosstab, both dynamically, which is a fair challenge for me.

I intend to use the form footer to display the datasheet cell info, to avoid
multiple combo's as below. I need some features of datasheets such as freeze
columns.

Thanks for the references - I have browsed your website many times, and take
this opportunity to thank you for its detailed content.


swas