I need to add many fields of data to a table using a form-CodePudding

I'm trying to create a form which needs to fill approx. 50 fields of data into a table in ms access. The function is to long to input as one line and when I try to break the line it keeps giving me the following error: "Syntax error in INSERT INTO statement". Is there a different way to write this. the code is supposed to be called when the submit button is pressed in the form.

The form should store the data into a table I have called "Performance_Reports"

Private Sub addRecord_Click()

DoCmd.RunSQL "Insert into Performance_Reports" _
& "(Report_Id, Badge_Id, Review_Date, Progression, Overall_Rating, Next_Review, Emp_Expertise/Job_Knowledge, Emp_Quality, Emp_Dependability, Emp_Delivering_Solutions, Emp_EH&S, Emp_Continuous_Improvement, Emp_Initiative, Emp_Teamwork, EMp_Time_Management, Emp_Productivity, Emp_Lead_Self, Emp_Inspire_and_Empower, Emp_Achieve_Results, Emp_Drive_Change_&_Innovation, Emp_Builds_Trust, Emp_Ethics, Strengths/Achievements, Opportunities_for_Development, Area_Preference_1, Area_Preference_2, Area_Preference_3, Area_Preference_4, Job_Rotation_History, Emp_Assessment, Summary, Supervisor_Comments, Employee_Comments, Emp_Signature_Date, Sup_Signature_Date, Sup_Expertise/Job_Knowledge, Sup_Quality, Sup_Dependability, Sup_Delivering_Solutions, Sup_EH&S, Sup_Continuous_Improvement, Sup_Initiative, Sup_Teamwork, Sup_Time_Management, Sup_Productivity, Sup_Lead_Self, Sup_Inspire_and_Supower, Sup_Achieve_Results, Sup_Drive_Change_&_Innovation, Sup_Builds_Trust, Sup_Ethics, Sup_Assessment) VALUES" _
& "(reportRecord, Badge_ID, reviewDate, progOpt, avgRating, nextReview, empExpertise, empQuality, empDependability, empSolutions, empImpact, empEhs, empImprovement, empInitiative, empTeam, empTime, empProduct, empLead, empInspire, empAchieve, empTrust, empDrive, empEthics, txtStrengths, txtOpportunities, area1, area2, area3, area4, txtHistory, empAssess, txtSummary, txtSup, txtEmp, empDateSig, supDateSig, supExpertise, supQuality, supDependability, supSolutions, supImpact, supEhs, supImprovement, supInitiative, supTeam, supTime, supProduct, supLead, supInspire, supAchieve, supTrust, supDrive, supEthics, supAssess)"

End Sub

CodePudding user response：

First, you miss the spaces and brackets:

Private Sub addRecord_Click()

    DoCmd.RunSQL "Insert into Performance_Reports " & _
    "(Report_Id, Badge_Id, Review_Date, Progression, Overall_Rating, Next_Review, [Emp_Expertise/Job_Knowledge], Emp_Quality, Emp_Dependability, Emp_Delivering_Solutions, Emp_EH&S, Emp_Continuous_Improvement, Emp_Initiative, Emp_Teamwork, EMp_Time_Management, Emp_Productivity, Emp_Lead_Self, Emp_Inspire_and_Empower, Emp_Achieve_Results, [Emp_Drive_Change_&_Innovation], Emp_Builds_Trust, Emp_Ethics, [Strengths/Achievements], Opportunities_for_Development, Area_Preference_1, Area_Preference_2, Area_Preference_3, Area_Preference_4, Job_Rotation_History, Emp_Assessment, Summary, Supervisor_Comments, Employee_Comments, Emp_Signature_Date, Sup_Signature_Date, [Sup_Expertise/Job_Knowledge], Sup_Quality, Sup_Dependability, Sup_Delivering_Solutions, Sup_EH&S, Sup_Continuous_Improvement, Sup_Initiative, Sup_Teamwork, Sup_Time_Management, Sup_Productivity, Sup_Lead_Self, Sup_Inspire_and_Supower, Sup_Achieve_Results, [Sup_Drive_Change_&_Innovation], Sup_Builds_Trust, Sup_Ethics, Sup_Assessment) VALUES " & _
    "(reportRecord, Badge_ID, reviewDate, progOpt, avgRating, nextReview, empExpertise, empQuality, empDependability, empSolutions, empImpact, empEhs, empImprovement, empInitiative, empTeam, empTime, empProduct, empLead, empInspire, empAchieve, empTrust, empDrive, empEthics, txtStrengths, txtOpportunities, area1, area2, area3, area4, txtHistory, empAssess, txtSummary, txtSup, txtEmp, empDateSig, supDateSig, supExpertise, supQuality, supDependability, supSolutions, supImpact, supEhs, supImprovement, supInitiative, supTeam, supTime, supProduct, supLead, supInspire, supAchieve, supTrust, supDrive, supEthics, supAssess)"

End Sub

Next, all values must be concatenated with the SQL. See my function CSql.

However, you would be much better off using DAO, open a recordset, and use methods AddNew and Update to insert the record, resulting in much cleaner code.

CodePudding user response：

Based on the number of fields you want to populate into the table, i will suggest your create a bound form using form wizard and have the fields you want inserted to be the selected fields from the table to be added to the form.

Except if you are working in a client-server architecture/the access file is shared over a network and you are concerned about the processes being slow, even then a single access file can work well(with bound forms) over a network with five users accessing it.

So use the bound form functionality, then go to the control palette and choose a command button, place it on the form, follow the wizard that will be prompted and choose the records option, then add record, complete the wizard by giving the command button a meaningful caption.