Invalid Tax Class When Inserting Invoice Detail

[NeilN]

I've got a VBA script which inserts A/R invoices into ACCPAC 5.5. Works fine over the last four years for two databases (was tweaked as ACCPAC was updated). Now a third database has been set up and I get this error when inserting an invoice detail line: Tax class 0 is not in the range of 1 through 10.

Code:
ARINVOICE1detail1.Init
ARINVOICE1detail1Fields("IDACCTREV").Value = CStr(rsInvoiceDetail.Fields.Item(1).Value) ' Revenue Account
ARINVOICE1detail1Fields("AMTEXTN").Value = CStr(rsInvoiceDetail.Fields.Item(0).Value) ' Extended Amount w/ TIP
ARINVOICE1detail1.Insert

I've never had to do anything with tax classes before. The only tax-related code I have appears before the above snippet:
ARINVOICE1headerFields("CODETAXGRP").Value = "GPST"

Any ideas on what is going wrong here?

 

[ettienne]

Check the tax group for the vendor, this should fix it:
Edit the vendor and change the tax group and save the changes, then change the tax group back to what it was and save again.

 

[NeilN]

I apologize if I'm misunderstanding you.

I went to the A/R Customers module, pulled up the customer and went to the invoicing tab. The Tax Group is GPST. I cannot change or delete this as there is only one Tax Group set up. I reselected it and saved the record. The same error occurs.

 

[ettienne]

Sorry, I meant customer.
Something is wrong in the customer's tax setup, in order to change it you need to change to another tax group, save the record, then change it back and save again. Just selecting the same tax group and saving has no effect at all.
Create a new tax group just for the purpose of being able to change the tax group on the customer.

 

[DjangMan]

I've seen that tax group problem show up when customers are being created through automation. Everything looks correct and finally we give up and try what ettienne suggested and everything suddenly works.

 

[NeilN]

The customers *are* being created through automation. I'm waiting for the accounting group to create a new tax group so I can test etienne's suggestion.

The strange thing is, the other two databases also have automated customer creation and invoice creation works fine in them. If etienne's suggestion works, do you have any ideas on how I can "fix" all the customers? Changing all 1000+ by hand will be a real pain, not to mention dealing with future customer records.

 

[ettienne]

How are the customers created? Using the Accpac API or writing to the database?

 

[DjangMan]

Somewhere in the tax setup of the new company something isn't set up fully. I wish I new where so I could help another client with this process. Exporting and importing should do the trick. Export the customers and tax group. Copy the tax group column and then change the tax group column values to the new tax group. Import. Swtich the tax group columns to the original value and import again. There are faster ways to do that but they require much more typing.

 

[ettienne]

You can export/import the customers, change the tax group on the first import to the new tax group and then change it back on the second import.
Waiting on the accounting group? It takes about 5 seconds to create a tax group...

 

[NeilN]

Customers are created using the API. I've read enough posts on here to firmly realize that directly updating the database is Not a Good Idea Regarding tax group, I know but there are procedures...

ARCUSTOMER1Fields("IDCUST").Value = "W" + CStr(rsClients.Fields.Item(0).Value)

ARCUSTSTAT2.Init
ARCUSTSTAT2Fields("IDCUST").PutWithoutVerification ("W" + CStr(rsClients.Fields.Item(0).Value)) ' Customer Number
ARCUSTSTAT2Fields("CNTYR").PutWithoutVerification ("2009") ' Year
ARCUSTSTAT2Fields("CNTPERD").Value = "05" ' Period

