Drop down list box colour

[Billiam]

I have a bound drop down list box which displays 7 set choices: Active,
Interim, On Leave, suspended, de-certifed, resigned and deceased. I would
like to add colour to some of the choices, so the items are noticed more
easily (ie the colour red is the background to the choice Suspended, the
colour green is the background to the choice Active).
Can this be done within the same drop down list box?
Billiam

 

[Ken Snell \(MVP\)]

No, not with the built-in ACCESS combobox control.

 

[Jeff Boyce]

I am not aware of a way within Access, but you might want to visit Stephen
Lebans' website to see if he's done something like this...

http://www.lebans.com/

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Billiam]

Thank you for taking the time to respond, Ken. I really appreciate it.

 

[Billiam]

Thanks Jeff, I will have a look and see. I really appreciate your help!

 

[Billiam]

It does not appear to me that Stephen Lebans has done this...oh well!

 

[strive4peace]

How about using a subform with conditional formatting?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Billiam]

I am so sorry, Crystal, I did not receive notification of your reply...

I am afraid I am fairly new to all this, so bear with me
please...........................okay pretty please???

So if I make a seperate lookup/reference table, (Active, Suspended etc) base
a form on it and drop it on my main form that would work? As to the
conditional formatting, I do not know how to proceed--here is my best guess
(you can LOL if you like!:

I would add a conditional expression to the Validation Rule property of the
table
=IIf([Active] = "Yes", "Active (somehow add a hex value???ie green" "Active
somehow greyed out"
and somehow nest the other Status' in more IIf statements?????
:

I really do not know what to do, Crystal, any advice as to an examle posted
anywhere???

 

[strive4peace]

Hi Billiam (is that your name?)

in the design view of the form (you can do it in Form view but I feel it
is best to make design changes in design view):

click on the control

from the menu, choose Format, Conditional Formatting

for instance:
Expression Is --> [Active] = True
--> set Foreground Color, background color, Bold, Italic, Underline,
and/or enabled

~~~
for information on making a subform, read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal

*
have an awesome day
*

I am so sorry, Crystal, I did not receive notification of your reply...

I am afraid I am fairly new to all this, so bear with me
please...........................okay pretty please???

So if I make a seperate lookup/reference table, (Active, Suspended etc) base
a form on it and drop it on my main form that would work? As to the
conditional formatting, I do not know how to proceed--here is my best guess
(you can LOL if you like!:

I would add a conditional expression to the Validation Rule property of the
table
=IIf([Active] = "Yes", "Active (somehow add a hex value???ie green" "Active
somehow greyed out"
and somehow nest the other Status' in more IIf statements?????
:

I really do not know what to do, Crystal, any advice as to an examle posted
anywhere???

How about using a subform with conditional formatting?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Billiam]

Hi Crystal,

Hi Billiam (is that your name?)

It's a nickname ...Bill+William=Billiam

I would just like to thank you sincerely for taking the time to help....I
have learned so much reading through these forums, and I find your resonses
extremely illuminating! So please except my sincerest thanks for taking the
time to help all of us newbies out--YOU ARE AWESOME!!!

I am going to give this a try on Monday so will let you know how I do!
Have a great weekend!

Billiam

in the design view of the form (you can do it in Form view but I feel it
is best to make design changes in design view):

click on the control

from the menu, choose Format, Conditional Formatting

for instance:
Expression Is --> [Active] = True
--> set Foreground Color, background color, Bold, Italic, Underline,
and/or enabled

~~~
for information on making a subform, read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal

*
have an awesome day
*

I am so sorry, Crystal, I did not receive notification of your reply...

I am afraid I am fairly new to all this, so bear with me
please...........................okay pretty please???

So if I make a seperate lookup/reference table, (Active, Suspended etc) base
a form on it and drop it on my main form that would work? As to the
conditional formatting, I do not know how to proceed--here is my best guess
(you can LOL if you like!:

I would add a conditional expression to the Validation Rule property of the
table
=IIf([Active] = "Yes", "Active (somehow add a hex value???ie green" "Active
somehow greyed out"
and somehow nest the other Status' in more IIf statements?????
:

I really do not know what to do, Crystal, any advice as to an examle posted
anywhere???

How about using a subform with conditional formatting?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Billiam wrote:
I have a bound drop down list box which displays 7 set choices: Active,
Interim, On Leave, suspended, de-certifed, resigned and deceased. I would
like to add colour to some of the choices, so the items are noticed more
easily (ie the colour red is the background to the choice Suspended, the
colour green is the background to the choice Active).
Can this be done within the same drop down list box?
Billiam

 

[strive4peace]

Hi Billiam (thanks for explaining that <smile>)

you're welcome

please let us know if it works out

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

Hi Billiam (is that your name?)

It's a nickname ...Bill+William=Billiam

I would just like to thank you sincerely for taking the time to help....I
have learned so much reading through these forums, and I find your resonses
extremely illuminating! So please except my sincerest thanks for taking the
time to help all of us newbies out--YOU ARE AWESOME!!!

I am going to give this a try on Monday so will let you know how I do!
Have a great weekend!

Billiam

in the design view of the form (you can do it in Form view but I feel it
is best to make design changes in design view):

click on the control

from the menu, choose Format, Conditional Formatting

for instance:
Expression Is --> [Active] = True
--> set Foreground Color, background color, Bold, Italic, Underline,
and/or enabled

~~~
for information on making a subform, read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal

*
have an awesome day
*

I am so sorry, Crystal, I did not receive notification of your reply...

I am afraid I am fairly new to all this, so bear with me
please...........................okay pretty please???

So if I make a seperate lookup/reference table, (Active, Suspended etc) base
a form on it and drop it on my main form that would work? As to the
conditional formatting, I do not know how to proceed--here is my best guess
(you can LOL if you like!:

I would add a conditional expression to the Validation Rule property of the
table
=IIf([Active] = "Yes", "Active (somehow add a hex value???ie green" "Active
somehow greyed out"
and somehow nest the other Status' in more IIf statements?????
:

I really do not know what to do, Crystal, any advice as to an examle posted
anywhere???

How about using a subform with conditional formatting?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Billiam wrote:
I have a bound drop down list box which displays 7 set choices: Active,
Interim, On Leave, suspended, de-certifed, resigned and deceased. I would
like to add colour to some of the choices, so the items are noticed more
easily (ie the colour red is the background to the choice Suspended, the
colour green is the background to the choice Active).
Can this be done within the same drop down list box?
Billiam

 

[Billiam]

By menu, do you mean the form's property sheet menu? If so, I do not see a
tab or area called conditional formatting...or do you mean to place the
conditional formatting expression in the Format area of the form property
sheet, or in the validation area?

Thanks,
Billiam

 

[Billiam]

Hi Crystal,

If I right click while I am on the combo box I get the conditional
Formatting option. There are 3 conditions available and one default condition.

The default condition (if no other conditions are met) is working. The three
conditions I set up, are not.

1. Are there only three conditions allowed per combo box?

2. If so, can you combine more than one condition in a statement (for
example Suspended and de-certified have a red background within the same
condition expression?

3. If not, how do I add more conditions for the combobox?

4. The conditions which are not working are:

Condition 1
Expression Is [Active] = True
and I set the foreground & background colours, Bold and enabled.

Condition 2
Expression Is [Suspended] = True
and I set the background & foreground, Bold and enabled

I suspect the problem is I have included the Items list in the rowsource of
the combobox property sheet. Do they need to be in a lookup table instead?
There are 7 individual Statuses which *should* never change...

Thanks for any help!
Billiam

Hi Billiam (thanks for explaining that <smile>)

you're welcome

please let us know if it works out

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

Hi Billiam (is that your name?)

It's a nickname ...Bill+William=Billiam

I would just like to thank you sincerely for taking the time to help....I
have learned so much reading through these forums, and I find your resonses
extremely illuminating! So please except my sincerest thanks for taking the
time to help all of us newbies out--YOU ARE AWESOME!!!

I am going to give this a try on Monday so will let you know how I do!
Have a great weekend!

Billiam

in the design view of the form (you can do it in Form view but I feel it
is best to make design changes in design view):

click on the control

from the menu, choose Format, Conditional Formatting

for instance:
Expression Is --> [Active] = True
--> set Foreground Color, background color, Bold, Italic, Underline,
and/or enabled

~~~
for information on making a subform, read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal

*
have an awesome day
*




Billiam wrote:
I am so sorry, Crystal, I did not receive notification of your reply...

I am afraid I am fairly new to all this, so bear with me
please...........................okay pretty please???

So if I make a seperate lookup/reference table, (Active, Suspended etc) base
a form on it and drop it on my main form that would work? As to the
conditional formatting, I do not know how to proceed--here is my best guess
(you can LOL if you like!:

I would add a conditional expression to the Validation Rule property of the
table
=IIf([Active] = "Yes", "Active (somehow add a hex value???ie green" "Active
somehow greyed out"
and somehow nest the other Status' in more IIf statements?????
:

I really do not know what to do, Crystal, any advice as to an examle posted
anywhere???

How about using a subform with conditional formatting?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Billiam wrote:
I have a bound drop down list box which displays 7 set choices: Active,
Interim, On Leave, suspended, de-certifed, resigned and deceased. I would
like to add colour to some of the choices, so the items are noticed more
easily (ie the colour red is the background to the choice Suspended, the
colour green is the background to the choice Active).
Can this be done within the same drop down list box?
Billiam

 

[Billiam]

Crystal I am sorry, I just entered conditional formatting and came up with a
ton of entries, I thought I had searched it and nothing came up....

I tried using Field Value is equal to "Active" works.
Then I read a post that suggested applying conditional formats to all your
text boxes on a form by using Field Has Focus

I applied this to all of the text boxes on my form, so the user would know
where they were, and now the form causes Access 2007 to crash as there is a
"stack problem". Fortunately, I have a good backup!!!!!!

Is that much formatting too much??? (About 40 text boxes).

Finally, is there a way to bypass the limit of conditions programmatically
as I need 7 conditions and Access 2007 only has 4 (default+three
conditions)...
Billiam

Hi Billiam (thanks for explaining that <smile>)

you're welcome

please let us know if it works out

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

Hi Billiam (is that your name?)

It's a nickname ...Bill+William=Billiam

I would just like to thank you sincerely for taking the time to help....I
have learned so much reading through these forums, and I find your resonses
extremely illuminating! So please except my sincerest thanks for taking the
time to help all of us newbies out--YOU ARE AWESOME!!!

I am going to give this a try on Monday so will let you know how I do!
Have a great weekend!

Billiam

in the design view of the form (you can do it in Form view but I feel it
is best to make design changes in design view):

click on the control

from the menu, choose Format, Conditional Formatting

for instance:
Expression Is --> [Active] = True
--> set Foreground Color, background color, Bold, Italic, Underline,
and/or enabled

~~~
for information on making a subform, read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal

*
have an awesome day
*




Billiam wrote:
I am so sorry, Crystal, I did not receive notification of your reply...

I am afraid I am fairly new to all this, so bear with me
please...........................okay pretty please???

So if I make a seperate lookup/reference table, (Active, Suspended etc) base
a form on it and drop it on my main form that would work? As to the
conditional formatting, I do not know how to proceed--here is my best guess
(you can LOL if you like!:

I would add a conditional expression to the Validation Rule property of the
table
=IIf([Active] = "Yes", "Active (somehow add a hex value???ie green" "Active
somehow greyed out"
and somehow nest the other Status' in more IIf statements?????
:

I really do not know what to do, Crystal, any advice as to an examle posted
anywhere???

How about using a subform with conditional formatting?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Billiam wrote:
I have a bound drop down list box which displays 7 set choices: Active,
Interim, On Leave, suspended, de-certifed, resigned and deceased. I would
like to add colour to some of the choices, so the items are noticed more
easily (ie the colour red is the background to the choice Suspended, the
colour green is the background to the choice Active).
Can this be done within the same drop down list box?
Billiam

 

[strive4peace]

Hi Billiam,

"I applied this to all of the text boxes on my form, so the user would
know where they were"

I much prefer this method:

set the BackColor property to something like a pale yellow -- then set
the Back Style to be transparent. When they are IN the field, the back
color property will show. When they are not, the section back color
will show

"is there a way to bypass the limit of conditions programmatically
as I need 7 conditions "

no


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Crystal I am sorry, I just entered conditional formatting and came up with a
ton of entries, I thought I had searched it and nothing came up....

I tried using Field Value is equal to "Active" works.
Then I read a post that suggested applying conditional formats to all your
text boxes on a form by using Field Has Focus

I applied this to all of the text boxes on my form, so the user would know
where they were, and now the form causes Access 2007 to crash as there is a
"stack problem". Fortunately, I have a good backup!!!!!!

Is that much formatting too much??? (About 40 text boxes).

Finally, is there a way to bypass the limit of conditions programmatically
as I need 7 conditions and Access 2007 only has 4 (default+three
conditions)...
Billiam

Hi Billiam (thanks for explaining that <smile>)

you're welcome

please let us know if it works out

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

:

Hi Billiam (is that your name?)
It's a nickname ...Bill+William=Billiam

I would just like to thank you sincerely for taking the time to help....I
have learned so much reading through these forums, and I find your resonses
extremely illuminating! So please except my sincerest thanks for taking the
time to help all of us newbies out--YOU ARE AWESOME!!!

I am going to give this a try on Monday so will let you know how I do!
Have a great weekend!

Billiam


in the design view of the form (you can do it in Form view but I feel it
is best to make design changes in design view):

click on the control

from the menu, choose Format, Conditional Formatting

for instance:
Expression Is --> [Active] = True
--> set Foreground Color, background color, Bold, Italic, Underline,
and/or enabled

~~~
for information on making a subform, read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal

*
have an awesome day
*




Billiam wrote:
I am so sorry, Crystal, I did not receive notification of your reply...

I am afraid I am fairly new to all this, so bear with me
please...........................okay pretty please???

So if I make a seperate lookup/reference table, (Active, Suspended etc) base
a form on it and drop it on my main form that would work? As to the
conditional formatting, I do not know how to proceed--here is my best guess
(you can LOL if you like!:

I would add a conditional expression to the Validation Rule property of the
table
=IIf([Active] = "Yes", "Active (somehow add a hex value???ie green" "Active
somehow greyed out"
and somehow nest the other Status' in more IIf statements?????
:

I really do not know what to do, Crystal, any advice as to an examle posted
anywhere???

How about using a subform with conditional formatting?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Billiam wrote:
I have a bound drop down list box which displays 7 set choices: Active,
Interim, On Leave, suspended, de-certifed, resigned and deceased. I would
like to add colour to some of the choices, so the items are noticed more
easily (ie the colour red is the background to the choice Suspended, the
colour green is the background to the choice Active).
Can this be done within the same drop down list box?
Billiam

 

[strive4peace]

Hi Billiam,

"1. Are there only three conditions allowed per combo box? "

actually, there are 4 -- you have the normal condition too

" 2. If so, can you combine more than one condition..."

yes, choose --> Expression Is

and then the condition would be something like this:

[fieldname1] = "value1" AND [fieldname2] = "value2"

"3. If not, how do I add more conditions for the combobox?"

you cannot have more than 3 conditions and the normal formatting

"4. The conditions which are not working are:"

What is the data type for [Active] and [Suspended] ? If those
conditions aren't working, then they are probably text, not yes/no (as
they should be)

"I suspect the problem is I have included the Items list in the
rowsource of the combobox property sheet.
Do they need to be in a lookup table instead? "

They must be in the form RecordSource


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

If I right click while I am on the combo box I get the conditional
Formatting option. There are 3 conditions available and one default condition.

The default condition (if no other conditions are met) is working. The three
conditions I set up, are not.

1. Are there only three conditions allowed per combo box?

2. If so, can you combine more than one condition in a statement (for
example Suspended and de-certified have a red background within the same
condition expression?

3. If not, how do I add more conditions for the combobox?

4. The conditions which are not working are:

Condition 1
Expression Is [Active] = True
and I set the foreground & background colours, Bold and enabled.

Condition 2
Expression Is [Suspended] = True
and I set the background & foreground, Bold and enabled

I suspect the problem is I have included the Items list in the rowsource of
the combobox property sheet. Do they need to be in a lookup table instead?
There are 7 individual Statuses which *should* never change...

Thanks for any help!
Billiam

Hi Billiam (thanks for explaining that <smile>)

you're welcome

please let us know if it works out

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

:

Hi Billiam (is that your name?)
It's a nickname ...Bill+William=Billiam

I would just like to thank you sincerely for taking the time to help....I
have learned so much reading through these forums, and I find your resonses
extremely illuminating! So please except my sincerest thanks for taking the
time to help all of us newbies out--YOU ARE AWESOME!!!

I am going to give this a try on Monday so will let you know how I do!
Have a great weekend!

Billiam


in the design view of the form (you can do it in Form view but I feel it
is best to make design changes in design view):

click on the control

from the menu, choose Format, Conditional Formatting

for instance:
Expression Is --> [Active] = True
--> set Foreground Color, background color, Bold, Italic, Underline,
and/or enabled

~~~
for information on making a subform, read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal

*
have an awesome day
*




Billiam wrote:
I am so sorry, Crystal, I did not receive notification of your reply...

I am afraid I am fairly new to all this, so bear with me
please...........................okay pretty please???

So if I make a seperate lookup/reference table, (Active, Suspended etc) base
a form on it and drop it on my main form that would work? As to the
conditional formatting, I do not know how to proceed--here is my best guess
(you can LOL if you like!:

I would add a conditional expression to the Validation Rule property of the
table
=IIf([Active] = "Yes", "Active (somehow add a hex value???ie green" "Active
somehow greyed out"
and somehow nest the other Status' in more IIf statements?????
:

I really do not know what to do, Crystal, any advice as to an examle posted
anywhere???

How about using a subform with conditional formatting?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Billiam wrote:
I have a bound drop down list box which displays 7 set choices: Active,
Interim, On Leave, suspended, de-certifed, resigned and deceased. I would
like to add colour to some of the choices, so the items are noticed more
easily (ie the colour red is the background to the choice Suspended, the
colour green is the background to the choice Active).
Can this be done within the same drop down list box?
Billiam

 

[Billiam]

Hi Crystal,

What is the data type for [Active] and [Suspended] ? If those
conditions aren't working, then they are probably text, not yes/no (as
they should be)

I have a Field called "Status", text datatype, which is where the
Instructor's status is recorded from the bound combobox which supplies the
options. I decided against using a lookup table as the options have never
changed, and an Instructor can only have one status at a time. Do you still
think that I need to use Yes/No, and if so, would I have a field then for
each option in a lookup table???

Have a great day, and of course many thanks for your help!
Billiam

 

[strive4peace]

Hi Billiam,

no, Status is dynamic. If you have no need to know when particular
status happened, then keeping it in the table is ok

"I decided against using a lookup table as the options have never changed"

I do not understand...

generally Status is one of set choices -- it makes sense to have a teble
to supply them -- and use StatusID, a numeric foreign key, in your table
instead of storing the actual text value

I asked questions about [Active] and [Suspended] -- but you answered
with a question about [Status] -- are your issue resolved?


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

What is the data type for [Active] and [Suspended] ? If those
conditions aren't working, then they are probably text, not yes/no (as
they should be)

I have a Field called "Status", text datatype, which is where the
Instructor's status is recorded from the bound combobox which supplies the
options. I decided against using a lookup table as the options have never
changed, and an Instructor can only have one status at a time. Do you still
think that I need to use Yes/No, and if so, would I have a field then for
each option in a lookup table???

Have a great day, and of course many thanks for your help!
Billiam

 

[Billiam]

Hi Billiam,

no, Status is dynamic. If you have no need to know when particular
status happened, then keeping it in the table is ok

Yes, I agree Status is dynamic,however, I do not need to know when their
Status changed, and so I keep the current status in a field in the table.

"I decided against using a lookup table as the options have never changed"

I do not understand...

What I mean is that the list of Status choices is very small and they have
never changed over the organzation's history of 20+years. I understand that
that does not mean they will never change ;-)
As I have several other lookup tables, I decided not to go with a seperate
Lookup Table of Statuses to keep the structure more simplistic. If you think
the payoff is worth it, I believe it would not be too hard to move them to a
lookup table...But I still am unclear where the yes/no would come in? I
believe I would simply have a table called Status, StatusID, Status, and
under the Status Field, I would list the 7 status types. As far as the form
is concerned, I would use a combobox to choose the current Status from, and
store the Status ID in my table...am I doing this right?

I asked questions about [Active] and [Suspended] -- but you answered
with a question about [Status] -- are your issue resolved?

When you ask a question with square brackets around an item [Active] and
[Suspended] I think you are asking about fields or tables...and this is
confusing me. I only have 1 field called Status under which the status choice
is listed (Ie Active, Suspended) and the status choice is selected from
combobox. The datatype of the Status field is Text, and so all of the choices
are also text (Active, Suspended etc)
So I thought when I told you that the Field Status was datatype=Text, I
thought that would make it clear that the Status choices listed under that
field would also be text. I hope I am explaining my reasoning properly, but
it seems I must be missing something?

Best regards,
Billiam

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

What is the data type for [Active] and [Suspended] ? If those
conditions aren't working, then they are probably text, not yes/no (as
they should be)

I have a Field called "Status", text datatype, which is where the
Instructor's status is recorded from the bound combobox which supplies the
options. I decided against using a lookup table as the options have never
changed, and an Instructor can only have one status at a time. Do you still
think that I need to use Yes/No, and if so, would I have a field then for
each option in a lookup table???

Have a great day, and of course many thanks for your help!
Billiam

 

[strive4peace]

Hi Billiam,

sorry for the delay in responding, we had some issues...

Personally, I would create:

Statuses
- StatusID, autonumber
- Status, text, 15 (ie: Active, Suspended)

It appears that the statuses you listed relate to people that are
available for work ... so you might have:

EmpStatus
- EmpStatID, autonumber
- EmpID, long -- FK to Employees
- StatusID, long -- FK to Statuses
- StatDate, date -- date that Status is effective

If, for instance, StatusID = 1 --> Active:
you would use a combobox similar to the following to choose from
employees who are currently active:

RowSource:
SELECT Employees.EmpID, [EmpLast] & ", " & [EmpFirst] AS Employee
FROM Employees INNER JOIN EmpStatus ON Employees.EmpID = EmpStatus.EmpID
WHERE ((EmpStatus.StatusID=1) AND
(EmpStatus.StatDate=DMax("StatDate","EmpStatus","EmpID=" &
[employees].[EmpID])));



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Billiam,

no, Status is dynamic. If you have no need to know when particular
status happened, then keeping it in the table is ok

Yes, I agree Status is dynamic,however, I do not need to know when their
Status changed, and so I keep the current status in a field in the table.

"I decided against using a lookup table as the options have never changed"

I do not understand...

What I mean is that the list of Status choices is very small and they have
never changed over the organzation's history of 20+years. I understand that
that does not mean they will never change ;-)
As I have several other lookup tables, I decided not to go with a seperate
Lookup Table of Statuses to keep the structure more simplistic. If you think
the payoff is worth it, I believe it would not be too hard to move them to a
lookup table...But I still am unclear where the yes/no would come in? I
believe I would simply have a table called Status, StatusID, Status, and
under the Status Field, I would list the 7 status types. As far as the form
is concerned, I would use a combobox to choose the current Status from, and
store the Status ID in my table...am I doing this right?

I asked questions about [Active] and [Suspended] -- but you answered
with a question about [Status] -- are your issue resolved?

When you ask a question with square brackets around an item [Active] and
[Suspended] I think you are asking about fields or tables...and this is
confusing me. I only have 1 field called Status under which the status choice
is listed (Ie Active, Suspended) and the status choice is selected from
combobox. The datatype of the Status field is Text, and so all of the choices
are also text (Active, Suspended etc)
So I thought when I told you that the Field Status was datatype=Text, I
thought that would make it clear that the Status choices listed under that
field would also be text. I hope I am explaining my reasoning properly, but
it seems I must be missing something?

Best regards,
Billiam

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

:

What is the data type for [Active] and [Suspended] ? If those
conditions aren't working, then they are probably text, not yes/no (as
they should be)
I have a Field called "Status", text datatype, which is where the
Instructor's status is recorded from the bound combobox which supplies the
options. I decided against using a lookup table as the options have never
changed, and an Instructor can only have one status at a time. Do you still
think that I need to use Yes/No, and if so, would I have a field then for
each option in a lookup table???

Have a great day, and of course many thanks for your help!
Billiam