Help needed - appending a record to a table then updating

  • Thread starter Thread starter FatMan
  • Start date Start date
F

FatMan

Hi all:
What I would like to know is....

How do you append a blank record to a table via code and then reference the
field names to populate them with values I have stored in variables in my
code?

All help is greatly appreciated.

Thanks,
FatMan
 
Hi FatMan,
the easiest way is to use a sql append instruction in this way
let's say the table where you wanna append your data is named tabletoappend
and the fields in the table are called fiel1 and field2
CurrentDb.Execute ("INSERT INTO tabletoappend ( field1, field2 ) SELECT " &
var1 & " ,""" & var2 & """")

var1 is numeric and var2 is text
You can have more than 2 fields, this is just an example.

HTH Paolo
 
I may be mistaken on this, but I thought that if you did an INSERT with a
SELECT statement, then the SLECT statement had to be a full SQL SELECT query,
where if you want to add a single record with that format, I thought you had
to use the VALUES clause, something like

"INSERT INTO tabletoappend ( field1, field2 ) VALUES (" &
 
Paolo:
Thanks for he post. I have tried your suggestion and recieved the following
error....

Run-time eror '3061': Too few parameters. Expected 1"

The code I used was...

CurrentDb.Execute ("INSERT INTO tblFileName (FileName) SELECT " & Response)

Where...
tblFileName = the table name
FileName = the field
Response = the variable I would like to store in the field file name

I have never used SQL in any of my coding before and would really like to
understand how this statements works as I will be needing to use it to append
and populate a table with as many as 12 fields/variables.

Can you please help me out.

Thanks,
FatMan
 
I think filename is a string so you have to put it between quotation marks so

CurrentDb.Execute ("INSERT INTO tblFileName (FileName) SELECT """ & Response
& """")

The remark Jim Novi made is correct. You can use also
CurrentDb.Execute ("INSERT INTO tblFileName (FileName) VALUES """ & Response
& """")

Regards Paolo
 
I'm pretty sure this is what you need:

CurrentDb.Execute ("INSERT INTO tblFileName (FileName) VALUES(""" & Response
& """)")
 
Back
Top