SQL Delete Statement

[A real begineer]

Okay, here I am stuck yet again. No wonder my Mrs. isn't talking to me, the cats left home and there's no food in the fridge. Isn't VBA wonderful?

I have a problem understanding the SQL code properly. At the moment my total knowledge comes from creating queries, copying the SQL code to VBA, creating a form from the query. Deleting the reference to the query and then setting the recordsource for the form to the SQL code in VBA.

This all works quite splendidly.

However, now I want to run a delete query. In itself this is not onerous or difficult, using my vast skills above. However, this delete query is based on another query. My solutions so far, have followed the general approach of creating a form from query one, and then running query two from another form. This requires me to alter the references in query two to match the fields to the form matching query one.

The form we will refer to, as CHistory is the form created from our first query the code looks like this:

Private Sub Form_Load()
Dim strSQL As String
Dim varA As Variant

varA=DMax("[tblCResults.ID]","[tblCResults]","[HomePhone]="& Form_frmCPDetails![Text16])

strSQL = "SELECT tblCResults.*, tblCResults.HomePhone," _
& " " & varA & " AS RPH" _
& ", ([RPH]-5) AS Reference, tblCResults.ID FROM tblCResults" _
&"WHERE ((tblCResults.HomePhone)=[Forms]![frmCWaterAnalysis]![Text22]));"

Me.RecordSource = strSQL

End Sub

This code loads on frmCHistory being opened as a subform within a form called frmCWaterAnalysis.

Within the form frmCWaterAnalysis is a button at the moment called close, which calls a function checkRecord.

Function checkRecord()
Dim strSQL As String

strSQL="DELETE [Forms]![frmCHistory].*, [Forms]![frmCHistory]!Reference, [Forms]![frmCHistory]![tblCResults]!ID FROM tblCResults" _
& " WHERE ((([Forms]![frmCHistory]![tblCResults]![ID])<=[Reference])) and ((tblCResults.HomePhone)=[Forms]![frmCWaterAnalysis]![Text22])) ;"

DoCmd.RunSQL strSQL, True

End Function

The second code generates an error requesting first the [Forms]![frmCHistory]![tblCResults]!ID and then requests [Reference]. These errors I assume can be solved by linking the SQL statement to a different reference source within the form, however, even manually entering the correct references does not delete the intended record.

The object of the delete query is to keep the last five records and delete any other record matching the clients home phone.
As I said this is not a strong area for me, any assistance would be greatly appreciated.

 

[ElsVanMiert]

I think that the problem is the following:

SQL is always referencing to the table structure, not to the form structure.

This means that
"DELETE [Forms]![frmCHistory].*..."
should rather be
"DELETE TABLENAME.*" (Where TableName is the name of a table or query).

Solution of your problem: use the underlying Recordsetclone for your Delete query and requery your form afterwards.

 

[A real begineer]

Thanks for your assistance. I will try that and see how it goes.