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