×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

ACCPAC Macro/VBA

ACCPAC Macro/VBA

ACCPAC Macro/VBA

(OP)
Hi all,

I'd like to learn ACCPAC Macro / VBA, any suggestions that where should I start off?

Regards,

Benny

RE: ACCPAC Macro/VBA

If you have no programming experience then it may be a good idea to pick up a book on basic programming concepts and maybe Access VBA (it has good documentation and you can learn with it, ACCPAC has very poor documentation for it's VBA). You can also find free VBA (MS Office) tutorials on the web from sites such as www.freeskills.com.

Otherwise, start recording VBA macros and tear them appart. You will see which views are invloved, how the views are composed (or related so that they can 'talk' to each other) and which fields or objects are needed to update something. You can record Macros for almost any module, except bank recs. There are also some existing marcos that come with
Accpac, tear them apart as well.

If you have access to the the Accpac website techinical pages for the COMAPI make sure you go through them and understand what is going on.

And of course when you are stuck visit this forum and we can see if we can't give you a hand.

Thanks and Good Luck!

zemp

RE: ACCPAC Macro/VBA

(OP)
I'm using Accpac Advantage Series Corporate Edtion 5.1A.

I'm actually looking for codes that help me to change the AP Invoice Batch List's Year/Period values to current accounting month since the Year/Period values always follow the Document Date value.

I tried record some macros using the macro recorder and glance through the codes that was recorded. The codes only show step by step changes to the records, what I want is a loop to loop through all the Year/Period values to check and make sure the values are stick to current account month.

Any ideas for that?

Regards,

Benny

RE: ACCPAC Macro/VBA

From the macro you know which views and fields are involved and you should see how to update the fields that need updating.

Now you need to use the view to fetch the records that you want to deal with. Loop through those records and make the appropriate changes. The basics are like this.

1. open the view.
2. Browse for specific or all records.
3. Fetch the records
   Do while view.Fetch
      'update records here...
   loop
4. Close the view.

You will forgive me for not being exact with the syntax. I have never written an internal ACCPAC VBA macro with COMAPI. I always go extenally with the xAPI. Try and find some basic syntax (which can also be in the recorded macro's) and when or if you get post again.

Thanks and Good Luck!

zemp

RE: ACCPAC Macro/VBA

Hi there, I am trying to get a macro going in 5.1 where I export an IC table before the day end processing and then importing the IC table back afterwards. I used the macro recorder and got the process day end automated, but the macro recorder won't record the import and export process of my IC items... it does record the windows being opened and that's it! Any help would be fabulous...

RE: ACCPAC Macro/VBA

Unfortunaltly the macro recorder does not work for all processes. For example it does not work for Bank services (Bank Recs) at all.

First of all, by 'import/export' I am assuming that you are tajing the data out of Accpac and then placing it back in. If this is the case it may be better to use the external xAPI. Very similar to the macro (internal) COMAPI. This will allow you to read an external file and reimport it into Accpac. You canstill use a macro to determine which IC views and fields need to be used and then you just need to organize your data with the correct syntax.

Thanks and Good Luck!

zemp

RE: ACCPAC Macro/VBA

(OP)
The macros that recorded within ACCPAC is called COMAPI, but what is xAPI all about? Where can we learn it?

Regards,

Benny

RE: ACCPAC Macro/VBA

The xAPI is almost the same as the COMAPI. COMAPI is used internally in macro's. The xAPI is used externally, usually from a VB application.

Accpac does not have any really good documentation on the xAPI,at least I have not been able to find any over the last few years (correct me if I am wrong), so you may have to use a lot of trial and error.

Check the Accpac website for technical documentation and use the SDK. You can record macros and just make the switch to xAPI using the same views and fields. You will need to create a session and make some slight syntax adjustments. You can search this forum for 'xAPI' and see some more detailed explanations and examples, including the of creating a session object.

The following threads can get your search started.
Thread631-583563
Thread631-558105
Thread631-526541
Thread631-578651

Thanks and Good Luck!

zemp

RE: ACCPAC Macro/VBA

Thanks for the reply... I got my project started in VB and referenced that xAPI... What I'd like to do is cycle through the records of my vendor table to pull out all the info and save that to my file. So far I have:

Private Sub Form_Load()

  Dim Session As ACCPACXAPILib.xapiSession
  Set Session = CreateObject("ACCPAC.xapisession")
  Session.Open "ADMIN", "ADMIN", "SAMINC", Date, 0

  Dim ARCUSTOMER As ACCPACXAPILib.xapiView
  Set ARCUSTOMER = Session.OpenView("IC0310", "IC")
   
   At this point, I'd like to do a
   
   do while not(session.EOF)
       and get all the values per records
       and put then in a file (doesn't matter what file,
       but I don't know how to pull data out of the      
       recordset!
   loop

End Sub

Any help would be appreciated!
Thanks

RE: ACCPAC Macro/VBA

First off, your view is being set incorrectly. ARCUSTOMER view needs to be set like this.

  Set ARCUSTOMER = Session.OpenView("AR0024", "AR")

You have set it to an IC items view.

To open a view recordset you need to browse for the data first.

   With ARCUSTOMER
      .Init
      .Order = 0
      .Browse "", True 'all customers
      Do While .Fetch Then         
         msgbox Trim(.Fields("IDCUST").value) & ", " & Trim(.Fields("NAMECUST").value)
      loop
      .Cancel
   End With
   Set ARCUSTOMER = Nothing

That is basically how you can pull the records from the recordset.

Thanks and Good Luck!

zemp

RE: ACCPAC Macro/VBA

Hello Zemp,
Thanks for that awesome reply... I can now grab data and store it in my file. Now I'm trying to return the values into the table and update the values from my file. I was wondering if I should use the BulkPut or Update function. The sub is almost the same:

   Set Session = CreateObject("ACCPAC.xapisession")
   Session.Open "ADMIN", "ADMIN", "SAMLTD", Date, 0

   Dim ICVENDOR As ACCPACXAPILib.xapiView
   Set ICVENDOR = Session.OpenView("IC0340", "IC")
   Dim aICVENDOR as Array

   'Open file

   With ICVENDOR
      .Init
      .Order = 0
      .Browse "", True
      
      Do While .Fetch
         'I'm reading from my file here....
         'aICVENDOR = readfromfile()
         'Here, should I use BLKPUT or UPDATE, and how
         'could I use it...
        .BlkPut aICVENDOR
        .Update
      Loop

      .Post
   End With
   Set ICVENDOR = Nothing

What do you think... Any suggestions?

Thanks
Xenzat

RE: ACCPAC Macro/VBA

I am not sure I understand what you are trying to do. Are you taking the updated values from your file and tryiong to update the existing records in Accpac? Or are you trying to add new records into the Accpac database?

I have always looped through or searched (browsed) for the records and done any updates or insertions individually.

Thanks and Good Luck!

zemp

RE: ACCPAC Macro/VBA

Hi Zemp, thanks for the quick response. I am  taking updated values from my file and trying to update the existing records in Accpac... I reading out of an access DB and set the records up in the same order as the accpac table. Here's what I have so far...


    Set ICVENDOR = Session.OpenView("IC0340", "IC")
    
    myRS.MoveFirst
    With ICVENDOR
       .Init
       .Order = 0
       
        Do While Not (myRS.EOF)
        
           .Browse "ITEMNO = '" & myRS("ITEMNO") & "'", True
            If .Fetch Then 'update the record
                For iCt = 0 To 10
                    .Fields(iCt).Value = myRS(iCt).Value
                Next iCt
            Else 'create a new record
                For iCt = 0 To 10
                    .Fields(iCt).Value = myRS(iCt).Value
                Next iCt
            End If
            
            myRS.MoveNext
            .Update 'update Accpac with new info
         Loop
         .Post 'update accpac database
     End With
   
   myRS.Close
   myDB.Close
   set myRS = nothing
   set myDB = nothing
   set ICVENDOR = nothing

Not really sure about my syntax... I wish there was more literature abou this stuff... Thanks for looking at this...

RE: ACCPAC Macro/VBA

Hi Zemp,
Seems I got it working with a little perseverance... But I'd like to post the code nevertheless to get your opinion on it (I think there can be adjustments made to it...)
Here it goes:

    Set ICVENDOR = Session.OpenView("IC0340", "IC")
    
    myRS.MoveFirst
    With ICVENDOR
       .Init
       .Order = 0
       
        Do While Not myRS.EOF
        
            .Browse "ITEMNO = " & myRS("ITEMNO") & "", True
            If .Fetch Then 'update the record
                For iCt = 0 To 10
                    If Not (iCt = 8 Or iCt = 9) Then
                        .Fields(iCt).Value = myRS(iCt).Value
                    End If
                Next iCt

            End If
            myRS.MoveNext
            .Update 'update Accpac with new info
         Loop
         .Post 'update accpac database
     End With

I put a check for field 8 and 9 because Accpac lists these fields as not editable... But the rest seem to be editable!

let me know what you think, thanks
Xenzat

RE: ACCPAC Macro/VBA

Looks good, remember to destroy the view objects when you are done with them to ensure that they are removed from memory. After your .post place a .cancel and the a right at the end place 'Set ICVENDOR = nothing'.

FYI, Accpac lists many fields as non-editable and you can still edit them. Their documentation for the fields is non necesarily for the xAPI. So you will find lots of examples of this. The other way around is more frustrating. When the xAPI tells you you can't edit a field, that needs to be edited, it can cause lots of headaches. The only way is to try them and see what errors you get.

Thanks and Good Luck!

zemp

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close