VBA SQL Inert into Select No Error but not working

[Clete]

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​

 

[james_lankford]

comment this line out
DoCmd.SetWarnings False

 

[GinaWhipp]

Not sure I get it...

DoCmd.RunSQL "DELETE * FROM TotalInterest" <<<You just deleted everything from here
DoCmd.RunSQL "INSERT INTO TotalInterest (FileNo, TotInt) SELECT FileNumber, NewTotalInterest FROM TotalInterestQuery" <<<But I imagine it's in this query so what records would it be appending?

 

[Clete]

I delete the records from the total interest table because it has the total interest amount from the day before. At this point the TotalInterestQuery has already calculated the total interest for the day of and those are those are the records im trying to insert into the interest table which is now empty.

 

[Clete]

Re: VBA SQL Inert into Select No Error but not working (Commented Out Set Warnings to False)

When I debug this and step through... even though my query TotalInterestQuery has 2 records that I want to insert into the now empty Total Interest Table it says trying to append 0 records yes or no?

 

[GinaWhipp]

Re: VBA SQL Inert into Select No Error but not working (Commented Out Set Warnings to False)

What's the entire message? What happens if you run that APPEND query on its own?

 

[Clete]

Re: VBA SQL Inert into Select No Error but not working (Commented Out Set Warnings to False)

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.​

 

[GinaWhipp]

Re: VBA SQL Inert into Select No Error but not working (Commented Out Set Warnings to False)

Hmm, a little confusing what you said there but perhaps an UPDATE query if you are just looking to replace data in specific fields and not actually the whole record.

 

[Clete]

Re: VBA SQL Inert into Select No Error but not working (Commented Out Set Warnings to False)

Ill need to replace the whole record because some days the TotalInterestQuery will have new accounts that aren't already in the TotalInterest table.

 

[GinaWhipp]

Re: VBA SQL Inert into Select No Error but not working (Commented Out Set Warnings to False)

Then my only suggestion would be is to adjust the query till you get only what you want to append. This might required doing a variation on an find unmatched results.