How to show data in form based on a selected combo box value?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I was trying to populate the fields in the other text boxes(only a few of
the fields,not all of them) in the form based on the value selected in the
combo box.
But I have no idea how to proceed as this is the first time I am working on
MS-Access.
Could any one kindly guide me from this point.I would greatly appreciate
your help.
Thank you,
Veeru.
 
Hi,
I was trying to populate the fields in the other text boxes(only a few of
the fields,not all of them) in the form based on the value selected in the
combo box.
Click to expand...

You probably do NOT want to do this.

If you are trying to copy data from fields in the combo box's
RowSource table into fields in the form's table, to store those fields
redundantly in a second table...

Don't.

Not only does this waste space, it runs the major risk of update
anomalies: the field you have copied might be edited in its own table,
and you will now have INCORRECT copies of that information stored
elsewhere.

If you just want to *display* the fields on the Form, put a textbox on
the form with a Control Source such as

=comboboxname.Column(n)

using the Name property of the combo box; (n) is the zero based
subscript of the field in the Combo's row source query.

If you can explain why you want to store the data into another table,
please post back - it can be done, it just usually *shouldn't* be
done.

John W. Vinson[MVP]
 
Hi,
I really appreciate you for responding to the question.
But speaking frankly,I did not get your point exactly. I did not want to
store the data into another table.I just wanted to retrive the data from a
single table,based on the value selected in the combo box.
For example,if I select the 'department code' from the combo box, then the
department name and dept. head should be filled in automatically from a
atable called 'Departments'.
I hope the question is much clear now.
Thank you,
Veeru.
 
Hi,
I really appreciate you for responding to the question.
But speaking frankly,I did not get your point exactly. I did not want to
store the data into another table.I just wanted to retrive the data from a
single table,based on the value selected in the combo box.
For example,if I select the 'department code' from the combo box, then the
department name and dept. head should be filled in automatically from a
atable called 'Departments'.
I hope the question is much clear now.
Click to expand...

Where do you want the department name "filled in"?

If you want it DISPLAYED for human reading on your Form, my suggestion
will work: use

=[Department Code].Column(1)

if the department name is the second column in the combo box named
Department Code.

If you want it STORED in the table upon which this form is based, my
objection stands. The department name should exist in the Department
table; and unless you have some very clear reason to store it
redundantly, it should simply *not exist* in any other table (such as
the table upon which this form is based).

John W. Vinson[MVP]
 
John,
I posted a similar request to this (before finding this thread).
In my situation, I want to duplicate the data in a second table. The
rationale is that the first table provides the 'default' value, but the user
is able to override this. In either case the value needs to be stored in the
second table to capture the actual instance
For example:
products have a base value, but individual sales may use or alter the value
in the final sale. The alterations may be random, so there is no way to
capture it directly without wasting lots of added table entries.

In this case, how would you accomplish this?
-b

John Vinson said:
Hi,
I really appreciate you for responding to the question.
But speaking frankly,I did not get your point exactly. I did not want to
store the data into another table.I just wanted to retrive the data from a
single table,based on the value selected in the combo box.
For example,if I select the 'department code' from the combo box, then the
department name and dept. head should be filled in automatically from a
atable called 'Departments'.
I hope the question is much clear now.
Click to expand...

Where do you want the department name "filled in"?

If you want it DISPLAYED for human reading on your Form, my suggestion
will work: use

=[Department Code].Column(1)

if the department name is the second column in the combo box named
Department Code.

If you want it STORED in the table upon which this form is based, my
objection stands. The department name should exist in the Department
table; and unless you have some very clear reason to store it
redundantly, it should simply *not exist* in any other table (such as
the table upon which this form is based).

John W. Vinson[MVP]
Click to expand...
 
Never-mind.
I found a sample of what I wanted to do in the Northwind sample DB.
-b

Barry said:
John,
I posted a similar request to this (before finding this thread).
In my situation, I want to duplicate the data in a second table. The
rationale is that the first table provides the 'default' value, but the user
is able to override this. In either case the value needs to be stored in the
second table to capture the actual instance
For example:
products have a base value, but individual sales may use or alter the value
in the final sale. The alterations may be random, so there is no way to
capture it directly without wasting lots of added table entries.

In this case, how would you accomplish this?
-b

John Vinson said:
Hi,
I really appreciate you for responding to the question.
But speaking frankly,I did not get your point exactly. I did not want to
store the data into another table.I just wanted to retrive the data from a
single table,based on the value selected in the combo box.
For example,if I select the 'department code' from the combo box, then the
department name and dept. head should be filled in automatically from a
atable called 'Departments'.
I hope the question is much clear now.
Click to expand...

Where do you want the department name "filled in"?

If you want it DISPLAYED for human reading on your Form, my suggestion
will work: use

=[Department Code].Column(1)

if the department name is the second column in the combo box named
Department Code.

If you want it STORED in the table upon which this form is based, my
objection stands. The department name should exist in the Department
table; and unless you have some very clear reason to store it
redundantly, it should simply *not exist* in any other table (such as
the table upon which this form is based).

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Back
Top