"The command or action 'DeleteRecord' isn't available now."

B

bballhermit

Registered User.
Local time
Today, 16:02
Joined
Oct 14, 2010
Messages
40
I have a form for an administrator to manage task orders. It is composed of a list box and some text fields to modify the currently selected task order in the list box. There is also a button to delete the currently selected t.o. record. I have very similar forms for other things, such as employees, where the delete button is working fine, but for some reason this form returns a "The command or action 'DeleteRecord' isn't available now." error. Any ideas for me? The form's code is below.

Also, the T.O. data is stored in a single table with columns of TOID, ContractID (linking to another table with contracts), TONumber, and TOTitle.

Thanks!

Code: 
Option Compare Database
Option Explicit
'Import Filename Handle
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Type OPENFILENAME

    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
    
End Type
'Update Enabled Buttons Based on Null/Non-Null Dataset
Function UpdateEnables()

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("tblTOs", dbOpenDynaset)

    If rs.EOF Then
        Me.txtTONumber.Enabled = False
        Me.txtTOTitle.Enabled = False
        Me.cmboTOContract.Enabled = False
        Me.cmdDelete.Enabled = False
    Else
        Me.txtTONumber.Enabled = True
        Me.txtTOTitle.Enabled = True
        Me.cmboTOContract.Enabled = True
        Me.cmdDelete.Enabled = True
    End If

End Function
'Null/Non-Null Dataset Check
Private Sub Form_Open(Cancel As Integer)
    
    Call UpdateEnables

End Sub
'Update lstTaskOrders on Dataset Update
Private Sub lstTaskOrders_AfterUpdate()
On Error GoTo Err_AfterUpdate_Click

    Me.RecordsetClone.FindFirst "[TOID] = " & Me![lstTaskOrders]
    Me.Bookmark = Me.RecordsetClone.Bookmark

Exit_AfterUpdate_Click:
    Exit Sub

Err_AfterUpdate_Click:
    DoCmd.SetWarnings True
    MsgBox Err.Description
    Resume Exit_AfterUpdate_Click

End Sub
'Delete Key Handle
Private Sub lstTaskOrders_KeyUp(KeyCode As Integer, Shift As Integer)
    
    If KeyCode = vbKeyDelete Then
        Call cmdDelete_Click
    End If

End Sub
'Update txtTONumber on Dataset Update
Private Sub txtTONumber_AfterUpdate()

    If Me.Dirty Then Me.Dirty = False
    Me.lstTaskOrders.Requery

End Sub
'Update txtTOTitle on Dataset Update
Private Sub txtTOTitle_AfterUpdate()

    If Me.Dirty Then Me.Dirty = False
    Me.lstTaskOrders.Requery

End Sub
'Update cmboTOContract on Dataset Update
Private Sub cmboTOContract_AfterUpdate()

    If Me.Dirty Then Me.Dirty = False
    Me.lstTaskOrders.Requery

