SQL syntax Question?

[ardy]

Hello All:
In the code below I am creating a record with the INSET INTO statement and then Updating with additional info in various fields. it is not working the way I thought it would, so I am trying to create the record in it's entirety ……

SQL_Grade_GUSD_ID = "INSERT INTO Grades (GUSD_Student_ID) VALUES (" & Me.GUSD_Student_ID & ")"
        SQLM1_1_ELA = "UPDATE Grades SET Grades.Subject = ""BM1(ELA)"""
        SQLM1_2_ELA = "UPDATE Grades SET Grades.Type = ""Exam"""
        SQLM1_3_ELA = "UPDATE Grades SET Grades.Score = ""0"""
        SQLM1_4_ELA = "UPDATE Grades SET Grades.Nam = ""GUSD BM-1"""
            DoCmd.RunSQL SQL
            DoCmd.RunSQL SQL_Grade_GUSD_ID
            DoCmd.RunSQL SQLM1_1_ELA
            DoCmd.RunSQL SQLM1_2_ELA
            DoCmd.RunSQL SQLM1_3_ELA
            DoCmd.RunSQL SQLM1_4_ELA
                   Debug.Print SQL, SQL_Grade_GUSD_ID

I am running to syntax problems when I try to USE the INSERT INTO to create the record with all the info in one statement……

SQLM1_1_ELA = "INSERT INTO Grades ( GUSD_Student_ID, Subject, Type, Score, Nam ) " & _
"SELECT (" & Me.GUSD_Student_ID & ")"" AS GUSD_Student_ID, ""BM2(ELA)"" AS Subject, " & _
"""Exam"" AS Type, ""0"" AS Score, ""GUSD BM-1"" AS Nam " & _
"FROM Grades"

I am Getting this error:

Syntax error (missing operator) in query expression ‘(12345)” AS GUSD_STUDENT_ID,
“BM2(ELA)” As Subject, “Exam” AS Type, “0” As Score, “GUSD BM-1” AS Nam From Grades’

What Am I missing..... :banghead:

Ardy

 

[pr2-eugin]

Did I not help you yesterday in another similar thread?

SQLM1_1_ELA = "INSERT INTO Grades (GUSD_Student_ID, Subject, Type, Score, Nam ) " & _
              "SELECT " & Me.GUSD_Student_ID & " AS GUSD_Student_ID, 'BM2(ELA)' AS Subject, " & _
              "'Exam' AS [Type], '0' AS Score, 'GUSD BM-1' AS Nam " & _
              "FROM Grades"

Are you sure that GUSD_Student_ID and Score are Strings in the table?

 

[ardy]

Hello Paul....
Yes you did, and I really appreciate it. It was for the same project. I am trying to custom make a an applet(Grade Book) for my wife(teacher).

The fields are :
GUSD_Student_ID : Number
Subject: Text
Type: Text
Score: Number
Nam: Text

I tried your version with warning off so I can see any messages, I didn't get any syntax error but I got the standard message as for adding or not adding record, in my case it said 0 record added for the grade table, Guardian table works fine.....

Private Sub GUSD_Student_ID_AfterUpdate()
' Add student ID to Subform Student ID
    Dim GUSD_ID As String   ' Setting GUSD_ID to GUSD_Student_ID which is captured from the form.
    Dim SQL  As String
'    Dim SQL_Grade_GUSD_ID As String ' GUSD Student ID for Grade Table
    Dim SQLM1_1_ELA As String    ' add student ID and backfill some records BM1
    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
    If Me.Dirty Then Me.Dirty = False ' force saving the record
 '----------------------------------------------------
  On Error GoTo Err_GUSD_Student_ID_AfterUpdate
    'Prevent user warnings
 '   DoCmd.SetWarnings False
        '----------- create parent record
        SQL = "INSERT INTO Guardians (GUSD_Student_ID) VALUES (" & Me.GUSD_Student_ID & ")"
        DoCmd.RunSQL SQL
 
        '------New Not working----- Create Grade record
SQLM1_1_ELA = "INSERT INTO Grades (GUSD_Student_ID, Subject, Type, Score, Nam ) " & _
              "SELECT " & Me.GUSD_Student_ID & " AS GUSD_Student_ID, 'BM2(ELA)' AS Subject, " & _
              "'Exam' AS [Type], '0' AS Score, 'GUSD BM-1' AS Nam " & _
              "FROM Grades"

DoCmd.RunSQL SQLM1_1_ELA
Debug.Print SQLM1_1_ELA
               
'Allow user warnings
'  DoCmd.SetWarnings True
  
  DoCmd.OpenForm "frm_Gurdian_Details", , , "GUSD_Student_ID = " & GUSD_ID
 
Exit_GUSD_Student_ID_AfterUpdate:
    Exit Sub
 
Err_GUSD_Student_ID_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_GUSD_Student_ID_AfterUpdate
    
End Sub

 

[ardy]

OK finally got it figured out, It seems that the SQL INSET INTO needs to match the table in terms of number, order and type, then we can use VALUES to set or create records.....

In case somebody comes across this thread, I am posting the Code that works as of now.....

Private Sub GUSD_Student_ID_AfterUpdate()
' Add student ID to Subform Student ID
    Dim GUSD_ID As String   ' Setting GUSD_ID to GUSD_Student_ID which is captured from the form.
    Dim SQL  As String
    Dim SQLM1_1_ELA As String    ' add student ID and backfill some records BM1
    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
    If Me.Dirty Then Me.Dirty = False ' force saving the record
    DoCmd.RunCommand acCmdSaveRecord
 '----------------------------------------------------
  On Error GoTo Err_GUSD_Student_ID_AfterUpdate
    'Prevent user warnings
    DoCmd.SetWarnings False
        '----------- create parent record
        SQL = "INSERT INTO Guardians (GUSD_Student_ID) VALUES (" & Me.GUSD_Student_ID & ")"
        DoCmd.RunSQL SQL
 
        '----------- Create Grade record
    SQLM1_1_ELA = "INSERT INTO Grades (GUSD_Student_ID, [Nam], [Subject], [Standard_ID], [Type], " & _
                  "TA_Date, Total_Score, Score, [Verbal_Grade], [Note]) VALUES (" & Me.GUSD_Student_ID & ", 'GUSD BM-1', " & _
                  "'BM1(ELA)', '', 'Exam', Now(), 100, 0, '', '')"

DoCmd.RunSQL SQLM1_1_ELA
Debug.Print SQLM1_1_ELA

'Allow user warnings
  DoCmd.SetWarnings True
  
  DoCmd.OpenForm "frm_Gurdian_Details", , , "GUSD_Student_ID = " & GUSD_ID
 
Exit_GUSD_Student_ID_AfterUpdate:
    Exit Sub
 
Err_GUSD_Student_ID_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_GUSD_Student_ID_AfterUpdate
    
End Sub

I still am thankful to all of you Gurus out there that put-up with us.....

Thank You
Ardy