Verifying duplicate AP Invoice number 1

[Bluejay07]

Hello,

I'm trying to create a function that will check to see if an invoice number for a particular vendor already exists. If it does, then I can change the invoice number. My problem is that accpac returns a duplicate invoice number error message before my check is complete. I've tried with .read and .browse/fetch.

How can I check for duplicates without getting an error?

Public Function Check_Invioce_InUse(pValue As String, pVendor As String) As Boolean
'// Verify if the invoice has already been created.

   Dim APIBC As AccpacCOMAPI.AccpacView
   Dim APIBH As AccpacCOMAPI.AccpacView
   Dim APIBD As AccpacCOMAPI.AccpacView
   Dim APIBS As AccpacCOMAPI.AccpacView
   Dim APIBHO As AccpacCOMAPI.AccpacView
   Dim APIBDO As AccpacCOMAPI.AccpacView
      
   '// Set the views.
   DMRLIDBLink.OpenView "AP0020", APIBC
   DMRLIDBLink.OpenView "AP0021", APIBH
   DMRLIDBLink.OpenView "AP0022", APIBD
   DMRLIDBLink.OpenView "AP0023", APIBS
   DMRLIDBLink.OpenView "AP0402", APIBHO
   DMRLIDBLink.OpenView "AP0401", APIBDO

   '// Compose the views.
   APIBC.Compose Array(APIBH)
   APIBH.Compose Array(APIBC, APIBD, APIBS, APIBHO)
   APIBD.Compose Array(APIBH, APIBC, APIBDO)
   APIBS.Compose Array(APIBH)
   APIBHO.Compose Array(APIBH)
   APIBDO.Compose Array(APIBD)
   
   On Error GoTo ERR_Handler
   
   If Trim$(pValue) <> vbNullString And pVendor <> vbNullString Then
      With APIBH
         .Order = 1
         .Fields("IDVEND").Value = Trim$(pVendor)
         .Fields("IDINVC").Value = Trim$(pValue)  <-- ERRORS after this is processed.
         If .Read Then
            Check_Invioce_InUse = True
            Debug.Print "duplicate: " & pValue
         End If
         .Close
      End With
      Set APIBH = Nothing
   End If
   
'   If Trim$(pValue) <> vbNullString And Trim$(pVendor) <> vbNullString Then
'      With APIBH
'         .Browse "IDVEND = " & Trim$(pVendor) & " AND IDINVC = """ & Trim$(pValue) & """", True
'         If .Fetch Then Check_Invioce_InUse = True
'      End With
'      Set APIBH = Nothing
'   End If

   
   Exit Function
   
ERR_Handler:
   ErrorAccpac Err.Number, Err.Description, "modGeneral.Check_Invioce_InUse"
   'Resume Next
End Function

If at first you don't succeed, then sky diving wasn't meant for you!

 

[ettienne]

I use the CS0120 view and run a query like:
select Count(IDINVC) AS Invoices from APIBH where IDVEND='1200' and IDINVC='A123'

If Invoices = 0 then the invoice does not exist.

 

[Bluejay07]

Thanks ettienne.
I'll try that.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Bluejay07]

Your solution worked.
Thank you.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[ramam1]

Why APIBH and not APOBL?

 

[Jay Converse]

Because they might not be posted yet.

 

[DjangMan]

To ramam1's point - it would be better to check both because someone could purge posted batches. However, my answer is always "no" when asked if someone should purge data. (But there are exceptions to that, too.)

 

[Bluejay07]

Thanks for the comments and suggestions.

In this case, I'm importing data into a new company and it wouldn't have posted batches yet.

If at first you don't succeed, then sky diving wasn't meant for you!