DoCmd Questions. Tuffy.

[quest4]

In my futile quest to delete a record in a subform I have stumbled across a couple DoCmd items which almost work. I first discovered DoCmd.RunCommand acCmdDeleteRow, but when I ran it I got a message that it was not available. Why? Then I tried this:
[Forms]![frmECNMasterData200]![BOMComponent(s)]! PartNumber.SetFocus
DoCmd.Echo False
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.Echo True
DoCmd.SetWarnings True
DoCmd.Close acForm, "frmWarningECN_BOM"
It almost works, except that it deletes the first record instead of the record where the SetFocus has put the curser. Why? Is there a way of telling it CurrentRecord? Thank you to anyone who can answer these questions

 

[MelissaT]

Are you trying to delete a record by clicking on a button? Is so, are you putting the button on the main form or the subform. If you put the button on the subform and on the OnClick event procedure put the code below, it will work.

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


Melissa
Designing Access databases since 1999

 

[quest4]

Thank you for the response, Melissa. Actually it is returning to the subform from a Error Message Form, which has a couple of CmdButtons on it. The OnClick of that form engages the code above, and basically it is a bad part number that was entered and needs to be deleted from the subform. We are entering BOM's for parts on this subform. I tried your code eariler and it failed, the code above is as close as I have gotten to deleting the bad record, which usually is the last record in the subform and it has focus, in theory. Thank you again for the response.

 

[WildHare]

In the past, I've always found it easier to delete a single record on the MANY side of a One-to-Many relationship with a pre-written query that uses the form key values to delete the record in question.

Just have critieria in your query that will refer to the open form and subform keys, and delete the sub-form record.

Then, when you want to delete a record from the subform, make sure it is the 'current' record from the -many side table, and run your query.

It's a snap if the beast is designed correctly.

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[Gazonice]

If you want to delete a record or several records from a sub form based on a table you could try something like the example below or a variation of it.


DoCmd.RunSQL "DELETE FROM tblResults WHERE PrimKey = " & [ReferencePrimKey]

Garry

 

[quest4]

Thank you for the response, Gazonice. I vaguely tried something like and and I guess I screwed it up because I never got it working or even close. This is being done from a form in the OnClick event function, and the only reason that this form opened was the entered part number was a bad part number so I want to delete it. If it matters the subform is a continuous form. Thank you again for the response.

 

[LambertH]

Try...

[Forms]![frmECNMasterData200]![BOMComponent(s)]! PartNumber.SetFocus
DoCmd.RunCommand acCmdSelectRecord
DoCmd.Echo False
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.Echo True
DoCmd.SetWarnings True
DoCmd.Close acForm, "frmWarningECN_BOM"

Setting the focus to a field in a record does not select the record.

 

[quest4]

Thank you LambertH, for the suggestion. It deleted thwe fisrt row instead of the last. Thank you for the suggestions.