By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,036 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

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

P: 1
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]

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Combo239_NotInList(NewData As String, Response As Integer)
  2.  
  3. ' This procedure allows us to create a new value, using the NotInList event
  4. ' It passes multiple field values to the table.
  5.  
  6.     Dim strSQL
  7.     Dim NewFirstName As String
  8.     Dim NewLastName As String
  9.     Dim SpacePosition As Integer
  10.  
  11.     ' Find the space in the Actor FirstName/Last Name
  12.     ' to allow us to split the entry into two fields.
  13.     SpacePosition = InStr(NewData, " ")
  14.     If SpacePosition = 0 Then
  15.         MsgBox "Your entry has no space separating First Name and Last Name." _
  16.                & vbNewLine & vbNewLine & _
  17.                "Please enter a First and Last Name or choose an entry from " _
  18.                & "the list.", _
  19.                vbInformation, "Invalid Data !"
  20.         Response = acDataErrContinue
  21.         Exit Sub
  22.     End If
  23.  
  24.     ' Trim the data into first and last name using the space position.
  25.     NewFirstName = Trim(Left(NewData, SpacePosition - 1))
  26.     NewLastName = Trim(Mid(NewData, SpacePosition + 1))
  27.  
  28.     ' Check to see that the Actor First Name is not a
  29.     ' zero-length string. Ensure value is entered before creating
  30.     ' new Actor entry.  Place cursor at start of combo box using SelStart=0
  31.     If NewFirstName = "" Then
  32.         MsgBox "You have not entered details for the first name" _
  33.                & vbNewLine & vbNewLine & _
  34.                "Please fix entry.", vbInformation, "Invalid Data !"
  35.             Response = acDataErrContinue
  36.     End If
  37.  
  38.     ' Check to see that the Actor Last Name is not a
  39.     ' zero-length string. Ensure value is entered before creating
  40.     ' new Actor entry
  41.     If NewLastName = "" Then
  42.         MsgBox "You have not entered details for the last name" _
  43.                & vbNewLine & vbNewLine & _
  44.                "Please fix entry.", vbInformation, "Invalid Data !"
  45.         Response = acDataErrContinue
  46.         Exit Sub
  47.     End If
  48.  
  49.     ' If new entry includes First and Last Names create new Actor record
  50.     ' if the new entry does not exist.
  51.     MsgBox "A record for this person does not exist....." _
  52.            & vbNewLine & vbNewLine & _
  53.            "Now creating new Record.", vbInformation, _
  54.            "Unknown Actor Details....."
  55.     strSQL = "Insert Into TblContact ([FirstName], " & _
  56.     "[LastName]) " & _
  57.              "values ('" & NewFirstName & _
  58.              "','" & NewLastName & "');"
  59.     'MsgBox strsql
  60.     CurrentDb.Execute strSQL, dbFailOnError
  61.     Response = acDataErrAdded
  62.  
  63. End Sub
  64.  

Can anyone help Please!!!
Oct 9 '07 #1
Share this Question
Share on Google+
1 Reply


nico5038
Expert 2.5K+
P: 3,072
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)
Oct 9 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.