471,309 Members | 1,605 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

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

Need help! Trying to auto fill in a form

I am going nuts trying to figure this out, any help will be
appreciated.

I have an existing table called "Parts". And in this table I have the
following columns:
"ID" "Part Number" "Part Description" "Part Cost"

I am trying to build an order form that will have "Part Number" and
"Part Cost" both on the form and in that forms specific table.

This is what I want to accomplish: When I am filling out the order
form, I want a drop down box with the list of the Part Numbers from
the Parts table. When I select the part number, I want the Part Cost
field to auto fill with the corresponding part cost. In addition to
this, once I get past that hurtle, I'd like to be able to choose
multiple parts.

I've seen this on an existing access db, but can not figure out how
it's being done.

Thanks in advance!
Nov 12 '05 #1
3 2239
On 9 Feb 2004 08:58:45 -0800, MatGyver wrote:
I am going nuts trying to figure this out, any help will be
appreciated.

I have an existing table called "Parts". And in this table I have the
following columns:
"ID" "Part Number" "Part Description" "Part Cost"

I am trying to build an order form that will have "Part Number" and
"Part Cost" both on the form and in that forms specific table.

This is what I want to accomplish: When I am filling out the order
form, I want a drop down box with the list of the Part Numbers from
the Parts table. When I select the part number, I want the Part Cost
field to auto fill with the corresponding part cost. In addition to
this, once I get past that hurtle, I'd like to be able to choose
multiple parts.

I've seen this on an existing access db, but can not figure out how
it's being done.

Thanks in advance!


I think what you are trying to do here is usually setup as a detail section
of an Order Header -> Order Detail scenario. Usually the order header is on
a main form, and the deatils are in a subform (datasheet or otherwise).
Using a datasheet or continuous form on the details table allows you to
select multiple items for the order, adding a new row for each new item.
To make your combobox, add one to the form, set it's RowSource to following
SQL...

SELECT tp.ID, tp.[Part Number], tp.[Part Description], tp.[Part Cost] FROM
Parts tp;

Set whatever other properties you need like the BoundColumn, Columns,
Column Widths etc. In the AfterUpdate event add...

(Assuming combo is called Part Number and there is a textbox called Part
Cost)
Me![Part Cost] = Me![Part Number].Column(1) 'Gets value in sec. column

--
Mike Storr
veraccess.com
Nov 12 '05 #2
This is in the right direction of what I want to accomplish, but when
I follow these instructions, I get the ID# in the part number drop
down, and the Part number in the Part cost field. It looks like it's
just one column off. I've played with this for a while today so not to
bother anyone anymore, but again I'm stuck.

Thanks again!
I think what you are trying to do here is usually setup as a detail section
of an Order Header -> Order Detail scenario. Usually the order header is on
a main form, and the deatils are in a subform (datasheet or otherwise).
Using a datasheet or continuous form on the details table allows you to
select multiple items for the order, adding a new row for each new item.
To make your combobox, add one to the form, set it's RowSource to following
SQL...

SELECT tp.ID, tp.[Part Number], tp.[Part Description], tp.[Part Cost] FROM
Parts tp;

Set whatever other properties you need like the BoundColumn, Columns,
Column Widths etc. In the AfterUpdate event add...

(Assuming combo is called Part Number and there is a textbox called Part
Cost)
Me![Part Cost] = Me![Part Number].Column(1) 'Gets value in sec. column

Nov 12 '05 #3
The columns have a 0 based index, so if you want the 3rd field in the query,
then use 2 as the column index ie. .Column(2)
"MatGyver" <mh********@yahoo.com> wrote in message
news:a9*************************@posting.google.co m...
This is in the right direction of what I want to accomplish, but when
I follow these instructions, I get the ID# in the part number drop
down, and the Part number in the Part cost field. It looks like it's
just one column off. I've played with this for a while today so not to
bother anyone anymore, but again I'm stuck.

Thanks again!
I think what you are trying to do here is usually setup as a detail section of an Order Header -> Order Detail scenario. Usually the order header is on a main form, and the deatils are in a subform (datasheet or otherwise).
Using a datasheet or continuous form on the details table allows you to
select multiple items for the order, adding a new row for each new item.
To make your combobox, add one to the form, set it's RowSource to following SQL...

SELECT tp.ID, tp.[Part Number], tp.[Part Description], tp.[Part Cost] FROM Parts tp;

Set whatever other properties you need like the BoundColumn, Columns,
Column Widths etc. In the AfterUpdate event add...

(Assuming combo is called Part Number and there is a textbox called Part
Cost)
Me![Part Cost] = Me![Part Number].Column(1) 'Gets value in sec. column

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by John Flynn | last post: by
reply views Thread by xunling | last post: by
1 post views Thread by MSDN Account | last post: by
2 posts views Thread by bryan | last post: by
10 posts views Thread by L. R. Du Broff | last post: by
23 posts views Thread by vinod.bhavnani | last post: by
8 posts views Thread by skumar434 | last post: by
reply views Thread by U S Contractors Offering Service A Non-profit | last post: by
2 posts views Thread by XML Beginner | last post: by
1 post views Thread by jhaydon | last post: by
reply views Thread by rosydwin | last post: by

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.