#Deleted issue with Delete/Append Query

[WendyCha]

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!!!

[rpeare]

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

[WendyCha]

rpeare, do i need to add refresh to the on load event or somewhere else? Thanks!!

[rpeare]

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)

[WendyCha]

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!