Setting invisible property of control

J

Joan

Hi,
I have a form (called SalesScreen) where there are certain controls that I
want invisible. Even though I have the Visible property set to No in the
controls' property sheets, they are still appearing on the form ( in
datasheet view). The controls that I want to be invisible are:
[SalesPrice], [Store], [ReturnedSalesPrice], and [ReturnedStore]. I have
the visible property set to "No" for these controls.

I set the value of these fields in code. For instance in the AfterUpdate
event of [Store1] I have the following code:

<Private Sub Store1_AfterUpdate()
<Dim db As Database
<Dim rs As DAO.Recordset

<Set db = CurrentDb
<Set rs = db.OpenRecordset("EnterStoresQuery", dbOpenDynaset)

< If Forms!SalesScreen!Returned = "Y" Then
< Forms!SalesScreen!ReturnedStore = Format(Forms!SalesScreen!Store1,
">")

< Else
< Forms!SalesScreen!Store = Forms!SalesScreen!Store1
< End If
< If Forms!SalesScreen!Returned = "Y" And Not
IsNull(Forms!SalesScreen![Store1]) Then
< Forms!SalesScreen!ReturnedSalePrice = Forms!SalesScreen!SalesPrice1
< End If
< rs.Close
< db.Close
<End Sub

Likewise in the AfterUpdate event of [SalesPrice1] I have the following
code:

<Private Sub SalesPrice1_AfterUpdate()
< Dim db As Database
< Dim rs As DAO.Recordset

< Set db = CurrentDb
< Set rs = db.OpenRecordset("EnterStoresQuery", dbOpenDynaset)

< If Forms!SalesScreen!Returned = "Y" And Not
IsNull(Forms!SalesScreen![Store]) Then
< Forms!SalesScreen!ReturnedSalePrice = Forms!SalesScreen!SalesPrice1
< Else
< Forms!SalesScreen!SalesPrice = Forms!SalesScreen!SalesPrice1
< End If

< rs.Close
< db.Close
<End Sub

Is the form(datasheet view) still displaying these controls because their
value is set in the form module?

I cannot figure out why the controls are still being shown! Any help
figuring this out would be very appreciated.

Joan
 
A

Adrian Jansen

To make a control not show in datasheet view, set its column width to zero.
Easiest way is to drag the width to zero, then save the form. You might
also have to lock the column so that a user cannot re-drag it open again.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
K

Kelvin

A datasheet view is not really a form in a sense. A datasheet is a way of
showing all the data for the underlying data of a form. If you don't want
particular fields to show, modify the query that is the source for the form
to not include these fields. Unlike a real form a datasheet does not have
controls which can be turned on or off. If you switch your form to form
view you can see the visible property working.

Kelvin
 
G

George Nicholson

Joan:

From the Help on the Visible (re controls) and the ColumnHidden properties:
*****
The Visible property has no effect on a column in Datasheet view. To specify
whether a column is visible in Datasheet view, use the ColumnHidden
property.
Note: the ColumnHidden property is not available in Design view. You can
set it by using "HideColumns" on the Format menu when in Datasheet view or
via Visual Basic. Setting the ColumnWidth property to 0 will also set
ColumnHidden to True. To set or change the ColumnHidden property for a
table or query by using Visual Basic, you must use a column's Properties
collection (example provided).
*****
If you go the route of changing the ColumnHidden property when the form is
opened, I think you should consider also changing it back when the form
closes. If I'm reading this right, when you create/change the property it
is applied to the ~Field~ of the query/table itself and not just an instance
of the field (like a control might seem to be). That suggests that the
setting would therefore be in effect ~anywhere~ that particular field is
viewable in Datasheet view unless you change it back. Of course, depending
on your requirements, this may not be an issue.

Possible alternative: Removing the control from the form entirely will hide
it in both Form and Datasheet view. The field will still be present in the
Form's Recordset so it can still be accessed via code, even if it is not
present on the form itself. If you are only using the field in code, this
might also be a viable (and perhaps much easier) solution.

--
George Nicholson

Remove 'Junk' from return address.


Joan said:
Hi,
I have a form (called SalesScreen) where there are certain controls that I
want invisible. Even though I have the Visible property set to No in the
controls' property sheets, they are still appearing on the form ( in
datasheet view). The controls that I want to be invisible are:
[SalesPrice], [Store], [ReturnedSalesPrice], and [ReturnedStore]. I have
the visible property set to "No" for these controls.

I set the value of these fields in code. For instance in the AfterUpdate
event of [Store1] I have the following code:

<Private Sub Store1_AfterUpdate()
<Dim db As Database
<Dim rs As DAO.Recordset

<Set db = CurrentDb
<Set rs = db.OpenRecordset("EnterStoresQuery", dbOpenDynaset)

< If Forms!SalesScreen!Returned = "Y" Then
< Forms!SalesScreen!ReturnedStore = Format(Forms!SalesScreen!Store1,
">")

< Else
< Forms!SalesScreen!Store = Forms!SalesScreen!Store1
< End If
< If Forms!SalesScreen!Returned = "Y" And Not
IsNull(Forms!SalesScreen![Store1]) Then
< Forms!SalesScreen!ReturnedSalePrice = Forms!SalesScreen!SalesPrice1
< End If
< rs.Close
< db.Close
<End Sub

Likewise in the AfterUpdate event of [SalesPrice1] I have the following
code:

<Private Sub SalesPrice1_AfterUpdate()
< Dim db As Database
< Dim rs As DAO.Recordset

< Set db = CurrentDb
< Set rs = db.OpenRecordset("EnterStoresQuery", dbOpenDynaset)

< If Forms!SalesScreen!Returned = "Y" And Not
IsNull(Forms!SalesScreen![Store]) Then
< Forms!SalesScreen!ReturnedSalePrice = Forms!SalesScreen!SalesPrice1
< Else
< Forms!SalesScreen!SalesPrice = Forms!SalesScreen!SalesPrice1
< End If

< rs.Close
< db.Close
<End Sub

Is the form(datasheet view) still displaying these controls because their
value is set in the form module?

I cannot figure out why the controls are still being shown! Any help
figuring this out would be very appreciated.

Joan
Click to expand...
 
J

Joan

George
Thanks for replying to my post. Your last alternative sounded like the
best solution for me as I am only using those fields in code. But how do I
change my code to refer to the field and not the control? How do I set the
field ReturnedStore to the value of Forms!SalesScreen!Store1 ?
Do I just say : [ReturnedStore] = Forms!SalesScreen!Store1 ?

Joan


George Nicholson said:
Joan:

From the Help on the Visible (re controls) and the ColumnHidden properties:
*****
The Visible property has no effect on a column in Datasheet view. To specify
whether a column is visible in Datasheet view, use the ColumnHidden
property.
Note: the ColumnHidden property is not available in Design view. You can
set it by using "HideColumns" on the Format menu when in Datasheet view or
via Visual Basic. Setting the ColumnWidth property to 0 will also set
ColumnHidden to True. To set or change the ColumnHidden property for a
table or query by using Visual Basic, you must use a column's Properties
collection (example provided).
*****
If you go the route of changing the ColumnHidden property when the form is
opened, I think you should consider also changing it back when the form
closes. If I'm reading this right, when you create/change the property it
is applied to the ~Field~ of the query/table itself and not just an instance
of the field (like a control might seem to be). That suggests that the
setting would therefore be in effect ~anywhere~ that particular field is
viewable in Datasheet view unless you change it back. Of course, depending
on your requirements, this may not be an issue.

Possible alternative: Removing the control from the form entirely will hide
it in both Form and Datasheet view. The field will still be present in the
Form's Recordset so it can still be accessed via code, even if it is not
present on the form itself. If you are only using the field in code, this
might also be a viable (and perhaps much easier) solution.

--
George Nicholson

Remove 'Junk' from return address.


Joan said:
Hi,
I have a form (called SalesScreen) where there are certain controls that I
want invisible. Even though I have the Visible property set to No in the
controls' property sheets, they are still appearing on the form ( in
datasheet view). The controls that I want to be invisible are:
[SalesPrice], [Store], [ReturnedSalesPrice], and [ReturnedStore]. I have
the visible property set to "No" for these controls.

I set the value of these fields in code. For instance in the AfterUpdate
event of [Store1] I have the following code:

<Private Sub Store1_AfterUpdate()
<Dim db As Database
<Dim rs As DAO.Recordset

<Set db = CurrentDb
<Set rs = db.OpenRecordset("EnterStoresQuery", dbOpenDynaset)

< If Forms!SalesScreen!Returned = "Y" Then
< Forms!SalesScreen!ReturnedStore = Format(Forms!SalesScreen!Store1,
">")

< Else
< Forms!SalesScreen!Store = Forms!SalesScreen!Store1
< End If
< If Forms!SalesScreen!Returned = "Y" And Not
IsNull(Forms!SalesScreen![Store1]) Then
< Forms!SalesScreen!ReturnedSalePrice = Forms!SalesScreen!SalesPrice1
< End If
< rs.Close
< db.Close
<End Sub

Likewise in the AfterUpdate event of [SalesPrice1] I have the following
code:

<Private Sub SalesPrice1_AfterUpdate()
< Dim db As Database
< Dim rs As DAO.Recordset

< Set db = CurrentDb
< Set rs = db.OpenRecordset("EnterStoresQuery", dbOpenDynaset)

< If Forms!SalesScreen!Returned = "Y" And Not
IsNull(Forms!SalesScreen![Store]) Then
< Forms!SalesScreen!ReturnedSalePrice = Forms!SalesScreen!SalesPrice1
< Else
< Forms!SalesScreen!SalesPrice = Forms!SalesScreen!SalesPrice1
< End If

< rs.Close
< db.Close
<End Sub

Is the form(datasheet view) still displaying these controls because their
value is set in the form module?

