Hiding columns?

  • Thread starter Thread starter Ilan
  • Start date Start date
I

Ilan

[Access 2000]


I have a form with a subform which displays records in a datasheet. Two
of the columns in the datasheet do not need to be displayed, because they
have a default value, and I don't want the user to change this value. One
way to do this is by hiding the columns in the datasheet. Is this safe
enough? The user is free to unhide these columns, isn't he?

I would much prefer to hide these columns in the grid when I design the
form. I note that when designing the subform for the datagrid, I can
alter the invidividual properties of the 'fields' in the datasheet. One
such property is 'visible'. If I change the value of this property to 'no',
the field still appears in the datasheet in 'runtime'.

Does anyone have any suggestions?

Thanks

Ilan
 
Ilan said:
[Access 2000]


I have a form with a subform which displays records in a datasheet.
Two of the columns in the datasheet do not need to be displayed,
because they have a default value, and I don't want the user to
change this value. One way to do this is by hiding the columns in
the datasheet. Is this safe enough? The user is free to unhide
these columns, isn't he?

I would much prefer to hide these columns in the grid when I design
the form. I note that when designing the subform for the datagrid,
I can alter the invidividual properties of the 'fields' in the
datasheet. One such property is 'visible'. If I change the value of
this property to 'no', the field still appears in the datasheet in
'runtime'.

Does anyone have any suggestions?
Click to expand...

If the columns have default values in the *table design*, you don't need
to have them on your form at all. If they're not on the form, they
won't appear in the form's datasheet and can't be unhidden.
 
Thanks for this. I think I failed to give enough detail.

Perhaps it would be simplest to give an example:

I have a table called 'sailors' and another table called 'ports'. I also
have a table called 'girls'

tblSailor
ID

tblPort
ID

tblGirl
SailorFK
PortFK
GirlName

Now, what I want is a form for adding girls to a sailors port. I have form
with two combo-boxes for this purpose. First, the user selects the sailor
from the combobox, then the user selects the port. Then, I have a datasheet
which shows all the sailor's girls in the port. I want this datasheet to
display only the list of girls which the sailor has in this port. I also
want the user to be able to add new girls by only having to type the girl's
name.

The fields of the query are as follows:

qryGirls
SELECT SailorFK, PortFK
FROM tblgirls
WHERE SailorFK = cmbSailor AND PortFK=cmbPort

Now, the datasheet displays three fields: the sailor, the port and the girl.
To ensure that the user now enters girls only for the selected port and
sailor, I have set the default value of the qryGirls.SailorFK to cmbSailor,
and the default value of the qryGirls.PortFK to cmbPort. However, if I do
not hide these fields, the user is free to change these values.

How do I prevent him from doing so? Am I going about this in the right way?

Many thanks

Ilan

Dirk Goldgar said:
Ilan said:
[Access 2000]


I have a form with a subform which displays records in a datasheet.
Two of the columns in the datasheet do not need to be displayed,
because they have a default value, and I don't want the user to
change this value. One way to do this is by hiding the columns in
the datasheet. Is this safe enough? The user is free to unhide
these columns, isn't he?

I would much prefer to hide these columns in the grid when I design
the form. I note that when designing the subform for the datagrid,
I can alter the invidividual properties of the 'fields' in the
datasheet. One such property is 'visible'. If I change the value of
this property to 'no', the field still appears in the datasheet in
'runtime'.

Does anyone have any suggestions?
Click to expand...

If the columns have default values in the *table design*, you don't need
to have them on your form at all. If they're not on the form, they
won't appear in the form's datasheet and can't be unhidden.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Ilan said:
Thanks for this. I think I failed to give enough detail.

Perhaps it would be simplest to give an example:

I have a table called 'sailors' and another table called 'ports'. I
also have a table called 'girls'

tblSailor
ID

tblPort
ID

tblGirl
SailorFK
PortFK
GirlName

Now, what I want is a form for adding girls to a sailors port. I
have form with two combo-boxes for this purpose. First, the user
selects the sailor from the combobox, then the user selects the port.
Then, I have a datasheet which shows all the sailor's girls in the
port. I want this datasheet to display only the list of girls which
the sailor has in this port. I also want the user to be able to add
new girls by only having to type the girl's name.

The fields of the query are as follows:

qryGirls
SELECT SailorFK, PortFK
FROM tblgirls
WHERE SailorFK = cmbSailor AND PortFK=cmbPort

Now, the datasheet displays three fields: the sailor, the port and
the girl. To ensure that the user now enters girls only for the
selected port and sailor, I have set the default value of the
qryGirls.SailorFK to cmbSailor, and the default value of the
qryGirls.PortFK to cmbPort. However, if I do not hide these fields,
the user is free to change these values.

How do I prevent him from doing so? Am I going about this in the
right way?
Click to expand...

I love your example!

You're making it harder than it needs to be, assuming I've understood
your intentions correctly. Using the tables in your example, set the
recordsource of your datasheet subform to this SQL statement:

SELECT SailorFK, PortFK, GirlName FROM tblGirls;

Note that no sailor or port criteria are being applied.

Remove any default values you may have applied to the controls on the
subform. Delete from the subform (but not from its recordsource) any
controls that are bound to SailorFK and PortFK. Now these fields exist
in the recordsource, but not as controls on the form at all.

For the subform control on the main form, set these properties:

Link Child Fields: SailorFK;PortFK
Link Master Fields: cmbSailor;cmbPort

That's all. The subform will list, by name, only those girls for the
sailor and port that are currently selected on the main form, and a new
girl can be added for that sailor/port combination simply by typing her
name in the blank new record.
 
Thanks, this worked perfectly.

May I just ask one question, so that I can understand what is going on? How
does the subform know that it has to display only one field? If the query
says:

"SELECT SailorFK, PortFK, GirlName FROM tblGirls"

then why don't all three fields get displayed in the subform? Does Access
sort of know that there is no point in displaying 'foreign' keys?


Many thanks for your help.
 
Thanks, this worked perfectly.

May I just ask one question, so that I can understand what is going on? How
does the subform know that it has to display only one field? If the query
says:

"SELECT SailorFK, PortFK, GirlName FROM tblGirls"

then why don't all three fields get displayed in the subform? Does Access
sort of know that there is no point in displaying 'foreign' keys?


Many thanks for your help.
 
Ilan said:
Thanks, this worked perfectly.

May I just ask one question, so that I can understand what is going
on? How does the subform know that it has to display only one field?
If the query says:

"SELECT SailorFK, PortFK, GirlName FROM tblGirls"

then why don't all three fields get displayed in the subform? Does
Access sort of know that there is no point in displaying 'foreign'
keys?
Click to expand...

No, it's not that smart. But a subform in datasheet view only displays
those columns from the recordsource that are bound to controls on the
form. Since there are no controls on the form for the foreign key
fields, the way we've set it up, those columns don't show up in the
datasheet.
 
Thank you.
This has been very helpful.

Dirk Goldgar said:
No, it's not that smart. But a subform in datasheet view only displays
those columns from the recordsource that are bound to controls on the
form. Since there are no controls on the form for the foreign key
fields, the way we've set it up, those columns don't show up in the
datasheet.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top