INSERT INTO bracket error (1 Viewer)

robina

Access Developer
Local time
Today, 07:21
Joined
Feb 28, 2012
Messages
102
Hi,
I have a INSERT INTO SQL statement I am using in a master form named Emp_New.
I am trying to save txtEmp_ID and TxtCID data from the subform named Class_Catalog subform into a table named Classes_taken. I get the bracket error. Here is my code:

Private Sub CmdSave_Click()

Dim mySQL As String
mySQL = "INSERT INTO Classes_taken([Emp_ID],[Class_ID])VALUES([forms![Class_Catalog subform]![txtEmp_ID],[forms![Class_Catalog subform]![TxtCID])"

DoCmd.RunSQL mySQL


End Sub
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:21
Joined
Jan 23, 2006
Messages
15,364
Private Sub CmdSave_Click()

Dim mySQL As String
mySQL = "INSERT INTO Classes_taken([Emp_ID],[Class_ID])VALUES([forms![Class_Catalog subform]![txtEmp_ID],[forms![Class_Catalog subform]![TxtCID])"

DoCmd.RunSQL mySQL


End Sub

You need to get the "rendered value" from the form controls. Access has to get the value of the control (not the control name itself) and you have to put those values in your SQL statement.

You will need something like
mySQL = "INSERT INTO Classes_taken([Emp_ID],[Class_ID])VALUES('" & [forms![Class_Catalog subform]![txtEmp_ID] & "','" & [forms![Class_Catalog subform]![TxtCID] & "')"

As a debugging tip, place a Debug.Print MySql before the DoCmd.RunSQL... line.

This will print the value of MySql to the immediate window. You can usually see errors in the SQL, or you can try to copy and run the sql in the Query window to see if there are errors.
 

PhilsAVBAGuy

New member
Local time
Today, 10:21
Joined
Jul 17, 2012
Messages
3
I see a problem with the structure of you SQL... try this:

"INSERT INTO Classes_taken([Emp_ID],[Class_ID])VALUES(" & forms![Class_Catalog subform]![txtEmp_ID] & "," & forms![Class_Catalog subform]![TxtCID] & ");"
I know you have to separate the call to the form value from the text passed to the SQL statement otherwise it will literally read "forms!....." instead of the value need from the form call.
 

robina

Access Developer
Local time
Today, 07:21
Joined
Feb 28, 2012
Messages
102
The code shows no error in the code window, but when i click save it highlights the code. I've attached my dbase with the forms and tables involved. Its saved as access 2003. Interestingly, when I click the undo button is says do you want to delete 1 record. So its getting there!
 

Attachments

  • Training1.mdb
    1 MB · Views: 82

Users who are viewing this thread

Top Bottom