1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Create Form that Cycles through fields

Discussion in 'Business Applications' started by r24igh, Sep 6, 2016.

Thread Status:
Not open for further replies.
Advertisement
  1. r24igh

    r24igh Thread Starter

    Joined:
    May 18, 2016
    Messages:
    31
    Hi All,

    Quick one - trying to create a data entry/ edit form that has 3 years worth of dates in it. I.e. 2016, 2017, 2018

    Project Name| Project Manager |
    Jan 16 effort
    Feb 16 effort
    March 16 effort
    April 16 effort
    May 16 effort
    June 16 effort
    and so on....

    Some projects will go into other years and what I'm trying to get to is a form that has a button that can cycle through to the next year rather than creating 3 separate forms with a button that will just take you to the next form... can it be done? If so, can anyone tell me how?
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,827
    When you say "cycle through to the next year" I am not quite sure what you mean.
    Do you mean display records or Create new records?
     
  3. r24igh

    r24igh Thread Starter

    Joined:
    May 18, 2016
    Messages:
    31
    Thanks for replying OBP. So, what I mean is that say I have a table that pulls together a load of information i.e. Project Manager name, Project Code, Project Name and the number of days spent on that project each month (I have each month up to mid 2019). Some projects will move out into the next year and I don't want to have 24 fields in the report and have to update each month because it looks poor. I'd ideally want the users to be able to flick between years when filling in the information. The only resolution I currently have is to make 3 forms, populating each year with the corresponding year on the button. I have attached a couple of images to help articulate what I'm trying to do - hopefully these help.
     

    Attached Files:

  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,827
    This is one of the reasons for having your data as I described in your previous thread, because then you could filter by the date, or month or year.
    At the moment your Month/Year are Field Names and it is very very difficult if not impossible to filter a form by field name.
    If you added another field to each record that contained the Year for each record then you could use that to filter the form.
     
  5. r24igh

    r24igh Thread Starter

    Joined:
    May 18, 2016
    Messages:
    31
    Thanks again OBP, your input has been exceptionally helpful. Apologies for these probably very obvious mistakes however - I have had very little experience with Access and was asked to create something ASAP and have found fundamental errors as my access knowledge has developed.

    Can you please suggest how I can split out the fields - I am happy that there will be two tables, linked by a relationship to the Primary key however, to avoid further mistakes in the future I'd appreciate your expertise. The fields I have are in the attached xls
     

    Attached Files:

  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,827
    The way that the data is normally organised is to have the actual date or the month/year as an "input" in to a field.
    You could have this data already entered in a table and then selected with a combo box.
    So the sub table would have
    Key Field - holds the key from the main table for the project for the Relationship
    Dated - holds the Month/Year
    Value - the current value that goes in the fields named month/year
     
  7. r24igh

    r24igh Thread Starter

    Joined:
    May 18, 2016
    Messages:
    31
    Great - this makes a lot more sense. However, I can't understand how this would work unless for each project ID there are 36 rows, 1 for each month so that effort can be added across months... I'm no doubt missing something though. How would I get all of the months required?
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,827
    You are correct there would be 36 records for each project, which would be displayed 12 at a time, depending on which year you select.
    But they would be on a subform and the Project Details would be on the mainform.

    With Access you have to get away from thinking in Excel style. :)
     
    r24igh likes this.
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Tags:
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1177601

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice