Refresh Recordset after NotInList update

[PC]

Hi all,

I'm use the "NotInList" event to allow users create new records using a
combo box (code below). This part is working fine. My problem is I want the
form to go to the new record after it has been created to allow users enter
the new data. As it stands the users has to close the form and reopen it for
the new Record to become available (note after the execution of this code
the new record appears in the list in the combo box but cannot be selected.

I imagine this is a simple problem but I can't seem to find the answer.

Code follows:

Private Sub cmbTransRef_NotInList(NewData As String, Response As Integer)

Response = acDataErrContinue
If MsgBox("Record " & NewData & " does not exist. Create new Record?",
vbYesNo) = vbYes Then
Dim db As Database
Dim rst As Recordset
Dim sqlTransRef As String

Set db = CurrentDb()
sqlTransRef = "SELECT [tblBankMovementsMain].[TransactionID],
[tblBankMovementsMain].[TransRef] FROM [tblBankMovementsMain]"
Set rst = db.OpenRecordset(sqlTransRef, dbOpenDynaset)
rst.AddNew
rst!TransRef = NewData
rst.Update

Response = acDataErrAdded
rst.Close
End If

Me.Repaint

End Sub


Any advice appreciated

 

[Albert D. Kallal]

You seem to left out the detail that this is combo box to "move to" or
search for a new record. We are also to assume that this combo box is
un-bound...right? (again, this info was/is needed).

To show any new records added on a form via code, you have to requery the
form (me.requery). You could try using a me.requery, but I don't think it
will work while the notinlist event has been fired.

(so, try placing a me.Requery in the notinlist code if a new record is
added..but as mentioned...you can't really requery a whole form while in the
middle of a combo box selection!

So, if the above does not work, then I would thus try setting some flag in
the notinlist event, and then in the combo after update event, I would try
the following:

in notinList, I would use a forms level flag, and a forms level var call
NewTrans

Set rst = db.OpenRecordset(sqlTransRef, dbOpenDynaset)
rst.AddNew
rst!TransRef = NewData
rst.Update

NewTrans = NewData
bolMyAdded = True

Then, in the after update

if bolMyAdded = True then
' seach/move to new reocrd.
me.Requery
me.cmbTransRef = NewTrans
end if

' your other remaing code here in the combo after update event that moves
to the correct record can follow...