DAO set Currentdb error 2

[Apollo13a]

Hello, When I run this function I get an error that says "Can't not find Table or query". The error is on the line Set rsDelete = CurDB.OpenRecordset(strSQL)

Public Function DeleteRoutine(ID As Integer) As Boolean
Debug.Print

Dim IDToDelete As Integer
IDToDelete = ID

Dim strSQL As String
Dim strSQLD As String

Dim CurDB As DAO.Database
Set CurDB = CurrentDb

Dim rsDelete As DAO.Recordset

strSQL = "tblCurrentStatus.tblCurrentStatusPK, " & _
"tblCurrentStatus.tblHistoryID, " & _
"tblCurrentStatus.tblReferralID, " & _
"tblCurrentStatus.Status, " & _
"tblCurrentStatus.OnTheListStatus, " & _
"tblCurrentStatus.StatusDate, " & _
"tblCurrentStatus.IndexNumber, " & _
"tblCurrentStatus.DateWritten, " & _
"tblCurrentStatus.TicketNumber " & _
" FROM tblCurrentStatus" & _
" WHERE (((tblCurrentStatus.tblHistoryID)=" & IDToDelete & "))"


strSQLD = "delete tblCurrentStatus.tblCurrentStatusPK, " & _
"tblCurrentStatus.tblHistoryID, " & _
"tblCurrentStatus.tblReferralID, " & _
"tblCurrentStatus.Status, " & _
"tblCurrentStatus.OnTheListStatus, " & _
"tblCurrentStatus.StatusDate, " & _
"tblCurrentStatus.IndexNumber, " & _
"tblCurrentStatus.DateWritten, " & _
"tblCurrentStatus.TicketNumber " & _
" FROM tblCurrentStatus" & _
" WHERE (((tblCurrentStatus.tblHistoryID)=" & IDToDelete & "))"

Set rsDelete = CurDB.OpenRecordset(strSQL)


If Not rsDelete.BOF And Not rsDelete.EOF Then
    rsDelete.FindFirst "tblhistoryID= " & IDToDelete & ""
    
      DoCmd.RunSQL (strSQLD)
      DeleteRoutine = True
      
Else
       DeleteRoutine = False
       
End If

      rsDelete.Close
Set rsDelete = Nothing

End Function

Any ideas?
Thanks
Jim

 

[Eupher]

Hi, Jim,

Looks like you're missing the SELECT keyword from your strSQL string, i.e.

strSQL = "[red]SELECT[/red] tblCurrentStatus.tblCurrentStatusPK, " & _
...

Ken S.

 

[JerryKlmns]

And it would be much faster and easier if you just delete that record like

Dim RecAffected

CurrentProject.Connection.Execute "Delete From tblCurrentStatus Where tblHistoryID = " & IDToDelete & ";", RecAffected, adCmdText
If RecAffected>0 then
DeleteRoutine = True
Else
DeleteRoutine = False
Endif

If it exists then it is deleted and RecAffected says how many where actually deleted, else nothing happens. You check RecAffected and set DeleteRoutine appropriately.

 

[Apollo13a]

That was it. Thank you both for the info.