This routine is a Global Extension of the NotInList Event for a combobox. You can call it from any combobox on any form or subform without having to write the usual routine everytime.
The calling NoInList Event needs to supply three arguements to the routine, only two of which come from the user, and its still general enough to be included in a library.
The routine prompts the user with the usual msgbox. Since I always like things lookin good, I always use the old 97 style (1st line bold) display format. If this is not perferred, you'll have to recode the msgbox portion & variables. So, in a module in the module window, add the following to the declarations section:
Public Msg As String 'Your Message Public Style As Integer 'Example: vbCritical + vbOKOnly Public Title As String ' TitleBar text of MsgBox Window Public Const NL As String = vbNewLine 'Next Line Public Const DL As String = NL & NL ' Skip a line Public Const DQ As String = """"
Add the following MsgBox handler routine to the same module(if ya like you can use it for all msgboxes. Just assign proper to the Msg, Style, & Title variables. Then call the uMsg function. It works exactly the same as MsgBox and returns the same values):
Public Function uMsg() As Integer Beep uMsg = Eval("MsgBox(" & DQ & Msg & DQ & "," & Style & "," & DQ & Title & DQ & ")") End Function
Add the following AddToList routine to the same module (this is the Global Routine):
Public Function AddToList(curForm As Form, tblName As String, _ fldName As String) As Boolean Dim db As DAO.Database, rst As DAO.Recordset Dim frmNames As Collection, flg As Boolean, Cbx As ComboBox Dim frmMainName As String, curFrmName As String, CurCbxName As String Dim frmMain As Form, sfrm1 As Form, sfrm2 As Form, sfrm3 As Form Dim n As Integer, lvl As Integer, SQL As String
Set frmNames = New Collection
'Since were just adding one record, the SQL loads only one record 'in the recordset. This prevents a large number of records 'from loading and taxing resources. SQL = "SELECT TOP 1 * FROM " & tblName & ";"
'Acquire all form/subform names from Main Form to 'subform in the chain, that holds the calling combobox. Do If curFrmName = frmMainName Then frmNames.Add frmMainName flg = True Else frmNames.Add curForm.Name Set curForm = curForm.Parent curFrmName = curForm.Name End If Loop Until flg
'Setup Object Reference to each form/subform. 'User can now reference any form in the chain. 'frmMain - the main form. 'sfrm1 - 1st subform level. 'sfrm2 - 2nd subform level. 'sfrm3 - 3rd subform level. 'Note: subforms only go as deep as user has desgined. 'Remaining sfrms will be empty. For n = frmNames.Count To 1 Step -1 lvl = n - frmNames.Count - 1
If lvl = -1 Then Set frmMain = Forms(frmNames.Item(n)) ElseIf lvl = -2 Then Set sfrm1 = frmMain(frmNames.Item(n)).Form ElseIf lvl = -3 Then Set sfrm2 = sfrm1(frmNames.Item(n)).Form Else Set sfrm3 = sfrm2(frmNames.Item(n)).Form End If Next
'Setup Object Reference to the Combobox. 'User can reference the Combobox for other data. If frmNames.Count = 1 Then Set Cbx = frmMain(CurCbxName) ElseIf frmNames.Count = 2 Then Set Cbx = sfrm1(CurCbxName) ElseIf frmNames.Count = 3 Then Set Cbx = sfrm2(CurCbxName) Else Set Cbx = sfrm3(CurCbxName) End If
'The Global NotInList Event Msg = "'" & Cbx.Text & "' is not in the ComboBox List!" & _ "@Click 'Yes' to add it." & _ "@Click 'No' to abort." Style = vbInformation + vbYesNo Title = "Not In List Warning!"
If uMsg() = vbYes Then Set db = CurrentDb() Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
rst.AddNew if isnumeric(rst(fldName)) then rst(fldName) = Val(Cbx.Text) Else rst(fldName) = Cbx.Text End If rst.Update
AddToList = True End If
Now . . . . in the NotInList event of each combobox, add the following code. You have to prescribe names in purple:
'YourTableName? - table you wish to add the new data to. 'YourFieldName? - the field in the table that will receive the data. If AddToList(Me, "TableName", "FieldName") Then Response = acDataErrAdded Else Response = acDataErrContinue Me!ComboBoxName.Undo 'Optional. Restores previous text. End If
Thats it! Give it a spin . . . . .
Thanks to FancyPrairie and others for the inspiration!