Not In List event to include edit form

[Wysy]

Hi,
Is it possible to include in the Not In List event coding to open and edit the form the the combobox is based on? I know about the List Items Edit Form, but i would like to use VBA coding for more control.
thanks

 

[bob fitz]

Hi,
Is it possible to include in the Not In List event coding to open and edit the form the the combobox is based on? I know about the List Items Edit Form, but i would like to use VBA coding for more control.
thanks

Yes. The record that needs to be added to the combo box can sometimes be added without the need to open a dedicated form. Depends on the data that needs to be added

 

[Wysy]

It is necessary to open a dedicated form. One solution is obvious:
List Item Edit Form is used to open dedicated form to edit records and at the same time NotInList event used with insert SQL code to append newData. This works fine. However i have tried to use only VBA code in NotInList event:
Private Sub Horse_NotInList(NewData As String, Response As Integer)
If MsgBox("", vbYesNo) = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL "insert into horseTB([horse]) values ('" & NewData & "');"
DoCmd.OpenForm "frmHorse", , , , , acDialog
If CurrentProject.AllForms("frmHorse").IsLoaded = False Then
Response = acDataErrAdded
End If
Else
Response = acDataErrContinue
End If
End Sub


This works fine too. However i wanted to open the dedicated form in datasheet view using
Docmd.OpenForm "frmHorse",acFormDS
and then it does not work. When click yes of the message box, form opens and at the same time message of "The item is not in list...etc". It seems like the different view modes makes a difference in code running. Can this be?

 

[bob fitz]

Have you tried opening a form that has a sumform in datasheet view

 

[moke123]

first, You should be turning warnings back to true.
I'm not sure i'm following what your doing. Why open a datasheet?
do you want the datasheet to show all the records? is it to add further info on the horse?

 

[Wysy]

No there is no subform.
Yes i would like to work with datasheet view and yes i need to add extra infos too.
It seems to me that what makes a difference in open the form in datasheet vs normal view is that datasheet mode delays the notinlist event because upon opening the "item not in the list..." message shows up, on the other hand that does not happen in normal view.

 

[bob fitz]

Have your datasheet form as a subform, then the main form can be opened as Dialog and the code will stop running until the main form is closed

 

[Wysy]

i don't think i can open a list item edit form as a subform.

 

[bob fitz]

i don't think i can open a list item edit form as a subform.

Have you actually tried though

 

[moke123]

list item edit form is different than the NIL event although they are similiar with the same end result. I still dont understand why you would use an unfiltered datasheet as part of the process. If there are 200 or even 20 horses why display them all in a datasheet to add just one? Your code does not supply a pointer to the new record. try the below code and see if it does what you want.

Private Sub Horse_NotInList(NewData As String, Response As Integer)
If MsgBox("", vbYesNo) = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL "insert into horseTB([horse]) values ('" & NewData & "');"
DoCmd.SetWarnings True
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

DoCmd.OpenForm "frmHorse", , , , , acDialog

End Sub

the code below is a generic NIL procedure you can use with any combobox.(put it in a stand alone module) I use it but dont know who the original author is.

'
' usage..... Response = AddNewToList(NewData, "LtblCounties", "txtCounty", "Counties")
'
Public Function AddNewToList(NewData As String, stTable As String, _
                             stFieldName As String, strPlural As String, _
                             Optional strNewForm As String) As Integer
    On Error GoTo err_proc
    'Adds a new record to a drop down box list
    'If form name passed, then open this form to the newly created record

    'Declare variables
    Dim rst As DAO.Recordset
    Dim IntNewID As Long
    Dim strPKField As String
    Dim strMessage As String

    ' Display message box asking if user wants to add the new item
    strMessage = "'" & NewData & "' is not in the current list. " & Chr(13) & Chr(13) & _
                 "Do you want to add it to the list of " & strPlural & "?" & Chr(13) & Chr(13) & _
                 "(Please check the entry before proceeding)."

    If MsgBox(strMessage, vbYesNo + vbQuestion + vbDefaultButton2, "Add New Data") = vbYes Then
        Set rst = CurrentDb.OpenRecordset(stTable, , dbAppendOnly)
        rst.AddNew
        rst(stFieldName) = NewData                'Add new data from combo box
        strPKField = rst(0).Name                  'Find name of Primary Key (ID) Field
        rst.Update
        rst.Move 0, rst.LastModified
        IntNewID = rst(strPKField)

        'if a form specified, then open the form with the primary key equal to the new record ID as the criteria
        If strNewForm <> "" Then DoCmd.OpenForm strNewForm, , , strPKField & "=" & IntNewID, , acDialog

        AddNewToList = acDataErrAdded                'Set response 'Data added'
    Else
        AddNewToList = acDataErrContinue             'Set response 'Data NOT added'
    End If

exit_proc:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Exit Function

err_proc:
    MsgBox "Error in Function: 'AddNewToList'" & Chr(13) & Err.Description, , "Function Error"
    Resume exit_proc

End Function
'Notes:
'1. The Primary Key field must be numeric (long integer) and must always be the first field in the table.
'2. The 'Limit to List' property of the combo box must be set to 'Yes'
'3. strNewForm is opened in edit mode as the new record is added first and the form then opened to that record. A consequence of this is that other fields in the table must have their 'Required' property set to 'No' or a (valid) default property value set in the table design.
'4. FieldNamePlural is there simply to make the message grammatically correct; in the AddNewToList code the message box string generated as: strMessage = "'" & NewData & "' is not in the current list. " & Chr(13) ..... would result in the warning message (e.g.):
'London' is not in the current list. "
'Do you want to add it to the list of Cities?
'(Please check the entry before proceeding).
'5. If an edit form is opened (strNewForm <> "") then the field that is displayed in the combo box should be in a locked control on the form, alternatively remove the acDialog argument and ensure the combo box is re-queried when the form is closed.
'6. The form 'strNewForm' should have it's 'Allow Additions' and 'Data Entry' properties set to 'No' to prevent users from adding additional entries to the entry requested by the Not In List event.
'Enjoy!
'Edit1: 18/02/2013: Added Note 4
'Edit2: 02/03/2014: Added Notes 5 & 6.

you would call this in your NIL event like this

Response = AddNewToList(NewData, "horseTB", "horse", "Horses","frmHorse")

this will add the value and open your form filtered to the one record.