Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2010
    Posts
    5

    Unanswered: Basic VBA for MS Access 2007

    Hey all,
    I am designing a software with access 2007 for invoicing and inventory and i need some basic VBA knowledge like adding and substracting. However, i dont have the smallest idea of VBA. I need an introduction to VBA starting from the most basic things.


    The things i need the VBA for is for example:
    - add invoice amount on the customer balance
    - remove sold amount of products from inventory
    - substract customer balance when a payment is made
    etc...

    thanks
    appreciated

  2. #2
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Using VBA

    You may want to rethink your way of looking at things a bit. Everything you mentioned can be handled via report designs -- you don't really need VBA at all for those tasks.

    It sounds like you want to retain derived fields in the database ( e.g. customer balances). This is against basic database principles - if your database contains what you have sold clients, and what they have paid, you can always calculate balance.

    I am certainly not saying there aren't good reasons for using VBA, but for different reasons.

    Regards

    John S.
    (currently) Guangxi U
    Nanning, China

  3. #3
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by bretta91 View Post
    Hey all,
    I am designing a software with access 2007 for invoicing and inventory and i need some basic VBA knowledge like adding and substracting. However, i dont have the smallest idea of VBA. I need an introduction to VBA starting from the most basic things.


    The things i need the VBA for is for example:
    - add invoice amount on the customer balance
    - remove sold amount of products from inventory
    - substract customer balance when a payment is made
    etc...

    thanks
    appreciated
    Since you have 2007, have you looked at the NorthWind Trader example. It already does the basics of what you are asking about. See if that gives you any ideas.

    Inventory control is NOT an easy task to handle with software in general.

    See: Inventory Control: Quantity on Hand
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  4. #4
    Join Date
    Apr 2010
    Posts
    5
    [QUOTE=praxis1949;6466469]You may want to rethink your way of looking at things a bit. Everything you mentioned can be handled via report designs -- you don't really need VBA at all for those tasks.



    praxis1949 this is the best news ive heard so far! Can you give me more information about how to do it using reports? (as i know reports are used only to retreive information) have you done it before?

  5. #5
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    The Flexibility of Reports

    Excuse me if I am stating the obvious , or misunderstand your question

    Reports have great flexibility both for doing calculations on a record by record basis, and for providing summary data. I have provided you (attached) with a an example of a full accounting report done for a local charity.

    Reports have additional flexibility because they can be built on tables or queries. A query, depending on your database design, can combine data from two or more tables (such as billings and receipts).

    Regards

    John S

  6. #6
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Examples

    The attachments promised are attached
    Attached Files Attached Files

  7. #7
    Join Date
    Apr 2010
    Posts
    5
    Quote Originally Posted by praxis1949 View Post
    Excuse me if I am stating the obvious , or misunderstand your question

    Reports have great flexibility both for doing calculations on a record by record basis, and for providing summary data. I have provided you (attached) with a an example of a full accounting report done for a local charity.

    Reports have additional flexibility because they can be built on tables or queries. A query, depending on your database design, can combine data from two or more tables (such as billings and receipts).

    Regards

    John S

    Thanks for the reply John. however i cannot use this for my problem i think. let me detail my problem in an example.

    When i fill an Invoice_From the following things i need to do with VBA:
    - add the balance of the invoice on the balance of the customer (in the customer table Customer_balance)
    - remove the sold item amount from the quantities in the inventory (in the items table item_Qty)

    anyhow let me get to my 1st question of how to learn basic VBA

  8. #8
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by bretta91 View Post
    anyhow let me get to my 1st question of how to learn basic VBA
    Maybe this will help: Access Basics-HiTech Coaching for Access Databases and more ...

    I do agree with John's suggestions. You should use a query to do the calculations and/or do the calculations of a form or report.

    Quote Originally Posted by bretta91 View Post
    When i fill an Invoice_From the following things i need to do with VBA:
    - add the balance of the invoice on the balance of the customer (in the customer table Customer_balance)
    This is normally not done. You should not store calculated values that are based on data in other fields in the same or different records. If you will study the rules of data normalization, it will explain why in more detail. Learning this concept and applying it will serve you well.

    Quote Originally Posted by bretta91 View Post
    - remove the sold item amount from the quantities in the inventory (in the items table item_Qty)
    Quantity on hand is also calculated from the inventory transactions. It should not be stored for the same reasons the you do not store the Custom Balance.

    I posted a link previous that show how to use VBA code properly to handle Quantity on Hand.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  9. #9
    Join Date
    Apr 2010
    Posts
    5
    Quote Originally Posted by HiTechCoach View Post
    Maybe this will help: Access Basics-HiTech Coaching for Access Databases and more ...

    I do agree with John's suggestions. You should use a query to do the calculations and/or do the calculations of a form or report.



    This is normally not done. You should not store calculated values that are based on data in other fields in the same or different records. If you will study the rules of data normalization, it will explain why in more detail. Learning this concept and applying it will serve you well.



    Quantity on hand is also calculated from the inventory transactions. It should not be stored for the same reasons the you do not store the Custom Balance.

    I posted a link previous that show how to use VBA code properly to handle Quantity on Hand.
    i know we dont store it, but after so many transactions the calculation time will increase, thats why i want to store them and manipulate them after each transaction. maybe its not the standard way but this is my 1st project of this scale

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •