Results 1 to 8 of 8
  1. #1
    carmenv323 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Location
    Massachusetts
    Posts
    78

    Insert Query causing Error 2766

    I have a form that opens to a specific table - I'm able to update the table while the form is open but I'm not able to append to it. the error is with the tblRequests table which is the table for my Form.



    in my code, strSQL, strSQLa, and strSQLb update the tables just fine, strSQLc will not append and gives me the error, however when I run the Append query manually it appends just fine. Can you help me figure it out? I thought that maybe it's because the form is open but how can I append the record to the table? I need to have the indicated field be the same...

    Code:
    Private Sub Form_AfterUpdate()
       On Error GoTo ErrHandler
      
       If tSave <> 1 Then Exit Sub
        Dim db As DAO.Database
        Set db = CurrentDb
       
        DoCmd.SetWarnings False
       
        Dim strSQL, strSQLa, strSQLb, strSQLc As String
       
        '=====================================================
        '    UPDATE tblRequests STATUS TO "Pending Request"
        '=====================================================
        strSQL = "Update tblRequests Set tblRequests.[cboEstimateStatus]  = 'Pending Request' where tblRequests.[txtProjectNumber] = '" & Me!txtProjectNumber & "'"
        Debug.Print strSQL
        DoCmd.RunSQL strSQL
       
        '=====================================================
        '    UPDATE tblMultipleRequests STATUS TO "Pending Request"
        '=====================================================
        strSQLa = "Update tblMultipleRequests Set tblMultipleRequests.[cboEstimateStatus]  = 'Pending Request' where tblMultipleRequests.[txtProjectNumber] = '" & Me!txtProjectNumber & "'"
        Debug.Print strSQLa
        DoCmd.RunSQL strSQLa
    
     
        '=====================================================
        '    UPDATE tblMultipleRequests FIELDS = tblRequests FIELDS
        '=====================================================
        strSQLb = _
        "UPDATE tblMultipleRequests Set " & _
            "tblMultipleRequests.txtTaskCode = [tblRequests].[txtTaskCode], tblMultipleRequests.txtQPNumber = [tblRequests].[txtQPNumber],  tblMultipleRequests.cboEstimatingAction = [tblRequests].[cboEstimatingAction], tblMultipleRequests.cboEstimatePurpose = [tblRequests].[cboEstimatePurpose],  " & _
            "tblMultipleRequests.cboEstimateGrade = [tblRequests].[cboEstimateGrade], tblMultipleRequests.txtStatusNotes = [tblRequests].[txtStatusNotes],  tblMultipleRequests.dtRequestDate = [tblRequests].[dtRequestDate], tblMultipleRequests.dtISD = [tblRequests].[dtISD],  " & _
            "tblMultipleRequests.dtRequestedCompletion = [tblRequests].[dtRequestedCompletion], tblMultipleRequests.RequestByPerson = [tblRequests].[RequestByPerson],  tblMultipleRequests.cboRequestByOrganization = [tblRequests].[cboRequestByOrganization], tblMultipleRequests.txtReqOrg = [tblRequests].[txtReqOrg],  " & _
            "tblMultipleRequests.cboProjectDesignPhase = [tblRequests].[cboProjectDesignPhase], tblMultipleRequests.cboVoltageClass = [tblRequests].[cboVoltageClass], tblMultipleRequests.txtSLOther = [tblRequests].[txtSLOther], tblMultipleRequests.txtScopeDescription = [tblRequests].[txtScopeDescription],  " & _
            "tblMultipleRequests.txtPreviousEstimate = [tblRequests].[txtPreviousEstimate], tblMultipleRequests.txtNotes = [tblRequests].[txtNotes],  tblMultipleRequests.txtProjectDeliverablesFolder = [tblRequests].[txtProjectDeliverablesFolder], tblMultipleRequests.IsProgram = [tblRequests].[IsProgram],  " & _
            "tblMultipleRequests.IsRelease = [tblRequests].[IsRelease], tblMultipleRequests.txtProgramEstimate = [tblRequests].[txtProgramEstimate],  tblMultipleRequests.cboElectConst = [tblRequests].[cboElectConst], tblMultipleRequests.cboCivilConst = [tblRequests].[cboCivilConst],  " & _
            "tblMultipleRequests.txtExecutePlnNotes = [tblRequests].[txtExecutePlnNotes], tblMultipleRequests.cboSiting = [tblRequests].[cboSiting],  tblMultipleRequests.IsSiting = [tblRequests].[IsSiting], tblMultipleRequests.txtSiting = [tblRequests].[txtSiting],  " & _
            "tblMultipleRequests.IsSCS = [tblRequests].[IsSCS], tblMultipleRequests.cboSCS = [tblRequests].[cboSCS],  tblMultipleRequests.txtSCS = [tblRequests].[txtSCS],  tblMultipleRequests.bFinancialConstraints = [tblRequests].[bFinancialConstraints], tblMultipleRequests.txtFinancialConstraint = [tblRequests].[txtFinancialConstraint],  " & _
            "tblMultipleRequests.bConstructabilityReview = [tblRequests].[bConstructabilityReview], tblMultipleRequests.bExternalCustomer = [tblRequests].[bExternalCustomer],  tblMultipleRequests.txtExternalCustomer = [tblRequests].[txtExternalCustomer], tblMultipleRequests.bProjectDeliverables = [tblRequests].[bProjectDeliverables],  " & _
            "tblMultipleRequests.txtCCEMails = [tblRequests].[txtCCEMails], tblMultipleRequests.bMultipleEstimates = [tblRequests].[bMultipleEstimates],  tblMultipleRequests.bEnvironmentalReceived = [tblRequests].[bEnvironmentalReceived], tblMultipleRequests.bPriors = [tblRequests].[bPriors],  " & _
            "tblMultipleRequests.Priors = [tblRequests].[Priors], tblMultipleRequests.PriorsWBS1 = [tblRequests].[PriorsWBS1], tblMultipleRequests.PriorsWBS2 = [tblRequests].[PriorsWBS2],  tblMultipleRequests.PriorsWBS3 = [tblRequests].[PriorsWBS3], tblMultipleRequests.PriorsWBS4 = [tblRequests].[PriorsWBS4], tblMultipleRequests.PriorsWBS5 = [tblRequests].[PriorsWBS5],  " & _
            "tblMultipleRequests.PriorsWBS6 = [tblRequests].[PriorsWBS6], tblMultipleRequests.PriorsWBS7 = [tblRequests].[PriorsWBS7], tblMultipleRequests.PriorsWBS8 = [tblRequests].[PriorsWBS8],  tblMultipleRequests.PriorsWBS9 = [tblRequests].[PriorsWBS9], tblMultipleRequests.PriorsWBS10 = [tblRequests].[PriorsWBS10], tblMultipleRequests.PriorsWBS11 = [tblRequests].[PriorsWBS11],  " & _
            "tblMultipleRequests.PriorsWBS12 = [tblRequests].[PriorsWBS12], tblMultipleRequests.PriorsWBS13 = [tblRequests].[PriorsWBS13], tblMultipleRequests.PriorsWBS14 = [tblRequests].[PriorsWBS14], tblMultipleRequests.PriorsWBS15 = [tblRequests].[PriorsWBS15],  " & _
            "tblMultipleRequests.PriorsWBS16 = [tblRequests].[PriorsWBS16] WHERE tblMultipleRequests.[txtProjectNumber] = '" & Me!txtProjectNumber & "'"
        Debug.Print strSQLb
        DoCmd.RunSQL strSQLb
       
        '=====================================================
        '    APPEND tblMultipleRequests FIELDS TO tblRequests FIELDS
        '=====================================================
    strSQLc = _
        "INSERT INTO tblRequests(txtTaskCode, txtQPNumber, cboEstimatingAction, cboEstimatePurpose, cboEstimateGrade, txtStatusNotes, dtRequestDate, dtISD, dtRequestedCompletion, RequestByPerson, cboRequestByOrganization, txtReqOrg, cboProjectDesignPhase, cboVoltageClass, txtSLOther, txtScopeDescription, txtPreviousEstimate,  " & _
            "txtNotes, txtProjectDeliverablesFolder, IsProgram, IsRelease, txtProgramEstimate, cboElectConst, cboCivilConst, txtExecutePlnNotes, cboSiting, IsSiting, txtSiting, IsSCS, cboSCS, txtSCS, bFinancialConstraints, txtFinancialConstraint, bConstructabilityReview, bExternalCustomer, txtExternalCustomer, bProjectDeliverables, txtCCEMails,  " & _
            "bMultipleEstimates, bEnvironmentalReceived, bPriors, Priors, PriorsWBS1, PriorsWBS2, PriorsWBS3, PriorsWBS4, PriorsWBS5, PriorsWBS6, PriorsWBS7, PriorsWBS8, PriorsWBS9, PriorsWBS10, PriorsWBS11, PriorsWBS12, PriorsWBS13, PriorsWBS14, PriorsWBS15, PriorsWBS16)" & _
        "SELECT tblMultipleRequests.txtTaskCode, tblMultipleRequests.txtQPNumber, tblMultipleRequests.cboEstimatingAction, tblMultipleRequests.cboEstimatePurpose, tblMultipleRequests.cboEstimateGrade, tblMultipleRequests.txtStatusNotes, tblMultipleRequests.dtRequestDate, tblMultipleRequests.dtISD, tblMultipleRequests.dtRequestedCompletion,  " & _
            "tblMultipleRequests.RequestByPerson, tblMultipleRequests.cboRequestByOrganization, tblMultipleRequests.txtReqOrg, tblMultipleRequests.cboProjectDesignPhase, tblMultipleRequests.cboVoltageClass, tblMultipleRequests.txtSLOther, tblMultipleRequests.txtScopeDescription, tblMultipleRequests.txtPreviousEstimate, tblMultipleRequests.txtNotes,  " & _
            "tblMultipleRequests.txtProjectDeliverablesFolder, tblMultipleRequests.IsProgram, tblMultipleRequests.IsRelease, tblMultipleRequests.txtProgramEstimate, tblMultipleRequests.cboElectConst, tblMultipleRequests.cboCivilConst, tblMultipleRequests.txtExecutePlnNotes, tblMultipleRequests.cboSiting, tblMultipleRequests.IsSiting, tblMultipleRequests.txtSiting,  " & _
            "tblMultipleRequests.IsSCS, tblMultipleRequests.cboSCS, tblMultipleRequests.txtSCS, tblMultipleRequests.bFinancialConstraints, tblMultipleRequests.txtFinancialConstraint, tblMultipleRequests.bConstructabilityReview, tblMultipleRequests.bExternalCustomer, tblMultipleRequests.txtExternalCustomer, tblMultipleRequests.bProjectDeliverables, tblMultipleRequests.txtCCEMails,  " & _
            "tblMultipleRequests.bMultipleEstimates, tblMultipleRequests.bEnvironmentalReceived, tblMultipleRequests.bPriors, tblMultipleRequests.Priors, tblMultipleRequests.PriorsWBS1, tblMultipleRequests.PriorsWBS2, tblMultipleRequests.PriorsWBS3, tblMultipleRequests.PriorsWBS4, tblMultipleRequests.PriorsWBS5, tblMultipleRequests.PriorsWBS6, tblMultipleRequests.PriorsWBS7,  " & _
            "tblMultipleRequests.PriorsWBS8, tblMultipleRequests.PriorsWBS9, tblMultipleRequests.PriorsWBS10, tblMultipleRequests.PriorsWBS11, tblMultipleRequests.PriorsWBS12, tblMultipleRequests.PriorsWBS13, tblMultipleRequests.PriorsWBS14, tblMultipleRequests.PriorsWBS15, tblMultipleRequests.PriorsWBS16  " & _
        "WHERE tblMultipleRequests.[txtProjectNumber] = '" & Me!txtProjectNumber & "'"
       
        Debug.Print strSQLc
        DoCmd.RunSQL strSQLc
       
        
        DoCmd.SetWarnings True
       
     
    '    db.Execute "Delete * From tblMultipleRequests", dbFailOnError
    
    '    DoCmd.Close acForm, "frmRequests"
      
    '    DoCmd.OpenForm "NavigationForm"
     
        'Me.Requery
       ' Me.KeyPreview = False
       ' Me.dtRequestDate = Format(Now, "mm/dd/yyyy")
       ' Me.RequestByPerson.Value = ResolveCurrentUserName
       
    ExitHandler:
        'DoCmd.SetWarnings True
        tSave = 0
        Set db = Nothing
        Exit Sub
       
    ErrHandler:
       MsgBox Err.Description, , "Error #" & Err.Number
       Resume ExitHandler
    End Sub

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    A quick look finds a missing space before SELECT


    Code:
     PriorsWBS15, PriorsWBS16)" & _
        "SELECT tblMultipleRequests.txtTaskCode, tblMultipleRequests.txtQPNumber, tblMultipleRequests.cboEstimatingAction, tblMultipleRequests.cboEstimat
    And I think you need to specify the source table

    Code:
    tblMultipleRequests.PriorsWBS16 From tblMultipleRequests " & _
        "WHERE tblMultipleRequests.[txtProjectNumber] = '" & Me!txtProjectNumber & "'"
    Of course the number of receiving fields must equal the number of sending fields. There are over 50 so I'll leave the counting to you.


    And in case txtProjectNumber is actually a number

    Code:
     "WHERE tblMultipleRequests.[txtProjectNumber] = " & txtProjectNumber
    Last edited by davegri; 11-30-2021 at 08:47 PM. Reason: added source table and need for delimiters

  3. #3
    carmenv323 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Location
    Massachusetts
    Posts
    78
    Not sure what you mean...I tried to remove the spaces as much as it would let me - the error is:
    The object doesn't contain the Automation object 'tblRequests."

    Should I change the code here?
    Code:
    "WHERE tblMultipleRequests.[txtProjectNumber] = '" & Me!txtProjectNumber & "'"
    Me! is technically tblRequests ()

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Should I change the code here?
    Yes. In strSQLc if it's numeric.

    Not sure what you mean...I tried to remove the spaces as much as it would let me
    You misunderstand, I'm asking you to ADD space. " Select.....

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Me! is technically tblRequests ()
    Me refers to the FORM that the code is behind. You'll find that Me. will work better than Me! because Me. (dot) will provide intellisense showing you the possible valid entries.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI, I just wanted to make a few comments on issues I see in the code.


    - You should use "dB.Execute strSQL, dbFailOnError" to execute the SQL - don't mess around with having to set warnings true/false.

    - You have a line "Dim strSQL, strSQLa, strSQLb, strSQLc As String"
    Only strSQLc is declared as a string. All other variables are Variants. You MUST explicitly declare each variable type. I would use
    Code:
        Dim strSQL  As String
        Dim strSQLa As String
        Dim strSQLb As String
        Dim strSQLc As String

    - What is tSave?? A field in the form record set? A control on the form? An undeclared variable in the code?

    - Do you have Option Explicit as the 2nd line in ALL modules?


    In the attached text file, I reformatted your code the way I would write it, just to show you a different coding method.
    Please note that I included davegr's suggestions.

    I capitalize SQL keywords and break lines at the keywords.
    I have a space at the beginning of the line, not at the end.That way I can easily see if I missed a space in the SQL.
    I break the lines into shorter lengths to make it easier to read.
    I NEVER use "DoCmd.RunSQL" to execute the SQL: I always use "dB.Execute" (or CurrentdB.Execute)


    In the tables "tblMultipleRequests" and "tblRequests", there are repeating fields "PriorsWBS1", "PriorsWBS2", ...... "PriorsWBS16". To me, this indicates your table structures are not normalized. Obviously, I don't know what the purpose of your dB is, but maybe the design deserves another look.


    Again, the attached text file is the way I would structure the code.
    Attached Files Attached Files

  7. #7
    carmenv323 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Location
    Massachusetts
    Posts
    78
    Request Database.zip

    Thanks Steve!

    I am trying your code and now I'm getting an error called #3061 Too few parameters, expected 58 but I just don't see where the issue lies.... this is on strSQLb where the fields update and it's not getting to the append at all now.

    I've attached the database, I hope this helps. tSave is used because I have similar forms in case they have to go back and finish - you'll see it in the module.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So I've been looking over your dB - it would have helped to also have the BE to be able to see the field names and data types and the relationships.
    Again, the top 2 lines of EVERY MODULE should be
    Code:
    Option Compare Database
    Option Explicit
    You can set an option to have Option Explicit added automatically to any NEW modules (in the IDE -> TOOLS -> Options -> Editor tab -> Check "Require Variable Declaration".
    When you add the "OPTION Explicit" line, you will find many errors in the code.

    ---------------------------------------------

    The BE is on OneDrive?? Not a good idea. (BE = C:\Users\VAZQUC\OneDrive - Eversource Energy\My Documents\Cost Estimating Log_BE.accdb)

    ---------------------------------------------

    You named a control (Previous Estimate(s) as "Previous year rev / reference #". It had spaces and special characters...

    ---------------------------------------------

    I saw quite a lot of field names beginning with "cbo". (cboSiting, cboSCS, cboProjectDesignPhase,...) Are these Look-up FIELDS?

    ---------------------------------------------

    As to the error for the Update code....
    Code:
        strSQLb = "UPDATE tblMultipleRequests Set"
        strSQLb = strSQLb & " tblMultipleRequests.txtTaskCode = [tblRequests].[txtTaskCode],"

    This will not execute because Access doesn't know what value to update the field to.

    Syntax:
    UPDATE table SET newvalue WHERE criteria
    If the data type of "txtTaskCode" is Text, you could write
    Code:
        strSQLb = "UPDATE tblMultipleRequests Set"
        strSQLb = strSQLb & " tblMultipleRequests.txtTaskCode = 'Code 1',"
    or, if there was a form open with the data, you could use
    Code:
        strSQLb = "UPDATE tblMultipleRequests Set"
        strSQLb = strSQLb & " tblMultipleRequests.txtTaskCode = '" & Forms!frmRequests.txtTaskCode & "', "
    But you would have to specify which record to use.

    Just having a table name and a field ([tblRequests].[txtTaskCode]), Access doesn't know how to resolve the reference.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error with INSERT INTO query
    By mdavid in forum Forms
    Replies: 2
    Last Post: 11-17-2017, 06:38 AM
  2. StrConv causing a read only error?
    By snipe in forum Forms
    Replies: 3
    Last Post: 08-12-2014, 11:02 AM
  3. Replies: 3
    Last Post: 03-05-2013, 11:17 AM
  4. Error 2766
    By Alex Motilal in forum Queries
    Replies: 5
    Last Post: 04-05-2012, 12:00 AM
  5. Query Criteria Causing Data Mismatch Error
    By jrubenol in forum Queries
    Replies: 2
    Last Post: 09-14-2011, 09:34 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums