appending table from form

[Guest]

I have put the following code in a button_click event on the form myform to
append mytable.
ID, FirstName, LastName, are the field names of the table AND the Form
textbox names, they are identical
======CODE========
dim sql as string

sql = "INSERT INTO [mytable] (ID, FirstName, LastName,)"
sql = sql + "values (Forms![myform]![ID], Forms![myform]![FirstName],
Forms![myform]!LastName)"

DoCmd.RunSQL sql
======CODE========
I get a parameter box popping up asking me to input a parameter for ID.

any help on what to do would be apreciated, I just want it to append without
a parameter box.

 

[Duane Hookom]

This should never work since you have an extra comma following LastName. You
should also add some spaces. Consider replacing your code with:

dim sql as string

sql = "INSERT INTO [mytable] (ID, FirstName, LastName) "
sql = sql & "Values (" & Forms![myform]![ID] & ",""" & _
Forms![myform]![FirstName] & """,""" & _
Forms![myform]!LastName) & """)"

DoCmd.RunSQL sql

This assumes the ID field is numeric and the others are text.

 

[Guest]

First, I would not use sql as a name. It is a reserved word in Access. The
most common name used is strSQL ( the name has no relevance to Access, but to
we human types, it means str is a string variable, and SQL says it an SQL
statment.)

However, the real problem is your references to your form controls has to be
outside the quotes. Anything inside quotes is just a string value.

strSQL = "INSERT INTO [mytable] (ID, FirstName, LastName,) VALUES (" & _
Forms![myform]![ID] & ", " & Forms![myform]![FirstName] & ", " & _
Forms![myform]!LastName & ");"

In case you are not familiar with the Underline character at the end of a
line of VBA code, it mean the line continues on the next line. This is
useful for keeping you code on the screen so you don't have to scroll left
and right to read it.

 

[Guest]

OK, that seemed to work but now I am getting a pop up message that it cannot
find form myform.

the code is in a module that is attached to myform.

 

[mcescher]

OK, that seemed to work but now I am getting a pop up message that it cannot
find form myform.

the code is in a module that is attached to myform.

Change 'myform' to the name of the form that this code is in. Or, you
can eliminate that part altogether.

strSQL = "INSERT INTO [mytable] (ID, FirstName, LastName,) VALUES (" &
_
ID & ", '" & FirstName & "', '" & LastName & "');"

HTH,

Chris M.

 

[Guest]

Change 'myform' to the name of the form that this code is in.

"Arnold" reply:
myform is the name of the form that the code is in.

Or, you can eliminate that part altogether.

strSQL = "INSERT INTO [mytable] (ID, FirstName, LastName,) VALUES (" &
_
ID & ", '" & FirstName & "', '" & LastName & "');"

I tried the above and got:

"Syntax error (missing operator) in query expression ",'new','individual'."

 

[Guest]

I tried it your way and seemed to work but now getting message that it cannot
find the form. The code is attached to the form. Any Ideas?

 

[Duane Hookom]

Is your form really named "myform"? Usually when you see sample code like
this it means to substitute your form and control names. If the code is
running in the form with the controls, use:

sql = "INSERT INTO [mytable] (ID, FirstName, LastName) "
sql = sql & "Values (" & Me![ID] & ",""" & _
Me![FirstName] & """,""" & _
Me!LastName) & """)"

DoCmd.RunSQL sql

My next question would be "is your table really named 'mytable'?

 

[Guest]

I tried it your new way, now just get a pop up saying "syntax error in insert
into statement"

Here is my code:

mysql = "INSERT INTO [tblCase_Associated Individuals]
(caiAssociatedIndividualID, caiAssIndFirstName, caiAssIndLastName,
caiDLNumber, caiDLState, caiSSN, caiMobileAreaCode, caiMobilPhoneNumber,
caiBusAreaCode, caiBusPhoneNumber, caiResAreaCode, caiRePhoneNumber,
caiEmailAddress, caiBusAddress, caiBusAddress2, caiBusCity, caiBusState,
caiBusZipCode, caiResAddress, caiResAddress2, caiResCity, caiResState,
caiResZipCode, caiComments, caiCreateDate, caiUserID, caiComputerName)"
mysql = mysql + "values (" & Me![caiAssociatedIndividualID] & ",""" & _
Me![caiAssIndFirstName] & """ , """ & Me![caiAssIndLastName] & """, """ & _
Me![caiDLNumber] & """ , """ & Me![caiDLState] & """ , """ & _
Me![caiSSN] & """ , """ & Me![caiMobileAreaCode] & """ , """ & _
Me![caiMobilPhoneNumber] & """ , """ & Me![caiBusAreaCode] & """ , """ & _
Me![caiBusPhoneNumber] & """ , """ & Me![caiResAreaCode] & """ , """ & _
Me![caiRePhoneNumber] & """ , """ & _
Me![caiEmailAddress] & """ , """ & Me![caiBusAddress] & """ , """ & _
Me![caiBusAddress2] & """ , """ & Me![caiBusCity] & """ , """ & _
Me![caiBusState] & """ , """ & Me![caiBusZipCode] & """ , """ & _
Me![caiResAddress] & """ , """ & Me![caiResAddress2] & """ , """ & _
Me![caiResCity] & """ , """ & Me![caiResState] & """ , """ & _
Me![caiResZipCode] & """ , """ & Me![caiComments] & """ , """ & _
Me![caiCreateDate] & """ , """ & Me![caiUserID] & """ , """ & _
Me![caiComputerName] & """);"

Is your form really named "myform"? Usually when you see sample code like
this it means to substitute your form and control names. If the code is
running in the form with the controls, use:

sql = "INSERT INTO [mytable] (ID, FirstName, LastName) "
sql = sql & "Values (" & Me![ID] & ",""" & _
Me![FirstName] & """,""" & _
Me!LastName) & """)"

DoCmd.RunSQL sql

My next question would be "is your table really named 'mytable'?

--
Duane Hookom
MS Access MVP

I tried it your way and seemed to work but now getting message that it
cannot
find the form. The code is attached to the form. Any Ideas?

 

[Duane Hookom]

Note that I used & rather than + to concatenate strings. I also mentioned
earlier to make sure you have spaces where necessary. I would make sure you
have one before "values". There is a difference between delimiting text and
date values. Dates must use "#" rather than quotes. Your final
mysql = mysql + "values (" ....
might create a line containing too many characters. After building the
string mysql, add a line
Debug.Print mysql
You can then press Ctrl+G to open the debug window to paste the statement
into a sql view.

--
Duane Hookom
MS Access MVP

I tried it your new way, now just get a pop up saying "syntax error in
insert
into statement"

Here is my code:

mysql = "INSERT INTO [tblCase_Associated Individuals]
(caiAssociatedIndividualID, caiAssIndFirstName, caiAssIndLastName,
caiDLNumber, caiDLState, caiSSN, caiMobileAreaCode, caiMobilPhoneNumber,
caiBusAreaCode, caiBusPhoneNumber, caiResAreaCode, caiRePhoneNumber,
caiEmailAddress, caiBusAddress, caiBusAddress2, caiBusCity, caiBusState,
caiBusZipCode, caiResAddress, caiResAddress2, caiResCity, caiResState,
caiResZipCode, caiComments, caiCreateDate, caiUserID, caiComputerName)"
mysql = mysql + "values (" & Me![caiAssociatedIndividualID] & ",""" & _
Me![caiAssIndFirstName] & """ , """ & Me![caiAssIndLastName] & """, """ &
_
Me![caiDLNumber] & """ , """ & Me![caiDLState] & """ , """ & _
Me![caiSSN] & """ , """ & Me![caiMobileAreaCode] & """ , """ & _
Me![caiMobilPhoneNumber] & """ , """ & Me![caiBusAreaCode] & """ , """ & _
Me![caiBusPhoneNumber] & """ , """ & Me![caiResAreaCode] & """ , """ & _
Me![caiRePhoneNumber] & """ , """ & _
Me![caiEmailAddress] & """ , """ & Me![caiBusAddress] & """ , """ & _
Me![caiBusAddress2] & """ , """ & Me![caiBusCity] & """ , """ & _
Me![caiBusState] & """ , """ & Me![caiBusZipCode] & """ , """ & _
Me![caiResAddress] & """ , """ & Me![caiResAddress2] & """ , """ & _
Me![caiResCity] & """ , """ & Me![caiResState] & """ , """ & _
Me![caiResZipCode] & """ , """ & Me![caiComments] & """ , """ & _
Me![caiCreateDate] & """ , """ & Me![caiUserID] & """ , """ & _
Me![caiComputerName] & """);"

Is your form really named "myform"? Usually when you see sample code like
this it means to substitute your form and control names. If the code is
running in the form with the controls, use:

sql = "INSERT INTO [mytable] (ID, FirstName, LastName) "
sql = sql & "Values (" & Me![ID] & ",""" & _
Me![FirstName] & """,""" & _
Me!LastName) & """)"

DoCmd.RunSQL sql

My next question would be "is your table really named 'mytable'?

--
Duane Hookom
MS Access MVP

I tried it your way and seemed to work but now getting message that it
cannot
find the form. The code is attached to the form. Any Ideas?

 

[Guest]

Thanks, that did it.

Since my table had default values for id and date I decided to take them out
of my code.