How to programically delete new record? 1

[kzutter]

How can I delete (or cancel) the form's current record when Me.NewRecord is true?
Using DoCmd.RunCommand acCmdDeleteRecord throws error #2046 (The command or action 'DeleteRecord' is not available now.)

I tried Me.Undo, but that does not delete (or cancel) the record.

I need to do this from code, as the user requires both "Create New Record" and "Delete Current Record" buttons.

 

[TheAceMan1]

How are ya kzutter . . .

Use Me!Undo twice!

[blue]   Me!Undo
   Me!Undo[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[kzutter]

Thanks Ace!

One thing that was tripping me up was that I was expecting the form to move off of the new record. It appeared as though it was not canceling the new record.

Here is what I ended up with - so far works with all the testing I have done.

If Me.NewRecord Then
Me.Undo
Me.Undo
DoCmd.GoToRecord acDataForm, Me.Name, acFirst
Else
DoCmd.RunCommand acCmdDeleteRecord
End If

 

[rbenson1]

I have a recordset that always returns 1 record (The record that was just added to the table)

I take the information from the record and copy it field by field into specific cells in an excel spreadsheet set up to act as a form.

It then prints the form

The problem I am having is that there seems to be a delay from when the recordset is opened until the recordset fields become available to read/copy.

As a work around I have a loop that tests the EOF property of the recordset until its false.

The result being a 8 to 15 second delay from the time the user clicks the 'print' button.

My question is: What is causing the delay and is there a faster, better way, to copy a specific record into an excel spreadsheet.

Here is my code: *NOTE: PrintNcr is called from a button

Private Sub Form_Open(Cancel As Integer)
    ' Create an instance of Excel and add a workbook
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Open("C:\PATH\NCR.xlsx")
    Set xlWs = xlWb.Worksheets("Sheet1")
    
    ' Display Excel and give user control of Excel's lifetime
    xlApp.Visible = False
    xlApp.UserControl = True
    
    ' Set the string to the path of your database
    strDB = "C:\PATH\TestDB.accdb"
    
    ' Open a connection to the data source
    cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=" & strDB & ";"
    
End Sub

Sub PrintNcr(NcrID As Long)
On Error GoTo ErrorHandler:
       
    ' Open recordset based on NcrLog table
    rst.Open "SELECT * FROM NCR WHERE ID=" & NcrID & ";", cnt
        
    ' Check version of Excel
    If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
        'EXCEL 2000,2002,2003, or 2007: Use CopyFromRecordset
         
        ' Copy the recordset to the worksheet
        If rst.EOF = True Then
            Do While rst.EOF = True
                On Error Resume Next
                    rst.MoveFirst
            Loop
        End If
        
        xlWs.Cells(2, 102).Value = rst.Fields(0).Value
        xlWs.Cells(8, 31).Value = rst.Fields(1).Value
        xlWs.Cells(8, 47).Value = rst.Fields(2).Value
        xlWs.Cells(8, 60).Value = rst.Fields(3).Value
        xlWs.Cells(8, 103).Value = rst.Fields(4).Value
        xlWs.Cells(13, 2).Value = rst.Fields(5).Value
        xlWs.Cells(13, 37).Value = rst.Fields(6).Value
        xlWs.Cells(13, 47).Value = rst.Fields(7).Value
        xlWs.Cells(13, 85).Value = rst.Fields(8).Value
        xlWs.Cells(23, 15).Value = rst.Fields(10).Value
        xlWs.Cells(39, 2).Value = rst.Fields(9).Value
        xlWs.Cells(46, 56).Value = rst.Fields(11).Value
        xlWs.Cells(46, 97).Value = rst.Fields(12).Value
        
        xlWs.PrintOut
        xlApp.DisplayAlerts = False

        'Note: CopyFromRecordset will fail if the recordset
        'contains an OLE object field or array data such
        'as hierarchical recordsets
        
    Else
        MsgBox ("Excel 97 not supported")
    End If
ErrorHandler:
    rst.Close
    Set rst = Nothing

End Sub

Private Sub Form_Close()
    'Dereference excel instances
    xlApp.Quit
    Set xlWs = Nothing
    Set xlWb = Nothing
    Set xlApp = Nothing
    
    'Close ado connection
    cnt.Close
    Set cnt = Nothing
End Sub

 

[rbenson1]

I apologize, I posted to the wrong thread....