Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    VBA to append.

    I set up an append query to transfer data in the event of CASH SALE when creating a SALES INVOICE and wanting to issue a RECEIPT. Because all the info is there, I want to spare the user the effort to create a RECEIPT. There is a button PROCESS RECEIPT and VBA result in the correct transfer of the needed fields. These documents(Sales Invoice and Receipt) have Main Forms and Sub Forms, so it must go through the Append query twice. When I present a develoment to my customer, I would like this action not to have the user clicking YES a few times, what is normal when one activate an append query. Is VBA the answer? My son and me have 500 videos, and he just finnished 3 years college in programming, but we are not there yet with VBA.

    My current process.
    a. There are Subqueries q04InvSales, and q04InvSalesSub.
    b. The fields info in these subqueries get appended to tables t02Receipt and t02ReceiptSub.
    c. When opening form f02InvSales the Invoice number is comitted by the time the invoice is completed, the Invoice number is in T006. It is used to filter the subquery so the current invoice info will be returned by these subqueries in a.
    d. To try and keep it short I will name just two fields each. In q04InvSales there is LdgAcc_ID04 that should append to LdgAcc_ID08 in t02Receipt, and also CmbEnt_ID19 that should append to CmbEnt_ID18.
    e. In q04InvSalesSub there is LdgAcc_ID05 that appends to LdgAcc_ID10 and CmbEnt_ID06 that appends to CmbEnt_ID12 in t02ReceiptSub.
    f. It also appends the open Invoice number to t02Receipt. It is important that when the control is clicked it first checks whether the Invoice Number is already in InvSalID in the t02Receipt table. It should give a pop up message about not duplicating it.



    There are a few more fields, but if I can be lead to understand how VBA can do it we will learn a lot. We are paranoid about user focus. Every click we can spare the user is important. When processing the invoice there are buttons GOTO RECEIPT, CREATE RECEIPT, MENU(to exit form), SAVE and PRINT etc. The consideration was to create the RECEIPT when click on MENU(exit). But I think the action best be done when the user is satisfied with the invoice to click the CREATE RECEIPT (cmd01) button. Is it possible doing this through VBA that Access will not ask the questions? Just click and RECEIPT is processed?
    Thank you.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do you have your business processes clearly defined?
    Have you identified required outputs from your proposed system?
    Have you identified tables and relationships?
    Have you tested your structure with some test data and sample "scenarios" to ensure you can access the required data and satisfy your require outputs?
    Be cautious not to jump into a physical database without some "paper based testing" of your evolving design.

    Lot's of info and videos etc re Database Planning and Design.

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    For 30 years I owned sizable businesses and have an accounting degree. I am not too bad at business processing. Wrote 100's of pages on that. My process decision I believe falls inside GAAP. (General Accepted Accounting Practice). For any products or service sold I issue an invoice. If not fully paid I define it as a credit sale. Cash sales process an invoice as well as a Receipt. What would you suggest on that?. About my issue. It is working right now. As said I want to skip having to click 4 times when Access prompt me, when activating the Append query. My apology if this is a lot.

  4. #4
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    A second reason why I need to do this is. The append query causes the losing of numbers easily.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My intent is/was not to criticize, but to identify steps in the development of your database application.

    Please tell us more about
    The append query causes the losing of numbers easily.

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Is it possible doing this through VBA that Access will not ask the questions?
    What questions? Are they the "You are about to append ..." dialogues?
    are you using docmd.runSql?
    if so you can suppress these by turning warnings off and then back on.

    a better way is to use the .execute method which has no warning dialogues.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Using vba or macros, you can process as many successive steps as you like; all you need is a trigger and a well thought out series of events that need to occur in what order. I think many of us here are not well practiced in macros since vba is mostly the preferred approach. Unless things have changed over the years, the fundamental flaw with macros is that they're not much good at handling errors. They also don't validate data very well, if at all, and are not nearly as capable when it comes to complex procedures.

    If you are developing an application for sale, you have a whole lot of other stuff to be concerned about if you care about your product and protecting it (which you no doubt do). If you need links to development concepts around normalization, naming practices and reserved words, pitfalls to avoid, then we can help with that. Believe it or not, pencil and paper are still important development tools to help you flesh out ideas, process flows, keeping your data types compatible and potential traps.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Hi Orange my friend, thank you for time you spent towards me. I also am very sensitive to show appreciation. In the past I worked myself almost to my grave. Now at 55 Access became a big passion. My wife was a programmer but she died a few years ago. I want to develop the ULTIMATE PROCESSING APPARATUS, what I want for business I can not buy anywhere. As a rookie I am proud of what I got so far. In 30 years of business I failed to find programmers including my wife to program an application that do much more than the average international accounting package. My skills get better every day like everyone that try. Wish I had more time though. As said, even having the whole of internet with 1000's of tutorials, time is not on my side. I learn faster if you guys help. But I spent many hours reading and watching clips. Have to create more time to learn more VBA since design in Access I am not doing so bad. I want to find the VBA code to achieve what I request in this thread.
    Thank you.

  9. #9
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Hi, I tried to be as clear as possible on the VBA I need, that is what will help me. It couldn't be too difficult for experienced developers. I trust that the process is clear to me, and correct. I simply want to process a invoice for any cash sale and it should generate a Receipt automatically. Any advice on the process is also welcome. And yes I can see there is a lot of other stuff to be concerned about, but if you read my other threads you will see it is getting attention. Right now the VBA code as requested above will make me happy.

  10. #10
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Hi moke123. If you can help with the exact VBA code to turn the warnings of and on. I rather want code that can carry the few fields info over from my subquery to the Receipt table, as in my opening thread here. Thank you.

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Instead of this:
    Code:
    DoCmd.SetWarnings False
    DoCmd.RunSQL "...your SQL string"
    DoCmd.SetWarning False
    use this
    Code:
    CurrentDB.Execute "...your SQL string", dbFailOnError
    This will show an error message if the code fails but otherwise run without interruption
    Whereas switching off SetWarnings means you won't know if anything goes wrong

    Also I suggest you include error trapping in ALL procedures
    You can also configure this in the VBE:
    Attached Thumbnails Attached Thumbnails VBEOptions.PNG  
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Ridders52. Thank you. My VBA to execute the Append queries is as follows.

    Private Sub cmd01_Click()
    DoCmd.OpenQuery "InvToRctMain", acViewNormal, acEdit
    DoCmd.OpenQuery "InvToRctSub", acViewNormal, acEdit
    End Sub

    What do you mean SQL String?
    Your suggestion is CurrentDB.Execute "...your SQL string", dbFailOnError. How do I combine them?

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a link describing executing Action query without set warnings



    If you are building queries with the graphical wizard, you can switch to SQLview to see the "SQL" that will be processed by Access.
    See the jpgs below to see the Design view and the underlying SQL.

    Click image for larger version. 

