Look up field

  • Thread starter Thread starter Aurora
  • Start date Start date
A

Aurora

I am using Access 2003
I have a form created in which I have to type descriptions of our ball
products.
Ex: 0.75mm Tungsten Carbide, or 0.33mm Ceramic, or 1.25mm Plastic etc.
I am trying to create a field where I would only have to type the number -
0.75, or 0.33 or 1.25 - and be able to select the material to go with the
size. I made a Look up data base for the description field. But after I
type the number and select the description - the material over writes the
number. Leaving me with only the material and no size. Can anyone help me
figure out how to do this? I have a thousand plus records to enter and I am
trying to make it as easy as possible and consistent so that my sorting and
searches will work easier.

Thank you - Aurora
 
Use two fields in your table and have the lookup bound to the second field.
When you output the data just combine them like this --
Ball Bearing: [Size] & " " & [Material]
 
You can do this if you have a field for the size and a separate field for
the material.

The combo will probably have a hidden first column for the ID, the next
column will be the size and the 3rd column will be the material.
After user selects a size from the combo, you run code in the combo's after
update event to put the matching material value in the material control.

Sample code
--------------
Me.[MaterialControlName] = Me.[ComboName].Column(2)
----------------------

Alternatively you can put the following in the Material text box's control
source.

Sample control source
-------------
= Me.[ComboName].Column(2)
---------------


Replace my object names with your object names.
Combo columns start their numbering at zero.
The 1st column is 0, 2nd column is 1 etc.
The square brackets are required where field and control names have spaces
in them.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Thank you for responding. I thought of that but was hoping I could do it all
in one field.

Aurora

KARL DEWEY said:
Use two fields in your table and have the lookup bound to the second field.
When you output the data just combine them like this --
Ball Bearing: [Size] & " " & [Material]

--
Build a little, test a little.


Aurora said:
I am using Access 2003
I have a form created in which I have to type descriptions of our ball
products.
Ex: 0.75mm Tungsten Carbide, or 0.33mm Ceramic, or 1.25mm Plastic etc.
I am trying to create a field where I would only have to type the number -
0.75, or 0.33 or 1.25 - and be able to select the material to go with the
size. I made a Look up data base for the description field. But after I
type the number and select the description - the material over writes the
number. Leaving me with only the material and no size. Can anyone help me
figure out how to do this? I have a thousand plus records to enter and I am
trying to make it as easy as possible and consistent so that my sorting and
searches will work easier.

Thank you - Aurora
Click to expand...
Click to expand...
 
Thank you for responding. I thought of this but was hoping I could do it all
in one field.

Aurora

Jeanette Cunningham said:
You can do this if you have a field for the size and a separate field for
the material.

The combo will probably have a hidden first column for the ID, the next
column will be the size and the 3rd column will be the material.
After user selects a size from the combo, you run code in the combo's after
update event to put the matching material value in the material control.

Sample code
--------------
Me.[MaterialControlName] = Me.[ComboName].Column(2)
----------------------

Alternatively you can put the following in the Material text box's control
source.

Sample control source
-------------
= Me.[ComboName].Column(2)
---------------


Replace my object names with your object names.
Combo columns start their numbering at zero.
The 1st column is 0, 2nd column is 1 etc.
The square brackets are required where field and control names have spaces
in them.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Aurora said:
I am using Access 2003
I have a form created in which I have to type descriptions of our ball
products.
Ex: 0.75mm Tungsten Carbide, or 0.33mm Ceramic, or 1.25mm Plastic etc.
I am trying to create a field where I would only have to type the number -
0.75, or 0.33 or 1.25 - and be able to select the material to go with the
size. I made a Look up data base for the description field. But after I
type the number and select the description - the material over writes the
number. Leaving me with only the material and no size. Can anyone help
me
figure out how to do this? I have a thousand plus records to enter and I
am
trying to make it as easy as possible and consistent so that my sorting
and
searches will work easier.

Thank you - Aurora
Click to expand...


.
Click to expand...
 
Back
Top