|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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.
|
|
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.
--
Duane Hookom
MS Access MVP
"Arnold Klapheck" <ArnoldKlapheck[ at ]discussions.microsoft.com> wrote in
message news:42FAF2BC-27C9-44ED-B7B2-93E863504FAF[ at ]microsoft.com...
[Quoted Text] >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.
|
|
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.
"Arnold Klapheck" wrote:
[Quoted Text] > 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.
|
|
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.
|
|
Arnold Klapheck wrote:
[Quoted Text] > 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.
|
|
"mcescher" wrote:
> 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.
[Quoted Text] >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'."
|
|
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?
|
|
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
"Arnold Klapheck" <ArnoldKlapheck[ at ]discussions.microsoft.com> wrote in
message news:9FB083C5-7F9A-42B6-AC93-B5E4BBC155A9[ at ]microsoft.com...
[Quoted Text] >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?
|
|
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] & """);"
"Duane Hookom" wrote:
[Quoted Text] > 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 > > > "Arnold Klapheck" <ArnoldKlapheck[ at ]discussions.microsoft.com> wrote in > message news:9FB083C5-7F9A-42B6-AC93-B5E4BBC155A9[ at ]microsoft.com... > >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? > > > |
|
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
"Arnold Klapheck" <ArnoldKlapheck[ at ]discussions.microsoft.com> wrote in
message news:26155950-1E92-4651-9EA9-E2886E0F7EA3[ at ]microsoft.com...
[Quoted Text] >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] & """);" > > "Duane Hookom" wrote: > >> 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 >> >> >> "Arnold Klapheck" <ArnoldKlapheck[ at ]discussions.microsoft.com> wrote in >> message news:9FB083C5-7F9A-42B6-AC93-B5E4BBC155A9[ at ]microsoft.com... >> >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? >> >> >>
|
|
Thanks, that did it.
Since my table had default values for id and date I decided to take them out
of my code.
|
|
|