End Sub
'Import Task Orders Template CSV
Private Sub cmdImportCSV_Click()
On Error GoTo Err_ImportCSV_Click

    DoCmd.Close

    Dim stDocName As String
    Dim intResponse As Integer

    Dim OFName As OPENFILENAME
    OFName.lStructSize = Len(OFName)
    OFName.lpstrFilter = "CSV Files (*.csv)" + Chr$(0) + "*.csv" + Chr$(0) + "All Files (*.*)" + Chr$(0) + "*.*" + Chr$(0)
    OFName.lpstrFile = Space$(254)
    OFName.nMaxFile = 255
    OFName.lpstrFileTitle = Space$(254)
    OFName.nMaxFileTitle = 255
    OFName.lpstrInitialDir = "C:\"
    OFName.lpstrTitle = ""
    OFName.flags = 0
    If GetOpenFileName(OFName) Then
        intResponse = MsgBox("Are you sure you want to import this file? Duplicate records will be overwritten by the new data.", vbYesNo + vbExclamation, "Infoscitex Corporation PMBS")
        Select Case intResponse
            Case vbYes
                DoCmd.SetWarnings False
                DoCmd.TransferText acImportDelim, "importspecTOs", "tblTOsTemp", OFName.lpstrFile
                DoCmd.SetWarnings True
                DoCmd.RunSQL "UPDATE tblTOs INNER JOIN tblTOsTemp ON (tblTOs.ContractID=tblTOsTemp.ContractID) AND (tblTOs.TONumber=tblTOsTemp.TONumber) SET tblTOs.TOTitle=[tblTOsTemp].[TOTitle] WHERE ((tblTOs!ContractID=tblTOsTemp!ContractID) AND (tblTOs!TONumber=tblTOsTemp!TONumber) AND (tblTOs!TOTitle<>tblTOsTemp!TOTitle));"
                DoCmd.RunSQL "INSERT INTO tblTOs (ContractID, TONumber, TOTitle) SELECT DISTINCT tblTOsTemp.ContractID, tblTOsTemp.TONumber, tblTOsTemp.TOTitle FROM tblTOsTemp LEFT JOIN tblTOs ON (tblTOs.ContractID=tblTOsTemp.ContractID) AND (tblTOs.TONumber=tblTOsTemp.TONumber) WHERE tblTOs.ContractID IS NULL AND tblTOs.TONumber IS NULL;"
                DoCmd.RunSQL "DELETE FROM tblTOsTemp;"
                MsgBox "File Imported Succesfully. Some records may have been ommitted if they had a null value in a required field.", vbOKOnly + vbInformation, "Infoscitex Corporation PMBS"
            Case Else
                MsgBox "Import Cancelled.", vbOKOnly + vbInformation, "Infoscitex Corporation PMBS"
        End Select
    Else
        MsgBox "Import Cancelled.", vbOKOnly + vbInformation, "Infoscitex Corporation PMBS"
    End If
    
    Dim stDocName2 As String
    Dim stLinkCriteria2 As String

    stDocName2 = "fmnuTaskOrders"
    DoCmd.OpenForm stDocName2, , , stLinkCriteria2
    
Exit_ImportCSV_Click:
    Exit Sub

Err_ImportCSV_Click:
    MsgBox Err.Description
    Resume Exit_ImportCSV_Click

End Sub
'Launch Add New Task Order Form
Private Sub cmdAdd_Click()
On Error GoTo Err_Add_Click

    DoCmd.Close

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmAddTaskOrder"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Add_Click:
    Exit Sub

Err_Add_Click:
    MsgBox Err.Description
    Resume Exit_Add_Click
    
End Sub
'Delete Currently Selected Task Order
Private Sub cmdDelete_Click()
On Error GoTo Err_delete_Click

    Dim stDocName As String
    Dim intResponse As Integer
    
    intResponse = MsgBox("Are you sure you want to delete this task order?", vbYesNo + vbExclamation, "Infoscitex Corporation PMBS")
    
    Select Case intResponse
        Case vbYes
            DoCmd.SetWarnings False
            DoCmd.RunCommand acCmdDeleteRecord
            DoCmd.SetWarnings True
            Me.Requery
            Me.lstTaskOrders.Requery
            Call UpdateEnables
        Case Else
            MsgBox "Task order not deleted.", vbOKOnly + vbInformation, "Infoscitex Corporation PMBS"
    End Select

Exit_delete_Click:
    Exit Sub

Err_delete_Click:
    DoCmd.SetWarnings True
    MsgBox Err.Description
    Resume Exit_delete_Click
    
End Sub
'Return to Administrator Menu
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click

    DoCmd.Close
    
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "fmnuAdministratorMenu"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClose_Click:
    Exit Sub

Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
    
End Sub
 
You need to indicate at what line the error occurs. :)
 
Could you point me to a resource showing me how to debug to determine where the error occurs? I am pretty new to Access, and if I knew how to determine where in the code the error pops up, I might be able to figure it out on my own...

I can tell you that the error probably occurs in the delete function re-copied here:

Code: 
'Delete Currently Selected Task Order
Private Sub cmdDelete_Click()
On Error GoTo Err_delete_Click

    Dim stDocName As String
    Dim intResponse As Integer
    
    intResponse = MsgBox("Are you sure you want to delete this task order?", vbYesNo + vbExclamation, "Infoscitex Corporation PMBS")
    
    Select Case intResponse
        Case vbYes
            DoCmd.SetWarnings False
            DoCmd.RunCommand acCmdDeleteRecord
            DoCmd.SetWarnings True
            Me.Requery
            Me.lstTaskOrders.Requery
            Call UpdateEnables
        Case Else
            MsgBox "Task order not deleted.", vbOKOnly + vbInformation, "Infoscitex Corporation PMBS"
    End Select

Exit_delete_Click:
    Exit Sub

Err_delete_Click:
    DoCmd.SetWarnings True
    MsgBox Err.Description
    Resume Exit_delete_Click
    
End Sub

Thanks.
 
When the error occurs do you get a modal popup that offers you <End>, <Debug> and <Help> options? If so click <Debug>.
If not click, at the same time, hit <Ctrl>+<Break> and see if it breaks into code at a particular line...
 
Ok, right. So, debug kicks me to the line highlighted below, which is kind of obvious, as that was the error, but that doesn't help me determine why this command isn't available?

Code: 
'Delete Currently Selected Task Order
Private Sub cmdDelete_Click()
On Error GoTo Err_delete_Click

    Dim stDocName As String
    Dim intResponse As Integer
    
    intResponse = MsgBox("Are you sure you want to delete this task order?", vbYesNo + vbExclamation, "Infoscitex Corporation PMBS")
    
    Select Case intResponse
        Case vbYes
            DoCmd.SetWarnings False
[COLOR="DarkRed"]            DoCmd.RunCommand acCmdDeleteRecord[/COLOR]
            DoCmd.SetWarnings True
            Me.Requery
            Me.lstTaskOrders.Requery
            Call UpdateEnables
        Case Else
            MsgBox "Task order not deleted.", vbOKOnly + vbInformation, "Infoscitex Corporation PMBS"
    End Select

Exit_delete_Click:
    Exit Sub

Err_delete_Click:
    DoCmd.SetWarnings True
    MsgBox Err.Description
    Resume Exit_delete_Click
    
End Sub
 
I would never rely on DoCmd.RunCommand to delete a record. Note that no particular record is specified from no particular table? Yikes! Keep your fingers crossed.
If this code runs on a form, consider using the form's delete method. Best option is a delete query or delete query text using data from the form, like the ID field. Consider ...
Code: 
CurrentDB.Execute _
  "DELETE FROM SomeTable " & _
  "WHERE SomeTableID = " & me.SomeTableID
See how that leaves zero room for ambiguity?
Cheers,
 
Ok, thanks, that does make more sense. How then do I get it to delete the record that is currently selected in the form's list box, then? Is that what your "me.SomeTableID" handles? Does the "me" grab whatever record is selected? Thanks.
 
'Me' is a reference to the object in which code is currently running, so for code on a form it refers to the form.
A listbox is commonly configured such that the value of the control is equal to the ID of item selected in the list. Certainly this is what the listbox wizard tries to do. If that is the case, and if your listbox is called MyList, you can delete the selected item using ...
Code: 
currentdb.execute _
  "DELETE FROM YourTable " & _
  "WHERE YourTableID = " & Me.MyList
 
Thanks, lagbolt, could you please specify a tad more on that sql statement? I'm real new. So, I have a form with a listbox called lstEmployees pulling data from a table called tblEmployees. I want the delete command (called by a button on the form) to delete the currently selected record in the listbox. Here is the code you gave me modified. What do I need to set the where statement equal to? What else is needed? Thanks.

Code: 
currentdb.execute _
  "DELETE FROM tblEmployees " & _
  "WHERE tblEmployees = " & Me.lstEmployees
 
Nevermind, I figured it out. I just had to do something like...

Code: 
"WHERE tblEmployees.EmployeeID ="

Thanks.
 
You must log in or register to reply here.

Similar threads

J
code to close my current form on pressing the button to open my Main Menu
2
Replies
20
Views
1,080
Edgar_
E
mike60smart
Syntax
Replies
8
Views
498
Mark_
M
Y
New with a problem
Replies
4
Views
415
Pat Hartman
P
J
Debugger complains about [apparently] non-existant characters
Replies
9
Views
440
The_Doc_Man
The_Doc_Man
A
Solved VBA code for deleting Outlook 'Appointments' lying within a specified date range
Replies
8
Views
615
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom