Inserting customer Macro

[flpro]

Hi there,

Basically I'm looking at the code from a macro created within ACCPAC 5.2 to insert a new customer. I need 2 things explained.

1) How many tables in SQL Server are being modified?

2) I know ARCUS is being used but what tables do the views:
mDBLinkCmpRW.OpenView "AR0022", ARCUSTSTAT2
and
mDBLinkCmpRW.OpenView "AR0021", ARCUSTCMT3
refer to?

So basically it seems to me when a new customer is added with minimum information, 3 tables need to be updated, the ARCUS and 2 unknown tables.


Thank you.

Here's the code from the macro.

*********************************************
Sub MainSub()
'
' ACCPAC Macro file: C:\Program Files\ACCPAC\MACROS\newcust.AVB
' Recorded at: Sat Jun 24 11:38:03 2006
'

On Error GoTo ACCPACErrorHandler

Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

Dim mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkSysRW = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)

Dim ARCUSTOMER1 As AccpacCOMAPI.AccpacView
Dim ARCUSTOMER1Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0024", ARCUSTOMER1
Set ARCUSTOMER1Fields = ARCUSTOMER1.Fields


Dim ARCUSTSTAT2 As AccpacCOMAPI.AccpacView
Dim ARCUSTSTAT2Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0022", ARCUSTSTAT2
Set ARCUSTSTAT2Fields = ARCUSTSTAT2.Fields


Dim ARCUSTCMT3 As AccpacCOMAPI.AccpacView
Dim ARCUSTCMT3Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0021", ARCUSTCMT3
Set ARCUSTCMT3Fields = ARCUSTCMT3.Fields


ARCUSTOMER1.Init
ARCUSTSTAT2.Init

ARCUSTSTAT2Fields("CNTYR").PutWithoutVerification ("2006") ' Year
ARCUSTSTAT2Fields("CNTPERD").Value = "06" ' Period

ARCUSTSTAT2.Init

ARCUSTSTAT2Fields("CNTYR").PutWithoutVerification ("2006") ' Year
ARCUSTSTAT2Fields("CNTPERD").Value = "06" ' Period

ARCUSTCMT3.Init
ARCUSTCMT3.Browse "(IDCUST = "" "")", 1
ARCUSTCMT3.Fetch
ARCUSTOMER1Fields("IDCUST").Value = "SOSAH" ' Customer Number
ARCUSTSTAT2.Init

ARCUSTSTAT2Fields("IDCUST").PutWithoutVerification ("SOSAH") ' Customer Number
ARCUSTSTAT2Fields("CNTYR").PutWithoutVerification ("2006") ' Year
ARCUSTSTAT2Fields("CNTPERD").Value = "06" ' Period

ARCUSTCMT3.Init

ARCUSTCMT3Fields("IDCUST").PutWithoutVerification ("SOSAH") ' Customer Number

ARCUSTCMT3.Browse "(IDCUST = ""SOSAH "")", 1
ARCUSTOMER1Fields("NAMECUST").Value = "Hector Sosa" ' Customer Name

ARCUSTOMER1Fields("IDGRP").Value = "5" ' Group Code

ARCUSTOMER1Fields("CODETAXGRP").Value = "1" ' Tax Group
ARCUSTOMER1Fields("TEXTSTRE1").PutWithoutVerification ("123 main streest") ' Address Line 1
ARCUSTOMER1Fields("NAMECITY").PutWithoutVerification ("fort lauderdale") ' City
ARCUSTOMER1Fields("CODESTTE").PutWithoutVerification ("Fl") ' State/Prov.
ARCUSTOMER1Fields("CODEPSTL").PutWithoutVerification ("33312") ' Zip/Postal Code
ARCUSTOMER1Fields("TEXTPHON1").PutWithoutVerification ("954-123-4567") ' Phone Number
ARCUSTOMER1Fields("AMTCRLIMT").PutWithoutVerification ("150.000") ' Credit Limit (Cust. Curr.)

ARCUSTOMER1.Insert
ARCUSTOMER1.Update




Exit Sub

ACCPACErrorHandler:
Dim lCount As Long
Dim lIndex As Long

If Errors Is Nothing Then
MsgBox Err.Description
MsgBox "VBA Macros cannot run where Accpac is deployed as a Web Server"
Else
lCount = Errors.Count

If lCount = 0 Then
MsgBox Err.Description
Else
For lIndex = 0 To lCount - 1
MsgBox Errors.Item(lIndex)
Next
Errors.Clear
End If
Resume Next

End If

End Sub

 

[jayconverse]

Customer statistics, and Customer Comments. Go here for a full list:

http://support.accpac.com/techsupport/aom/advantage.asp

Jay Converse
IT Director
Systemlink, Inc.

 

[ettienne]

You only need ARCUST to add a customer, the other 2 tables are not required, unless you want to add comments and stats.
Comment out the lines in your macro that refer to ARCUSTSTAT2 and ARCUSTCMT3.

 

[DjangMan]

The documentation that Jay has directed you to will also give you the table names.

However, you shouldn't need to worry about the tables if you are using the views. When you get to 5.3 you are going to have more tables added to the list above and perhaps even more when 5.4 is released.

Regards,

Django

 

[flpro]

Thanks Guys,

Also another question. I have seen in other posts that some people are doing this right from vb.net. Do you know if Visual Foxpro is supported for doing this? I would guess so since their other product (I think it's ACCPAC Pro) is written in Visual Foxpro if I'm not incorrect.

Thanks again.

 

[DjangMan]

As long as the environment that you are developing in will support COM then you can use the ComAPI.

Django

 

[flpro]

Thanks Django,

By any chance do you have to happen to have the .net code of a simple add into a table in accpac? I could use it to translate it into foxpro and speed up the process.

Thanks.

 

[DjangMan]

Sorry,

I'm a Delphi programmer. You should be able to record a macro, which will record in VBA, and convert it to your .Net language.

Regards,

Django

 

[flpro]

No problem.

I "almost" became a delphi programmer but when I switched companies they used foxpro and that's what I ended up learning pretty good.

In the macro I see that it updates only a few fields, but on the table on SQL Server I see that a lot of the fields don't have a "default" value so I'm guessing that these ACCPAC views have the code to fill in the values of these fields but this code is not shown on the macro. Am I right?

Another possibility, I don't have any experience with manipulating macros but I was thinking if it's possible to somehow run a macro and send parameters to it from an external application, that should do the job. And if the macro can return a success code even better. Can macros handle this?

Thanks.

 

[ettienne]

There's a lot of business logic 'hidden' in the views, this takes care of all the values in those fields.
You cannot really call macros and pass parameters, that's a bit too much for VBA.

 

[DjangMan]

You can do something kludgy like writing your parameters to an ini file and then have the macro read the ini file. The macro can do what it needs to do and write out it's results somewhere else - like another ini file or a database.

Regards,

Django

 

[ettienne]

Have you checked the Misys configurator macros? The use optional tables and read/write data there - something else to consider. BTW you can still use optional tables in macro in 5.3 and even if the customer does not have a licence for optional fields. I use optional tables sometimes instead of ini files or other files, just depends which mood grabs me.