Auto Complete Form

[mandy84angel]

I have a form which stores employee data. On the form is an area that states
the region, location, and a yes/no. I have a separate table that lists these.
Is there a way that on the form, if I select the location, it will
automatically complete the region and yes/no for me?

 

[Jeff Boyce]

It may only be a matter of semantics, ...

Access tables store data, Access forms display data. Your form is not
storing data.

What is the relationship between the tables? That is, what does "region,
location, yes/no" have to do with "employee data". How these are related
will affect how you'll get this done.

By the way, you really (REALLY) don't want to be (re-)storing the same
'region, location, yes/no' data for each employee. That might be how you'd
do it if you were limited to using a spreadsheet, but Access is a relational
database. You won't get good use of Access' relationally-oriented
features/functions if you try to feed it 'sheet data.

That said, there is a way to display (not store, display) the 'region,
yes/no' information for a selected 'location'.

But "how" depends on "what". What data structure (i.e. tables and fields)
are you using now? And what are you doing to "select a location"?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JANE BOOTH]

I have a form which stores employee data. On the form is an area that
states
the region, location, and a yes/no. I have a separate table that lists
these.
Is there a way that on the form, if I select the location, it will
automatically complete the region and yes/no for me?

 

[Proposal Doctor]

One thing that you can do Mandy, assuming you are running Access 2007, is to
use the multivalue data type for Location and Region.

For your lookup table, define it with Location and Region if your business
rules allow some staff members to be associated with more than one Location
(Miami, Orlando, Denver) and Region (e.g., Southeast, Gulf Coast, Rockies).

Your main Employee table will then use the Location as a field. Set the
Location to a multivalue field if your business rules require this.

You will need a query to display the Region unless the Location field is
selected by the user.

Now the dropdown on your form will expose the Locations and Regions. But
you can hide the Region field by setting its field size to "0" in the
properties dialog.

You'll eventually need a form to edit the lookup table.

Sorry, but I don't understand the purpose of the Yes/No field.

HTH.

David