How to add many fields of data to a table using a form

Question

I'm trying to create a form to fill approximately 50 fields of data into a table in MS Access.

The function is too long to input as one line. When I try to break the line it gives me:

"Syntax error in INSERT INTO statement".

The code is to be called when the submit button in the form is pressed.

The form should store the data in a table 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

[How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) -- you will see the problem. — Andre, Nov 12 '21 at 17:00
Any reason why you don't just create a form based on the table, and use that ? it will take you ZERO code to do this. — Albert D. Kallal, Nov 14 '21 at 03:45

score 1 · Answer 1 · answered Nov 12 '21 at 17:32

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.

score 0 · Answer 2 · answered Nov 28 '21 at 23:09

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.

How to add many fields of data to a table using a form

2 Answers2