Results 1 to 14 of 14
  1. #1
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99

    Command Button that is Automated And Returns Answer

    HI, Everyone so recently i saw a guy build a calculator in Access Wow.... on my own db. project I've discovered a series of calculations in queries "d" ;"m" ;"yyyy"... that returns the preferred result a Release date now i want to build a calculation Form where i put in some variables and press a Command button btn and it returns the desired results how do I go about writing a code or programming this button

    HERE,S an e.g..... Returns a calculation in currency note: colunm1and 2 are date fields column3 and 4 answers in number of days and column5 a currency field



    column4:datediff("d";column1;[column2])
    column3:datediff("d";Date();[column2])
    Amt:CCur([column3]/[column4]*[column5])

    there are other sql statements to write e.g. Returns a calculation in days months & years but am late for work will updated the thread,
    Could I code a Command button btn to do this ?

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It would appear that you would have a form, and some text boxes to accept some criteria. Then a command button with a click event. The click event would invoke some vba code to check that criteria was entered; criteria is valid; perform some calculation based on your logic and code, and return the results in the appropriate controls on the form. You would also include some validation code that included error handling in the case that criteria were not supplied; criteria values were not valid.

    Good luck with your project.
    Last edited by orange; 06-04-2020 at 05:34 AM. Reason: spelling

  3. #3
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    So am trying to learn vba especially for MS Access is there a book i can be pointed to??? say beginner to Advanced ALSO I've tried this code to automate the button as a calculation function that will give an answer.
    i keep getting the message Expected End Sub as I try to execute the code can anyone explain why??

    Private Sub cmdbtn_Click()
    Function GetDate(DOC As Date, intYrs As Integer)
    Dim FD As Date, intDays As Integer
    FD = DateAdd("yyyy", intYrs, DOC)
    intDays = DateDiff("d", DOC, FD)
    GetDate = DateAdd("d", -1 * Round(intDays / IIf(intDays > 366, 3, 2), 0), FD)
    End Function
    End Sub

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You can't enclose a function in a sub.

    Move the function code out of the sub.
    Then your sub should be

    Code:
    Private Sub cmdbtn_Click()
    GetDate
    End Sub
    You will of course need a means of entering the two values required by your GetDate function
    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

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    There is a TON of free stuff on the net - articles, videos; some publishers do a whole series of videos, all free. If your preference is a book, you could probably find a pdf copy of old books on line (e.g. Access 2000 Bible), or try Amazon or eBay for used books. While some of them might seem dated, they would suffice to start with since not much has changed wrt VBA over the years. The biggest difference between new and old would be the properties, methods and events for some objects that were not around way back when, but the principles of how to work with them are the same, regardless of how old these objects are. What you need is the fundamentals, not necessarily the latest and greatest. Unless you have a mind like a sponge and will remember everything you set your eyes upon, don't expect to master vba any time soon. My main caution is that some old books will treat certain aspects of Access as if they are great things but you should avoid them. One would be replication. Regardless of how great it was or wasn't, it is no longer. Don't waste time on it. Another would be the impending demise of DAO in favour of ADO. M$ reversed the course so it is no longer true. Suggest also that you research multi value fields, table level lookup fields and attachment fields before believing whatever a book tells you about them. After all, the authors need to stay on the good side of M$ (that's my opinion) so it is likely that they would never use these abominations but will not admit it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by Micron View Post
    Unless you have a mind like a sponge and will remember everything you set your eyes upon, don't expect to master vba any time soon.
    .... Actually i don't have that kind of brain jus my love for Access is driving my determination this stage in my life. I have bought books constantly online researching... i recently had a bad experience purchasing books and not receiving them, guess because of all that's happening right now... after learning the calculations in sql... want to have a button that could perform this action every time

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, you might have your answer in post 4? Not sure as your original post is kind of confusing. You seem to have a mix of sql, vba and forms information, and it might be better to state what you want to do in general terms rather than ask if you can do this or that - if the answer given doesn't fix your issue.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by Micron View Post
    Well, you might have your answer in post 4? Not sure as your original post is kind of confusing. You seem to have a mix of sql, vba and forms information, and it might be better to state what you want to do in general terms rather than ask if you can do this or that - if the answer given doesn't fix your issue.
    No don't misunderstand me!
    in my line of work we have some calculations to do. we usually do them on paper am thinking it might be difficult for me to explain but i did in an earlier posted thread However long story short i did figure it out by running queries in sql language Great! am looking now for further skill; so am asking could i have a cmdbutton called calculate in a form that will automate this function... this sql language posted @ 1
    some calculations are base on a release date so A date field must be the answer
    other calculations are base on monies owed to the courts so a currency field must be the answer as i indicated at post 1
    Remember i did say am not a programmer just a newbie excited about db and access
    BUT i could leave it in sql and pace my growth don't need t learn everything like there's no tomorrow LOL

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you can imagine it, it can likely be done in Access, whether that is with a button click or anything else. It is generally accepted practice to do such calculations in forms (not tables) and to not store calculations. Doing so introduces the issue of having to identify all cases where an input can change and recalculate the stored value accordingly, so it is seldom advisable. Better to do so on the fly, whatever that takes. Without an understanding of what the inputs and desired outputs are, it is not possible to give you any explicit suggestions on what needs to be done, but I've yet to come across a task that cannot be handled, given enough sql or code and some thought - or all three.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by Jamesy_007 View Post
    column4:datediff("d";[column1];[column2])
    column3:datediff("d";Date();[column2])
    Amt:CCur([column3]/[column4]*[column5])
    I've notice and tried unsuccessfully when user opens this form. The form i built to run the calculation above the textbox field also control source to this expression.... Amt =CCur([Time to Serve]/[Days in Sentence]*[Fine]) when opens is says #Error don't know how to get rid of it. I imagine it displays this because there's no values in the prior fields...because when user put values in the other fields the required Answer is given

    How to get rid of this #Error when the form opens

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You could make a db copy, compact/repair it, zip it and upload it here for analysis. Or you could try giving the fields a default value of 1 if you don't mind seeing 1 as the answer instead of #Error. If you've given the fields a default value of 0 then that might explain the result as the problem would be division by zero.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by Micron View Post
    You could make a db copy, compact/repair it, zip it and upload it here for analysis.
    ok no problem i just realized my db on my home computer is not a true representation of my db in work. there's some tables and queries missing due to confidentiality ill try my best to sanitize and give an accurate representation of what am trying to accomplish; Loving you guys lol loving this forum so.....

    Get back to you shortly

  13. #13
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99

    Cool

    Hey Micron LOOK over this post db now is the real replica the best i can
    Please look at queries and their Calculated Fields
    Please Check frmAutomation and frmCalculateF

    That zip before was a piece of.......

    Notice MS Access in work is 2010 and Home lap is 2016 jus stating


    Attachment 42260
    Attached Files Attached Files

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It's been several days and I've seen hundreds of posts here and elsewhere, so I'm not clear on where this needs to go.
    Your form calculatefines works without a button. Do you really need one? You can eliminate the #error flag in the amount to pay field either by simply entering a fine amount, or you could try this expression as its control source
    =IIf(Not IsError([txtAmtPay]),CCur([txtTimetoServe]*[txtFine]/[txtDays]),0)

    If neither of those approaches seem adequate, you can either control what controls are visible based on prior values being present or you can move all the expressions out of the controls and put them in code and resort to the calculate button. You shouldn't try to do both. Altering the visibility of controls is a bit trickier because you have to code for the possibility that someone deletes a value after a subsequent control has been made visible. Not too hard, just more possibilities to deal with.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-25-2019, 12:16 PM
  2. Replies: 1
    Last Post: 09-12-2014, 06:09 AM
  3. Replies: 3
    Last Post: 08-04-2013, 07:11 AM
  4. automated button labels
    By scotty in forum Forms
    Replies: 1
    Last Post: 10-18-2010, 04:55 AM
  5. Replies: 1
    Last Post: 07-27-2010, 02:27 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