J
JGODFREY
Guest
I have a DB with Master table 'Stocks' and a one-many child table 'purchases' for details of buy & sell. On my main form, I have a sub-form for purchases, and a button for "enter transaction' which calls a new form 'Order' having all the fields, in which I enter the details of my transaction.
I am now playing around with INSERT queries to add the record.
I have searched many web sites, in particular 'msdn' which has a lot of good info, and have learned that a basic insert query will copy records from one table to another. However, I needed to copy info from a form text boxes.
I played around creating various queries including: (in design view, which in SQL view looked like) ,
INSERT INTO purchases ( Name, [Date], [Bid/Offer], [Buy price], Account, Expiry )
SELECT 'Cash Minerals' AS Expr1, #1/1/2001# AS Expr2, 5000 AS Expr3, 0.25 AS Expr4, 'EC' AS Expr5, #1/30/2003# AS Expr6;
This correctly added a record. However, when I tried to adapt this to get data from my form:
INSERT INTO purchases ( Name, [Date], [Bid/Offer], [Buy price], Account, Expiry )
SELECT Forms!ORDERS.NAME AS Expr1, #1/1/2001# AS Expr2, 5000 AS Expr3, 0.25 AS Expr4, 'EC' AS Expr5, #1/30/2003# AS Expr6;
it inserted a new record but with 'ORDERS' as the company name.
There must be a way to insert a new record from a data entry form but it is sure not well documented in any of the resources I have accessed (no pun intended) I have searched the sample databases (Northwind, and the other ones from microsoft ) but cannot get any significant help.
This must be such an easy problem that no one else needs to post problems such as I!
Thanks for any assistance you may provide.
John
I am now playing around with INSERT queries to add the record.
I have searched many web sites, in particular 'msdn' which has a lot of good info, and have learned that a basic insert query will copy records from one table to another. However, I needed to copy info from a form text boxes.
I played around creating various queries including: (in design view, which in SQL view looked like) ,
INSERT INTO purchases ( Name, [Date], [Bid/Offer], [Buy price], Account, Expiry )
SELECT 'Cash Minerals' AS Expr1, #1/1/2001# AS Expr2, 5000 AS Expr3, 0.25 AS Expr4, 'EC' AS Expr5, #1/30/2003# AS Expr6;
This correctly added a record. However, when I tried to adapt this to get data from my form:
INSERT INTO purchases ( Name, [Date], [Bid/Offer], [Buy price], Account, Expiry )
SELECT Forms!ORDERS.NAME AS Expr1, #1/1/2001# AS Expr2, 5000 AS Expr3, 0.25 AS Expr4, 'EC' AS Expr5, #1/30/2003# AS Expr6;
it inserted a new record but with 'ORDERS' as the company name.
There must be a way to insert a new record from a data entry form but it is sure not well documented in any of the resources I have accessed (no pun intended) I have searched the sample databases (Northwind, and the other ones from microsoft ) but cannot get any significant help.
This must be such an easy problem that no one else needs to post problems such as I!
Thanks for any assistance you may provide.
John