'
' 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.
