Results 1 to 5 of 5
  1. #1
    WendyCha is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    6

    Exclamation #Deleted issue with Delete/Append Query

    I wasnt sure where to put this as I dont know if this a table, query or other issue. I will try to explain the best way possible.

    Basic problem:
    I have an append query. On a form load I run this code:

    Private Sub Form_Load()
    Dim qryName As String
    qryName = "Usage_HPPG"
    CurrentDb.Execute "Delete * FROM Usage_HPPG_Table", dbFailOnError
    CurrentDb.Execute qryName, dbOnError
    End Sub

    The append query(Usage_HPPG) finds the most recent year and places it in Usage_HPPG_Table which then displays that on a label on a form. I clear the table and run the query again on the form load. I have 100 records. This works perfectly for records 3-100 but for the first 2 records I can only see #Deleted in the label when I open the form. I checked the table and the information is there. I need help ASAP!


    More Details if Needed:


    I have a form with a subform. The form is based upon a table called Measures. It has a field called "Measure Number". There are approximately 100 measure numbers or records. The subform is from a table called Numeric and contains columns named "deliverable" and "deliverable year" and "Measure Number". There is already a one to many relationship between Measures and Numeric based upon the Measure Number.

    I created an append query called Usage_HPPG that finds the criteria("deliverable"), the Max of "deliverable year" and "Measure Number" and places it in a table called Usage_HPPG_Table. I created a one to many relationship between the two tables Measures and Usage_HPPG_Table based upon the Measure Number.

    The reason for this is so that when a user puts in a year in the subform for a particular Measure Number or record. The query will find the most recent year(max year) and place it in a field on the form(currently displayed as a label).

    Thanks for ANY help!!!

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    try refreshing your data before you actually view it

    me.refresh refreshes your database with all the most current information

    you may also need to requery a specific combo box or list box with

    <objectname>.requery

    if it's on the active form

  3. #3
    WendyCha is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    6
    rpeare, do i need to add refresh to the on load event or somewhere else? Thanks!!

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    CurrentDb.Execute qryName, dbOnError

    right after this line of your on load event.

    if anything on your form relies on having updated information you will need to requery that object as well (subforms, combo boxes, list boxes, etc)

  5. #5
    WendyCha is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    6
    I added me.refresh, tested it with no luck. I tried all objects to requery with no luck so i just omitted the object part and requeried the whole db. It seems to work ! I hope by omitting the object that i wont run into any problems but I'm so happy to finally not see #deleted anymore!! Thanks rpeare! You're the best!

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

Similar Threads

  1. Trying to Delete record using delete query
    By MooseOTL in forum Access
    Replies: 13
    Last Post: 10-04-2011, 02:30 AM
  2. Replies: 3
    Last Post: 07-27-2011, 02:07 PM
  3. Replies: 7
    Last Post: 07-21-2011, 01:01 PM
  4. Replies: 4
    Last Post: 06-14-2010, 02:31 PM
  5. Append query won't append
    By yelkenli in forum Queries
    Replies: 5
    Last Post: 02-12-2010, 11:19 AM

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 - Senior Forums