Not in list (1 Viewer)

[Mihail]

Hello !
Teach me please how to deal with the NotInList event for a combo.

The scenario is very simple:

Two tables:
One of this table (T1) contain a field (FK2) that is a look up to the second table (T2).
The field FK2 is required field in T1.
Both tables contain, of course, more other required fields (I refer especially to T1).

Two forms:
Form F1 based on T1 and form F2 based on T2.
In F1 will be a combo cmbFK2 in order to add/edit the FK2 field.

Now, the problem:
I need an entry Enew that is not in combo list (because not exist in T2)
So, the NotInList event will raise.
On the other hand, Access will not allow me (at this time) to leave F1 (in order to open F2) because the required fields (in F1) are not filled (yet) with data.

The question is obviously:
How to manage this situation ??

Thank you !

 

[JHB]

Hi Mihail.
In the Combo box NotInList event, can't you use the parameter NewData to add the value to the list?
You have also the "List items Edit Form" where you can put in the form name you want to use if an item is not in the list.

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

If you set the Response = Cancel, then the dialogbox ("The text you en....") is suppressed.
Maybe I've misunderstanding the problem, then post a small database with some sample data.

 

[Mihail]

Thank you for answer, JHB.
I'll try to design a database because I haven't one because I avoid this approach because I don't know how to manage it.

 

[Mihail]

This is a quick designed database.

 

[JHB]

Hi Mihail.
Here is the database back again with some code behind the NotInList event.

 

[Mihail]

Thank you, JHB for your effort but is not what I am looking for,
My mistake, and sorry for that, is that I fill the "OtherRequiredField" field in T2 only with the "x" values, so I give you wrong information.

Now, I refer only to T2 table:
Think that F2 are Names (Mihail, Jon etc) and OtherRequiredField contain birthdays for this guys.
So, when I add a name, I must fill the OtherRequiredField with the birthday for this nea guy.

If I have the same information in this field ("x", again sorry for this mistake) is no more necessary to store it in a table. I can store this in a Public Constant variable in VBA.

Thank you again, JHB, but I remain in standby.

 

[Mihail]

Ufff. Finally is working.
Thank you very much JHB. I use your update query idea for this.
Also, I understand why I can not done this in the past: because I never open frmT2 as acDialog :banghead:.

Thank you !!!!!

This is the code:

Private Sub ID_T2_NotInList(NewData As String, Response As Integer)
Dim TheResponse As VbMsgBoxResult
    TheResponse = MsgBox("Do you want to add the value " & NewData & " to the list?", vbYesNo)
  
    If TheResponse = vbYes Then
        DoCmd.OpenForm "frmT2", , , , acFormAdd, acDialog, NewData
        'Check if the NewData value has been added
        If DCount("F2", "T2", "F2= '" & NewData & "'")>0 Then 'YES, NewData has been added
            Response = acDataErrAdded
        Else 'NO, NewData has NOT been added
            Response = 0
        End If
    End If
End Sub