insert info into a table

[Simon]

I have a form that lists all the products i sell, what i require is a
button next to each product that i can click on it will then ask how
many product i would like to order then it will copy the following
information which is displayed on the form to tblOrderProducts

ProductID
ProductName
Price
QuantityPurchase ( this is what i type in pop up box)

Hope someone can point me in right direction

Thanks

 

[Al Campagna]

Simon,
Create an Append query against tblOrderProducts, using the
information from the form.
An example criteria for ProductID in that Append query would be...
= Forms!frmYourFormName!ProductID
and against Price...
=Forms!frmYourFormName!Price
and against QuantityPurchase...
=Forms!frmYourFormName!QuantityPurchase
(You probably don't have to save the ProductName. You can always relate
that back to your Products table using the ProductID.)

From your description though, it seems a bit odd that your using an
append query to populate your OrderProducts table. A more usual arrangement
would be to open an Order form (the one), and directly populate that with
Products and Prices and Qtys in a ItemsOrdered subform. (the many).
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Jerry Whittle]

The right direction would not be a button to move data to another table.
Instead your tblOrderProducts shouldn't even have three of those fields (or
at least two of them).

Instead the tblOrderProducts should have the ProductID field as the foriegn
key for that table. Open up the Relationships window and create a link
between the two tables based on the ProductID fields. Enable Referiential
Integrity. Cascade Update is a good option; however, I'd suggest to NOT do
Cascade Delete. Save the Relationships.

Next go to your Order form in design view. Add a subform to it based on the
tblOrderProducts. With the relationship defined between the two tables,
Access should be able to do this easily. Now when you want to create a new
order, you just go to the Product in the products form and you should be able
to enter the data in the subform.

Now for the matter of the Price. Normally you would just link to Products
table to get the Product Name and Price; however, the Price could change over
time. Therefore you probably would want to store the Price in the
tblOrderProducts. You could use a DLookup of the Price field in the Products
table to get this data for new records.