Lookup in a table

[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

 

[Wayne Morgan]

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

 

[Howard]

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

.

 

[Howard]

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

.