Lookup in a table

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

When using a lookup, Access plugs in the following select
statement ...

SELECT [Component Lookup].[Component ID], [Component
Lookup].[Description] FROM [Component Lookup] ORDER BY
[Component Lookup].[Description];

I need to perform the same operation in my code so I can
grab some additional fields from the lookup table.

I was able to grab these fields using the 'on click' event
as follows:

If Component_ID.ListIndex < 0 Then Exit Sub

Supplier_ID.Value = Component_ID.Column(2,
Component_ID.ListIndex)

Part_No.Value = Component_ID.Column(3,
Component_ID.ListIndex)

Unit_Cost.Value = Component_ID.Column(4,
Component_ID.ListIndex)

With what you're going to suggest for above, will these
statements still work. Thanks!
-Howard
 
You say that this works, if so, it should be fine. You could do a direct
lookup on the table again, but that would be slower than just getting the
value you already have in your combobox as you are doing.

As far as setting the lookup field in the table. It works, but I don't
recommend it. For more information look here.
http://www.mvps.org/access/lookupfields.htm
 
Hi Wayne,

I apologise, as I wasn't clear. The select statement I
gave you was from a form with a combo box lookup which,
yes, does work. I now need to do the same thing elsewhere
in my code that does not have the luxury of using a combo
box to do a lookup. It's a routine that is adding a many
many records to a table at the click of a button. While
adding these records, I need to obtain some of the
information to stuff in the fields from other lookup
tables. Using the example I gave, how do I do the lookup
(select statement) from inside my routine? Thanks!
-Howard
-----Original Message-----
You say that this works, if so, it should be fine. You could do a direct
lookup on the table again, but that would be slower than just getting the
value you already have in your combobox as you are doing.

As far as setting the lookup field in the table. It works, but I don't
recommend it. For more information look here.
http://www.mvps.org/access/lookupfields.htm

--
Wayne Morgan
Microsoft Access MVP


When using a lookup, Access plugs in the following select
statement ...

SELECT [Component Lookup].[Component ID], [Component
Lookup].[Description] FROM [Component Lookup] ORDER BY
[Component Lookup].[Description];

I need to perform the same operation in my code so I can
grab some additional fields from the lookup table.

I was able to grab these fields using the 'on click' event
as follows:

If Component_ID.ListIndex < 0 Then Exit Sub

Supplier_ID.Value = Component_ID.Column(2,
Component_ID.ListIndex)

Part_No.Value = Component_ID.Column(3,
Component_ID.ListIndex)

Unit_Cost.Value = Component_ID.Column(4,
Component_ID.ListIndex)

With what you're going to suggest for above, will these
statements still work. Thanks!
-Howard
Click to expand...


.
Click to expand...
 
Wayne, please read my other reply first ...

Someone else pointed me in the direction of the "Evils of
Lookup Tables". It's too late for me to change with this
application as it would require too many changes. However,
I will avoid them in future applications. But, what do I
do if I want to limit the user to selecting from a finite
set of values when entering a value into a form field, if
I don't use lookup tables? Thanks!
- Howard
-----Original Message-----
You say that this works, if so, it should be fine. You could do a direct
lookup on the table again, but that would be slower than just getting the
value you already have in your combobox as you are doing.

As far as setting the lookup field in the table. It works, but I don't
recommend it. For more information look here.
http://www.mvps.org/access/lookupfields.htm

--
Wayne Morgan
Microsoft Access MVP


When using a lookup, Access plugs in the following select
statement ...

SELECT [Component Lookup].[Component ID], [Component
Lookup].[Description] FROM [Component Lookup] ORDER BY
[Component Lookup].[Description];

I need to perform the same operation in my code so I can
grab some additional fields from the lookup table.

I was able to grab these fields using the 'on click' event
as follows:

If Component_ID.ListIndex < 0 Then Exit Sub

Supplier_ID.Value = Component_ID.Column(2,
Component_ID.ListIndex)

Part_No.Value = Component_ID.Column(3,
Component_ID.ListIndex)

Unit_Cost.Value = Component_ID.Column(4,
Component_ID.ListIndex)

With what you're going to suggest for above, will these
statements still work. Thanks!
-Howard
Click to expand...


.
Click to expand...
 
Howard said:
Someone else pointed me in the direction of the "Evils of
Lookup Tables". It's too late for me to change with this
application as it would require too many changes. However,
I will avoid them in future applications. But, what do I
do if I want to limit the user to selecting from a finite
set of values when entering a value into a form field, if
I don't use lookup tables?
Click to expand...

I hope no one ever told you to avoid lookup TABLES. What
they should have said is that you should stay away from
lookup FIELDS in tables. Lookup tables are an extremely
common and useful tool in a db programmer's bag. Lookup
fields may even be mildly useful in a query.

Lookup tables as used in combo or list boxes are essential
to many types of data entry forms.
 
Howard,

To do the same thing as the code you have but without the combobox, use the
DLookup function. This will allow you to specify a field in a table or
stored query and add criteria to make sure that you get the value from the
correct record.
 
Back
Top