Refresh Recordset after NotInList update

  • Thread starter Thread starter PC
  • Start date Start date
P

PC

Hi all,

(I posted this a couple of days ago but I think I left out some point so
apologies for reporting)



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. I
have tried "requery" and a number of other option but I can't get it to work

Points to note: This is an unbound Combo which is used to search for records
and display results and also to add new record.

NotInList and AfterUpdate 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





Sub cmbTransRef_AfterUpdate()

On Error GoTo Err_cmbTransRef_AfterUpdate
' Find the record that matches the control.
RunCommand (acCmdRefresh)
Me.RecordsetClone.FindFirst "[TransactionID] = " & Me![cmbTransRef]
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.TransDate.SetFocus
Me.cmbTransRef = Null

Err_cmbTransRef_AfterUpdate:
Exit Sub
End Sub


Any advice appreciated
 
Hi Art,

Tried me.requery but the problem was it can't be run on the whole form from
within a combo box event.

Paul


Art said:
Hi PC
How about me.requery instead of me.repaint?

Hope This helps
-----Original Message-----
Hi all,

(I posted this a couple of days ago but I think I left out some point so
apologies for reporting)



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. I
have tried "requery" and a number of other option but I can't get it to work

Points to note: This is an unbound Combo which is used to search for records
and display results and also to add new record.

NotInList and AfterUpdate 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





Sub cmbTransRef_AfterUpdate()

On Error GoTo Err_cmbTransRef_AfterUpdate
' Find the record that matches the control.
RunCommand (acCmdRefresh)
Me.RecordsetClone.FindFirst "[TransactionID] = " & Me! [cmbTransRef]
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.TransDate.SetFocus
Me.cmbTransRef = Null

Err_cmbTransRef_AfterUpdate:
Exit Sub
End Sub


Any advice appreciated


.
Click to expand...
Click to expand...
 
Back
Top