Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28

Vba

  1. #16
    Perfac is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Location
    Gauteng Johannesburg
    Posts
    232
    Many months ago when I started using Access, I had no clue. I think that generally for accounting databases that aim to render Balance sheet and the full accounting ability to most kinds of businesses, the following features are important. I am confident that we are on the right track.
    1. Entities like, Employees, Customers, Suppliers, Creditors, Banks we have 12. Each with own table and query.
    2. We created a CombinedEntity table and query. Ken Sheridan taught me how. The 12 entities combine here and it work well.
    3. Documents like Purchase Invoice, Sales Invoice, Payment, Receipt and Journal all have their own table and query.(Main and Sub)
    4. The same as the entities, we created a CombinedTransactions table and query that brings all transactions to a combined query.
    5. An invoice(most documents) use many fields in a long line, one line in the main form and possibly multiple on the subform; Discount, Vat and Total. I opt to set up Union query because these separate transactions on the same invoice must end up one below the other to be able to get them all in a Ledger. (hope you understand me.)


    6. Our eyes and ears are always wide open to learn.

  2. #17
    GinaWhipp's Avatar
    GinaWhipp is online now Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    141
    Well, had to download sample and noticed the Me.T001 never equals 2 so nothing is ever going to happen.
    Gina Whipp
    Microsoft MVP (Access 2010-2015)
    https://www.access-diva.com/tips.html

  3. #18
    Perfac is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Location
    Gauteng Johannesburg
    Posts
    232
    Hi Gina. This is a Purchase invoice. When T001 = 1 it is a cash purchase, and when T002 = 2 is a credit pruchase. In our active database there are hundreds of both.

  4. #19
    Perfac is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Location
    Gauteng Johannesburg
    Posts
    232
    Quote Originally Posted by June7 View Post
    Did some edits on my previous post. Sorry, no suggested fix, just identified possible cause.

    Usually a form does data entry/edit for one table. Why is this form's RecordSource so complicated?
    We do focus on simplicity, a query has to be as complicated as need to be to get the job done. This form and its source is a Purchase Invoice with one subform.
    a. As I described, more than half the fields of this form are stored in the CombinedTransactions table and query. The rest in the Purchase Invoice table.
    b. This Purchase invoice query must return Vat Rates from a VAT table, since Vat Rates do change.
    c. Suppliers information must be fetched from Suppliers Table because some are not registered for VAT, and the Invoice report needs all the other information of the supplier.
    d. We use "Images" a lot. The images are stored outside Access.
    e. The Inventory tables keeps all info about Inventory and assets all info about assets. If assets or inventory are bought the Purchase Invoice join with those for logic reasons. The same with Overhead expenses.
    f. Sales Prices are based on Purchase prices as a principle. We have a Business Policy table which stores general SalesMarkuponcost. That may change. On each page of every Inventory item and asset item there are a field "management adjustment on sales price". In the event of anything be sold different than the Business Policy.

    You may call it complicated and I am an accountant an owned 3 sizable businesses over 30 years. I also may not know of an existing system that follow these points to make a system perfect. But it leads to outstanding accounting, making it easy and more accurate. We have regular discussions to make sure to remove any click of the mouse not needed. Any field that can be validated or calculated, we do that. It increases accuracy, keeps users focus. We do have well studied and experienced knowledge on how the perfect accounting system should look for the general medium and small business. It is an obsession to create the best system on the planet, and we are getting there.

  5. #20
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,138
    Fine, if it all works, even if it is unorthodox. I have no problem with unorthodox. I built a database that 'violates' many accepted principles of relational database because it works for us.

    I am even more confused by fR2PurchaseInvoice. Its RecordSource query is not editable, yet the form allows to select data in controls and creates records. Selecting Cash results in a new record in t02PurchaseInvoice, selecting Credit does not. Seems T002 click event controls this.

    Debug your code. Set a breakpoint on the problem line. Select credit and at the time T003 is clicked, PchInvID is Null. Can see on the form that the PchInvID control is empty.

    If images are external, what is Voucher01 attachment field for?
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  6. #21
    Perfac is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Location
    Gauteng Johannesburg
    Posts
    232
    We treat the active system with serious respect, it continue do the work correct. This separate DB we are busy improvements, I am testing the new thoughts you suggested. I can see it will all work and I am exited about it.

    I seriously advice customers to go for a "Paperless Environment" absolutely no paper in the office. There was a time my business had 80 4 drawer filing cabinets. After a decade I realized to build all those filing costed millions on labor. It also disturb user focus. Spending time looking for vouchers that were misplaced. If there are no documents in office you cant use time to look for them. My answer to you is the Voucher attachment is there to scan every possible document and attach it to the transaction in our DB. Don't you do it like that?

    I enjoy every minute on Access, but I have overdone it. This weekend my concentration level is a bit low but I box on. Thanks for your advice I am busy perfecting this invoice.

  7. #22
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,138
    I already stated that embedding images in table attachment field likely slows performance as well as quickly uses up Access 2gb file size limit.

    Alternative is to keep image file (PDF ?) external and save file path in a text field. You state "The images are stored outside Access." So ask again, why do you have attachment field?
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  8. #23
    Perfac is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Location
    Gauteng Johannesburg
    Posts
    232
    There was a time when we tested and learned the differences between OLE Objects, Image and Attachments. We use a lot of Images. Every form like Invoice, Payment, Payslip and many others, we set it up that the selected third parties logo or photo jump up, wherever they are selected. Employee photos on payslips, and inventory images against inventory item. Images improve accuracy, I think it is dynamic and customers are impressed. All photos and Logo's are placed by image.

    There are features making us disapprove using OLE objects. Watching some tutorials and testing, I once thought OLE can store multiple vouchers as well against one document, but storing inside Access makes it no option. I believe there are issues of resaving that increases work here as well.

    Your latest suggestion is again going to bring improvement in our strategy. The main reason why we use attachments up to now is the feature of being able to attach multiple scanned documents to one transaction. Some guy on the forum from Netherlands said he goes well over the file size limit. I am very sensitive and concerned about that. Somebody mentioned that connecting the back end to SQL Server could help with that, but others remarked that doesn't work.

    You say, save file path in a text field. What if I want to save 5 vouchers against one document? I am sure there is a way, and I think Ruben knows how. We definitely will do that. What do we do to overcome the threat of 2gig file size limit?

  9. #24
    GinaWhipp's Avatar
    GinaWhipp is online now Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    141
    Well, took a bit to bet thru this BUT I am with June7 on this (and so is any other serious Access developer), images do not belong in an Access database. There is no *going over the limit* when it comes to size your database will just corrupt. So, to handle images have a look at...
    https://www.access-diva.com/d15.html

    As for the Combo Box BUT there is no *1*. It is not the first value in the Combo Box so it will never equal that. Understand?
    Gina Whipp
    Microsoft MVP (Access 2010-2015)
    https://www.access-diva.com/tips.html

  10. #25
    Perfac is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Location
    Gauteng Johannesburg
    Posts
    232
    GinaWhipp. I don't understand you. All my writings show how serious we are to keep images or other attachments outside our Access development. Since our start many months ago, there were never images inside our DB. I confirmed that we will for sure find a different way store attachments outside as well. Secondly the field T001, definitely has 1 and 2 as a selection. Our system has always worked like that, and it works now. The shortened separate DB that I posted may be a mess since I was trying something different over the weekend. We haven't got close to using 100 megabytes and we have more than a 1000 objects. I do not foresee that 2gb will become an issue but I will seek more experience to be prepared long before it happen.

  11. #26
    GinaWhipp's Avatar
    GinaWhipp is online now Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    141
    Hmm, I must have misread, y apologies... not enough coffee.

    When I downloaded that database the bound column did not have a 1 or 2 in it. Was I looking at the wrong Combo Box? Your initial statement...
    Code:
    If (Me.T001) = 2 Then
    But when I downloaded the file the first column had no 1 or 2.
    Gina Whipp
    Microsoft MVP (Access 2010-2015)
    https://www.access-diva.com/tips.html

  12. #27
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,138
    @GinaWhipp, note that combobox T001 RowSource is a query that limits the list of transaction types to only values with document type of "Purchase Invoice". That is two items: "Cash Purchase" and "Credit Purchase" - TrnTypeID 1 and 2. These are the values the code checks for. That much is working.

    What is screwed up is the code allows record in t02PurchaseInvoice only for type 1 yet it is for only type 2 code wants to save the concatenated value.

    Perfac, why the restrictions? Should not these actions occur for both types?
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  13. #28
    Perfac is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Location
    Gauteng Johannesburg
    Posts
    232
    Thank you very much for your efforts. There will be improvements because of your help. A "light" went on today and some change in decision made it obsolete to discuss my previous posts in this thread. It will be much simpler now. I will still answer you.

    The first ComboBox T001 will stay as is. As June7 rightly said there is a Table and Query we name TransactionTypes with 27 types. For this PURCHASE INVOICE there are 2 options. Cash Purchase and Credit Purchase. J7 I hope I answer you. In the event of a cash purchase only Bank can be credited. When Credit Purchase is selected, a Supplier gets credited. The two different choices leads to different handling of the invoice. I am sure that is the best way to do it. Tested this with many opinions. Cash Invoices ends up correct in the Cash Books, and Purchase Journals shows all invoices, cash and credit.

    I used Pastel and other well known accounting packages, I wondered how their back end is set up. If a business have 5 bank accounts and add a sixth. Our system have one table storing all receipts, another table stores all payments, even if there are 5 bank accounts. Numbering is complicated but done through programming. Two sets of number needed. Adding another bank account, there is no need for more tables for a new bank account. Probably needs more explanation.

    I still want to do the concatenation for the reasons I told in previous posts. There must be a simple character I miss, Ruben will know how, tomorrow morning. The newest decision is to do away with the combined transactions query for many reasons. From the start we used Union queries and it does the job well. Two changes that bring great improvement, will be that all calculations gets done through VBA. The other change is to store Vouchers outside Access.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums