use lookup field with non bound control

[Jimmy]

I'm not sure the subject line is a very clear description of my problem
so...

I have two relevant tables for this issue...
the lookup table tblLookupItems with three fields [description]
[DefaultCost] [DefaultPrice]. The control source for my form tblExpenses.
In this table there are three relevant fields [ItemDescription] [ItemCost]
[InvPrice].

On the form the [ItemDescription] field is set up as a combobox to get
values from tblLookupItems. These values however, are stored in tblExpenses
as text and are not linked to the lookup table. The form simply uses the
lookup table for possible items. The user of the form does not need to
select one of these items, they can type in there own. The rowsource for the
combo box is... SELECT DISTINCTROW [LookupItems].[Name],
[LookupItems].[DefaultCost] FROM [LookupItems]; LimitToList is set to No and
from the table definition Required is set to No. This works fine.

What I want to do, is when the user selects one of the item in the list,
have the data in the [DefaultCost] and [DefaultPrice] fields automatically
entered into [ItemCost] and [InvPrice] fields respectively as the default
values. Thus if the user selects Item A from the list, the [DefaultCost] and
[DefaultPrice] will be entered into [ItemCost] and [InvPrice] and no longer
be linked to tblLookupItems anymore. In other words, if tblLookupItems were
deleted, tblExpenses would show the following record:
Item A $4.00 $8.00

If this is unclear let me know and I will try to clarify.

Jimmy

[ray]

Hi Jimmy,

For this to work, you will need to add the DefaultPrice to the
rowsource for the combobox. As in
SELECT DISTINCTROW [LookupItems].[Name], [LookupItems].[DefaultCost],
[LookupItems].[DefaultPrice] FROM [LookupItems]

Set the column width of the third column in the combobox to 0 if you
don't want the user to see it.

Then, try in the AfterUpdate event for the combobox:

me!ItemCost = me!ComboBoxName .Column(1)
me!InvPrice = me!ComboBoxName .Column(2)

Note that the column count is zero based, so the third column is
referred to as .column(2).

Ray


Jimmy wrote:

I'm not sure the subject line is a very clear description of my problem
so...

I have two relevant tables for this issue...
the lookup table tblLookupItems with three fields [description]
[DefaultCost] [DefaultPrice]. The control source for my form tblExpenses.
In this table there are three relevant fields [ItemDescription] [ItemCost]
[InvPrice].

On the form the [ItemDescription] field is set up as a combobox to get
values from tblLookupItems. These values however, are stored in tblExpenses
as text and are not linked to the lookup table. The form simply uses the
lookup table for possible items. The user of the form does not need to
select one of these items, they can type in there own. The rowsource for the
combo box is... SELECT DISTINCTROW [LookupItems].[Name],
[LookupItems].[DefaultCost] FROM [LookupItems]; LimitToList is set to No and
from the table definition Required is set to No. This works fine.

What I want to do, is when the user selects one of the item in the list,
have the data in the [DefaultCost] and [DefaultPrice] fields automatically
entered into [ItemCost] and [InvPrice] fields respectively as the default
values. Thus if the user selects Item A from the list, the [DefaultCost] and
[DefaultPrice] will be entered into [ItemCost] and [InvPrice] and no longer
be linked to tblLookupItems anymore. In other words, if tblLookupItems were
deleted, tblExpenses would show the following record:
Item A $4.00 $8.00

If this is unclear let me know and I will try to clarify.

Jimmy

[Bob Quintal]

"Jimmy" <no**@none.comw rote in
news:4I******** *******@fe04.ne ws.easynews.com :

I'm not sure the subject line is a very clear description of
my problem so...

I have two relevant tables for this issue...
the lookup table tblLookupItems with three fields
[description]
[DefaultCost] [DefaultPrice]. The control source for my form
tblExpenses. In this table there are three relevant fields
[ItemDescription] [ItemCost] [InvPrice].

On the form the [ItemDescription] field is set up as a
combobox to get values from tblLookupItems. These values
however, are stored in tblExpenses as text and are not linked
to the lookup table. The form simply uses the lookup table for
possible items. The user of the form does not need to select
one of these items, they can type in there own. The rowsource
for the combo box is... SELECT DISTINCTROW
[LookupItems].[Name], [LookupItems].[DefaultCost] FROM
[LookupItems]; LimitToList is set to No and from the table
definition Required is set to No. This works fine.

What I want to do, is when the user selects one of the item in
the list, have the data in the [DefaultCost] and
[DefaultPrice] fields automatically entered into [ItemCost]
and [InvPrice] fields respectively as the default values. Thus
if the user selects Item A from the list, the [DefaultCost]
and [DefaultPrice] will be entered into [ItemCost] and
[InvPrice] and no longer be linked to tblLookupItems anymore.
In other words, if tblLookupItems were deleted, tblExpenses
would show the following record: Item A $4.00 $8.00

If this is unclear let me know and I will try to clarify.

Jimmy

It's not unclear. In the combobox's AfterUpdate event, you want
a little VB code that takes the value of combobox column two and
puts that in the [ItemCost] field.

me![Itemcost] = me.combobox.col umn(1)
'NOTE: the column property is ZERO-based, the first column is 0,
the second is 1, the third is 2. PITA.

you'll also want to modify the combobox's rowsource to add the
third column, DefaultPrice, then you can add code to stuff its
value into the correct textbox of field.


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Jimmy]

Mucho Danke! My problem was I was trying to bring the tblLookupItems into
the query that was my record source for the form and it kept throwing off
one of the subforms.

thanks for the quick response guys.

Jimmy