Need help! Trying to auto fill in a form

[MatGyver]

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!

[Mike Storr]

Re: Need help! Trying to auto fill in a form

On 9 Feb 2004 08:58:45 -0800, MatGyver wrote:
[color=blue]
> 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![/color]

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

[MatGyver]

Re: Need help! Trying to auto fill in a form

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!
[color=blue]
> 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[/color]

[Mike Storr]

Re: Need help! Trying to auto fill in a form

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" <mhunsworth@yah oo.com> wrote in message
news:a9650242.0 402101215.b5535 38@posting.goog le.com...[color=blue]
> 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!
>[color=green]
> > I think what you are trying to do here is usually setup as a detail[/color][/color]
section[color=blue][color=green]
> > of an Order Header -> Order Detail scenario. Usually the order header is[/color][/color]
on[color=blue][color=green]
> > 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[/color][/color]
following[color=blue][color=green]
> > SQL...
> >
> > SELECT tp.ID, tp.[Part Number], tp.[Part Description], tp.[Part Cost][/color][/color]
FROM[color=blue][color=green]
> > 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[/color][/color]