Results 1 to 7 of 7
  1. #1
    chloe is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    11

    Exclamation Wondering how to design a event code to do two tasks: refresh page and add status to column in table


    Good morning!

    I am a pretty new access user and, while I am computer savvy, I'm running into some issues with coding. I have a button on my form that I have setup so that when the person entering information it "saves it" and clears the form. Really its really just a refresh code so that the form appears blank and ready for the next entry (shown below). What I'm wondering is:

    1) Can I program this event code to do TWO things - refresh the page, and then update the status of the form to a column in the table?
    Example: When I click this button, it will add the word "Pending" to the Status column of my table and refresh/save the form as new. I want it to do both things. My goal is to have a couple different buttons on my form that will generate different statuses, depending on the stage of production (Pending, In Progress, Verified, Complete).

    Right now, my event procedure code is: DoCmd.GoToRecord acDataForm, "DatabaseA", acNewRec

    What do I need to do to have it do both of those features. Can I even code one button to do two things or is that asking too much of access?

    Also, part 2 of this question, If this button thing can work, is there a way so that I can create a "status box" on the form that reflects what the column currently says? My thought process is, if a record comes up and shows its "in progress" the user will know someone is working on it vs. "pending" where it means something needs to be done, verified meaning it has to undergo review by a supervisor, and complete meaning everything is done. It sounds more complicated than it is.

    If anyone has any other suggestions, I'm open for ideas, but this would be the ideal way for this form to function.

    Thanks a bunch!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you looked at setting the form's Data Entry property to True/Yes?
    Not sure exactly where your Status would go...

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't really understand issue. If status is a value saved with record, can have textbox bound to field for display.

    Yes, each status button can have code that populates the status field and saves record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    chloe is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    11
    The issue is I want to click a button that says "save and refresh" that will both refresh the page for a new entry and place the "status" column in the linked data table to entered. I want another button the form to say "save and answer" that will both refresh the page for a new entry and change the status column in the table to say "in progress". I don't know how to do this. Code examples would be great. I do not want a text box or a list box, I want to just click the button and have it do those two things.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think you are in for some custom vba coding. When you save a record you can update tables with the data you want.
    I think you are confusing WHAT you want to happen, with HOW you think it should be done.

    Anyway, good luck with your project.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Are you saying the field you want to edit is not part of the form RecordSource? Where is 'status column'?

    "Refresh the page for a new entry" by moving to new record row. That will also save the current record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    First off, moving to a New Record has nothing to so with refreshing a form; that's something else, entirely, and you're confusing the issue by using it, here!

    Secondly, as June7 said, the act of moving to a New Record will save the Current Record and make the Form ready for a New Record, with all Controls empty.

    Assuming that the Status Field is part of the RecordSource for the Form, simply place a Control on the Form with its Control Source set to that Field.

    Rather than having four 'save and whatever' buttons, I'd have one button for moving to a New Record (using your above code) which will save the Record and use a Combobox as the Control for the selection of the Status Field. It's not hard to do:

    1. Place a Combobox on the Form
    2. Select "I will type the Values I want"
    3. Under Col1 enter Pending, In Progress, Verified and Complete
    4. Step through the rest of the Combobox Wizard
    5. With the Combobox selected, go to Properties - Data and set the Control Source to the Status Field.


    If you want, you can make the Status Field required by using code in the Form_BeforeUpdate event to check that a status has been selected, and cancel the update until it is.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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

Similar Threads

  1. How to design table of working-tasks in a meaningful way?
    By ICBSprod in forum Database Design
    Replies: 1
    Last Post: 02-12-2014, 09:49 AM
  2. Column Widhts event code
    By alka in forum Access
    Replies: 2
    Last Post: 11-21-2013, 07:18 PM
  3. Replies: 4
    Last Post: 06-20-2013, 10:26 PM
  4. Replies: 1
    Last Post: 06-28-2012, 01:46 AM
  5. Design table - keeping worker status
    By snoopy2003 in forum Database Design
    Replies: 8
    Last Post: 02-23-2011, 12:48 PM

Tags for this Thread

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