Name:	queryDesignView.jpg 
Views:	15 
Size:	57.3 KB 
ID:	32595

    Click image for larger version. 

Name:	querySQLView.jpg 
Views:	15 
Size:	26.7 KB 
ID:	32596

  14. #14
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Code:
    Private Sub cmd01_Click()
    docmd.setwarnings false
    DoCmd.RunSql "InvToRctMain", acViewNormal, acEdit
    DoCmd.RunSql "InvToRctSub", acViewNormal, acEdit
    docmd.setwarnings true
    End Sub
    But as Colin and I have indicated .execute is better

    Code:
    currentdb.execute "InvToRctMain", dbfailonerror
    currentdb.execute "InvToRctSub", dbfailonerror
    or

    Code:
    with currentdb
    .execute "InvToRctMain", dbfailonerror
    .execute "InvToRctSub", dbfailonerror
    end with

  15. #15
    Join Date
    Apr 2017
    Posts
    1,673
    To elaborate Oreange's example:

    1. When you can see the query in design view, you always can see it in SQL view too;
    2. When you can see the query in SQL view, then sometimes you can see it in design view too

    What you see in SQL view, is named SQL string. You can save it as a string variable, like:
    Code:
    strSQL  = "SELECT Employee.EmpID, Employee.LName & " " & Employee.Fname AS [Employee] FROM Employee"
    You can edit this query string using VBA code, like:
    Code:
    strSelection = "1,2,3"
    strWhereClause = "WHERE EmployeeID IN (" & strSelecction & ")"
    strSQL = strSQL & " " & strWhereClause
    And then you can execute the edited query from VBA, like:
    Code:
    CurrentDB.Execute strSQL
    , or you can replace the row source of some combo box on form with created SQL string:
    Code:
    Me.cbbEmployee.RowSource = strSQL
    About your initial question - google for "Access VBA SetWarnings". You have to set warnings off for time your queries are running. (Don't forget to set them on again after all queries are executed!). Depending where in your code you set warnings off/on, they aren't given at all, or user get warning for 1st/last query - as you want to set this up. Btw, I'm not sure you can do this with macros - but I never use them too.

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

Similar Threads

  1. Replies: 19
    Last Post: 10-03-2017, 09:10 AM
  2. Replies: 1
    Last Post: 09-02-2015, 08:44 AM
  3. Access can't append all the records in the append query
    By fluffyvampirekitten in forum Access
    Replies: 2
    Last Post: 08-27-2015, 01:53 AM
  4. Replies: 1
    Last Post: 10-06-2011, 08:37 AM
  5. Replies: 7
    Last Post: 07-21-2011, 01:01 PM

Posting Permissions

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