Not in List Combo box working (1 Viewer)

Ones_Zeros · Jan 12, 2020

Hello
I’m having this similar issue and was wondering if you would help me?
I have all this data set as you have indicated but I need help with the coding.

The combobox must have it's RowSource set to either a table or a query (not value list or file)

The combobox must have the RowSource Type set to Table/Query.

You must set the Limit To List property to Yes (this is the one I forgot to check).

Here is what I have...
I have a form entry that allows users to add data to table called “work_tracker”
On this form is a combo box configured to pull data from table “Location” this allows a user to select data from this drop down list.

I don’t need to open a form, I just need the new data that’s not in the list to be added to table “Location” so the user can select it.

What would be the coding that I need to add get this new data added?
I have found some sample coding but I can’t get it to work.

I appreciate your help

Thanks

moke123 · Jan 12, 2020

Re: "NOT IN LIST" event not working on combo box

I use the code posted here ....http://www.utteraccess.com/forum/Combo-List-Event-t1986550.html Its easy to use and flexible.

Note that it will either enter the NewData directly or if you use the optional argument it will open a form.

moke123 · Jan 12, 2020

Cross Posted https://www.accessforums.net/showthread.php?t=79358

jdraw · Jan 12, 2020

You may find this Edit Not in List video helpful.

theDBguy · Jan 12, 2020

Hi. Welcome to AWF!

Ones_Zeros · Jan 12, 2020

Hello
I think I got it working.
I just wanted to update the tread to maybe help someone else.
I was able to take a sample code and make it work for me.

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

Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Location...")
If i = vbYes Then
strSQL = "Insert Into Location " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

I did have a question.
After I add the new data to the NotInList Combo list, the new data is not sorted on ascending order. The older data is sorted correctly, just not the new data. Is there a simple way to sort this new combo list data in acceding order?

thanks again for helping

Gasman · Jan 12, 2020

Try

Code:

Me.Location.Requery

Ones_Zeros · Jan 12, 2020

Hello
I added this

Me.Location.Requery

to the bottom of the code above, but no luck.

the new data is still not sorted in acceding order.
I do have the data in “Location” table sorted in acceding order.

any other ideas?

thanks again for your help

Gasman · Jan 12, 2020

Please post your new code within code tags

Ones_Zeros · Jan 12, 2020

Here is my code

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

Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Location...")
If i = vbYes Then
strSQL = "Insert Into Location " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue

Me.Location.Requery
End If
End Sub

I added it to the same code on the form to the OnNotInList Event Procedure

Thanks for looking over this and helping!

Gasman · Jan 12, 2020

I would have thought you would need it in the logic path of when you added the new data?, not in the other path.?

Or outside of the If logic completely, which is a little wasteful as it only needs to be requeried if you ever add new data. ?

HTH

moke123 · Jan 12, 2020

This doesnt look right

Code:

strSQL = "Insert Into Location values ('" & NewData & "');"

try

Code:

strSQL = "Insert Into tblLocation(Location) values ('" & NewData & "');"

substitute your table and field names in above

Not in List Combo box working (1 Viewer)

Ones_Zeros

New member

moke123

AWF VIP

moke123

AWF VIP

jdraw

Super Moderator

theDBguy

I’m here to help

Ones_Zeros

New member

Gasman

Enthusiastic Amateur

Ones_Zeros

New member

Gasman

Enthusiastic Amateur

Ones_Zeros

New member

Gasman

Enthusiastic Amateur

moke123

AWF VIP

Similar threads

Users who are viewing this thread