INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Combo Boxes

GLOBAL Not In List Event Handler by TheAceMan1
Posted: 13 Jun 04 (Edited 7 Nov 04)

To All . . . . .

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:

CODE

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):

CODE

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):

CODE

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 & ";"
   
   frmMainName = Screen.ActiveForm.Name
   curFrmName = curForm.Name
   CurCbxName = Screen.ActiveControl.Name
   
   '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
   
End Function
Now . . . . in the NotInList event of each combobox, add the following code. You have to prescribe names in purple:

CODE

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

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close