Run-time error '-2147417848 (80010108)' - Again

[Antenne88]

Hi,
It seems this error is caused for a hundred different reasons. I had it before in the same project in a different procedure but was able to sort it out there.
But this time I'm stuck again.

The setup:

The project is an invoicing system where data from different userforms is passed to tables on different worksheets.

1. User enters data in "Enter Invoice" form and transfers data to 3 different worksheets by clicking a button.
2. User opens another userform from the "Enter Invoice" form (and on top of it) and enters data in "Enter Payment" form. Transfer of data to 2 different worksheets. "Enter Payment" form is closed.
3. User creates PDF for invoice by clicking a button on the "Enter Invoice" form. The PDF opens on top of the "Enter Invoice" form. User prints invoice directly from the PDF. PDF is then closed again.

I use the same procedure for all data transfers. I have not added the code because at this stage I think that is actually has nothing to do with the code itself.

The problem:

As long as the PDF is not actually printed the code does not break. I can enter as many invoices and payments as I like - as long as don't print the PDF (I use the printer button on Adobe Reader). If my user prints the PDFs the code breaks occasionally. Could be after 2 invoices/payments or after 10. Unpredictable.

My user is running this program on 2 different computers, entering all data twice - which is obviously very annoying - just to have a backup in case one program crashes. He runs the same version of MS Office (2013) and the same operating system (Windows 10) on both systems and I have the same setup here too. But this problem existed already when we were still on Windows 7.

As I mentioned already, the user is entering all invoices and payments on 2 different computers. The program crashes only on the computer where the PDF is actually printed. On the backup computer the PDF is created and opened too, in order to save it to the hard drive. But printing is only done regularly from the "main" computer, unless the program crashes - then the invoice is printed from the backup computer. During my testing here on my computer I do not print the PDF either, and I can tell you I already entered hundreds of invoices/payments for testing and debugging reasons and the code didn't break a single time. After a major change in the code my user entered about 30 invoices/payments in the main computer without printing the PDF and no error there either. There was no need to actually print the PDF, he only updated my changed version of the program.

The code always breaks in the sub that is used by all transfers. But it breaks only when the payment data is transferred to the payment sheet. Which is only one of 5 data transfers that are necessary to complete one invoice/payment cycle. It always breaks at the point where a new list row is added to the table.

My question: Has anybody an idea why the actual printing process could cause this error? My user is using a HP printer which is connected to the router and used by both computers.

Any help is very much appreciated!

 

[mole999]

post the code (between code tags) that actually breaks and indicate where, very difficult to identify a reason without seeing it

 

[Antenne88]

Hi mole999,

thanks for the fast reply. This is the procedure that breaks.
It breaks in the commented line. On the sheet itself the list row has actually been added before the code crashed.

Public Sub addDataToWorksheet(theSheet As Worksheet, theData() As Variant)

Dim theTable As ListObject
Dim theRow As ListRow
            
Set theTable = theSheet.ListObjects(1)
    
theSheet.Activate
    
Set theRow = theTable.ListRows.Add(theTable.ListRows.Count + 1)   'This line is marked when code breaks
    
theRow.Range.value = theData

End Sub

This is a part of the procedure on the "Enter Payment" form which is triggered when the "Save Payment" button is clicked.
The top part (Fill Journal) is transferring the data collected on the sheet to a worksheet called "Journal" - which never gives me any trouble.
The error always happens when the lower part (Fill Payment) runs.

'more code....

'Fill Journal

    'create and fill array to pass values to journal sheet
    Dim journalData(1 To 10) As Variant
    
    journalData(1) = nextPaymentNumber  'theNextPaymentNumber
    journalData(2) = Me.CBInvPackNumbers.value
    journalData(3) = paymentDate
    journalData(4) = clientName
    journalData(5) = ""
        
    If packageCode <> "" Then
        journalData(6) = packageCode
        journalData(10) = packSize
    Else
        journalData(6) = ""
        journalData(10) = ""
    End If
    
        journalData(7) = ""
        journalData(8) = paymentAmount
        journalData(9) = paidBy
        
    Call addDataToWorksheet(shJournal, journalData)    'always works fine
    
    
'Fill Payments

    'create and fill array to pass values to payments sheet
    Dim paymentData(0 To 8) As Variant
    
    paymentData(0) = nextPaymentNumber  'this is a Long value that is generated by the program and not entered by the user
    
'payments can be made either from "Enter Invoice" form or from "Enter Package" form.
'depending from which form the payment form has been entered, the second item in the array is either a long or a string

    If Me.LbInvPackNr.Caption = "Invoice Nr." Then
        paymentData(1) = invoiceNumber                             'paymentData(1) contains  a Long value
    ElseIf Me.LbInvPackNr.Caption = "Package Nr." Then
        paymentData(1) = packageNumber                           'paymentData(2) contains a String value
    End If
    
    paymentData(2) = InvoiceDate
    paymentData(3) = invoiceAmount
    paymentData(4) = clientName
    paymentData(5) = paymentDate
    paymentData(6) = paymentAmount
    paymentData(7) = tipAmount
    paymentData(8) = paidBy
        
    Call addDataToWorksheet(shPayments, paymentData)

'more code....

 

[Antenne88]

The data is collected from the "Enter Payment" userform, not from a sheet.
Also the comments should read paymentData(1) twice, not paymentData(2).
I tried to correct it, but it took me too long and my edit wasn't accepted as my 10 minutes were up.

 

[Antenne88]

This is the code that prints the invoice. The button is on the "Enter Invoice" form. A similar code runs from the "Enter Package" form.
Those variables that are not declared within this sub are declared on module level.

Private Sub cmdPrint_Click()

    Dim sh_PrintInvoice As Worksheet
    Dim rng_printArea As Range
    Dim path As String
            
    Set sh_PrintInvoice = ThisWorkbook.Sheets("Print Invoice")
    Set rng_printArea = sh_PrintInvoice.Range("Print_Area")
        
    path = "C:\EMT2\Invoices\"
    createNewFolder (path)           'creates a new folder for the current business year if it doesn't already exist
    
    invNrToPrint = Me.cboChooseInvoiceToPrint.value
        
    fillInvoiceToPrint (invNrToPrint)        'passes userform values to "Print Invoice" worksheet
    
    On Error GoTo solveit
    rng_printArea.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
    path & Year(Date) & "\" & sh_PrintInvoice.Range("PrintInvoice_Number") & "_" & sh_PrintInvoice.Range("PrintInvoice_Name") & ".pdf", Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True

       Exit Sub
    
solveit:
    theFinalMessage = "It seems the PDF for this invoice is already open." & vbNewLine & _
    "Please close the PDF and try again."
    CustomMsgBoxForm.showMessage (theFinalMessage)
    CustomMsgBoxForm.Show
    

End Sub