Results 1 to 3 of 3
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: Can anyone tell me why do I get this error? Runtime error 3134

    Dear All,
    I have a form from which I raise invoices. I never had any problem with it but for some strange reason after I only added one more field to the query which is called SageInvNumber it is giving this error.

    The error is "Runtime Error 3134" Sytanx error in INSERT INTO statement.

    Below is the code behind the command button and the code breaks on DoCmd.RunSQL (strSQL)

    PHP Code:
    Private Sub cmdInvoiceClient_Click()
        
    Dim strAccountRef As String
        Dim strSQL 
    As String
        Dim intInvoiceExists 
    As Integer
        Dim ReportName 
    As String
        Dim rsNewDetails 
    As New Recordset    'Making a recordest of new entry, to update fields with financial figures
        
        
        '
    Me.Requery cant use requery since it's based on a query and sorted by date of order
        Me.InvoiceDate = Format(Date, "Medium Date")
        Me.Recalc
    If Len(Me.SageInvNumber) > 0 Then intInvoiceExists = 1
        
        ' 
    If invoice has already been created for this Booking ID then
        
    ' tell the user, otherwise create an invoice for this Booking ID.
        If intInvoiceExists = 1 Then
            MsgBox ("An invoice has already been created")
            
        Else
            '
    Create new record in Invoice table
            
    'The autonumber field Invoice.id becomes the unique Invoice number
            strSQL = "INSERT INTO Invoice ( Bookingid ) SELECT " & strAccountRef & ";"
            [COLOR="Yellow"]DoCmd.RunSQL (strSQL)[/COLOR]
        
            temp_SageInvNumber = Generate_Sage_Invoice(Bookingid)
        End If
        
        '
    Details for new invoice
        
        rsNewDetails
    .Open _
            
    "SELECT * " _
            
    "FROM Booking " _
            
    "WHERE id = " strAccountRef_
                CurrentProject
    .ConnectionadOpenKeysetadLockOptimistic
        
        
        With rsNewDetails
            
    !InvoiceDate Me.InvoiceDate
            
    !WorkHours Me.WorkHours
            
    !ClientRate Me.ClientRate
            
    !ClientExpenses Me.ClientExpenses
            
    !JourneyMiles Me.JourneyMiles
            
    !ClientRatePerMile Me.ClientRatePerMile
            
    !ClientAmountLessVAT Me.txtClientAmount
            
    If Len(temp_SageInvNumber) > 0 Then
                
    !SageInvNumber temp_SageInvNumber
            End 
    If
            .
    Update
            
    .Close
        End With

        
    'Emi 25-11-05
        temp_SageInvNumber = ""
        
        '
    Print InterpreterInvoice
        ReportName 
    "rptInvoiceClient"
            
    DoCmd.OpenReport _
            ReportName
    :=ReportName_
            View
    :=acViewPreview_
            wherecondition
    :="Booking.id = " Me.AccountRef
            
    'View:=acViewPreview to preview the fax
            '
    View:=acViewNormal to print the fax


    End Sub 
    I do apologise for posting such a long code. I would really appreciate any help.
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Just at a quick galance, from the error message, it sounds like you're trying to insert an imcorrect data type, i.e. you'tr trying to insert a number into a text field, text into a numerical field, or possibly one numerical datatpe into another numerical datatype that is too small to hold it. I'd check your table to see how the new field is defined then check against your code.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    May 2006
    Posts
    386
    Yes, as stated in my thread, I have created a new field in my main table "Booking" called "SageInvNumber" the datatype is "Number" and the code is supposed to pull out an invoice number from Sage which I think is a number datatype.
    Are you suggesting that I should change the datatype of my table "sageInvNumber" to text?

    thank you for your time.
    Emi-UK
    Love begets Love, Help Begets Help

Posting Permissions

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