Auto populating in a table

[Chantel33]

In my form, I would like the table field to populate based on the selection
from a combobox in another field. The two colums are Area and Task. On my
form I would like to choose my task and the area populates in my table. I
presently have a task box [Area]based on the the combo box [Task], so when
you choose the task the area auto-populates based on a query. The problem i
am having is the Area ID shows up in the Area field on my table instead of
the actually area. I need the area details to show for use in reports. I am
sure it is simply but i am new to access and can't seem to figure out what i
am doing wrong, am i storing the wrong selection?

The reason I auto-populated in the form, is for user convenience, but I
don't want necessarily need the area to show in the form, but i do need it to
show in the table and it has to correspond to the task choosen. Help!!

 

[Chantel33]

I'm sorry i am new to this, so you are stating if i only really need the area
to show on the reports then i can us a computed control to display the value?

however if i do need the area details to show in the table then i should
keep my form as is and use the AfterUpdate event procedure with:

Me.Area = Me.cboTask.Column(1)

is this correct?

Unless you need to edit the Area value in the task box control you
should not include a column in the form's underlying table for this
value. That introduces redundancy ands the possibility of
inconsistent data. You can always return the value corresponding to
the selected Area_ID in a computed control in a form or report (as you
are doing) or in a column in q query which joins the form's underlying
table to the New Task by Area table on the Area_ID columns.

There are situations when its legitimate, and indeed necessary, to
store a derived value in a column in a table. An example is a
UnitPrice column in an InvoiceDetails table where the UnitPrice value
is derived form a value in a Products table. The latter will change
over time, but you need the value in the InvoiceDetails table to
remain static at the value when the invoice was created. In a case
like that, instead of using a computed control in a form, you bind the
control to the underlying column and assign a value to it in the combo
box's AfterUpdate event procedure. So, if your situation is analogous
to this, you'd bind a text box to an Area column in the form's
underlying table and assign a value to it in the cbotask control's
AfterUpdate event procedure with:

Me.Area = Me.cboTask.Column(1)

Ken Sheridan
Stafford, England

task box:
=[cbotask].[column](1)

combo box:

Name: cbotask
Control source: Task
Row source: SELECT [Area_ID], [Area], [Task]
FROM [New Task by Area]
ORDER BY [Task]
Column Widths 0,0,8

this is working fine for what i want the form to do but i assume the
=[cbotask].[column](1) is the problem when it populates in the table

In my form, I would like the table field to populate based on the selection
from a combobox in another field. The two colums are Area and Task. On my
form I would like to choose my task and the area populates in my table. I
presently have a task box [Area]based on the the combo box [Task], so when
you choose the task the area auto-populates based on a query. The problem i
am having is the Area ID shows up in the Area field on my table instead of
the actual area description. I need the area details to show for use in reports. I
am sure it is simple but i am new to access and can't seem to figure out what i
am doing wrong, am i storing the wrong selection?

The reason I auto-populated in the form, is for user convenience, but I
don't necessarily need the area to show in the form, but i do need it to
show in the table and it has to correspond to the task choosen. Help!!

 

[ken]

It does sound to me as if you are introducing redundancy. This is not
merely inefficient, it is risky as it undermines the integrity of the
database and leaves it open to inconsistent data. Essentially each
'fact' must be stored once and once only in a database, e.g. in a
table of addresses my address includes the town of Stafford which is
in the county of Staffordshire, which is in the UK. To have columns
for town, county and country in the addresses table would be wrong;
this is what's meant by redundancy. All that's necessary is to have a
column for townID (not the name as these can be duplicated). This is
because the fact that Stafford is in Staffordshire is recorded in the
Towns table and that Staffordshire is in the UK is recorded in the
Counties table. There will also be a Countries table at the top of
this hierarchy as this allows 'referential integrity' to be enforced,
preventing an invalid country being entered in the Counties table.

So the question arises, is the 'fact' that task A is always, without
exception, associated with area B recorded somewhere in a 'referenced'
table? If so then in a 'referencing' table which records tasks you
only need a column for task, not for area. Once the task is known the
area is known; in the jargon its said that area is 'functionally
dependent' on task.

If on the other hand task A could also be associated with area C or
area D, then area is not functionally dependent on task, so its
legitimate to have both task and area columns in a referencing table.
This in turn brings us to your other point, that the Area_ID value is
being stored in the 'referencing' table, not the area name. This is
how it should be. Knowing the Area_ID we automatically know the area
name, again because the latter is 'functionally dependent' on the
former. It doesn't matter that you need to show the area name in
reports, because all you have to do is base the reports on a query
which joins the tables on Area_ID and include the area name in the
columns returned by the query. You can then bind a control to the
area name column in the report. Joining tables in queries like this
to return whatever columns you need from different tables is how you
can show the data you require without introducing any redundancy into
the database.

Ken Sheridan
Stafford, England

In my form, I would like the table field to populate based on the selection
from a combobox in another field. The two colums are Area and Task. On my
form I would like to choose my task and the area populates in my table. I
presently have a task box [Area]based on the the combo box [Task], so when
you choose the task the area auto-populates based on a query. The problem i
am having is the Area ID shows up in the Area field on my table instead of
the actually area. I need the area details to show for use in reports. I am
sure it is simply but i am new to access and can't seem to figure out what i
am doing wrong, am i storing the wrong selection?

The reason I auto-populated in the form, is for user convenience, but I
don't want necessarily need the area to show in the form, but i do need it to
show in the table and it has to correspond to the task choosen. Help!!