Solved Combobox NotInList event (1 Viewer)

[Romio_1968]

I have a combo that ads data to a junction table with two numerical fields (Author_IDFK, Title_IDFK)
This junction table makes the link between a Titles that contains an indexed field called Author_ID, and an Author table, that contains an indexed field called Title_ID. A many-to-many relationship is created unsing the table TitleAuthor_Junction.

The Combobox is located on a subform. It's control source is Author_IDFK

the row sorce is a query winth the following SQL code:

SELECT Author.Author_ID, Author.Author_Name
FROM Author
ORDER BY Author.Author_Name;

A not in list event is attached to the combo:


Private Sub Author_ID_NotInList(NewData As String, response As Integer)
response = acDataErrContinue
NewData = FormatName(NewData) 'Fix the name format Me.Author_ID.Value = NewData
If MsgBox(NewData & ", nu este in lista de autori. Doriti sa adaugati autorul in lista?", vbQuestion + vbYesNo, "Adauga Autor") = vbYes Then
response = acDataErrAdded
CurrentDb.Execute "insert into Author (author_name) select '" & NewData & "'"
End If
End Sub

So if a string (Doe, John) is not in the list, a message pops and asking the user if he wants to add the name in the Authors table
If the answer is Yes, the record is saved. But before saving the name in the table, the name is formated (changed to Proper Case, trimmmed). Trimming.
The good name stored in the NewData string is saved in the new record. All fine yp to now.

And Here comes the problem. It is not fatal, yet, annoying
Even if the correct record is saved into Authors Table the Combo is still poppulated with the initial value, so after the MsbBox that confirms that the new Name was added, it is triggered a default Not In List message (not even the custom one set in the code) and the user have the option to select from list the good name, that is naou avalable for pick.
Of course it is not really a big deal, since no bad records are added orany fatal crashes, yet, the behavior is annoying

If the initial string entered by user would be automaticaly be modified in the textbox, so the user do not need to select the new good name from the list, it will be a charm.

Thank you for the help

 

[The_Doc_Man]

Presumably, your NotInList event "sees" the name that is not in the list. Save that name in the local declaration area of the event code. While still in the NotInList event, execute the code that adds the new entry. But before you actually leave the NotInList event, do a .Requery on the combo. That should have the effect of reloading the combo with all prior data AND the new value you just added. I'm not sure whether it would reset the individual .Selected flags for each row in the .RowSource, but if you now do a combo.Undo, nothing should be selected. Still in the NotInList event, now step through the rows until you find the name that you just added (because you saved it earlier) and set the .Selected property of that row to TRUE. Set the new name in the .Value of the combo and you should be done.

ComboBox.Selected property (Access)

Office VBA reference topic

learn.microsoft.com

 

[Gasman]

TBH I thought the NIL did that automatically?
I seem to recall that is how I was using it when entering over 20k entries.

 

[Romio_1968]

Presumably, your NotInList event "sees" the name that is not in the list. Save that name in the local declaration area of the event code. While still in the NotInList event, execute the code that adds the new entry. But before you actually leave the NotInList event, do a .Requery on the combo. That should have the effect of reloading the combo with all prior data AND the new value you just added. I'm not sure whether it would reset the individual .Selected flags for each row in the .RowSource, but if you now do a combo.Undo, nothing should be selected. Still in the NotInList event, now step through the rows until you find the name that you just added (because you saved it earlier) and set the .Selected property of that row to TRUE. Set the new name in the .Value of the combo and you should be done.

ComboBox.Selected property (Access)

Office VBA reference topic

learn.microsoft.com

I tried the requery like this


Private Sub Author_ID_NotInList(NewData As String, response As Integer)

response = acDataErrContinue
NewData = FormatName(NewData) 'Fix the name format Me.Author_ID.Value = NewData
If MsgBox(NewData & ", nu este in lista de autori. Doriti sa adaugati autorul in lista?", vbQuestion + vbYesNo, "Adauga Autor") = vbYes Then
response = acDataErrAdded
CurrentDb.Execute "insert into Author (author_name) select '" & NewData & "'"
End If
Me.Author_ID.Requery 'Refresh the data in the combo
End Sub

It triggers a 2118 runtime error> You must save the current field before you run the Requery action

 

[Romio_1968]

Presumably, your NotInList event "sees" the name that is not in the list. Save that name in the local declaration area of the event code. While still in the NotInList event, execute the code that adds the new entry. But before you actually leave the NotInList event, do a .Requery on the combo. That should have the effect of reloading the combo with all prior data AND the new value you just added. I'm not sure whether it would reset the individual .Selected flags for each row in the .RowSource, but if you now do a combo.Undo, nothing should be selected. Still in the NotInList event, now step through the rows until you find the name that you just added (because you saved it earlier) and set the .Selected property of that row to TRUE. Set the new name in the .Value of the combo and you should be done.

ComboBox.Selected property (Access)

Office VBA reference topic

learn.microsoft.com

Despite the fact that is triggering an error, I think the Requery is not even needed, since the List is updated with the new added name.
The problem is that the combo still stores in it the initial value. That must be somehow updated

 

[mike60smart]

Hi

On the "ON Enter" property of the Combobox use:-

Me.ComboboxName.Requery