Enable different controls for each record in continuous form

[Guest]

Hi

I have some code for a control 'No of Officers' in my continuous subform
which, depending on the number (from 1 to 8) selected will make visible that
many controls next to it where I enter each officer's ID number. firstly, My
code isn't exactly what I want. All the invisible controls are visible until
I make a selection in 'no of officers' where it then makes the remainder
invisible. I have the code in the after update of the 'no of officers'
control. Where can I put it so that they all remain invisible until the
selection is made.

Then, if i select 2 officers, only 2 controls appear (I want this), but when
I go onto the next record and select 4 officers, then all records allow 4
controls to be visible, same as if i then select 1 officer, then all records
allow only 1 control to be visible. I have checked, it doesnt interfere with
my data, but it doesnt behave how i would like it. How can I make the code
behave independently on each record of the continuous form?

If you need more info, please ask. I am teaching myself to use VBA at the
moment, so am not very good at it.

Kind Regards
Rigby

 

[RoyVidar]

rigby wrote in message

Hi

I have some code for a control 'No of Officers' in my continuous subform
which, depending on the number (from 1 to 8) selected will make visible that
many controls next to it where I enter each officer's ID number. firstly, My
code isn't exactly what I want. All the invisible controls are visible until
I make a selection in 'no of officers' where it then makes the remainder
invisible. I have the code in the after update of the 'no of officers'
control. Where can I put it so that they all remain invisible until the
selection is made.

Then, if i select 2 officers, only 2 controls appear (I want this), but when
I go onto the next record and select 4 officers, then all records allow 4
controls to be visible, same as if i then select 1 officer, then all records
allow only 1 control to be visible. I have checked, it doesnt interfere with
my data, but it doesnt behave how i would like it. How can I make the code
behave independently on each record of the continuous form?

If you need more info, please ask. I am teaching myself to use VBA at the
moment, so am not very good at it.

Kind Regards
Rigby

When working with continuous forms, what you see, isn't multiple
controls, but multiple instances of the same control - so if you alter
the properties of one control programatically, then it affects all
instances of it.

In a situation as you describe, I'd probably take a look at conditional
formatting from the format menu, where you won't be able to alter
controls visible property, but at least the enabled property.

But one question/comment - you are asying you have 1-8 controls on this
form, which you intend to "make available" based on a selection. To me,
I'm afraid, that looks like an unnormalized structure, where you
perhaps
should have another table for these officers, then tie them to this
record by the record id of it. But again, just a comment/question.

Should it be true that you have normalization issues, the sooner you
deal with them, the less pain will be experienced as the app continue
to grow ...

 

[Guest]

When working with continuous forms, what you see, isn't multiple
controls, but multiple instances of the same control - so if you alter
the properties of one control programatically, then it affects all
instances of it.

I understand what you are saying here. And realise I won't be able to do as
i had hoped to with these controls. It's not a big problem, I was just hoping
to make it more 'user-friendly'.

In a situation as you describe, I'd probably take a look at conditional
formatting from the format menu, where you won't be able to alter
controls visible property, but at least the enabled property.

I have had a look at this option too and don't think it will suit my needs
in this case, but i found it quite interesting and may have a use for it
elsewhere.

But one question/comment - you are asying you have 1-8 controls on this
form, which you intend to "make available" based on a selection. To me,
I'm afraid, that looks like an unnormalized structure, where you
perhaps
should have another table for these officers, then tie them to this
record by the record id of it. But again, just a comment/question.

This sounds interesting, but i am not sure what you mean about 'unnormalized
structure'. This is primarily an Employee Database for security officers and
ALL their details. This particular part of the database we are discussing is
a statistical planning section where i hope to show who and where these guys
are working and when. The 8 controls are becuase there canbe up to a max of 8
officers in one place at one time (around the world). i have the form made so
when you select their ID number from a lookup query, it will produce their
initials in the next un-enabled control.

not sure where i am going with this, just trying to give you a better
picture. The continuous form is to show all entries for each day of the
month, no more, no less.

Should it be true that you have normalization issues, the sooner you
deal with them, the less pain will be experienced as the app continue
to grow ...

Thank you for all your help

Kind Regards
Rigby

 

[RoyVidar]

rigby wrote in message
<[email protected]> :

Comments inline

I understand what you are saying here. And realise I won't be able to do as
i had hoped to with these controls. It's not a big problem, I was just hoping
to make it more 'user-friendly'.

You can usually achieve that through conditional formatting.

I have had a look at this option too and don't think it will suit my needs
in this case, but i found it quite interesting and may have a use for it
elsewhere.

You did check out the "expression is" selection there, which will allow
you to use test conditions not involving this control. For instance

[txtNumOfOfficers]>2

which could be used as criterion for the third officer control?

This sounds interesting, but i am not sure what you mean about 'unnormalized
structure'. This is primarily an Employee Database for security officers and
ALL their details. This particular part of the database we are discussing is
a statistical planning section where i hope to show who and where these guys
are working and when. The 8 controls are becuase there canbe up to a max of 8
officers in one place at one time (around the world). i have the form made so
when you select their ID number from a lookup query, it will produce their
initials in the next un-enabled control.

not sure where i am going with this, just trying to give you a better
picture. The continuous form is to show all entries for each day of the
month, no more, no less.

I could very well be wrong, but here's my thougts ...

A calendar with a fixed number of entries (here officers) per day is in
my eyes a normalization issue, at least if this mirrors how the
information is stored in the tables. If this is stored in a table - one
record per each day? if so, you are using an unnormalized structure.

With such, you cannot use simple SQL to find out how many were where,
you will have to resort to either "clumsy" SQL or programming.

Now consider

tblDays
dtDate (PK)
other fields

tblOfficers
OffID ()PK
OffName
other fields

tblOfficerOnDay
dtDate (FK from tblDays)
OffID (FK from tblOfficers)
(the date and officer id would make the composite primary key)
other fields?

With a structure like this, it would be easy to get information through
SQL ...

Here's a bit of reading "Fundamentals of Relational Database Design",
by
Paul Litwin.
http://r937.com/relational.html

If I haven't misread completely, this looks like 1NF - repeating
groups.

 

[Guest]

Roy Vidar

Thank you so much for all your help so far. Unfortunately I am unable to
work on the database for a few days, but i have started attempting your
suggestion. I will do some more reading up of it and I will let you know how
it turns out.

Thanks again.

Kind Regards
Rigby