insert into statement from a form

[Guest]

I need to store the current data displayed in a form window into a different
table. I have tried to use SQL insert into statement as below: -

Dim SQL As String

SQL = "INSERT INTO Contacted(ContactID, FirstName, LastName) Values (" &
Me!ContactID & ", " & Me!FirstName &", " & Me!LastName & ")"

DoCmd.RunSQL SQL

I tested this statement using the one field ContactID and it worked fine, I
then added the other fields and I now get a dialog box asking me for a value
(text box displayed to enter data into) for the first name although the
actual dialog box has the first name as its title bar.

Where am I going wrong, I have now looked at several SQL INSERT INTO
statements from forums and other literature all give various answers but are
either the same or very similar to what I am using. Help, please.....

 

[Ken Snell [MVP]]

Assuming that FirstName and LastName are text fields, delimit the values
from the form with ' characters so that Jet sees those values as text
strings:

SQL = "INSERT INTO Contacted(ContactID, FirstName, LastName) Values (" &
Me!ContactID & ", '" & Me!FirstName & "', '" & Me!LastName & "')"

 

[Dan Artuso]

Hi,
Strings have to be delinited with quotes (single or double), we'll use single
here. You can run into problems if names have embeded sinlge quotes such as:
O'Neil
IN that case you have to double up the single quotes within the string. You can use the
replace function to do that.

So...
SQL = "INSERT INTO Contacted(ContactID, FirstName, LastName) Values (" &
Me!ContactID & ", '" & Me!FirstName &"', '" & Replace(Me!LastName,"'","''") & "')"

That should get you started.
Numbers need no delimiters and dates use #