none
Sr Infor Analyst RRS feed

  • Question

  • I tried to create a form today so that I am able to insert information using the buttons Add, Edit, Delete, Clear, Close. All of my buttons work except for my Add which keeps giving me error Run Time Error 3134, I have been trying for 2 hours to figure out what it may be (even changed Date and Group to Date1 and PGroup so not to confuse those words in Access).  I cannot figure out and i followed a step by step example and have everthing the same as they did but mine will not work! Does anyone know what the issue may be? the bolded part is where I keep getting an issue

    Private Sub cmdadd_Click()
        If Me.cbopgroup.Tag & "" = "" Then
            'this is for insert new
            'add data to table
                CurrentDb.Execute "INSERT INTO Test(PGroup, Topic, Subtopic, [Date1], Comments) " & _
                    "VALUES(" & Me.cbopgroup & ",'" & Me.cbotopic & "','" & _
                    Me.cbosubtopic & "','" & Me.txtdate1 & "','" & Me.txtcomments & "')"
        Else
            'this is for updating
            CurrentDb.Execute "UPDATE Test " & _
                " SET PGroup=" & Me.cbopgroup &
                ", Topic='" & Me.cbotopic & "'" & _
                ", Subtopic='" & Me.cbosubtopic & "'" & _
                ", Date1='" & Me.txtdate1 & "'" & _
                ", Comments='" & Me.txtcomments & "'" & _
                " WHERE PGroup=" & Me.cbopgroup.Tag
           
        End If
           
        'clear form
        cmdclear_Click
        'refresh data in list on form
        frmTestSub.Form.Requery
    End Sub

    Tuesday, July 7, 2015 8:15 PM

Answers

  • I'm not sure what you are doing with the tag property of cboPGroup.  This appears strange, particularly in your update SQL.  It's worth mentioning that your queries indicate that you a setting a field designated for storing a date value as a string. 

    I've tested your SQL for both the Insert and Update queries and I don't get an error.  Although, I did modify the update query to use the value of cboPGroup rather than its tag.

    Your example here does have one issue.  Your Update query is missing an underscore, though that would cause a compile error, and not what you are encountering.

    I made a minor modification to your code which will allow you to test your SQL if necessary.  See below:

    Private Sub cmdAdd_Click()
        
        Dim strSQL As String
        
        If (Me.cboPGroup.Tag & "" = "") Then
            'Insert SQL
            strSQL = "INSERT INTO LMWilson(PGroup, Topic, Subtopic, Date1, Comments) " & _
            "VALUES(" & Me.cboPGroup & ",'" & Me.cboTopic & "','" & _
            Me.cboSubTopic & "','" & Me.txtDate1 & "','" & Me.txtComments & "')"
        Else
            'Update SQL
            strSQL = "UPDATE LMWilson " & _
            " SET PGroup=" & Me.cboPGroup & _
            ", Topic='" & Me.cboTopic & "'" & _
            ", Subtopic='" & Me.cboSubTopic & "'" & _
            ", Date1='" & Me.txtDate1 & "'" & _
            ", Comments='" & Me.txtComments & "'" & _
            " WHERE PGroup=" & Me.cboPGroup.Tag
        End If
             
        Debug.Print strSQL
        
        CurrentDb.Execute strSQL
        
        'clear form
        cmdClear_Click
        'refresh data in list on form
        frmLMWilsonSub.Form.Requery
        
     End Sub
    

    Tuesday, July 7, 2015 9:08 PM
  • The error is Run Time Error 3134, Syntax error in INSERT INTO

    This is the section:

     CurrentDb.Execute "INSERT INTO Test(PGroup, Topic, Subtopic, [Date1], Comments) " & _
                    "VALUES(" & Me.cbopgroup & ",'" & Me.cbotopic & "','" & _
                    Me.cbosubtopic & "','" & Me.txtdate1 & "','" & Me.txtcomments & "')"
       


    As I said, I didn't get an error on either SQL run.

    Use the code example just provided and put a break at the CurrentDB.Execute statement.  You will need to modify it a little.  I changed the table name from "Test" to "LMWilson."  Look to the immediate window and examine the SQL that was output to see if anything looks strange.

    If you can't see the error in the SQL then do the following:

    Copy the SQL statement from the immediate window.

    Press [Alt]+[F11] to return to the Access window. 

    Create a blank query and go to the SQL editor. 

    Paste the SQL string from the VBA immediate window and try to run your query.


    Tuesday, July 7, 2015 9:18 PM

All replies

  • What is the error message?  Please indicate which line the error occurs on as it isn't evident in your post.
    Tuesday, July 7, 2015 8:32 PM
  • The error is Run Time Error 3134, Syntax error in INSERT INTO

    This is the section:

     CurrentDb.Execute "INSERT INTO Test(PGroup, Topic, Subtopic, [Date1], Comments) " & _
                    "VALUES(" & Me.cbopgroup & ",'" & Me.cbotopic & "','" & _
                    Me.cbosubtopic & "','" & Me.txtdate1 & "','" & Me.txtcomments & "')"
       


    • Edited by lmwilson Tuesday, July 7, 2015 9:03 PM
    Tuesday, July 7, 2015 9:02 PM
  • I'm not sure what you are doing with the tag property of cboPGroup.  This appears strange, particularly in your update SQL.  It's worth mentioning that your queries indicate that you a setting a field designated for storing a date value as a string. 

    I've tested your SQL for both the Insert and Update queries and I don't get an error.  Although, I did modify the update query to use the value of cboPGroup rather than its tag.

    Your example here does have one issue.  Your Update query is missing an underscore, though that would cause a compile error, and not what you are encountering.

    I made a minor modification to your code which will allow you to test your SQL if necessary.  See below:

    Private Sub cmdAdd_Click()
        
        Dim strSQL As String
        
        If (Me.cboPGroup.Tag & "" = "") Then
            'Insert SQL
            strSQL = "INSERT INTO LMWilson(PGroup, Topic, Subtopic, Date1, Comments) " & _
            "VALUES(" & Me.cboPGroup & ",'" & Me.cboTopic & "','" & _
            Me.cboSubTopic & "','" & Me.txtDate1 & "','" & Me.txtComments & "')"
        Else
            'Update SQL
            strSQL = "UPDATE LMWilson " & _
            " SET PGroup=" & Me.cboPGroup & _
            ", Topic='" & Me.cboTopic & "'" & _
            ", Subtopic='" & Me.cboSubTopic & "'" & _
            ", Date1='" & Me.txtDate1 & "'" & _
            ", Comments='" & Me.txtComments & "'" & _
            " WHERE PGroup=" & Me.cboPGroup.Tag
        End If
             
        Debug.Print strSQL
        
        CurrentDb.Execute strSQL
        
        'clear form
        cmdClear_Click
        'refresh data in list on form
        frmLMWilsonSub.Form.Requery
        
     End Sub
    

    Tuesday, July 7, 2015 9:08 PM
  • when I replace it with that code I get the error of Run Time Error 3061 Too Few Parameters. Expected 1.

    and it highlights the line: CurrentDb.Execute strSQL

    Tuesday, July 7, 2015 9:15 PM
  • The error is Run Time Error 3134, Syntax error in INSERT INTO

    This is the section:

     CurrentDb.Execute "INSERT INTO Test(PGroup, Topic, Subtopic, [Date1], Comments) " & _
                    "VALUES(" & Me.cbopgroup & ",'" & Me.cbotopic & "','" & _
                    Me.cbosubtopic & "','" & Me.txtdate1 & "','" & Me.txtcomments & "')"
       


    As I said, I didn't get an error on either SQL run.

    Use the code example just provided and put a break at the CurrentDB.Execute statement.  You will need to modify it a little.  I changed the table name from "Test" to "LMWilson."  Look to the immediate window and examine the SQL that was output to see if anything looks strange.

    If you can't see the error in the SQL then do the following:

    Copy the SQL statement from the immediate window.

    Press [Alt]+[F11] to return to the Access window. 

    Create a blank query and go to the SQL editor. 

    Paste the SQL string from the VBA immediate window and try to run your query.


    Tuesday, July 7, 2015 9:18 PM
  • I have my original code in there, but now I am only getting a compile error, syntax error and its highlighting this:

    where is the underscore missing?

     

    Else
            'this is for updating
            CurrentDb.Execute "UPDATE Test " & _
                " SET PGroup=" & Me.cbopgroup &
                ", Topic='" & Me.cbotopic & "'" & _
                ", Subtopic='" & Me.cbosubtopic & "'" & _
                ", Date1='" & Me.txtdate1 & "'" & _
                ", Comments='" & Me.txtcomments & "'" & _
                " WHERE PGroup=" & Me.cbopgroup.Tag
           
        End If

    Thursday, July 9, 2015 6:09 PM
  • I have my original code in there, but now I am only getting a compile error, syntax error and its highlighting this:

    where is the underscore missing?

     

    Else
            'this is for updating
            CurrentDb.Execute "UPDATE Test " & _
                " SET PGroup=" & Me.cbopgroup &
                ", Topic='" & Me.cbotopic & "'" & _
                ", Subtopic='" & Me.cbosubtopic & "'" & _
                ", Date1='" & Me.txtdate1 & "'" & _
                ", Comments='" & Me.txtcomments & "'" & _
                " WHERE PGroup=" & Me.cbopgroup.Tag
           
        End If


     " SET PGroup=" & Me.cbopgroup &
    Thursday, July 9, 2015 6:39 PM