By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,121 Members | 1,782 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,121 IT Pros & Developers. It's quick & easy.

No one has solved this: Runtime error 2118

P: n/a
I perused old posts for an answer to this for at least an hour, and
I've found a work-around, but no definitive answer.

Synopsis of the problem:

On NotInList or ctl.Requery commands where a record has been deleted
(or appended via SQL), Access returns Runtime error 2118 when you get
to the Requery command which reads:

'You must save the current field before you run the Requery action.'

Access still carries out all requested deletions or additions in the
underlying data, but will not display it in the absense of some kind of
Escape command such as:

SendKeys "{Esc}", True

With that work-around in place, everything comes off just fine, but
without it, the requery fails.

Curiously this only occurs some of the time. If the Delete Query is
attached to an image's MouseUp event, it works, if it is attached to a
label's Click event it doesn't.

*****Working Code****
Private Sub imgDelete_MouseUp(Button As Integer, Shift As Integer, X As
Single, Y As Single)

strDeleteSQL = Me!cbo.Value
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings False
strDeleteSQL = "DELETE Items.* " & _
"FROM Items " & _
"WHERE (((Items.ID)=" & glngID & _
") AND ((Items.Item)='" & strDeleteSQL & "'));"
DoCmd.RunSQL strDeleteSQL
DoCmd.SetWarnings True
Forms!frmOptions.sfrmPurchases.Requery

*****End Code*****

I once used the following code to carry out the same operation, but it
has since failed as well, and this SQL approach was a work-around for
that:

****Old Code****

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

****End Code*****

The second statement in that code fails and invokes Runtime error 3021,
'No current record'.

The escape work-around solves it as well, but that's not the real
problem I'm trying to solve.

Here's the code that results in Runtime error 2118:

****Failing Code****
Private Sub lnkBreakdown_Click()

....
DoCmd.SetWarnings False
strDeleteSQL = "DELETE Items.* " & _
"FROM Items " & _
"WHERE (((Items.ID)=" & glngID & _
") AND ((Items.Item)=""" & strID & """));"
DoCmd.RunSQL strDeleteSQL
For j = 0 To i 'i set earlier by Ubound for variables
strAppendSQL = "INSERT INTO Items " & _
"(ID, Item, Type, Composition, [No]) " & _
"VALUES (" & glngID & ", '" & strItem(j) & "', " & _
"'A', '" & strComp(j) & "', 1);"
DoCmd.RunSQL strAppendSQL
Next j
DoCmd.SetWarnings True
Forms!frmOptions.sfrmPurchases.Requery

*****End Code****

As mentioned 'SendKeys "{Esc}", True' makes it work, but otherwise it
does not.

Here's the question(s):

1. Why does it work in one instance and not another without the escape
clause?

2. Why doesn't the old Menu command work any longer? I know it is some
kind of EOF/BOF thing, but I'm not sure how to get around it without an
escape command.

Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.