VBA SQL Inert into Select No Error but not working

[cleteh]

The line DoCmd.RunSQL "INSERT INTO TotalInterest (FileNo, TotInt) SELECT FileNumber, NewTotalInterest FROM TotalInterestQuery" The code runs without any errors but its not inserting the record from the query into the the table total interest query. Any help would be greatly appreciated this one procedure has been holding up a project for nearly a week now.


Private Sub Form_Open(Cancel As Integer)
Dim maxDate As Date
maxDate = DMax("SystemDate", "SystemDate")
If maxDate = Date Then
Else
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO SystemDate (SystemDate) VALUES (Date());"

DoCmd.OpenQuery ("Daily Interest")
DoCmd.OpenQuery ("TotalInterestQuery")

DoCmd.RunSQL "DELETE * FROM TotalInterest"
DoCmd.RunSQL "INSERT INTO TotalInterest (FileNo, TotInt) SELECT FileNumber, NewTotalInterest FROM TotalInterestQuery"
DoCmd.Close acQuery, "Daily Interest"
DoCmd.Close acQuery, "TotalInterestQuery"
DoCmd.SetWarnings True
End If
End Sub

[cleteh]

When I step through this my total interest query shows 2 records which is correct.... but on the line to insert these records it asks if I want to append 0 records. It should say 2. This after removing the line where i set the warnings to false.

[cleteh]

Is there a way to just replace the contents of the table? Instead of adding the records to whats already there.

I see whats happening... once i delete the records from the total interest table and run the Insert into line its rerunning the Total Interest Query. The problem with that is the Total Interest Query uses the value in the total interest table to calculate current interest and now it's empty so I have 0 records after the insert into line runs.

How can I just replace the contents of the table with the results of the query? If I don't delete the contents of the total interest table I get a primary key violation. I then added record number as the primary key to get around this but the insert into statement adds the 2 records to the 2 records already there and I need them replaced.

[jonh]

I guess the question is, does the table need to be updated or should you be using a calculated field in a query?