UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Edit Discussion
> Combobox NotInList Event    
Combobox NotInList Event

When you use a Combobox control, it's often linked to a table (or a query based on one or more tables), and you may want your user to be able to enter a new value, and have it available in the list of choices from that point forward. This is where the NotInList event for a combobox can be very useful.

Here's a simple example: In your Recipes database you have a table of ingredients, and it includs two fields; the IngredientID (the primary key) and a text field called strIngredientName. Once the user enters a new recipe ingredient into the combobox, you should prompt them to make sure they haven't committed a simple typo. Then, once they've confirmed that it's really a new ingredient, insert it into the table with a simple Append query:

CODE
Private Sub lngIngredientID_NotInList(NewData As String, Response As Integer)

   Dim strSQL As String
   
'   The user just entered a value that's not in the source table.
'   Prompt the user to verify that they wish to add the new value.
   If MsgBox("That is not in the list of ingredients. Add it?", _
        vbOKCancel, "New Ingredient?") = vbOK Then
'       Set the Response argument to indicate that data is being added.
'       (This will automatically re-query the combobox when done.)
       Response = acDataErrAdded
'       Add the user-entered string (NewData) to the appropriate table.
       strSQL = "INSERT INTO tblIngredients( strIngredientName ) " & _
                "VALUES (""" & NewData & """);"
       CurrentDb.Execute strSQL, dbFailOnError
   Else
   ' If user chooses Cancel, suppress the error message and undo changes.
       Response = acDataErrContinue
   End If

End Sub

Now, if you have a more complex data in your combobox, like say a a club member's name, you can alter this code to not only parse out the member's first name and last name, but you can also open a data entry form to enter the other details that aren't displayed in the combobox. To accomplish this, you can use DAO instead of an Append query.

In this example, your table of club members includes separate First and Last name fields, but you are concatenating the two names in the ComboBox's RowSource:

SELECT MemberID, [strFirstName] & " " & [strLastName] AS List FROM tblMembers ORDER BY strLastName, strFirstName;

The user can't enter two separate values in the ComboBox, but they can type "George Smith", and you can use code to parse out the first and last names into separate fields.

CODE
Private Sub lngClubPresidentID_NotInList(NewData As String, Response As Integer)

   Dim rst As DAO.Recordset, lngMembID As Long

   If MsgBox("Add " & NewData & " as new member?", vbYesNo, _
                        "New Member?") = vbYes Then
       Set rst = CurrentDb.OpenRecordset("tblClubMembers")
       rst.AddNew
       rst.Fields("strFirstName") = Trim(Left(NewData, InStr(1, NewData, " ") - 1))
       rst.Fields("strLastName") = Trim(Mid(NewData, InStr(1, NewData, " ") + 1))

'       Get the autonumber PK so we can open the Member form to this new record
       lngMembID = rst.Fields("MemberID")
       rst.Update
       rst.Close
       Set rst = Nothing
       
       Response = acDataErrAdded
       
'       Open the frmMembers form filtered to the new member so the user can add more detail.
'       The "AddNew" string sent through the OpenArgs will notify that form that it needs to
'       refresh this form's combobox when it closes.  Open it in Dialog mode so the user can't
'       accidentally close the original form until they're done editing this new member.
       DoCmd.OpenForm "frmClubMembers", , , "[MemberID]=" & lngMembID, , acDialog, "AddNew"
   Else
       Response = acDataErrContinue
       Me.lngClubPresidentID.Undo
   End If

End Sub

There is no need to create a special form for the adding of the Member details - you can just use the one you've already created for maintaining your member details. The DoCmd.OpenForm code above is meant to open your form for adding/editing members ("frmClubMembers"), filtering it to just the new record.

Since the Members form is being opened in Dialog mode, this subroutine pauses until that form is closed, at which point the combobox will be refreshed. The user should not change any values that directly affect the values displayed in the combobox (it will cause an error if the entered value does not match the value generated by this code), so the appropriate controls should be locked for this instance. That is why the "AddNew" argument is being passed as the OpenArgs argument to the form. Add the following code to the Open event for the frmClubMembers form to handle locking the appropriate fields:

CODE
Private Sub Form_Open(Cancel As Integer)

   If Me.OpenArgs = "AddNew" Then
'       Can't disable a control if it has focus, so set focus anywhere else.
       Me.Controls("strAddress").SetFocus
       Me.Controls("strFirstName").Enabled = False
       Me.Controls("strLastName").Enabled = False
   End If

End Sub

With this code, you can open the form normally to do business as usual. However, if the form detects that it's been opened by another form, it will lock the textboxes that are bound to the fields that display in the combobox.

These examples are certainly not the ONLY way to accomplish this sort of thing, but they should at least give you some guidance on how to be able to update your combobox's list of choices using the NotInList event.

Edit Discussion
Thank you for your support!
This page has been accessed 7,952 times.  This page was last modified 13:20, 8 July 2014 by doctor9.   Disclaimers