Auto Upload Of Invoices

[pellj001]

I have created a vb script file to automatically upload invoices to AccPac. I am trying to insert a procedure that checks whether this invoices has already been uploaded. This is the code:

///////////////
Function CheckUploaded(ZRBRValue As String) As Boolean
Dim AccPacCon As New ADODB.Connection
Dim InvoiceSQL As New ADODB.Recordset
AccPacCon.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=ReadOnly;Password=readonly;Initial Catalog=TSTCOM;Data Source=SQL1"

InvoiceSQL.Open "SELECT COUNT(*) AS RECCOUNT FROM dbo.ARIBHO Where OPTFIELD = 'ZRBR' And VALUE = '" & ZRBRValue & "'", AccPacCon, adOpenForwardOnly, adLockOptimistic

If InvoiceSQL.Fields("RECCOUNT").Value > 0 Then
CheckUploaded = True
Else
CheckUploaded = False
End If
End Function
//////////////

This works but can I do the same query so that I use the active AccPac session, instead of creating an adodb connection?

 

[Jay Converse]

Open the AROBL or ARIBH view, then do a .Browse.

 

[pellj001]

Thanks for your reply. I am trying to do this as suggested but cannot get it to work.

//////////
Dim ARINVOICE3header As AccpacCOMAPI.AccpacView
Dim ARINVOICE3headerFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0032", ARINVOICE3header
Set ARINVOICE3headerFields = ARINVOICE3header.Fields


ARINVOICE3header.Browse "IDINVC = " & InvoiceHeader.Fields("IDINVC").Value, False
temp = ARINVOICE3header.Exists
//////////


However temp always return false, even if I already uploaded.

 

[Jay Converse]

1. Add the customer number to the .Browse
2. Check it with:

temp = .Fetch

 

[pellj001]

Thanks again. I have modified as suggested as follows but this still always returns false.

////////////////
ARRECMAC3header.Browse "((IDRMIT = " & ReceiptHeader.Fields("IDRMIT").Value & ") AND (IDCUST = " & ReceiptHeader.Fields("IDCUST").Value & "))", 0

temp = ARRECMAC3header.Fetch
////////////////

I have tried also putting the actual values as below but still no luck

//////////////////

ARRECMAC3header.Browse "((IDRMIT = ""1-17"") AND (IDCUST = ""MYSLVALUE""))", 0

//////////////////

 

[Jay Converse]

You're getting confused. IDRMIT is a receipt, not an invoice.

 

[pellj001]

Oh sorry for that because I am trying to do both invoice and receipts. I have the same problem for both.

However, this is the check for the invoicing i am trying to make.

////////////
ARINVOICE3header.Browse "((IDINVC = '123456') AND (IDCUST = 'MYCUSTOMER'))", True
temp = ARINVOICE3header.Fetch
////////////

I have tried different variations, like removing the quotes, inserting duplicate quotes, but still temp returns false.

Thanks again, Joseph

 

[Jay Converse]

.Browse strings are not quoted like SQL. Try this:

ARINVOICE3header.Browse "IDINVC = 123456 AND IDCUST = MYCUSTOMER", True

or this:

ARINVOICE3header.Browse "IDINVC = """ & sInvoice & """ AND IDCUST = """ & sCustomer & """", True

 

[pellj001]

I have tried both but still no luck. I think I am missing something but cant figure out. This is the full function which returns the true/false result. Maybe please, you can spot a flaw.

////////////
Function CheckUploaded(Customer As String, InvoiceNo As String) As Boolean
Dim AccPacLink As AccpacCOMAPI.AccpacDBLink
Dim InvoiceHeader As AccpacCOMAPI.AccpacView
Set AccPacLink = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

AccPacLink.OpenView "AR0032", InvoiceHeader

InvoiceHeader.Browse "IDINVC = """ & InvoiceNo & """ AND IDCUST = """ & Customer & """", True
CheckUploaded = InvoiceHeader.Fetch
End Function

 

[Jay Converse]

It looks fine to me, this is a simple process, I do it all the time. Are you sure those invoices really exist in an existing batch?

 

[ettienne]

Try doing InvoiceHeader.Init before the InvoiceHeader.Browse

 

[pellj001]

Yes, of course they exist. I am uploading it using the macro, and then upload it again, but still no luck. Also, it is taking a lot of time (minutes) to fetch.

Thanks again for all your help
Joseph

 

[Jay Converse]

InvoiceHeader.Order = 1

 

[pellj001]

Ok now it's working. I found out what the problem was. Actually there is a process which when uploaded replaces the invoice number and stories it in the PO Number. The actual invoice number is the one generated by AccPac which I cannot determine during the upload routine.


Anyway, thanks very much for your help.

 

[pellj001]

I am trying to do the same for the receipts. It is working but it is slow. I am searching by the IDRMIT and IDCUST. Is there any trick to do this more fast like .Order = ??

 

[DjangMan]

Yes - use an ADO query.

Or, if you are using version 5.5 or 5.6 you can open view CS0120 which will let you run SQL against the database directly.

You can use the Accpac Object Model documentation to see which indexes are best suited for your searches.

Keep in mind that if you change the .order of a view make sure you change it back to 0 before you post transactions. I've had mysterious errors appear only to be solved by changing the .order before posting.

 

[pellj001]

Thanks DjangMan that would have also solved my original problem, as I believe using view CS0120 uses the AccPac active session. I have version 5.5.

The macro creates by ACCPAC generates several entries like:

temp = ARRECMAC3batch.Exists

Can these be safely removed?

 

[DjangMan]

Yes.