Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Posts
    74

    Unanswered: Missing Operator???

    When my code runs and gets to db.execute strSQL I get an eror that there is a missing operator in my SQL Statement. I am sure I have just stared at this too long... Can someone help me? I have attached a picture of the error.

    Code:
     
    Function SubmitTicket()
    '*******************************************************************
    'This module writes all data from the userform to the Issues table.*
    '*******************************************************************
        '***Variable Declaration
            Dim RFN As String, RLN As String, RCN As String, AFN As String, ALN As String, AID As String, _
            TA As String, System As String, Region As String, IssType As String, IssTime As String, ROC As String, strID As Variant
            Dim strSQL As String, strResponse As String, strStatus As String, ONAME As String
            Dim db As DAO.Database
     
     
        '***Get Values
     
        RFN = Nz(Me.tbRFN, "")
        RLN = Nz(Me.tbRLN, "")
        ONAME = RFN & Chr(32) & RLN
        RCN = Nz(Me.tbRCN, "")
        AFN = Nz(Me.tbAFN, "")
        ALN = Nz(Me.tbALN, "")
        AID = Nz(Me.tbAID, "")
        TA = Nz(Me.cbxAssign, "")
        System = Nz(Me.cbxSystem, "")
        Region = Nz(Me.cbxRegion, "")
        IssType = Nz(Me.cbxIType, "")
        IssTime = Now
        ROC = Nz(Me.tbROC, "")
        strStatus = "Pending Investigation"
     
     
        '***Get the description & write it to the table
            Me![Desc] = Nz(Me!tbDesc.Value, "")
     
        '***Get the RowId
            strID = Nz(Me.tbID, "")
        Select Case strID
            Case Is <> ""
                '***Set the SQL String to insert the data
                    strSQL = "INSERT INTO ISSUES (RFN, RLN, ONAME, RCN, AFN, ALN, AID, TA, SYSTEM, REGION, ISSTYPE, ISSTIME, ROC, STATUS) Values ('" _
                    & RFN & "','" & RLN & "','" & ONAME & "','" & RCN & "','" & AFN & "','" & ALN & "','" & AID & "','" & TA & "','" & System & "','" _
                    & Region & "','" & IssType & "','" & IssTime & "','" & ROC & "','" & strStatus & "' Where (([Issues].[ID] = " & strID & "));"
     
                    Set db = CurrentDb
                    db.Execute strSQL, dbFailOnError
     
                '***Test to see if the row was written, and if we need to insert an attachment
                    If db.RecordsAffected = 1 Then
                        '***Tell them it was successful
                            strResponse = MsgBox("Ticket Submitted Successfully." & Chr(13) & "The window will now be closed.", vbOKOnly + vbInformation, "Notification Submitted")
                        '***Close the form
                            DoCmd.Close acForm, "IssueTicket"
     
                        Else
                            strResponse = MsgBox("The Ticket was not submitted.", vbOKOnly + vbCritical, "Fatal Error")
     
                    End If
            Case Is = ""
                strResponse = MsgBox("The RowId was not set.", vbOKOnly + vbCritical, "Fatal Error")
     
        End Select
     
    End Function

    Thanks,
    Brent Blevins
    Attached Thumbnails Attached Thumbnails error.bmp  

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    You have a Where clause, but no record source to select any "Where" stuff from. If you remove the Where clause, this SQL should INSERT one record into ISSUES. But the data is all coming from constants (as least as far as the SQL is concerned).

    By the way, staring and not seeing is a VERY common ailment.

  3. #3
    Join Date
    Jun 2007
    Posts
    74
    I don't understand your response,

    Where (([Issues].[ID] = " & strID & "));"

    The table is "Issues", and the field is "ID". the variable strID contains the ID of the row that I need to insert the data into.

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Where refers to the table you are getting the data FROM, not the table you are INSERTing the data into.

    You do not have a table you are getting any data FROM in your SQL statement. That is, you don't have a FROM clause in your SQL statement, therefore, you are not getting data FROM any record source. If there is no FROM clause, you can not have a WHERE clause. It is illogical.

    If you need to add the strID field to the fields you are putting into the record, then do so, but you can't do it with a WHERE clause.

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by Brent Blevins
    The table is "Issues", and the field is "ID". the variable strID contains the ID of the row that I need to insert the data into.
    Hi

    If I read this correct you are trying to add/change the value of an existing row in Issues table where the ID = strID ??

    If this is correct, then I think you need an UPDATE query not an Append (INSERT INTO) query ??


    MTB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •