Solved NotInList Problem (1 Viewer)

[zelarra821]

Hi guys. I want to add items to a combo box if it's not in it.

This is the code:

    Dim ctl As Control
    
    Set ctl = Me.Articulo
    If MsgBox("Value is not in list. Add it?", vbYesNo, NombreBD) = vbYes Then
        Response = acDataErrAdded
        CurrentDb.Execute "INSERT INTO TArticulos (Articulo) VALUES ('" & NewData & "')"
    Else
        Response = acDataErrContinue
        ctl.Undo
    End If

When I try it, adding some item that I know is not in the list, it tells me that it is not in the list. Now if I try a Me.CmbName.Requery it tells me that it has to save the record first. So I don't know what I can do to fix it. Could someone tell me how to fix it?

Thank you.

 

[Ranman256]

dont add the item to combo IN the combo
remove the item from list
make a popup INPUTBOX to ask for the new entryh, default it to the item
run the apd query
refresh combo

 

[zelarra821]

Could you do an example? I don't understand. I think that what you want to say is not to use not in list event. I don't know what means remove the item from combo, default it to item and run apd query. Thanks.

 

[MsAccessNL]

I would try to put the line “Response = acDataErrAdded” after the exeCute line. I don’t know if this is the solution, but i woutd try it out.

 

[Ranman256]

put a button beside the combo box with Add New Code txt
the event would be:

public sub btnAdd_click()
  dim vRet
vRet = inputbox("Enter new code to add","Add Code")
if vRet = "" then exit sub

sSql = "Insert into tTable (Code) values ('" & vRet & "'")
docmd.runSql sSql

cboBox.REquery   'to get newly added code to show
cboBox = vRet

end sub

 

[zelarra821]

Thanks to all. I'll do it as Ranman256 says.

 

[arnelgp]

you can do without any button to save new Item list.
just type anything on the textbox portion of the listbox.

in design view of the combo, Property->Data:

Limit to List = Yes

now, add code to the combo's NotInList Event:

Private Sub Articulo_NotInList(NewData As String, Response As Integer)
    Dim ctl As Control
   
    Set ctl = Me.Articulo
    If MsgBox("Value is not in list. Add it?", vbYesNo, NombreBD) = vbYes Then
        Response = acDataErrAdded
        CurrentDb.Execute "INSERT INTO TArticulos (Articulo) VALUES ('" & NewData & "')"
    Else
        Response = acDataErrContinue
        ctl.Undo
    End If
End Sub

there is no need to Requery your combobox.

 

[zelarra821]

Thanks, @arnelgp , I will probe it, because I remember I changed Limit to list to Yes but I'm not sure.

 

[arnelgp]

try this demo.

 

[zelarra821]

It doesn't work for me because I'm using FAYT, developed by @MajP

 

[arnelgp]

It doesn't work for me because I'm using FAYT

then try the code of ranman.

 

[arnelgp]

this is no FAYT but is similar but more compact in code
and imo, easier to follow.
Open exampleForm and press "New Project" to add new project.

the original code can be found here:
access-examples/ComboboxFilterControl.zip at main · josef-poetzl/access-examples · GitHub

 

[zelarra821]

Something similar happens to me with Ranman's code. When I update the combo, FAYT is lost. I have written to @MajP, to see if he can give me a solution. As an option, I like the NotInList event better.

 

[zelarra821]

Ok. Thanks

 

[Josef P.]

I made another small adjustment in the example so that you can create a new project at "NotInList".

Private Sub m_CbxFilterControl_EmptySearchRecordset(ByRef Cancel As Boolean)
   If MsgBox("No matching records found." & vbNewLine & "Insert new project?", vbYesNo + vbDefaultButton2) = vbYes Then
      InsertAndSelectNewProject
      Cancel = True
   Else
      Cancel = True ' True = show all recordsets, False = show no records
   End If
End Sub

Private Sub InsertAndSelectNewProject()
    DoCmd.OpenForm FormName:="NewProject", WindowMode:=acDialog, Datamode:=acFormAdd
    m_CbxFilterControl.DeactivateSearchMode
    With Me.cbxSelection
        .Requery
        .Value = TempVars("NewProjectID").Value
        TempVars("NewProjectID").Value = Null
    End With
End Sub

/edit: Source: https://github.com/AccessCodeLib/AccessCodeLib/blob/master/usability/ComboboxFilterControl.cls

 

[zelarra821]

Ohhh, great!!!! Thanks a lot.

 

[arnelgp]

I made another small adjustment in the example so that you can create a new project at "NotInList".

still need improvement.
on you New uploaded db.
try typing "Project 1" (without quote).
it is not in the list so, it will ask.
you press "Yes" and add the record.
it will show on your original combo.
but when you search for it (using *proj*), it will not show on the filtered list.

also, another challenge, on normal, NotInList, the "new" item that you type is available (as NewData).
therefore you can actually insert NewData to the newProject form (without retyping it again).

 

[Josef P.]

Fixed.

 

[Josef P.]

also, another challenge, on normal, NotInList, the "new" item that you type is available (as NewData).
therefore you can actually insert NewData to the newProject form (without retyping it again).

I thought of that too, but where do you write the new value? - One would have to know if the user typed the project number, the name or the company.

 

[arnelgp]

on your Class you can add another Public Property, that will save the .Text property of the combobox.
you save it before you "clear" the combo.

example, on variable declaration:

Public TextValue As String

Private Sub m_Combobox_NotInList(NewData As String, Response As Integer)
    Me.TextValue = m_ComboBox.Text
   CheckFilterValue m_ComboBox.Text
   Response = acDataErrContinue
End Sub

from ExampleForm you passed the TextValue as OpenArgs:

Private Sub InsertAndSelectNewProject()
    DoCmd.OpenForm FormName:="NewProject", WindowMode:=acDialog, OpenArgs:=m_CbxFilterControl.TextValue
   ...

and on NewProject, Load event, catch the string passed and assign it to the
projectNum. the form must not be DataEntry.