ARCUSTCMT3.Init
ARCUSTCMT3Fields("IDCUST").PutWithoutVerification ("W" + CStr(rsClients.Fields.Item(0).Value)) ' Customer Number
ARCUSTCMT3.Browse "(IDCUST = ""W" + CStr(rsClients.Fields.Item(0).Value) + """)", 1
ARCUSTOMER1Fields("NAMECUST").Value = rsClients.Fields.Item(1).Value ' Customer Name

ARCUSTOMER1Fields("IDGRP").Value = "TST" ' Group Code
ARCUSTOMER1Fields("TEXTSNAM").Value = Left(rsClients.Fields.Item(1).Value, 15) ' Short Name

ARCUSTOMER1Fields("CODETAXGRP").Value = "GPST" ' Tax Group
If Not (IsNull(rsClients.Fields.Item(2).Value)) Then _
ARCUSTOMER1Fields("TEXTSTRE1").PutWithoutVerification (rsClients.Fields.Item(2).Value) ' Address Line 1
If Not (IsNull(rsClients.Fields.Item(3).Value)) Then _
ARCUSTOMER1Fields("TEXTSTRE2").PutWithoutVerification (rsClients.Fields.Item(3).Value) ' Address Line 2
If Not (IsNull(rsClients.Fields.Item(4).Value)) Then _
ARCUSTOMER1Fields("TEXTSTRE3").PutWithoutVerification (rsClients.Fields.Item(4).Value) ' Address Line 3
If Not (IsNull(rsClients.Fields.Item(5).Value)) Then _
ARCUSTOMER1Fields("NAMECITY").PutWithoutVerification (rsClients.Fields.Item(5).Value) ' City
If Not (IsNull(rsClients.Fields.Item(6).Value)) Then _
ARCUSTOMER1Fields("CODESTTE").PutWithoutVerification (rsClients.Fields.Item(6).Value) ' State/Prov.
If Not (IsNull(rsClients.Fields.Item(7).Value)) Then _
ARCUSTOMER1Fields("CODEPSTL").PutWithoutVerification (rsClients.Fields.Item(7).Value) ' Zip/Postal Code
If Not (IsNull(rsClients.Fields.Item(8).Value)) Then _
ARCUSTOMER1Fields("CODECTRY").PutWithoutVerification (rsClients.Fields.Item(8).Value) ' Country
If Not (IsNull(rsClients.Fields.Item(9).Value)) Then _
ARCUSTOMER1Fields("TEXTPHON1").PutWithoutVerification (CleanNumbers(rsClients.Fields.Item(9).Value)) ' Phone Number
If Not (IsNull(rsClients.Fields.Item(10).Value)) Then _
ARCUSTOMER1Fields("TEXTPHON2").PutWithoutVerification (CleanNumbers(rsClients.Fields.Item(10).Value)) ' Fax Number
If Not (IsNull(rsClients.Fields.Item(11).Value)) Then _
ARCUSTOMER1Fields("NAMECTAC").PutWithoutVerification (rsClients.Fields.Item(11).Value) ' Contact Name
If Not (IsNull(rsClients.Fields.Item(12).Value)) Then _
ARCUSTOMER1Fields("EMAIL1").PutWithoutVerification (rsClients.Fields.Item(12).Value) ' Contact's E-mail
If Not (IsNull(rsClients.Fields.Item(13).Value)) Then _
ARCUSTOMER1Fields("CTACPHONE").PutWithoutVerification (CleanNumbers(rsClients.Fields.Item(13).Value)) ' Contact's Phone
If Not (IsNull(rsClients.Fields.Item(14).Value)) Then _
ARCUSTOMER1Fields("CTACFAX").PutWithoutVerification (CleanNumbers(rsClients.Fields.Item(14).Value)) ' Contact's Fax


ARCUSTOMER1.Insert

 

[ettienne]

That seems fine, I'm assuming you do a ARCUSTOMER1.Init before all of that.
Just some tips:
1. You can leave out ARCUSTSTAT2 and ARCUSTCMT3 - unless you are specifically putting data there, but I do not see anything saved there.
2. Instead of If Not(IsNull(...)) you can ARCUSTOMER1Fields("TEXTSTRE1").PutWithoutVerification "" & rsClients.Fields.Item(2).Value

 

[NeilN]

Ok, a new tax group was created. I went into ACCPAC, pulled up the customer record, changed the tax group, saved, reverted back to the original tax group, and saved again. Still get same error. I then changed the tax group for the customer and did not change it back. Same issue. Any other ideas?

 

[ettienne]

What happens when you enter an invoice in AR?

 

[NeilN]

Entering an invoice manually works.

 

[ettienne]

OK, then you must be missing something in your routine.
Record another macro and run it, see if you can get that going.