How to get Field to vanish with code

[doyle60]

I'm trying to get a field in a datasheet sub to disappear when a
certain frame option is chosen. I'm trying something like this:

If Forms.Map010Mainfrm.RollUpFrame < 11 Then
Me.Size1.Visible = True
'Else
Me.Size1.Visible = False
End If

Where do I put such a code?

I've noticed that even when I make it invisible by hand, not even
using the code, the field shows up. I can't make it go away at all.

Thanks,

Matt

 

[Jim Burke in Novi]

It's hard to say where that goes without knowing more about how your
application is set up. Is Map010Mainfrm a form that you use to get to this
other form that has the Size1 field on it? And does Map010Mainfrm stay open
when you go to that 2nd form? When you open up that 2nd form, do you always
want the field visible property set that way for as long as that 2nd form is
open? If that's the case, then you would put the code that you showed that
sets the visible property in that 2nd form's open event. If I'm wrong about
how your appl is set up, the answer may be different - may need more info.
Also, you don't use '.' as a separator when referencing form fields, use '!',
e.g.

If Forms!Map010Mainfrm!RollUpFrame < 11 Then
Me!Size1.Visible = True
Else
Me!Size1.Visible = False
End If

'.' is only used to reference properties, not forms or controls.

 

[John W. Vinson]

I'm trying to get a field in a datasheet sub to disappear when a
certain frame option is chosen.

That's one of the many limitations of datasheet view. A datasheet cell doesn't
have a Visible property. You can set its width to 0.001 to conceal it but the
user can always undo that.

Consider using a Continuous form instead. A textbox on a continuous form
*does* have a Visible property that you can manipulate, and if you like the
appearance, it can be made to look very much like a datasheet.

 

[Douglas J. Steele]

For continuous forms, you need to use the ColumnHidden property:

If Forms!Map010Mainfrm!RollUpFrame < 11 Then
Me!Size1.ColumnHidden = True
Else
Me!Size1.ColumnHidden = False
End If

or, more succinctly,

Me!Size1.ColumnHidden = (Forms!Map010Mainfrm!RollUpFrame < 11)

 

[doyle60]

Thanks all. But continuous doesn't work for me. What can I do if I
must use a datasheet sub?

The proeblem with changing the width is that when it comes back I
would want it to come back at exactly the same width it was before
vanishing. Otherwise, it would annoy the user as there is too many
columns already and they do slide off the sub.

But this isn't that important and if it is too involved, forget it.

Thanks again,

Matt

 

[John W. Vinson]

Thanks all. But continuous doesn't work for me. What can I do if I
must use a datasheet sub?

The proeblem with changing the width is that when it comes back I
would want it to come back at exactly the same width it was before
vanishing. Otherwise, it would annoy the user as there is too many
columns already and they do slide off the sub.

But this isn't that important and if it is too involved, forget it.

It would be VERY involved if it's possible at all.

I've found very few or no cases where I find datasheets either necessary or
even worthwhile.

Just from curiosity, why MUST you use a datasheet? What does it do that a
continuous form doesn't? The only thing I can think of is that it lets the end
user resize columns and rows... which I'd consider a disadvantage, not an
advantage.

 

[doyle60]

I do not know much about continuous forms because I never use them.
But from what I understand, you cannot copy a column or a row and
paste it into another in a continuous form. One of my users can order
a style in 20 colors, filling in the Body Color column with each
color. The style could have a matching trim color. If so they can
easily copy the column's data over to the the Trim Color column. This
is just an example of many vertical copy and paste things. There are
examples too of wanting to copy and paste records (horozontal data).

My company has a computer that uses continuous datasheets and they are
a huge mistake. The users hate it as they should.

When my users have 50 rows and they must fill them all in with White,
all they have to do is press control quotes and the machine copies it
all down from the first (I set my control quotes to drop down one
after it fills it in so you don't have to be a piano player to fill in
all 50.) I've also created some other tools to make data entry easy.
I want my users to love me! Sure, you can program anything in a
continous form too, but I believe the datasheet is freer for copying
and pasting into excel as well, no?

