473,327 Members | 2,025 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

use lookup field with non bound control

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
Oct 9 '06 #1
3 3535
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
Oct 9 '06 #2
"Jimmy" <no**@none.comwrote in
news:4I***************@fe04.news.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.column(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

Oct 9 '06 #3
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
Oct 10 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: my-wings | last post by:
I've been reading about how evil Lookup fields in tables are, but I've got to be missing something really basic. I know this subject has been covered before, because I've just spent an hour or two...
3
by: xmp333 | last post by:
Hi, Is there a way to use a 2 column table as a lookup for a combo box, having one field show up on the form, and the other field actually written to the output table? Here's an example: I...
1
by: James | last post by:
I am used to VB6 but need to develop something in .Net. I need to create several bound combo-boxes which will use lookup tables to get their values. I created a form using the dataform wizard....
4
by: jon f kaminsky | last post by:
Hi- I've seen this problem discussed a jillion times but I cannot seem to implement any advice that makes it work. I am porting a large project from VB6 to .NET. The issue is using the combo box...
5
by: Ryker | last post by:
I have a form that has name address, ect. The key is social security number. I have a lookup box where social security number is entered and the table is searched for a matching number. If there...
2
by: Greg Strong | last post by:
Hello All, Is it possible to change table field lookup properties in code? I've been able to change other field properties in code, however so far no luck with field lookup properties. What...
11
by: Paul H | last post by:
Suppose I have a table called tblPeople and I want a field to illustrate whether each person prefers cats or dogs. I could do it one of three ways. 1. A plain text field Create a text field in...
1
by: Nestor01 | last post by:
I use a lookup table which contacts 2 fields (item#, name) When a customer places an order, the order form contains a drop down to select the item by name (from the lookup table). The detail is...
14
by: Mark | last post by:
I have a table with a field that uses a combobox to populate values. The Lookup tab within table design mode is the following: Display Control Combo Box Row Source Type ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.