Delete record error

[Guest]

Hi,

I have a form with 2 subforms on it. I have checked off "Enforce
Referential Integrity" and "Cascade Update Fields" and "Cascade Delete
Related Records" in the Edit Relationships window of the 2 relationships
(main to sub1 and main to sub2).

The delete button is on the mainform. My code for deleting a record is:
Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

Me.AllowDeletions = True
DoCmd.SetWarnings False ' Suppresses the warning message
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.AllowDeletions = False
DoCmd.SetWarnings True ' Turns the warning message setting back on
DoCmd.Close acForm, Me.Name

Exit_cmdDelete_Click:
Exit Sub

Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click

End Sub

After the line "DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
" I get this error: "You entered an expression that has an invalid reference
to the property |."

What is this error and how can I fix it?

Any help is appreciated.

Thanks

 

[Guest]

Hi.

I have checked off "Enforce
Referential Integrity" and "Cascade Update Fields" and "Cascade Delete
Related Records" in the Edit Relationships window of the 2 relationships
(main to sub1 and main to sub2).

One cannot set referential integrity on forms, only tables. I'll assume
that these settings are on the tables that these forms are bound to, and that
the main form is bound to the table on the one side of the relationship, and
that each of the subforms is bound to a table on the many side of the
relationship. If this assumption is incorrect, please advise me.

What is this error and how can I fix it?

Your code is written in VBA 4.0, which works fine in Access 95, but it
doesn't always work in later versions. I'll assume you aren't using Access
95, so I'll recommend converting this old code to your current version of
Access's VBA, which I'll guess is VBA 6.2 (Access 2002 & 2003). I believe
the equivalent to the Access 95 menu items selected in your code (I haven't
got Access 95 menus handy to compare with) would be:

RunCommand acCmdSelectRecord
RunCommand acCmdDeleteRecord

But making this recommended change probably won't fix your current error,
because something else is causing it. Make sure that all bound controls on
the form and subforms are named differently than their sources. For example,
a text box bound to the ID field shouldn't be named ID (the default when
using the Form Wizard), but something like txtID to differentiate the control
from the form's ID Property. And make sure that all bound controls have a
valid source.

Also, if your code fails, then the warnings will not be turned back on, so
add code in your error handler to turn the warnings back on.

If you are using Access 2002 with SP-3 installed, then your code will never
work, because there's a bug preventing records from being deleted while using
the Access library methods when there's a bound form and subform. If this is
the case, then you'll have to use SQL to delete the records.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

 

[Guest]

Hi,

"RunCommand acCmdSelectRecord
RunCommand acCmdDeleteRecord" worked I didn't even have to change
anything else.

Thanks

 

['69 Camaro]

Excellent! It turned out to be easier than I thought it would be.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)