Building Expressions in a Form

I have created a fill-in form for new exemption certificates in our master
listing table. One of the columns is called "Issued State" with a list box
of all 50 states to choose from. I also have a column for each state
following the "Issued State" column. I would like to have an "x" appear in
the appropriate state column when the state is chosen in the "Issued State"
column. In other words, if I choose CA (California) in the "Issued State"
column, I would like an "x" to appear in the column "CA". Is there a way to
do this?

Why? As in "why do you want to have both a highlighted listbox and a
state-specific checkbox?" Won't that require a lot of screen to hold all
those states?

So, if the "Issued State" is the critical piece of information, why are you
using a listbox with all 50 states showing? A combobox control would still
allow for the selection of a single state, but would only display the chosen
state. This would take less screen space than a listbox, and a LOT less
screen space than a listbox plus 50 checkboxes.

If you can describe further why you are considering the approach you
mentioned, the newsgroup's readers may be able to offer other
alternatives...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"jartax" wrote in message
...
I have created a fill-in form for new exemption certificates in our master
listing table. One of the columns is called "Issued State" with a list
box
of all 50 states to choose from. I also have a column for each state
following the "Issued State" column. I would like to have an "x" appear
in
the appropriate state column when the state is chosen in the "Issued
State"
column. In other words, if I choose CA (California) in the "Issued State"
column, I would like an "x" to appear in the column "CA". Is there a way
to
do this?

That's a good question. What we are doing is entering new documentation in
this Access database to do queries for tax audit, etc. purposes. But also we
are saving the database to an Excel spreadsheet for the Billing Dept to check
on the exemption status of customers. The reason for all fifty states, one
in each column, is that some customers are exempt in more than one state and
that gives us the ability to "check" which states in which they are exempt.

If there is not an easy way to do it, it's no big deal to put an "x" in the
boxes myself. I just thought if there was it would save me a step. Thanks
for your response.
--
jr


"Jeff Boyce" wrote:

Why? As in "why do you want to have both a highlighted listbox and a
state-specific checkbox?" Won't that require a lot of screen to hold all
those states?

So, if the "Issued State" is the critical piece of information, why are you
using a listbox with all 50 states showing? A combobox control would still
allow for the selection of a single state, but would only display the chosen
state. This would take less screen space than a listbox, and a LOT less
screen space than a listbox plus 50 checkboxes.

If you can describe further why you are considering the approach you
mentioned, the newsgroup's readers may be able to offer other
alternatives...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"jartax" wrote in message
...
I have created a fill-in form for new exemption certificates in our master
listing table. One of the columns is called "Issued State" with a list
box
of all 50 states to choose from. I also have a column for each state
following the "Issued State" column. I would like to have an "x" appear
in
the appropriate state column when the state is chosen in the "Issued
State"
column. In other words, if I choose CA (California) in the "Issued State"
column, I would like an "x" to appear in the column "CA". Is there a way
to
do this?

It sounds like you are (now) describing a "many-to-many" relationship ("...
some customers are exempt in more than one state..."). While you would be
limited in how you could structure your data for a spreadsheet, if you use
Access (or another relational database), you can create a
"junction/relation/resolver" table that lists:

Exemption
ExemptionID (optional primary key -- could use the two following
foreign keys, combined, instead)
CustomerID
StateID
(...?other info the state's exemption -- e.g., ExemptionDate,
ExpirationDate, ...)

This gives you a way to store the fact that one customer could have zero,
one or many state exemptions (and that one state could have zero, one or
many customers with exemptions).

As for a way to handle this in a form (much preferred in Access over working
directly in the tables), you could use a combo box to list the states, and a
listbox to display the "exemptions". Put these on a form that let's you
select/edit a customer's record.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"JRoeter" wrote in message
...
That's a good question. What we are doing is entering new documentation
in
this Access database to do queries for tax audit, etc. purposes. But also
we
are saving the database to an Excel spreadsheet for the Billing Dept to
check
on the exemption status of customers. The reason for all fifty states,
one
in each column, is that some customers are exempt in more than one state
and
that gives us the ability to "check" which states in which they are
exempt.

If there is not an easy way to do it, it's no big deal to put an "x" in
the
boxes myself. I just thought if there was it would save me a step.
Thanks
for your response.
--
jr


"Jeff Boyce" wrote:

Why? As in "why do you want to have both a highlighted listbox and a
state-specific checkbox?" Won't that require a lot of screen to hold all
those states?

So, if the "Issued State" is the critical piece of information, why are
you
using a listbox with all 50 states showing? A combobox control would
still
allow for the selection of a single state, but would only display the
chosen
state. This would take less screen space than a listbox, and a LOT less
screen space than a listbox plus 50 checkboxes.

If you can describe further why you are considering the approach you
mentioned, the newsgroup's readers may be able to offer other
alternatives...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"jartax" wrote in message
...
I have created a fill-in form for new exemption certificates in our
master
listing table. One of the columns is called "Issued State" with a list
box
of all 50 states to choose from. I also have a column for each state
following the "Issued State" column. I would like to have an "x"
appear
in
the appropriate state column when the state is chosen in the "Issued
State"
column. In other words, if I choose CA (California) in the "Issued
State"
column, I would like an "x" to appear in the column "CA". Is there a
way
to
do this?