Using Not In List with Multiple Fields | Database Solutions for Microsoft Access

Microsoft Access Articles

GUI Design

Resources

Online Shop

Info

Microsoft Access Not In List Event

Using the Combo Box's Not In List event with multiple fields:

We usually provide database users with Combo Box controls in a Form to limit the data that the user may select from. However, if the data isn't already present in the underlying database table, then you must provide the user with an easy way to add new items to the list. We saw this approach in a previous tutorial on How to use the Microsoft Access Not In List event of a combo box

This previous tutorial is fine when adding a single field value to the underlying database table. What happens if the combo box contains multiple fields that need values adding to the table?

Consider the following example form, which contains a combo box displaying multiple values (ActorFirstName and ActorLastName):

Our form, containing combo boxes with multiple field values.

The combo box displayed contains details for Actors (First Names and Last Names). The values are contained in separate fields in the Actors table, but displayed in the combo box by concatenating the fields together. The table containing the data is structured as follows:

The Actors table in design view

The combo box is created using the Row Source of:

SELECT Actors.ActorID, [ActorFirstName] & " " & [ActorLastName]
AS Actors FROM Actors ORDER BY Actors.ActorLastName;

The SQL Query Builder

When we view the combo box on the form, we are only displaying the concatenated ActorFirstName and ActorLastName fields. If we want to add a new value to the Actors table, using the Combo Boxes NotInList event, we need to ensure that we pass a value to the ActorID field, the ActorFirstName field and the ActorLastName field. We need a value in the ActorID field in this case as we have used a numeric identifier, not an AutoNumber field.

So, what happens when we attempt to add a new value to the combo box?

If we look in the combo box list of Actors/Actresses, for the actor Bill Duke as we want to assign this actor to the movie Predator, we note that he has not been entered into our Actor table. So, we type in this value in the combo box. When the NotInList event fires, it will present us with a message box, informing us of the following:

The message box, providing information about the new actor entered into the combo box.

When we OK the message box, the record is added to the Actor table, and becomes available as a value in the combo box. All of this is possible by using the following code, attached to the NotInList event procedure of the combo box:

Private Sub Link_Movies_Actors_ActorID_NotInList(NewData As String, _
                                                 Response As Integer)

' This procedure allows us to create a new value, using the NotInList event
' It passes multiple field values to the table.

    Dim strSQL
    Dim NewActorFirstName As String
    Dim NewActorLastName As String
    Dim SpacePosition As Integer
    Dim lngNextID As Long

    ' Find the highest Actor ID in the Actors table and add 1
    lngNextID = DMax("[ActorID]", "Actors") + 1
    NewActorID = lngNextID

    ' Find the space in the Actor FirstName/Last Name
    ' to allow us to split the entry into two fields.
    SpacePosition = InStr(NewData, " ")
    If SpacePosition = 0 Then
        MsgBox "Your entry has no space separating First Name and Last Name." _
               & vbNewLine & vbNewLine & _
               "Please enter a First and Last Name or choose an entry from " _
               & "the list.", _
               vbInformation, "Invalid Data !"
        Response = acDataErrContinue
        Exit Sub
    End If

    ' Trim the data into first and last name using the space position.
    NewActorFirstName = Trim(Left(NewData, SpacePosition - 1))
    NewActorLastName = Trim(Mid(NewData, SpacePosition + 1))

    ' Check to see that the Actor First Name is not a
    ' zero-length string. Ensure value is entered before creating
    ' new Actor entry.  Place cursor at start of combo box using SelStart=0
    If NewActorFirstName = "" Then
        MsgBox "You have not entered details for the first name" _
               & vbNewLine & vbNewLine & _
               "Please fix entry.", vbInformation, "Invalid Data !"
        Response = acDataErrContinue
        Me.Link_Movies_Actors_ActorID.SetFocus
        Me.Link_Movies_Actors_ActorID.SelStart = 0
        Exit Sub
    End If

    ' Check to see that the Actor Last Name is not a
    ' zero-length string. Ensure value is entered before creating
    ' new Actor entry
    If NewActorLastName = "" Then
        MsgBox "You have not entered details for the last name" _
               & vbNewLine & vbNewLine & _
               "Please fix entry.", vbInformation, "Invalid Data !"
        Response = acDataErrContinue
        Exit Sub
    End If

    ' If new entry includes First and Last Names create new Actor record
    ' if the new entry does not exist.
    MsgBox "A record for this person does not exist....." _
           & vbNewLine & vbNewLine & _
           "Now creating new Record.", vbInformation, _
           "Unknown Actor Details....."
    strSQL = "Insert Into Actors ([ActorID], [ActorFirstName], " & _
    "[ActorLastName]) " & _
             "values ('" & NewActorID & "','" & NewActorFirstName & _
             "','" & NewActorLastName & "');"
    'MsgBox strsql
    CurrentDb.Execute strSQL, dbFailOnError
    Response = acDataErrAdded

