VBA Not In List find Item in combobox and run afterupdate code

[sxschech]

Thought this would not be tricky, maybe overlooking something. (Hope I'm using the bound terminology properly)
I created a unbound combobox in the header of a form and code in the after update event adds new record using values from all the cols in the combobox. This works well for the bound field. I thought I would extend the capability without having to create another combobox or button on the form, for cases where the user knows the eventcode (another field in the combo) rather than the acro (bound field in the combo), I put code in not in list event which does a dlookup to find the acro based on the eventcode. I tried to get that value back into the combobox and rerun using that, didn't work --combobox remained blank. I was successfully able to pass the eventcode to the after update directly (commented out), which worked for that piece of data, but since the other fields are derived from the combobox and the combobox was not set to the acro, the other fields are null.

Private Sub cboAcro_NotInList(NewData As String, Response As Integer)
'If entering an event code rather than acro,
'try to search for that instead
'20170310
    If Not IsNull(DLookup("Acronym", "Courses_Data", "[EVENTCODE] = " & "'" & NewData & "'")) Then
        Me.cboAcro = DLookup("Acronym", "Courses_Data", "[EVENTCODE] = " & "'" & NewData & "'")
        Me.cboAcro.Requery
        'Call DisplayProduct(NewData)
    End If
    
End Sub

 

[Andrzejek]

Since you have an "unbound combobox", wouldn't it just be:

Private Sub cboAcro_NotInList(NewData As String, Response As Integer)[green]
'If entering an event code rather than acro,
'try to search for that instead
'20170310[/green]
    If Not IsNull(DLookup("Acronym", "Courses_Data", "[EVENTCODE] = " & "'" & NewData & "'")) Then
        Me.cboAcro[blue].AddItem[/blue] DLookup("Acronym", "Courses_Data", "[EVENTCODE] = " & "'" & NewData & "'")
[green]        'Me.cboAcro.Requery[/green]
        [green]'Call DisplayProduct(NewData)[/green]
    End If
    
End Sub

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[sxschech]

Hi Andy, I didn't consider using additem because when I read about it, I thought that adds an item to the list. I am not adding an item to the list as it already exists. Are you saying that the add item will find the existing item? I'm trying to use the combobox for double duty so it's only a not in list event from the standpoint that what I type in is not in the searchable part:

Sample data in the combo:
[tt]
ABC | 123 | How to find a value | 03/10/2017
ACD | 124 | How to lose a value | 03/10/2017
...
...
ZZT | 212 | Find the last value | 10/03/2017
[/tt]


For example, let's say I type in "A" Then "C", it will select the row containing ACD. Then the after update runs and takes the data from Column(1), Column(2), Column(3) and appends to data table.

Second example, let's say I know the code 212, but I don't know that its letter is ZZT. I type 212 this fires the not in list event, however, since 212 is already there I wouldn't be adding it, but need to get that to be the selected Item and then execute the after update code to pull the Column(x) data elements.

If not doable,I guess I'll try creating a recordset to gather the data in order to replicate what the after update code does while inside the notinlist event.

 

[sxschech]

After doing some more searching, seems that NotInList won't do what I'm after. Saw another site that suggested the Beforeupdate.

http://www.fontstuff.com/mailbag/qaccess03.htm

In order to use that, it said LimitToList must be set to No and when I did that, Access refused to change it. Even after I changed the bound col to a visible field per the message. So gave up on that for now.

One of the sites referenced this link as the solution for limittolist=no, but clicking on it came up blank

http://www.microsoft.com/communitie...45-a860-3892f04c57d8&cat=&lang=&cr=&sloc=&p=3