Results 1 to 12 of 12
  1. #1
    archy321 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    17

    Previous/Next Month Button

    Hello,



    I really dont know how to word this question so i unfortunately havent been able to do much research into this so any help is greatly appreciated! I am trying to navigate a form with two buttons, a previous and next month button. So if i were to click the previous button once it would show last months records, click it again it would then show two months ago records. Next month would work the same way....just in the other direction. How would i go about doing this? Database7.zip attached is a quick mock up of what i am trying to do. Any help would be greatly appreciated!

    V/R
    Archy

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd probably do it by manipulating the record source of the subform. On load of the main form, filter it to the current month and store that in a hidden textbox or something. Behind the buttons, grab the value in the textbox, add a month, reset the subform record source using that and change the value in the textbox.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Played with it, decided to use form level variables for start and end dates. Set the start date in the load event and called a form level sub that sets the end date from the start date then sets the record source. The buttons add/subtract a month from the start date as appropriate and call the sub.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    archy321 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    17
    Hello,

    I am still fairly green when coming to access, could you please break this down a little bit more for me?

    V/R
    Archy

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Did you consider just putting a textbox on the form, formatted to a Date format? Then you could use the built in date calendar and navigate forwards and backwards as many months as required in one step. That is, you wouldn't have to click 3 times to go back 3 months. You would probably need help with that, I'm guessing.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by archy321 View Post
    I am still fairly green when coming to access, could you please break this down a little bit more for me?
    Have you tried any part of it? Here's a start:

    Code:
    Option Compare Database
    Option Explicit
    
    Dim dteStartDate              As Date
    Dim dteEndDate                As Date
    
    Private Sub Form_Load()
        dteStartDate = DateSerial(Year(Date), Month(Date), 1)
        SetSubSource
    End Sub
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Here is your db back with 2 variations on a theme. Sorry, don't do macros so I had to delete the embedded macros. Also, suggest you
    - don't allow your control names to be the same as your query/field names
    - don't have spaces/numbers/special characters (save perhaps for underscore _ ) in any names

    I renamed your subform control and subform because they were named the same & had spaces. Left the 1 in though.
    Version 1 uses the textbox with calendar option.
    2nd version opens up with 1 combo containing current year & the other the current month. List items are restricted to months/years for which you have data.
    Both apply/remove filter using filter buttons.

    Enjoy!

    Database7Micron.zip
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    There's your fish, or at least the fish Micron thinks you should have.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe archy321 doesn't like canned sardines...

  10. #10
    archy321 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    17
    Thank you both for all your help! None of this was covered in my Udemy course and everything i've had as far as research so far has been from google searches. This has helped immensely! I do not want to be a burden on the site and actually would like to help someday once i have learned a whole lot! If you point me in a direction (other than a college haha), i will read up everything!

    Thank you so very much again!

    V/R
    Archy

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Here's a start, which might not be what you're looking for
    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.ca/...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.ca/...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.ca/...cation-in.html

    Important for success:
    One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html

    Then there are a lot of useful links in post 3 of this one

    https://www.accessforums.net/showthread.php?t=65220

    some of which may be repeats of what I typically answer with.

  12. #12
    archy321 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    17
    Thank you for all of this great info! I'll be reading this throughout the week!

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

Similar Threads

  1. query readings from current and previous month
    By JRCharlie in forum Access
    Replies: 14
    Last Post: 04-15-2018, 01:41 PM
  2. Replies: 2
    Last Post: 02-12-2015, 05:14 AM
  3. Select Month from previous selected year
    By k0enf0rNL in forum Access
    Replies: 1
    Last Post: 01-15-2014, 12:14 PM
  4. Replies: 1
    Last Post: 03-02-2012, 11:09 AM
  5. "Previous Month" button
    By allochthonous in forum Programming
    Replies: 3
    Last Post: 09-10-2006, 12:15 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