Refresh combo box on subform after insert (1 Viewer)

GoodyGoody

Registered User.
Local time
Today, 12:53
Joined
Aug 31, 2019
Messages
120
Hi, I have a subform where I am adding runners to a race. Sometimes the runner exists but sometimes the runner needs to be added first. I'm using the OnNotInList Event to fire up the AddRunner form, which works fine and adds the runner no problem. The trouble is that the combo box on the subform only refreshes when you move to a new line. Is there any way I can force the cobo box to refresh. I've used all the obvious events but the trouble is that the combo box still has the text in it the user entered to discover the runner didn't exist in the Runners table and that text needs to be removed before you can move out of the combo box. Below is my OnNotInList code as I thought well let me move the focus in and out of the combo box and that should refresh it but I get an error because as I said the combo box still has data in it which the combo box still thinks is not in the list. Any clever workarounds from you geniuses out there? As ever, all suggestions most welcome:

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

Dim intAnswer As Integer

On Error GoTo ErrorHandler

intAnswer = MsgBox("Add " & NewData & " to the list of runners?", _
vbQuestion + vbYesNo)

If intAnswer = vbYes Then
DoCmd.OpenForm "frmUpdateRunners", acNormal, , , acFormAdd, acDialog

Forms!raceevententryform.Form!raceeventrunnersform.Form.SetFocus

Forms("RaceEventEntryForm").[raceeventrunnersform]![RaceTime].SetFocus
Forms("RaceEventEntryForm").[raceeventrunnersform]![RaceRunner].SetFocus
Else
Response = acDataErrDisplay ' Require the user to select an option
End If

Exit Sub

ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:53
Joined
May 7, 2009
Messages
19,233
Code:
…
…
If intAnswer = vbYes Then
DoCmd.OpenForm "frmUpdateRunners", acNormal, , , acFormAdd, acDialog
[COLOR="RoyalBlue"]Response = acDataErrAdded[/COLOR]
Forms!raceevententryform.Form!raceeventrunnersform .Form.SetFocus

Forms("RaceEventEntryForm").[raceeventrunnersform]![RaceTime].SetFocus
Forms("RaceEventEntryForm").[raceeventrunnersform]![RaceRunner].SetFocus
Else
Response = acDataErrDisplay ' Require the user to select an option
End If
 

GoodyGoody

Registered User.
Local time
Today, 12:53
Joined
Aug 31, 2019
Messages
120
Easy when you know how! Thanks a million.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:53
Joined
May 7, 2009
Messages
19,233
you're welcome!
 

Users who are viewing this thread

Top Bottom