Results 1 to 10 of 10
  1. #1
    LonnieCAR is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Location
    Kansas City, MO
    Posts
    32

    Deleting data from a report using form


    I have a project that was given to me because I have previous experience with MS Office, but my level of Access experience is somewhat below par. Here is my problem: We are using one form (Enter Required Training) to create a row of data that goes to a report to let us know when employees need to be trained by. This report is called 'Past Due - Required Trng'. When employees have completed a training course we go into a form called 'Enter Training Attendance' to update another report to show that they have completed training. However, once employees have completed a course, we have to manually go to the database where the required training courses are stored to manually delete them so that they do not show up on the report. My boss would like me to find a way to automatically delete the required training information without having to manually delete them each time. Is it possible to have the 'Enter Training Attendance' form delete the record created by the 'Enter Required Training' form so that it does not show-up on the report once completed? I have been messing around with the datasheets, events and even formatting but I can't seem to get anything to work. Any help would be amazing here. Thank you!

  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
    14,708
    Reports are really a mean to display data that exists is a table(s). Normally you would use a Form to enter data that will be stored in a Table. When you want to report information, you might use a form with some parameters to select StartDate for Report, EndDate for Report and a start button.

    You enter or select the appropriate dates, nit the start button and behind the scenes, the RecordSource (which identifies the data for your report is created and assigned) then the report opens.

    You might want to search for some youtube videos related to this. http://www.youtube.com/watch?v=AKzB9_9TsK8

    See Martin Green's site also http://www.fontstuff.com/access/acctut19.htm He has a number of tutorials (not video).

  3. #3
    LonnieCAR is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Location
    Kansas City, MO
    Posts
    32
    Then how do I have a form delete a row of data that was created by another form automatically. I can always show screenshots if that is possible.

  4. #4
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    Hi -

    Deleting data is never really a good idea if you can avoid it. Deleted, i.e. "missing" data invariably comes back to bite you in the backside.

    That said, reports do not "contain" data, therefore you cannot "delete" from them, and you cannot "update" them. When you use your forms, you are not adding to or updating reports - you are adding to or updating the table(s) upon which the reportS are based.

    Instead, what you have to do is define criteria that specify which data is to be included in the report - a filter, in other words.

    In your case, the "required training report" would list all training that has not yet been completed; this is where the criteria come in. Take a look at your data, and determine how, for any given training item, you determine whether or not it has been completed. Then, you design a query to exclude records that meet the "completed" criteria, and base the report on that query.

    Also, I hope your two forms "Enter Required Training" and "Enter Training Attendance" are updating records in the same table, because if they are not, STOP right there and redesign the database.

    Post back if you need more help.

    John

  5. #5
    LonnieCAR is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Location
    Kansas City, MO
    Posts
    32
    They are updating to the same table. Sorry if I am mixing-up some of the terminology here... I am still new and learning Access. What's the easiest way to design a query for doing what you described?

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,708
    You can run /open 1 form after another to achieve a record delete.

    But you wouldn't typically use a form to delete a record in a report.. That may be the way you see these, but tables are where data is stored; forms is how data in tables is usually modified --NEVER LET USERS INTERACT WITH TABLES DIRECTLY.
    and Reports are often printed to show users the status, or some summary of information at a point in time.

    There are some free videos by 599cd (although he also has a commercial business for Access training). He has a 12 video set for Access 2010. http://www.youtube.com/watch?v=pHiOXZEbK-4

  7. #7
    LonnieCAR is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Location
    Kansas City, MO
    Posts
    32
    I am the only one with access to everything. lol. I will see what I can do.

  8. #8
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    Hi -

    Please post the structure of the table (field names, data types); with that we can suggest the criteria for a query. If you have the proper data fields, the query should be quite easy.

    John

  9. #9
    LonnieCAR is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Location
    Kansas City, MO
    Posts
    32
    Both the 'Enter Training Attendance' form and the 'Enter Required Training' form create records in the 'Edit Employee Data' sheet. This is the sheet I have to go to manually delete records so that they do not show-up on the 'Past Due -Required Trng' report. The forms contain the following field names: Employee ID; Training; Provider; Completion Date, Reason; Approved by and a field for any attachments. The first one is a number and the date contains a date. All others contain text. Some of the text boxes have a drop down menu with preset options.

  10. #10
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    Hi -

    If both your forms are creating records in the tables, then that is a problem. The "Enter Required Training" form should be the one to create records - the "Enter Training Attendance" form should only update existing records, to indicate required training has been completed.

    Your table may need more fields in it, depending on what Completion Date is used for. If it means the date the training was completed, then that is OK, but if it means the date the training is required to be completed by, then you need another field. Rename Completion Date to Required Completion Date, and add another field called Actual Completion Date (suggestion).

    Now, uncompleted training will have a Null in Completion Date or Actual Completion Date, depending on how you use it (see above), and that will be the basis for a query.

    Create a new query, and add to it all fields from the Edit Employee Data table. In the criteria box for Completion Date or Actual Completion Date (see above, again), put Is Null as the criteria. When you run that query, you will get a list of training not completed. Save the query as Uncompleted_Training (for example), and use the query as the record source for the report.

    HTH

    John

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

Similar Threads

  1. deleting some data
    By kris335 in forum Access
    Replies: 2
    Last Post: 09-26-2012, 03:17 PM
  2. Replies: 11
    Last Post: 12-14-2010, 01:25 PM
  3. Deleting filtered data
    By mylesg in forum Queries
    Replies: 1
    Last Post: 10-23-2010, 07:18 PM
  4. Replies: 1
    Last Post: 07-19-2010, 09:50 AM
  5. Deleting certain data
    By swindmiller in forum Queries
    Replies: 3
    Last Post: 02-17-2010, 10:39 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
  •  
Tech Forums: Microsoft Office Forums