No Current Record - What does this mean? 2

[Dherrera]

I have a main form with a subform in it. When the main form is opened it generates an expense id number. the subform contains the expense information for that id number. the subform has its own table that is connected to the mainform's table via the expense id. the subform can have multiple entries and each one is distinguished by an id number.

i am having trouble canceling out of the current expense. i want to click a cancel button and have it delete all info from the subform's table that relates to the current expense id and also have delete the related info from the main form's table, but when i click on the button it tells me "No Current Record". any ideas?

 

[Schof]

How about posting your Cancel button's _OnClick event code?

 

[Dherrera]

here is the cancel button code.

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
    Beep
    
    If MsgBox("Are you sure want to cancel?  All data that has been entered will be lost.", vbQuestion + vbYesNo, "Do you wish to cancel?") = vbYes Then
        
        Form.Undo
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
        
        DoCmd.Close
        
    Else
        'do nothing
    End If
    
Exit_cmdDelete_Click:
    Exit Sub

Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
    
End Sub

 

[Schof]

What about deleting the records using something like this?

docmd.setwarnings false
docmd.runsql "DELETE * FROM <childTable> " & _
             "WHERE <expenseIDcol> = " & me.expenseID
docmd.runsql "DELETE * FROM <parentTable> " & _
             "WHERE <expenseIDcol> = " & me.<expenseIDctl>
docmd.setwarnings true
me.requery

where <childTable> is the subform's table
and <parentTable> is the main form's table
and <expenseIDcol> is the name of the column in the main/sub form's table
and <expenseIDctl> is the name of the control in the main form that contains the expense ID

 

[willir]

Wow, stop, cease!

but when i click on the button it tells me "No Current Record"

Are you deleting the main / parent record before deleting the child records. I smell orphans, and I have to wonder if you have the date integrity option set correctly with your data.

Two approaches...
- Easiest: In your reslationship, enforce "Cascade Deletes"
- Better: Before deleting the parent record...

Dim strSQL as String

If MsgBox("Are you sure want to cancel?  All data that has been entered will be lost.", vbQuestion + vbYesNo, "Do you wish to cancel?") = vbYes Then


     strSQL = "DELETE FROM YourExpenseTable WHERE ExpenseID = " & Me.ExpenseID  'on main form

     dbs.Execute strSQL

    '...Then your code

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

'No need for ELSE

End If

Why is code better (from my perspective)? So you can warn the end user, or set things up under your control.

Am I right that you were deleting the parent record before deleting the child?

Richard

 

[Dherrera]

i do have the relationship set to cascading deletes. i dont think that the parent record is being deleted first.

im getting an error on your code. its telling me 'object required' at the line: db.Execute strSQL
not really sure what that means.

 

[Dherrera]

what do you mean by date integrity?

 

[Dherrera]

The error is occuring on this line:

DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

 

[willir]

6" references "Delete", or more specifically, "acDelete" from the Edit Menu.

(...and "8" refers to "acSelectRecord")


My interpretation of this is that you may not be on a valid record.

If you are using code, or your own delete button, try using the menu to delete a record. If the menu approach works, then look at your code in how you are deleting the record.

 

[RoyVidar]

Yeah - no current record - means exactly that, one is trying to perform something that requires a (current) record, and it isn't there. This can happen for sevarel reasons, one is for instance trying to delete a record that isn't saved, I think it would also occur using those "menu thingies" on/after deleteing the last record of the form. Anyway - if it performs as it should (except the message), you could always just "remove" the message by adding the follwing in your errorhandler:

[tt]yourexit:
exit sub
yourerror:
if err.number<>3021 then
err.description
endif
resume yourexit
end sub[/tt]

The object required error, is probably because the dbs object variable insn't declared and/or instantiated, try just replace it with currentdb.

But - I'd go for something along the lines of what Schof presented. Deleting the child records first, then the parent record, in some controlled manner (and try a save of the current record, or better a Me.Undo before attempting anything?). Then just to put forward another method (ADO):

[tt]currentproject.connection.execute strSQL[/tt]

Roy-Vidar

 

[Dherrera]

thanks guys i appreicate you help. i went head and used the error handler to bypass the error message, it works fine.