Fill in a field based on input of data in another field

[Tammy]

Hi,

I'd like to know if this can be done without VBA, since I'm not familiar
with programming....Access 2007

My user has a table that contains countries: [tblAwardCountries]. When she
enters a value in her [PermanentCountry] field, she would like the database
to check to see if that country exists in [tblAwardCountries]. If it does
exist, she would like another field to automatically update to "Eligible for
Award".

Can this be done without programming? I thought of the IN operator, but
there are too many countries to have to type into the expression. Using a
table will be easier to keep updated.

Thanks for any suggestions!

 

[Jeff Boyce]

Tammy

I may be reading too much into your description ...

It sounds like your user is required to type in a [PermanentCountry] value
.... why?

If the database already possesses a tblAwardCountries, why not use a
combobox to list those and let the user select the correct one? And if it
is a new, never-been-entered Country, Access HELP has plenty of information
on using the LimitToList property and the NotInList event to handle adding a
new one.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Tammy]

Hi Jeff -

Thank you very much for your response. My user can use a lookup list to
enter the the [PermanentCountry] (got that one covered - thanks for
suggesting, though), but not all of the countries qualify for the award. She
has a list of countries that *do* qualify for the award - she would like
enter a value in the [PermanentCountry] field, and then check to see if that
value exists in the [tblAwardCountries] table. If it does exist in the award
table, she'd like a notification in another field that the country she
entered qualifies for the award.

We are just starting to work through this, so any ideas are helpful. All of
her records will have a permanent country, but not all countries qualify for
the award. The list is way too long for her to remember all of the countries
that qualify. We were hoping to find a way for Access to look at the
[PermanentCountry] field entry, then check to see if there is a match in the
[tblAwardCountries] table - if so, update another field based on whether the
country exists in the table. The field that is updated can be a text, memo,
or yes/no data type (could be something like [EligibleForAward]).

Again, haven't put this together yet, trying to figure out a way to do this
through Access, and not VBA. I can copy and paste code, but if something goes
wrong I woudn't know how to fix the code.

Thanks, again, Jeff. I hope that makes it a little clearer (?).

Tammy

I may be reading too much into your description ...

It sounds like your user is required to type in a [PermanentCountry] value
.... why?

If the database already possesses a tblAwardCountries, why not use a
combobox to list those and let the user select the correct one? And if it
is a new, never-been-entered Country, Access HELP has plenty of information
on using the LimitToList property and the NotInList event to handle adding a
new one.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi,

I'd like to know if this can be done without VBA, since I'm not familiar
with programming....Access 2007

My user has a table that contains countries: [tblAwardCountries]. When she
enters a value in her [PermanentCountry] field, she would like the
database
to check to see if that country exists in [tblAwardCountries]. If it does
exist, she would like another field to automatically update to "Eligible
for
Award".

Can this be done without programming? I thought of the IN operator, but
there are too many countries to have to type into the expression. Using a
table will be easier to keep updated.

Thanks for any suggestions!

.

 

[Jeff Boyce]

Are you saying that there are two separate lists? One for "permanent", one
for "awarded"?

If so, why?!

A single list (table) to hold Countries, with attributes (i.e.fields) for
"permanent" and "awarded" (or are those mutually exclusive categories?)...

Now, if there are MORE than just those two y/n categories, you may wish to
consider a list (table) of countries, then a related junction table that
holds valid pairs of country X attribute. For example, Country 1 might have
neither "permanent" nor "awarded", so it wouldn't be in that junction table.
Country 2 might have both, so would have TWO records in the junction table,
one for each.

All this depends on your underlying domain and table structure, about which
I can only guess so far.

More specific description may lead to more specific suggestion...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi Jeff -

Thank you very much for your response. My user can use a lookup list to
enter the the [PermanentCountry] (got that one covered - thanks for
suggesting, though), but not all of the countries qualify for the award.
She
has a list of countries that *do* qualify for the award - she would like
enter a value in the [PermanentCountry] field, and then check to see if
that
value exists in the [tblAwardCountries] table. If it does exist in the
award
table, she'd like a notification in another field that the country she
entered qualifies for the award.

We are just starting to work through this, so any ideas are helpful. All
of
her records will have a permanent country, but not all countries qualify
for
the award. The list is way too long for her to remember all of the
countries
that qualify. We were hoping to find a way for Access to look at the
[PermanentCountry] field entry, then check to see if there is a match in
the
[tblAwardCountries] table - if so, update another field based on whether
the
country exists in the table. The field that is updated can be a text,
memo,
or yes/no data type (could be something like [EligibleForAward]).

Again, haven't put this together yet, trying to figure out a way to do
this
through Access, and not VBA. I can copy and paste code, but if something
goes
wrong I woudn't know how to fix the code.

Thanks, again, Jeff. I hope that makes it a little clearer (?).

Tammy

I may be reading too much into your description ...

It sounds like your user is required to type in a [PermanentCountry]
value
.... why?

If the database already possesses a tblAwardCountries, why not use a
combobox to list those and let the user select the correct one? And if
it
is a new, never-been-entered Country, Access HELP has plenty of
information
on using the LimitToList property and the NotInList event to handle
adding a
new one.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi,

I'd like to know if this can be done without VBA, since I'm not
familiar
with programming....Access 2007

My user has a table that contains countries: [tblAwardCountries]. When
she
enters a value in her [PermanentCountry] field, she would like the
database
to check to see if that country exists in [tblAwardCountries]. If it
does
exist, she would like another field to automatically update to
"Eligible
for
Award".

Can this be done without programming? I thought of the IN operator, but
there are too many countries to have to type into the expression. Using
a
table will be easier to keep updated.

Thanks for any suggestions!

.

 

[Tammy]

Hi Jeff,

This is why I love this discussion group - I needed ideas, and you just gave
me a good one! I was thinking too deeply about this - I can see us using one
table for all countries, with an extra field that distinguishes whether the
country is eligible for the award or not (y/n would be perfect). Gosh, I feel
dumb for not coming up with that one on my own!

Thanks for putting up with my scramble thoughts! Your patience is greatly
appreciated!

Are you saying that there are two separate lists? One for "permanent", one
for "awarded"?

If so, why?!

A single list (table) to hold Countries, with attributes (i.e.fields) for
"permanent" and "awarded" (or are those mutually exclusive categories?)...

Now, if there are MORE than just those two y/n categories, you may wish to
consider a list (table) of countries, then a related junction table that
holds valid pairs of country X attribute. For example, Country 1 might have
neither "permanent" nor "awarded", so it wouldn't be in that junction table.
Country 2 might have both, so would have TWO records in the junction table,
one for each.

All this depends on your underlying domain and table structure, about which
I can only guess so far.

More specific description may lead to more specific suggestion...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi Jeff -

Thank you very much for your response. My user can use a lookup list to
enter the the [PermanentCountry] (got that one covered - thanks for
suggesting, though), but not all of the countries qualify for the award.
She
has a list of countries that *do* qualify for the award - she would like
enter a value in the [PermanentCountry] field, and then check to see if
that
value exists in the [tblAwardCountries] table. If it does exist in the
award
table, she'd like a notification in another field that the country she
entered qualifies for the award.

We are just starting to work through this, so any ideas are helpful. All
of
her records will have a permanent country, but not all countries qualify
for
the award. The list is way too long for her to remember all of the
countries
that qualify. We were hoping to find a way for Access to look at the
[PermanentCountry] field entry, then check to see if there is a match in
the
[tblAwardCountries] table - if so, update another field based on whether
the
country exists in the table. The field that is updated can be a text,
memo,
or yes/no data type (could be something like [EligibleForAward]).

Again, haven't put this together yet, trying to figure out a way to do
this
through Access, and not VBA. I can copy and paste code, but if something
goes
wrong I woudn't know how to fix the code.

Thanks, again, Jeff. I hope that makes it a little clearer (?).

Tammy

I may be reading too much into your description ...

It sounds like your user is required to type in a [PermanentCountry]
value
.... why?

If the database already possesses a tblAwardCountries, why not use a
combobox to list those and let the user select the correct one? And if
it
is a new, never-been-entered Country, Access HELP has plenty of
information
on using the LimitToList property and the NotInList event to handle
adding a
new one.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


Hi,

I'd like to know if this can be done without VBA, since I'm not
familiar
with programming....Access 2007

My user has a table that contains countries: [tblAwardCountries]. When
she
enters a value in her [PermanentCountry] field, she would like the
database
to check to see if that country exists in [tblAwardCountries]. If it
does
exist, she would like another field to automatically update to
"Eligible
for
Award".

Can this be done without programming? I thought of the IN operator, but
there are too many countries to have to type into the expression. Using
a
table will be easier to keep updated.

Thanks for any suggestions!


.

.

 

[Jeff Boyce]

Forest & trees, Tammy, forest & trees.

You'll spot something I'm not seeing sometime.

Thanks, and keep coming back.

Regards

Jeff B.

Hi Jeff,

This is why I love this discussion group - I needed ideas, and you just
gave
me a good one! I was thinking too deeply about this - I can see us using
one
table for all countries, with an extra field that distinguishes whether
the
country is eligible for the award or not (y/n would be perfect). Gosh, I
feel
dumb for not coming up with that one on my own!

Thanks for putting up with my scramble thoughts! Your patience is greatly
appreciated!

Are you saying that there are two separate lists? One for "permanent",
one
for "awarded"?

If so, why?!

A single list (table) to hold Countries, with attributes (i.e.fields) for
"permanent" and "awarded" (or are those mutually exclusive
categories?)...

Now, if there are MORE than just those two y/n categories, you may wish
to
consider a list (table) of countries, then a related junction table that
holds valid pairs of country X attribute. For example, Country 1 might
have
neither "permanent" nor "awarded", so it wouldn't be in that junction
table.
Country 2 might have both, so would have TWO records in the junction
table,
one for each.

All this depends on your underlying domain and table structure, about
which
I can only guess so far.

More specific description may lead to more specific suggestion...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi Jeff -

Thank you very much for your response. My user can use a lookup list to
enter the the [PermanentCountry] (got that one covered - thanks for
suggesting, though), but not all of the countries qualify for the
award.
She
has a list of countries that *do* qualify for the award - she would
like
enter a value in the [PermanentCountry] field, and then check to see if
that
value exists in the [tblAwardCountries] table. If it does exist in the
award
table, she'd like a notification in another field that the country she
entered qualifies for the award.

We are just starting to work through this, so any ideas are helpful.
All
of
her records will have a permanent country, but not all countries
qualify
for
the award. The list is way too long for her to remember all of the
countries
that qualify. We were hoping to find a way for Access to look at the
[PermanentCountry] field entry, then check to see if there is a match
in
the
[tblAwardCountries] table - if so, update another field based on
whether
the
country exists in the table. The field that is updated can be a text,
memo,
or yes/no data type (could be something like [EligibleForAward]).

Again, haven't put this together yet, trying to figure out a way to do
this
through Access, and not VBA. I can copy and paste code, but if
something
goes
wrong I woudn't know how to fix the code.

Thanks, again, Jeff. I hope that makes it a little clearer (?).

:

Tammy

I may be reading too much into your description ...

It sounds like your user is required to type in a [PermanentCountry]
value
.... why?

If the database already possesses a tblAwardCountries, why not use a
combobox to list those and let the user select the correct one? And
if
it
is a new, never-been-entered Country, Access HELP has plenty of
information
on using the LimitToList property and the NotInList event to handle
adding a
new one.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services
mentioned
in this post. Mention and/or description of a product or service
herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with
no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


Hi,

I'd like to know if this can be done without VBA, since I'm not
familiar
with programming....Access 2007

My user has a table that contains countries: [tblAwardCountries].
When
she
enters a value in her [PermanentCountry] field, she would like the
database
to check to see if that country exists in [tblAwardCountries]. If it
does
exist, she would like another field to automatically update to
"Eligible
for
Award".

Can this be done without programming? I thought of the IN operator,
but
there are too many countries to have to type into the expression.
Using
a
table will be easier to keep updated.

Thanks for any suggestions!


.

.