Requery combobox NotInList event

[Les Isaacs]

Hi All
I have a form frm_GlobalSettings with a combobox cmbDescription that finds a record based on the value selected. The row source type for cmbDescription is Table/query, and the row source is a select statement on the form's underlying table.

I want the user to be able to use cmbDescription to add a new record to the form's underlying table. I currently have

Private Sub cmbDescription_NotInList(NewData As String, Response As Integer)
          Dim strTmp As String
          'Get confirmation that this is not just a spelling error.
10        On Error GoTo cmbDescription_NotInList_Error
20        strTmp = "Add '" & NewData & "' as a new global setting?"
30        If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
40            DoCmd.RunSQL ("INSERT INTO [tbl_GlobalSettings] ( [Global_Description] ) " & _
                            "SELECT """ & NewData & """ AS Expr1;")
              'Notify Access about the new record, so it requeries the combo.
50            Response = acDataErrAdded
70            Forms!frm_GlobalSettings!cmbDescription.Requery
80            Forms("frm_GlobalSettings").Requery
90        Else
100           MsgBox ("OK - try again!")
110       End If
120       On Error GoTo 0
130       Exit Sub
cmbDescription_NotInList_Error:
140       Call WriteErrors(Erl, Err.number, Err.Description, "cmbDescription_NotInList", "VBA Document", "Form_frm_GlobalSettings")
End Sub

but when a new value is entered this errors on line 70 with
"runtime errror 2118 - you must save the current field before you run the requery action".

I've tried various ways around this but can't get it to work. If anyone knows what I'm doing wrong I'd be really grateful.
Thanks for any help.
Les

 

[Les Isaacs]

Hi AccessBlaster
Thanks for your reply - and apologies for the delay in my response (I've been out of the office).
Unfortunately me.dirty = false didn't work - the record didn't save!
If you have any further ideas I'd be really grateful!
Thanks again
Les

 

[bob fitz]

Les,

I'm not sure you need line #70.
What happens if you comment it out of the code.