Requerying Data in Combo box after adding a new record (1 Viewer)

GoodyGoody · Aug 31, 2019

HI,

I have a subform where users select a runner and post a time against the runner. If the runner is not in the Runner drop down I allow the user to add a new runner and return to the subform entry. I want the list now to contain the new record added but whatever I do the drop down list is only refreshed when the user goes to the next new line. I've tried all the events but none seem to requery the Runner box. Is this possible to do? BTW, I have put a Requery on the Macro that runs the AddNewRunner form before it returns to the subform I just left to add the new record. Thanks in advance. Stephen

CJ_London · Aug 31, 2019

what have you actually tried? Usual method is to just requery the combo control.

Gasman · Aug 31, 2019

I *thought* the NotInList event did all that for you?

https://docs.microsoft.com/en-us/office/vba/api/access.combobox.notinlist

arnelgp · Aug 31, 2019

add code on the Close event of AddNewRunner form:

Code:

Private Sub Form_Close()
[Forms]!MainFormName!subFormNameOfRunner.Form!runnerComboboxName.Requery
End Sub

GoodyGoody · Aug 31, 2019

Hi, Yes I'm requerying the combo box. I'm actioning the AddNew record by the OnNotInList event for the combo box. I successfully add the new record and in the same macro, requery the Combo box (I tried all the other Events such as OnDirty, OnChange etc but just got errors or the same result). But when I return to the same line on the subform (remember the cursor is still in the Combo box so hasn't lost focus I don't think as far as the form is concerned) despite deleting part of the name which previously didn't exist and caused the correct AddNew runner behaviour, it still doesn't requery the combo. I seem to only be able to get it to requery the Combo by exiting the combo completely and returning to it. Hmmm...

moke123 · Aug 31, 2019

What code are you using in the not in list event?
Are you setting the response to acDataErrAdded?

jdraw · Aug 31, 2019

You may get some info from this youtube video combo edit list by Richard Rost.

GoodyGoody · Aug 31, 2019

I'm using the Embedded Macro to first add the new runner and then requery the combo box on the subform before returning to it.

theDBguy · Aug 31, 2019

GoodyGoody said:
I'm using the Embedded Macro to first add the new runner and then requery the combo box on the subform before returning to it.

Can you post the code you’re using please? Thanks!

arnelgp · Aug 31, 2019

macro has limited use.
you need vba to put the new data in the combo.

Code:

Private Sub Area_NotInList(NewData As String, Response As Integer)
docmd.OpenForm FormName:="AddNewRunner",DataMode:=acFormAdd,WindowMode:=acDialog,OpenArgs:=NewData
Response = acDataErrAdded
End Sub

now form AddNewRunner must accept the parameter that is passed to it.
on AddNewRunner Load Event:

Code:

Private Sub Form_Load()
Me.txtRunner = Me.OpenArgs
End Sub

pbaldy · Aug 31, 2019

FYI I moved your thread out of the introductions forum. Welcome to AWF.

Requerying Data in Combo box after adding a new record (1 Viewer)

GoodyGoody

Registered User.

CJ_London

Super Moderator

Gasman

Enthusiastic Amateur

arnelgp

..forever waiting... waiting for jellybean!

GoodyGoody

Registered User.

moke123

AWF VIP

jdraw

Super Moderator

GoodyGoody

Registered User.

theDBguy

I’m here to help

arnelgp

..forever waiting... waiting for jellybean!

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread