SQL INSERT INTO – Not Working

[ardy]

Hello All….
I have two tables (Student, Guardians), well there are more but these two are involved in this SQL…. Student_ID is the linker between the two table (many to one – multiple guardians to a single student). I have a form(frm_Student_Add). My goal is once I add a student (capturing related information from the student) the Student_ID automatically gets added to the Guardian table. I have approached this from several ways none of which is working. So I figured I try the SQL statement. Here is what I am doing.

In the form(frm_Student_Add) I am doing a After Update code in one of the fields(Student_ID) using the code below. It should work but it doesn’t any idea why. I also am open to any suggestion as to approach ……

Private Sub GUSD_Student_ID_AfterUpdate()
' Add student ID to Subform Student ID
    Dim GUSD_ID As String
    Dim SQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    GUSD_ID = Me.GUSD_Student_ID
    MsgBox "The GUSD Student ID is " & GUSD_ID & vbNewLine _
    & "Adding The Student ID to Parent Information" 'For testing
 '----------------------------------------------------
  On Error GoTo Err_GUSD_Student_ID_AfterUpdate
    'Prevent user warnings
    DoCmd.SetWarnings False
        SQL = "INSERT INTO Guardians [(GUSD_Student_ID)] VALUES (""" & Me.GUSD_Student_ID & """)"
    Debug.Print SQL
    DoCmd.RunSQL SQL
'Allow user warnings
  DoCmd.SetWarnings True
 
Exit_GUSD_Student_ID_AfterUpdate:
    Exit Sub
 
Err_GUSD_Student_ID_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_GUSD_Student_ID_AfterUpdate

EndSub

 

[pbaldy]

What's the data type of the field? What's the result of the Debug?

 

[ardy]

Data type in both tables are

Number, Single, 0 decimal Places

The error is :

Syntax error in INSERT INTO statement.

 

[pbaldy]

You don't want the extra quotes for a numeric data type. Try

SQL = "INSERT INTO Guardians (GUSD_Student_ID) VALUES (" & Me.GUSD_Student_ID & ")"

 

[ardy]

thanks for the reply.......
It doesn't give me the error message, it adds the Student_ID to the Student table but it doesn't add it to Guardians table......
I have tried so many different variations on this, I think I ran into this before.....Don't quite undrestand why it doesn't add it the table......?:banghead:

 

[pbaldy]

Can you post the db here?

 

[ardy]

Thanks for your help........
I still need to do a lot of work on it but here it is....... from the initial list you can invoke the Add Student button and then you will get the form.....let me know if you have any question.

 

[pbaldy]

The set warnings line is suppressing an error that you can't add the record because your relationships require a related record in the students table. You can add this before running the SQL to force the record to save, then it should work:

If Me.Dirty Then Me.Dirty = False

 

[ardy]

Yes, ..... Yes.......
Thank You ,,,,,, It works like a charm......I see the problem...... Thank you.

 

[pbaldy]

Happy to help! Now, send some garlic up here!

 

[ardy]

LOL.........

I wish I could.......