Thanks, Doug -- However, I'm still getting an error message. I'm going to
copy my code in here now that I've changed it and would appreciate it if you
would take another look -- when I go to debug, it always puts a yellow arrow
beside the last line. The last line has the text box that I am trying to
insert into a memo field in the table, but from previous experience, that
little arrow isn't always on the line where the error is . . .
DoCmd.RunSQL "INSERT INTO BarriersSupports
([CLNO],[SocialBehaviorialMentalHealthSupports]," & _
"[MedicalServices],[PsychiatricServices],[TherapyServices],[CrisisTeamPlan]," & _
"[Staffing],[StaffTraining],[Environment],[IntermittentNontypicalInterventionsForSafety]," & _
"[CompatibleHousemates],[RiskTolerance],[BehaviorManagementServices],[TransitionProcess]," & _
"[AssistanceHealthyRelationships],[SocialOppurtunities],[Other],[Desc])" & _
"VALUES(" & Forms![FrmBeh]![Text10] & ", " & _
"'" & Forms![FrmBeh]![Combo68] & "', " & _
"" & Forms![FrmBeh]![Check24] & ", " & _
"" & Forms![FrmBeh]![Check26] & ", " & _
"" & Forms![FrmBeh]![Check28] & ", " & _
"" & Forms![FrmBeh]![Check30] & ", " & _
"" & Forms![FrmBeh]![Check32] & ", " & _
"" & Forms![FrmBeh]![Check34] & ", " & _
"" & Forms![FrmBeh]![Check36] & ", " & _
"" & Forms![FrmBeh]![Check38] & ", " & _
"" & Forms![FrmBeh]![Check40] & ", " & _
"" & Forms![FrmBeh]![Check42] & ", " & _
"" & Forms![FrmBeh]![Check54] & ", " & _
"" & Forms![FrmBeh]![Check56] & ", " & _
"" & Forms![FrmBeh]![Check58] & ", " & _
"" & Forms![FrmBeh]![Check60] & ", " & _
"" & Forms![FrmBeh]![Check62] & ", '" & Forms![FrmBeh]![Text84] & "');"
Douglas J. Steele said:
That's actually the one major difference between RunSQL (which isn't
actually obsolete, by the way) and Execute. RunSQL will resolve references
to controls in the SQL, Execute won't.
You need to move all 17 references to outside of the quotes:
CurrentDb.Execute "INSERT INTO
BarriersSupports([CLNO],[SocialBehaviorialMentalHealthSupports],[MedicalServices],[PsychiatricServices],[TherapyServices],[CrisisTeamPlan],[Staffing],[StaffTraining],[Environment],[IntermittentNontypicalInterventionsForSafety],[CompatibleHousemates],[RiskTolerance],[BehaviorManagementServices],[TransitionProcess],[AssistanceHealthyRelationships],[SocialOppurtunities],[Other],[Desc])"
& _
"VALUES(" & Forms![FrmBeh]![text10] & ", " & Forms![FrmBeh]![combo68] & ", "
& Forms![FrmBeh]![check24] & ", " & etc.
That assumes that the fields are numbers. If they're text, you need to
include quotes as well:
"VALUES('" & Forms![FrmBeh]![text10] & "', '" & Forms![FrmBeh]![combo68] &
"', '" & Forms![FrmBeh]![check24] & "', " & etc.
or
"VALUES(""" & Forms![FrmBeh]![text10] & """, """ & Forms![FrmBeh]![combo68]
& """, """ & Forms![FrmBeh]![check24] & """, " & etc.
For dates, you need to delimit the values with # characters, and the date
must be in a format that Access will recognize correctly (it tends not to
respect regional settings). Since you have no control over what Short Date
format your users have chosen, you're best off using something like
"VALUES(" & Format(Forms![FrmBeh]![text10], "\#yyyy\-mm\-dd\#") & ", "etc.
if it's date only, or
"VALUES(" & Format(Forms![FrmBeh]![text10], "\#yyyy\-mm\-dd hh\:nn\:ss\#")
& ", "etc.
if it's date and time
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Lanita said:
I wrote the entire message down: Here's the error message:
Run-time error '3061'
Too few parameters. Expected 17.
Here's the code:
CurrentDb.Execute "INSERT INTO
BarriersSupports([CLNO],[SocialBehaviorialMentalHealthSupports],[MedicalServices],[PsychiatricServices],[TherapyServices],[CrisisTeamPlan],[Staffing],[StaffTraining],[Environment],[IntermittentNontypicalInterventionsForSafety],[CompatibleHousemates],[RiskTolerance],[BehaviorManagementServices],[TransitionProcess],[AssistanceHealthyRelationships],[SocialOppurtunities],[Other],[Desc])"
& _
"VALUES(Forms![FrmBeh]![text10],Forms![FrmBeh]![combo68],Forms![FrmBeh]![check24],Forms![frmBeh]![check26],Forms![FrmBeh]![check28],Forms![FrmBeh]![check30],Forms![FrmBeh]![check32],Forms![FrmBeh]![check34],Forms![FrmBeh]![check36],Forms![FrmBeh]![check38],Forms![frmBeh]![check40],Forms![FrmBeh]![check42],Forms![FrmBeh]![check54],Forms![FrmBeh]![check56],Forms![FrmBeh]![check58],Forms![FrmBeh]![check60],Forms![FrmBeh]![check60],
Forms![FrmBeh]![text84]);"
I counted very carefully (I think) and there are 18 insert to fields and
18
values so I don't understand what the error is telling me.
:
I tried your suggestion and now I get an error "Too few parameters . . ."
Expected 17 . (can't remember the entire message) Any other
suggestions?
:
For a start, don't use 'DoCmd.RunSQL' it is obsolete.
Use 'CurrentDb.Execute' instead. You can include dbFailOnError so that
either all or nothing is inserted.
Try that and repost if you have the same trouble...
Steve
:
I'm trying to use the INSERT INTO statement to insert the value of a
text box
on a form into a memo field in a table. For some reason, if the text
in the
text box exceeds 125 characters, all the fields get inserted except
the text
box value. Everything is inserted as expected if the text in that
text box
is shorter than 125. Here's the code:
Private Sub Command86_Click()
DoCmd.RunSQL "INSERT INTO
BarriersSupports([CLNO],[SocialBehaviorialMentalHealthSupports],[MedicalServices],[PsychiatricServices],[TherapyServices],[CrisisTeamPlan],[Staffing],[StaffTraining],[Environment],[IntermittentNontypicalInterventionsForSafety],[CompatibleHousemates],[RiskTolerance],[BehaviorManagementServices],[TransitionProcess],[AssistanceHealthyRelationships],[SocialOppurtunities],[Other],[Desc])"
& _
"VALUES(Forms![FrmBeh]![text10],Forms![FrmBeh]![combo68],Forms![FrmBeh]![check24],Forms![frmBeh]![check26],Forms![FrmBeh]![check28],Forms![FrmBeh]![check30],Forms![FrmBeh]![check32],Forms![FrmBeh]![check34],Forms![FrmBeh]![check36],Forms![FrmBeh]![check38],Forms![frmBeh]![check40],Forms![FrmBeh]![check42],Forms![FrmBeh]![check54],Forms![FrmBeh]![check56],Forms![FrmBeh]![check58],Forms![FrmBeh]![check60],Forms![FrmBeh]![check60],
Forms![FrmBeh]![text84]);"
End Sub
The text box that is not inserting is Forms![FrmBeh]![text84]). All
of the
other fields are inserting as expected.
