Access 2000 Insert query - simple problem, simpler user!

[JGODFREY]

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

 

[EMP]

Try changing:-
SELECT Forms!ORDERS.NAME AS Expr1

to:-
SELECT Forms!ORDERS!NAME AS Expr1


Forms!ORDERS.NAME refers to the Name of the form.

Forms!ORDERS!NAME refers to the Name text box on the form.


Note: It is not considered good practice to use the words "Name" and "Date" as field names or control names, or to include special characters such as / and spaces in the names. As explained by Pat, they may cause confusions and errors, particularly when you use them in VBA.

 

[JGODFREY]

Hi! Thanks for your speedy reply.
I have searched the access help files and not found anything on INSERT queries, only APPEND queries which add records from an existing table to the current table. (search on msdn tells me these are the same??)
Looking in the Access Help Index for SQL queries I see:

What is an SQL query and when would you use one?
An SQL query is a query you create using an SQL statement. Examples of SQL-specific queries are the union query, pass-through query, data-definition query, and subquery.
This topic provides examples of:
Union query
Pass-through query
Data-definition query
Subquery

From there I had to resort to experimentation.
If I search for 'addnew' I get 'Scoping and Object-Naming Compatibility' with a whole lot of stuff that looks irrelevent (perhaps to the inexperienced eye).

I had not thought of using a bound form. I will experiment. Will this allow for more direct record insertion?

Hi EMP.

Yes I tried your variation using both ! but it made no difference.

I will try renaming fields in my test DB to see if this makes a difference.

Thanks both for your suggestions.

John

 

[JGODFREY]

Hi.
Actually my 'ORDERS' form is a bound form, bound to my purchases table. It has on it two buttons, one to add the record, with just the following default code:
Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click
DoCmd.GoToRecord , , acNewRec
Exit_Add_Record_Click:
Exit Sub
I have not yet figured out what extra code to add, as I am still playing around getting a query to work, so I can then Use its underlying SQL code, or use it to call a macro that executes the query.
Also I have on my main form checkboxes (bound to yes/no fields) labeled 'Own' and 'Bid/Offer'. In the above code on the add record on click event, I will add code check the 'Bid/Offer' box and assign 'y' to the table field. I already have code in the 'on current' event for the main form that calculates the net balance of shares bought & sold and displays this in a terxtbox. I will likely use the 'on update' and/or the 'on current' events to update 'Own'.
The other button is to exit without adding the record.

John

 

[JGODFREY]

Hi!

Thanks for your help & suggestions.
I discovered the problem. - It was in the field names (& form names ?) I chose. In particular on my entry form 'Orders' the field 'Name' Note in my original posting.

"SELECT Forms!ORDERS.NAME AS Expr1,"

After changing names, removing spaces, and '/' from field names, (and I think perhaps 'Orders' may have been messing things up) I replaced the query with:-

INSERT INTO purchases ( StockName, TDate, BidOffer, BuyPrice, Account, Expiry )
SELECT [Forms]![Order]![StockName] AS Expr1, [Forms]![Order]![TDate] AS Expr2, [Forms]![Order]![BidOffer] AS Expr3, [Forms]![Order]![BuyPrice] AS Expr4, [Forms]![Order]![Account] AS Expr5, [Forms]![Order]![Expiry] AS Expr6;

Seems to work now, but I still wonder if 'Order' may be a key word & give me trouble in the future??

Thanks,
John