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

 

[Pat Hartman]

You've run into a problem because you have used the name of a property as a column name.

Forms!ORDERS.Name will return the name property of the Orders form. VBA interpretes your code by matching it against known objects/properties/methods from the libraries listed in Tools/References. If it gets through all those libraries without recognizing a word, it looks in your user defined objects.

You need to rename some of your columns to avoid this type of problem. Date and Name are the ones I see in your example.

BTW, is there some reason you don't want to use a bound form?

1. If you look in help for the SQL Insert statement, it will show you the two normal formats.
2. If you look in help for addnew, you will see how to add records using DAO or ADO code.

 

[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

 

[Pat Hartman]

I'm confused as to what the current problem is so I'll just comment on some things that you have said.

SQL - ALL queries are written in SQL. It is the language that relational databases provide to allow data manipulation. It is in the public domain and it is standardized by ANSI (American National Stancards Institute). All RDBMS' support some level of this standard including Access (Jet). Access supports stored SQL statements which are called querydefs and you can see them listed in the queries tab of the database window. Querydefs are pereferred because they are validated and optimized as they are saved. All other SQL strings need to be validated and optimized EVERY time they are executed which takes time (not enough to notice) and is the biggest cause of database bloat.

Help has gotten worse with each version of Access. With A2K and newer, you have two paths into help. One from the database window and a second from the VBA code window. So, now you have to know what kind of a question you are asking before you ask so you can open the "proper" help version. You need to make sure to have a code window open and select help from that window to find anything relating to VBA coding. You also may have trouble if you did not installed ALL help files when office was installed. And finally, you need to have the ADO and DAO libraries selected in the references list in order to have any shot at seeing help for ADO or DAO.

 

[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

 

[Pat Hartman]

Since you're changing names, change Order also. It is best to use prefixes on your object names. That way there is very little chance of accidentally using a reserved word. There are many standards. It is more important to pick one and stick to it than to choose the "best" standard.

Here's a condensed list of what I use:

frm = form
sfrm = subform
rpt = report
srpt = subreport
q = query
m = macro
mod = module
tbl = table
txt = textbox
cmb = combo
lst = listbox
frm = frame