End Sub

If we look at this code in detail, we will see that it performs various actions.

To begin with, we dimension our variables that will be used throughout the procedure:

    Dim strSQL
    Dim NewActorFirstName As String
    Dim NewActorLastName As String
    Dim SpacePosition As Integer
    Dim lngNextID As Long

The first part of the code is needed to find a new ActorID, as we need to find this due to using a numeric data type. We use the DMax function to find the highest value in the Actors table, and then add 1 to this value. We store this new value in the variable - lngNextID:

    ' Find the highest Actor ID in the Actors table and add 1
    lngNextID = DMax("[ActorID]", "Actors") + 1
    NewActorID = lngNextID

The next section of the NotInList code is used to find the space position in the NewData (which is the data entered into the Combo Box:

    ' Find the space in the Actor FirstName/Last Name
    ' to allow us to split the entry into two fields.
    SpacePosition = InStr(NewData, " ")
    If SpacePosition = 0 Then
        MsgBox "Your entry has no space separating First Name and Last Name." _
               & vbNewLine & vbNewLine & _
               "Please enter a First and Last Name or choose an entry from " _
               & "the list.", _
               vbInformation, "Invalid Data !"
        Response = acDataErrContinue
        Exit Sub
    End If

We need to find the space position, as this is where we will then split the data into the two values, for ActorFirstName and ActorLastName. We also do some error checking here, as we need to ensure that there is a space in between the two values entered. If we can't find a space in the new data, we need to fire off an error message. If we enter a value such as Fred, without a last name value, we receive the following error:

The error message shown when we only enter a First Name value

If the data is entered correctly, we can then trim the data into two values (ActorFirstName and ActorLastName):

    ' Trim the data into first and last name using the space position.
    NewActorFirstName = Trim(Left(NewData, SpacePosition - 1))
    NewActorLastName = Trim(Mid(NewData, SpacePosition + 1))

We do this by looking for the SpacePosition in the text string and using the Left and Mid functions to find the appropriate data values. We then assign first and last name to the variables of NewActorFirstName and NewActorLastName.

We also error check to make sure that the user has entered a First Name or Last Name, as these fields will not accept zero-length strings. Each error will bring up and error message, and instruct the user to enter a correct value:

    ' Check to see that the Actor First Name is not a
    ' zero-length string. Ensure value is entered before creating
    ' new Actor entry.  Place cursor at start of combo box using SelStart=0
    If NewActorFirstName = "" Then
        MsgBox "You have not entered details for the first name" _
                vbNewLine  vbNewLine  _
               "Please fix entry.", vbInformation, "Invalid Data !"
        Response = acDataErrContinue
        Me.Link_Movies_Actors_ActorID.SetFocus
        Me.Link_Movies_Actors_ActorID.SelStart = 0
        Exit Sub
    End If

If there is no value for the Actor First Name, we will see the following error:

Error message if a First Name is not entered in the combo box

    ' Check to see that the Actor Last Name is not a
    ' zero-length string. Ensure value is entered before creating
    ' new Actor entry
    If NewActorLastName = "" Then
        MsgBox "You have not entered details for the last name" _
               & vbNewLine & vbNewLine & _
               "Please fix entry.", vbInformation, "Invalid Data !"
        Response = acDataErrContinue
        Exit Sub
    End If

If there is no value for the Actor Last Name, we will see the following error:

Error message if a First Name is not entered in the combo box
Error message if a Last Name is not entered in the combo box

Once all of the error checking has been performed, we display the message informing the user that the data is a new entry, and that a new record is being created.