-----Original Message-----
If I am understanding your question correctly you want to
add values to one more field [dbo_errorscan] that doesn't
exist in dbo_scantemp.
There may be a better way to do this but this works. I did
my execution of the SQL statement a little different. How
I approached this is to do a seperate insert statement for
each record to be inserted. because when using a select
statement as your foundation for the insert statement with
an additional column it gets a little hairy.
I also use a function called AddQuots which adds quotes
around strings to be passed in to SQL statements. for
myself it makes them a little easier to read and
troubleshoot. (i.e. addquotes("foo") is easier to read
than """foo""" it is pretty easy to get screwed up in
putting in quotes)
Dim SQL As String
Dim sql2 As String
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM dbo_scantemp
WHERE (clientID is NULL)")
rs.MoveFirst
While Not (rs.EOF)
sql2 = AddQuotes(rs.Fields(0))
For i = 1 To rs.Fields.Count - 1
sql2 = sql2 & ", " & AddQuotes(Nz(rs.Fields
(i), ""))
Next i
SQL = "INSERT INTO dbo_errorscan(field 1, field 2,
field 3, field 4, code)"& _
"VALUES(" & sql2 & ", " & AddQuotes("101") & ")"
db.Execute (SQL)
rs.MoveNext
Wend
db.Close
This also allow for dynamic expansion of the of the query
on either end, the source or the destination. If you need
to add another column called code 2 and want to call
it "202" by default you just add another field in the into
part of the SQL INTO(....code, code2) and modify the
values part VALUES(.... addquotes(101), addquotes(202))
Also if you add columns to the scantemp and add the
respective columns to your destination file, all will be
good to go without having to update your code.
HTH. Like I said there maybe othe ways to do this but I
know this works.
MW
mweyland @ mnqio . sdps . org
-----Original Message-----
Hello,
In the form I want to add 1 more value into a field name
code="101" in table dbo_errorscan. How can I do it with
this Sql below?
Sql = " INSERT into dbo_Errorscan " & _
" Select * From dbo_scantemp " & _
" Where clientid=NULL;"
CurrentDb.Execute Sql, dbFailOnError
Best Regards,
HM
.
.
