Want to type in one field and autofill data in another...in form

[LadySharon]

Hi. I thought this would be simple and would happen just because
joined the tables... but I guess not.

I just started this week so I would need a step by step answer to th
question. ASAP if possible...

Let me see if I can describe what I am trying to do.

I have two tables (really three as I have a customer table to but tha
doesn't come into play here) an "order" table and a "products
table.

In the products table I have only two data fields: Product Name an
Price. Product Name is the primary key.

In the order table I have a bunch of stuff like order #, ship date
payment type (cash or check) etc... includeing Product and Price.
I have joined the tables through Product Name/Product.

All I want to do on the form is when I type in (or perferably use
drop box/combo box... you know with the down arrow that pulls up
list?... which I can do btw) PRODUCT it autofills the price fiel
with the correct price useing the Products table.

Of course I'd like the price saved as well in the Order table. Unles
it is not neccisary to do that. But I still can't seem to get a norma
field in for a different table. That is I can't put normal fields fro
orders and products into the same form. (I tryed it only lists field
from the order table unless I do a combo box)

I found a way to do it sorta... Under lookup in the help area it ha
autolookup queries... and I set one of thouse up... but it doesn'
automaticaly work in the form. And it doesn't fill in the price fro
the order list useing the price from the products table.

Btw eventaly I would like to have multiple products under one order i
this is possible... (if not that's ok) and a total field... that is
want to have product name, price, number of products ordered, shippin
price if needed and a total field... all under order.

This is probally too advanced for me right now otherwise I'd figure i
out myself. I admit I am impatiant though... when I want to d
something I want to do it NOW. So if someone knows a way to do th
above and can step it out for me I would be gratefull.

Thank you.

- Sharon
PS. It might be easyer to email me.

(e-mail address removed)

 

[PC Datasheet]

Sharon,

You need the following tables:

TblCustomer
CustomerID
Customername
Etc

TblProduct
ProductID
ProductName
Price

TblOrder
OrderID
CustomerID
stuff like order #, ship date, payment type (cash or check) etc (NOT Product and
Price)

TblOrderLineItem
OrderLineItemID
OrderID
ProductID
Price

Note - if the product price won't change for the life of the database, you don't
need the Price field.

1. Create the following relationships"
a) CustomerID in TblCustomer to CustomerID in TblOrder
b) OrderID in TblOrder to OrderID in TblOrderLineItem
c) ProdcutID in TblProduct to ProductID in Tbl OrderLineItem
2. Create a form/subform where the main form is based on YblOrder and the
subform is based on TblOrderLineItem. Make sure the LinkMaster and LinkChild
field properties are set to OrderID
3. Use a combobox in the main form to enter the customer
4. Use a combobox in the subform to enter the product.
a) Name the combobox "ProductName"
b) Set the bound column to 1
c) Set the number of columns to 3
d) Set the column widths to 0;2;0
5. Name the Price field "Price"
6. Put the following code in the AfterUpdate event of the Product combobox:
Me!Price = Me!ProductName.Column(2)

 

[LadySharon]

Bumping thread... hope this works here... I have noticed replys both in
new threads AND under orginal threads... just makeing a new thread as
a reply is really sloppy... so I hope this board doesn't do that.


- Sharon