Re: Insert Into SQL

If you really want a book on SQL, "SQL for Mere Mortals" would be good (John Viescas and Michael Hernandez)

Hopefully you are aware that you can build any query in the graphical query design window, and then switch to SQL View (Query menu, in query design.) What I do is build the query there using any old criteria, and then copy it to the code window. You then have to concatenate the query into the string, adding the " quotes for Text fields or the # delimiter for Date fields.

Here's some info about how to add the quotes:
http://allenbrowne.com/casu-17.html

And here's the form I use to copy the SQL statement into code:
http://allenbrowne.com/ser-71.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Chuck" <libbeyc@xxxxxxxxxxxxxx> wrote in message
news:bbkmh3d9fpmthe7mg46nkm66s871dv4omv@xxxxxxxxxx
On Sun, 21 Oct 2007 11:15:39 +0800, "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
wrote:

You need to concatenate the value from Text1 on Form1 into the string.

This kind of thing:

Function InsertEm()
Dim db As DAO.Database
Dim strSql As String

If Not IsNull(Forms!Form1!Text1) Then
strSql = "INSERT INTO tblWT1 ( Mfg, Series, Model, Config, Options )
" & _
"SELECT tblMain.Mfg, tblMain.Series, tblMain.Model, " & _
"tblMain.Config, tblMain.Options FROM tblMain " & _
"WHERE tblMain.Mfg = """ & Forms!Form1!Text1 & """;"

Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) inserted."
End If
set db = Nothing
End Function

If Mfg is a Number field (not a Text field), drop the extra quotes, i.e.:
"WHERE tblMain.Mfg = " & Forms!Form1!Text1 & ";"


No luck.

Been looking in Amazon.com for a book on SQL. Most seem to be SQL Server.
Don't think those are what I need. I'm working on a simple little stand alone
A97 database on my home computer. If I ever get to a city large enough to have
a Barns and Noble, then I can 'peek' to see if anything looks like something I
could use. Any book suggestions you may have would be greatly appreciated.

Chuck
--

.

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)