Help trying to automatic lookup

  • Thread starter Thread starter Nadia
  • Start date Start date
N

Nadia

I have a table with over 40,000 records in it. The
fields i'm trying to get working
are "Code", "Description", and "Category" relating to the
code. I'm creating a form with these fields along with
others in the same table but want to be able to select a
particular code (i.e. 101) and have the Description and
Category fields automatically update with the correct
corresponding data to that code. I also have a table
that only list the codes, description, and category.
Please help. Thanks in advance.
 
Nadia said:
I have a table with over 40,000 records in it. The
fields i'm trying to get working
are "Code", "Description", and "Category" relating to the
code. I'm creating a form with these fields along with
others in the same table but want to be able to select a
particular code (i.e. 101) and have the Description and
Category fields automatically update with the correct
corresponding data to that code. I also have a table
that only list the codes, description, and category.
Please help. Thanks in advance.
Click to expand...

First off you should realize that what you are doing is incorrect in terms
of database design. The Description and Category data are already stored
once in your lookup table and that is the only place they should be stored.

Your second table should store ONLY the Code value and then you use a
query, subform, Dlookup(), ComboBox, or a myriad of other lookup techniques
to *display* the Description and Category any place you want to see them.
Don't make redundant copies of the data that run the risk of getting out of
synch should any of the values ever need to be updated.

What I would recommend with only two other fields involved is to have the
Code: 
 field on your form be represented with a ComboBox using your lookup
table as its RowSource.  Have all three columns of data in the CombBox
RowSource, but have Category and Description columns hidden by setting
their width to zero.

On your form along with the ComboBox you have two *unbound* TextBoxes with
ControlSource properties of...

Me.ComboBoxName.Column(1)
and
Me.ComboBoxName.Column(2)

....respectively.  This will cause those two TextBoxes to display the
appropriate values for Category and Description depending on the Code value
selected in the ComboBox.  It will look and act exactly the same as you
want, but without making redundant copies of the other two fields.  Those
fields would then be removed entirely from your second table as they are
not needed.
 
I set my Code field to a combo box and used the
Table/Query option and have my lookup table as the
RowSource. How do I get all 3 columns included??
So do those two unbound text boxes update the data to my
table?
-----Original Message-----
I have a table with over 40,000 records in it. The
fields i'm trying to get working
are "Code", "Description", and "Category" relating to the
code. I'm creating a form with these fields along with
others in the same table but want to be able to select a
particular code (i.e. 101) and have the Description and
Category fields automatically update with the correct
corresponding data to that code. I also have a table
that only list the codes, description, and category.
Please help. Thanks in advance.
Click to expand...

First off you should realize that what you are doing is incorrect in terms
of database design. The Description and Category data are already stored
once in your lookup table and that is the only place they should be stored.

Your second table should store ONLY the Code value and then you use a
query, subform, Dlookup(), ComboBox, or a myriad of other lookup techniques
to *display* the Description and Category any place you want to see them.
Don't make redundant copies of the data that run the risk of getting out of
synch should any of the values ever need to be updated.

What I would recommend with only two other fields involved is to have the
Code: 
 field on your form be represented with a ComboBox using your lookup
table as its RowSource.  Have all three columns of data in the CombBox
RowSource, but have Category and Description columns hidden by setting
their width to zero.

On your form along with the ComboBox you have two *unbound* TextBoxes with
ControlSource properties of...

Me.ComboBoxName.Column(1)
and
Me.ComboBoxName.Column(2)

....respectively.  This will cause those two TextBoxes to display the
appropriate values for Category and Description depending on the Code value
selected in the ComboBox.  It will look and act exactly the same as you
want, but without making redundant copies of the other two fields.  Those
fields would then be removed entirely from your second table as they are
not needed.


--
I don't check the Email account attached
to this message.     Send instead to...
RBrandt    at       Hunter      dot      com


.
[/QUOTE]
Click to expand...
 
Nadia said:
I set my Code field to a combo box and used the
Table/Query option and have my lookup table as the
RowSource. How do I get all 3 columns included??
Click to expand...

You include all three fields in the RowSource query and make sure the
ColumnCount property of the ComboBox is set to 3. Also make sure the bound
column property is set to 1 (assuming that the field
Code: 
 is the first
field in the query).

If you don't want to see the Category and Description fields when you drop
the ComboBox list down them you would use the CoilumnWidths property of the
ComboBox to set htose columns widhts to zero.  Something like...

0.75"; 0.00"; 0.00"



[QUOTE]
So do those two unbound text boxes update the data to my
table?[/QUOTE]

No.  The whole point is that they don't need to be (nor should they be)
stored in your table.  All you need to store is the Code value.

Don't think of your lookup tables as templates making it easy to *copy*
data over and over.  They should be used to *retrieve* the data when you
need it while storing it only one time in your database.
 
For whatever reason, it is not working out for me. Thank
you for your help though. It's much appreciated...I'm
giving up.
-----Original Message-----
I set my Code field to a combo box and used the
Table/Query option and have my lookup table as the
RowSource. How do I get all 3 columns included??
Click to expand...

You include all three fields in the RowSource query and make sure the
ColumnCount property of the ComboBox is set to 3. Also make sure the bound
column property is set to 1 (assuming that the field
Code: 
 is the first
field in the query).

If you don't want to see the Category and Description fields when you drop
the ComboBox list down them you would use the CoilumnWidths property of the
ComboBox to set htose columns widhts to zero.  Something like...

0.75"; 0.00"; 0.00"



[QUOTE]
So do those two unbound text boxes update the data to my
table?[/QUOTE]

No.  The whole point is that they don't need to be (nor should they be)
stored in your table.  All you need to store is the Code value.

Don't think of your lookup tables as templates making it easy to *copy*
data over and over.  They should be used to *retrieve* the data when you
need it while storing it only one time in your database.


--
I don't check the Email account attached
to this message.     Send instead to...
RBrandt    at       Hunter      dot      com



.
[/QUOTE]
Click to expand...
 
Back
Top