I cannot figure out why the controls are still being shown! Any help
figuring this out would be very appreciated.

Joan
Click to expand...
Click to expand...
 
G

George Nicholson

Yes.
If Store1 is the name of the field then:
Forms!SalesScreen!Store1
Forms!SalesScreen.Store1
should both work, as long as the field is in the Form's
RecordSource/Recordset, even if the field doesn't have a control present on
the form.

If called from the form itself, these should work:
Me!MyFieldName
Me.MyFieldName

Any confusion that you may have on this point may be due to the fact that
Access automatically gives controls the same name as the field upon which
it's based. If you manually change the name of the field (say, to
txtStore1) you'll see (via VBE's AutoListMembers option) that Store1 and
txtStore1 appear separately in the list of available items for the form. If
you then remove txtStore1, the field Store1 remains available because it is
still part of the Recordsource upon which the form is based.

--
George Nicholson

Remove 'Junk' from return address.


Joan said:
George
Thanks for replying to my post. Your last alternative sounded like the
best solution for me as I am only using those fields in code. But how do I
change my code to refer to the field and not the control? How do I set the
field ReturnedStore to the value of Forms!SalesScreen!Store1 ?
Do I just say : [ReturnedStore] = Forms!SalesScreen!Store1 ?

Joan


George Nicholson said:
Joan:

From the Help on the Visible (re controls) and the ColumnHidden properties:
*****
The Visible property has no effect on a column in Datasheet view. To specify
whether a column is visible in Datasheet view, use the ColumnHidden
property.
Note: the ColumnHidden property is not available in Design view. You can
set it by using "HideColumns" on the Format menu when in Datasheet view or
via Visual Basic. Setting the ColumnWidth property to 0 will also set
ColumnHidden to True. To set or change the ColumnHidden property for a
table or query by using Visual Basic, you must use a column's Properties
collection (example provided).
*****
If you go the route of changing the ColumnHidden property when the form is
opened, I think you should consider also changing it back when the form
closes. If I'm reading this right, when you create/change the property it
is applied to the ~Field~ of the query/table itself and not just an instance
of the field (like a control might seem to be). That suggests that the
setting would therefore be in effect ~anywhere~ that particular field is
viewable in Datasheet view unless you change it back. Of course, depending
on your requirements, this may not be an issue.

Possible alternative: Removing the control from the form entirely will hide
it in both Form and Datasheet view. The field will still be present in the
Form's Recordset so it can still be accessed via code, even if it is not
present on the form itself. If you are only using the field in code, this
might also be a viable (and perhaps much easier) solution.
Click to expand...
that
I
want invisible. Even though I have the Visible property set to No in the
controls' property sheets, they are still appearing on the form ( in
datasheet view). The controls that I want to be invisible are:
[SalesPrice], [Store], [ReturnedSalesPrice], and [ReturnedStore]. I have
the visible property set to "No" for these controls.

I set the value of these fields in code. For instance in the AfterUpdate
event of [Store1] I have the following code:

<Private Sub Store1_AfterUpdate()
<Dim db As Database
<Dim rs As DAO.Recordset

<Set db = CurrentDb
<Set rs = db.OpenRecordset("EnterStoresQuery", dbOpenDynaset)

< If Forms!SalesScreen!Returned = "Y" Then
< Forms!SalesScreen!ReturnedStore = Format(Forms!SalesScreen!Store1,
">")

< Else
< Forms!SalesScreen!Store = Forms!SalesScreen!Store1
< End If
< If Forms!SalesScreen!Returned = "Y" And Not
IsNull(Forms!SalesScreen![Store1]) Then
< Forms!SalesScreen!ReturnedSalePrice = Forms!SalesScreen!SalesPrice1
< End If
< rs.Close
< db.Close
<End Sub

Likewise in the AfterUpdate event of [SalesPrice1] I have the following
code:

<Private Sub SalesPrice1_AfterUpdate()
< Dim db As Database
< Dim rs As DAO.Recordset

< Set db = CurrentDb
< Set rs = db.OpenRecordset("EnterStoresQuery", dbOpenDynaset)

< If Forms!SalesScreen!Returned = "Y" And Not
IsNull(Forms!SalesScreen![Store]) Then
< Forms!SalesScreen!ReturnedSalePrice = Forms!SalesScreen!SalesPrice1
< Else
< Forms!SalesScreen!SalesPrice = Forms!SalesScreen!SalesPrice1
< End If

< rs.Close
< db.Close
<End Sub

Is the form(datasheet view) still displaying these controls because their
value is set in the form module?

I cannot figure out why the controls are still being shown! Any help
figuring this out would be very appreciated.

Joan
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

UpdateEvent 2
Runtime error 2
Edit and Update of field not working 5
how to code a button to export results of stored procedure to excel file 0
problems referencing a subform control? 1
Runtime Error 3001 Arguments Are Of The Wrong Type, Are Out Of TheAcceptable Range, or are in confli 4
Too Few Parameters in OpenRecordset 3
populate listbox form database table 0

Top