Requery Combo Box

[judyscofield]

I've put code in the 'On Not In List' event of a combo box that adds the entry to a codes table that the combo is based on. That works ok, but when I try to requery the combo box, I get the error 2118 - 'You must save the field before performing a requery.' Then I get the message that my choice isn't in the list. I'm not sure what it's expecting me to do. Code is:

Dim dbsInventory As Object
Dim rsGWID As Object
Set dbsInventory = CurrentDb
Set rsGWID = dbsInventory.OpenRecordSet("GWID&quot

With rsGWID
.AddNew
!GWID = NewData
.Update
End With

Dim ctlList As Control

Set ctlList = Forms![User Maintenance]!GWID
' Requery source of data for list box.
ctlList.Requery

 

[TTThio]

Hi Judy,

how about instead of using the above procedure, you can probably try this one. It works.

First, set the LimitToList property of the cbobox to YES
Then in it's NotInTheList event procedure:

Dim db as database
dim sSQL as string

set db = currentdb
sSQL = "Insert Into Table(field) Values (" & "'" & NewData & "'" & &quot"

If msgbox("Do you want to add a new item?&quot = vbOK Then
db.execute sSQL
Response = acDataErrAdded
Else
Response = acDataErrContinue
End if

Good Luck!

Tin Tin

 

[LonnieJohnson]

Here is another way to look at it.

Private Sub MyCombo_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
If MsgBox("This is not on the list. Do you wish to add it?", _
vbQuestion + vbYesNo) = vbYes Then
DoCmd.SetWarnings (0)
DoCmd.RunSQL "INSERT INTO MyTBL ( MyField )SELECT '" & NewData & "' AS Expr1;"
End If
MyCombo.Undo
MyCombo.Requery
MyCombo.Dropdown
End Sub
ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV