Insert a single record from a form to a table.

J

JRP

Hello, Could someone help me with this problem, I have a form with
product displayed on it from a product table. I want to be able to add
that one record that is in view to another table for ordering when the
user clicks on a button for "Order Part". I have tried everything I
know, but I'm clueless. I have tried several types of code including
SQL insert and select statements but I just can't figure it out. I can
insert all the records from the table but not just the one that is
displayed at the time.
 
A

Al Camp

JRP,
Your Product table should have a unique key field indentifier for each Product. Use an
Update query against Oders using the value on the form as a criteria, to identify just
that one record.
In the Update query use that unique identifier as a criteria as to which record to
update Orders with.
(use your own form/object names)
=Form!frmYourFormName!ProductID

But, I think your design is a bit complicated.
A normal Order form would have a Main form, and a Subform in a One to Many
relationship..
The Main form deals with all the "one" information about the order in a table like
Orders (OrderNo, Customer, Date, ShipTo, etc...)
On that main Order form you would have a continuous subform, based on another table
like ProductsOrdered, that contains all the "many" information about the Order. (multiple
entries of ProductID, ProductDescription, Qty, Price, LineTotal... etc)
For each ProductOrdered record, you would select a ProductID from a combobox based on
table Products. Your just using your Products table to supply identifying values
(ProductID) for each item in the order.

Here's a simplified example of the Main form... The ONE Order Info
OrderNo Date Customer ShipTo
15623 1/1/06 Smith & Co. Smith & Co

Here's a simplified example of the continuous Subform... The MANY Order Info
OrderNo ProductID Desc Qty Price LineTotal
15623 X123A4 Widget 3 1.00 3.00
15623 Y4287 Cam 2 1.25 2.50
15623 12BB44 Switch 1 1.29 1.29

It would be too complicated to go into much more "detail" than that in an email, but
those are the basics involved.

The first paragraph above should work for what you have now, but I think your design
may run into problems later.
 
J

JRP

Al said:
JRP,
Your Product table should have a unique key field indentifier for each Product. Use an
Update query against Oders using the value on the form as a criteria, to identify just
that one record.
In the Update query use that unique identifier as a criteria as to which record to
update Orders with.
(use your own form/object names)
=Form!frmYourFormName!ProductID

But, I think your design is a bit complicated.
A normal Order form would have a Main form, and a Subform in a One to Many
relationship..
The Main form deals with all the "one" information about the order in a table like
Orders (OrderNo, Customer, Date, ShipTo, etc...)
On that main Order form you would have a continuous subform, based on another table
like ProductsOrdered, that contains all the "many" information about the Order. (multiple
entries of ProductID, ProductDescription, Qty, Price, LineTotal... etc)
For each ProductOrdered record, you would select a ProductID from a combobox based on
table Products. Your just using your Products table to supply identifying values
(ProductID) for each item in the order.

Here's a simplified example of the Main form... The ONE Order Info
OrderNo Date Customer ShipTo
15623 1/1/06 Smith & Co. Smith & Co

Here's a simplified example of the continuous Subform... The MANY Order Info
OrderNo ProductID Desc Qty Price LineTotal
15623 X123A4 Widget 3 1.00 3.00
15623 Y4287 Cam 2 1.25 2.50
15623 12BB44 Switch 1 1.29 1.29

It would be too complicated to go into much more "detail" than that in an email, but
those are the basics involved.

The first paragraph above should work for what you have now, but I think your design
may run into problems later.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
Click to expand...
Al,

I have a main form with all the customer info It is linked to a subform
that has all of the product info in it. These come from two different
tables. "tblcustinfo" and "tblproduct". What i'm trying to do is have
the user pick the product they want ordered it is then inserted into a
table "tblOrderProduct" this table has product ordered and customer
account number. The user can then export and e-mail this report to a
sales branch. I know this is a watered down description of the function
of this database but I just wonder if you think I will still run into
trouble with this later like you mentioned earlier. I am new at access
so any information you provide is helpful.

Thanks.
 
A

Al Camp

JRP,
Every edition of Access comes with a sample database called Northwind.mdb
Check out that database. It has an sample Orders form that shows you what I was trying
to describe as to a more correct way to set up an Order Form.

I can't say for sure that your present design will have problems or not, but just that
it's not the usual way to create orders.

If you stay with the system you have now, then my previous suggestion should assist
you in updating Orders from a unique Product in your Products table.
 
Top