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

[Jason Northampton]

Hello This is the first time I've used a discusion forum and up until now I have managed to use and or modify VB code from the various sites on the web, until now!

This is a simple problem and I am trying to update a contact record with FirstName and LastName contained on a table TblContacts via a Combo Box using the Limit to List function to add new contacts and perform a search to check if the contact is new or already exists on the database.

I have modified some code published at http://www.databasedev.co.uk/multi-field-not-in-list.html which illustrates the example using the Key field set as a Long Integer rather than Autonumber. I have modified the code as shown below, but the fields do not up date. I am sure it is a typo somewhere but as being new to VB, finding this very difficult to debug. Can anyone help or this there an easier way of doing this.

Table: TblContacts
ContactID - Autonumber
FirstName - Textfield
LastName - Textfield

Form: FmCVLoader
Combo Box - Combo239
SELECT QryContactName.ContactID, QryContactName.Name FROM QryContactName ORDER BY [Name];

Query: ContactName
ContactID
Name: [FirstName] & " " & [LastName]

 Private Sub Combo239_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 NewFirstName As String
    Dim NewLastName As String
    Dim SpacePosition As Integer
 
    ' 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.
    NewFirstName = Trim(Left(NewData, SpacePosition - 1))
    NewLastName = 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 NewFirstName = "" Then
        MsgBox "You have not entered details for the first name" _
               & vbNewLine & vbNewLine & _
               "Please fix entry.", vbInformation, "Invalid Data !"
            Response = acDataErrContinue
    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 NewLastName = "" 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 TblContact ([FirstName], " & _
    "[LastName]) " & _
             "values ('" & NewFirstName & _
             "','" & NewLastName & "');"
    'MsgBox strsql
    CurrentDb.Execute strSQL, dbFailOnError
    Response = acDataErrAdded
 
End Sub
 

Can anyone help Please!!!

[nico5038]

The code looks OK, but has some flaws.
1) What when two John Doe's have to be entered into the system ?
2) What to do with a name like "John III Delaware", etc.
3) How is the Unique ID of the new record created ?

I guess at the moment number 3 is causing you the trouble.
When inserting a new record into a table without autonumber, you'll need to supplu the ID yourself. For this you can use a formula like: DMax("keyfield","tblPerson") + 1

Getting the idea ?

Nic;o)