Now perhaps continuous forms can copy and paste without programming
and if so it may be my loss.

Another reason I do not like continuous forms is that they cannot be
resized by the user, as you say below. This is a huge disadvantage to
me. I am presently creating a Projection sheet where a user fills in
data by months. So there are 12 columns for the months and another 5
columns for the key fields, and 5 others (to the right and not visible
without a scroll) for other data.

Yes, the 5 key fields could be header items but they may want to roll
up the data and not see, for example, by size. My query handles the
roll up by changing the size to "All". So instead of seeing the size
column read "All," I was thinking of making it dissappear as well.
Hence, my question.

But anyway, my datasheet is too wide already and there isn't a good
solution. It fits what they need to see and has the option to scroll
over just in case they want to roll up to a higher category of goods,
let's say, a Store, or a Division. If these columns were not there,
rolling up would not identify the data's Division, for example.

Anyway, there is no problem with what I am doing and making the column
disappear, my original question, may even be a bad thing.

Anyway, with this said, I may look into using a continuous form to do
a specific task. It may be easier on the mind and eyes.

Matt

 

[Douglas J. Steele]

Did you try ColumnHidden, as I suggested?

You might find "You Can Do That with Datasheets?", by Michael Kaplan, good
reading
http://msdn.microsoft.com/en-us/library/aa217449(office.11).aspx

 

[doyle60]

Yes, I tried it but I forget if I was actually trying ColumnHidden or
ColumnWidth. Whatever it was it had a 0 and 1not a true and false and
if the column came back it was in the default width. This posed a
problem and in the long run I realized that making the column go away
was more confusing anyway. Better that they see "All".

Anyway, I have question about continuous forms which I am not too
familiar with. It so happens that this project, for other reasons
than the above, could use a continuous form. Before I get started on
it I want to know if it is possible first. Perhaps it is a nested
continuous form or something.

Here is the job:
For users to fill in projection numbers in columns with Month names.
Projection numbers are predictions of future sales. They need to fill
in these numbers by Division, Style, Color, Dimen, Label, Size. Those
are the Keys but they belong to categories and groups and reps, etc.
which need to be outlined in the form.

Visibility:
The present screen has subforms for Past Sales, Production and On Hand
and Projections. The problem with the form is that they have to fill
in the size and than they see only those numbers. Than they fill out
their projections. Then they fill in the next size and see only those
numbers and fill in their projections. You see, there is a visibility
problem here. It would be nice if they fill in the Collection (which
contains many styles) and they see a continuous form with everything
style and SKU they need to do---all 60 to 100 of them.

What I want to show:
So if I do a continuous form I would want to lay this all out too.
This is an example of what they should see:

Style 2323 Color White Dimen None Label None Size S
Year 2007 Jan Feb Mar Etc. (Sales)
Year 2008 Jan Feb Mar Etc. (Sales)
[Small space here or line]
Year 2008 Jan Feb Mar Etc. (Open Production and On Hand)
[Small space here or line]
Year 2008 Jan Feb Mar Etc. (Projection)

[Larger space here and than the next SKU]
Style 2323 Color White Dimen None Label None Size M
Year 2007 Jan Feb Mar Etc. (Sales)
Year 2008 Jan Feb Mar Etc. (Sales)
[Small space here or line]
Year 2008 Jan Feb Mar Etc. (Open Production and On Hand)
[Small space here or line]
Year 2008 Jan Feb Mar Etc. (Projection)

Etc.

The Sales is from one table, the production and On Hand from another,
and the projection from still another table. The projection must be
editable.

So is this a nested continuous form? or what?

Though I will build a report that will show the above continous form,
it would be great if they could have this visibility on the screen. I
would probably not even have summations on the form, however, the
other form is better for that.

Thanks,

Matt

 

[Douglas J. Steele]

To the best of my knowledge, what you're describing can't be done using
continuous forms.

You might be able to use a FlexGrid or third party controls, but it can't be
done with the built in controls.