Names in a Combo Box

**PeterdeHoogh** · May 25 '07, 10:45 PM

Originally posted by puppydogbuddy

Yes....I hope it works. I am repeating it below without the comments:

Dim quot As String

quot = "

Hi, it's me again... still with some problems, sorry...
1. When I type quote = " the programme automatically adds another ", so I get quote = ""
2. As soon as I type the TableName in the required field the programme says: An expression was expected here.

After all the efforts you've done to help me I feel bad to bother you again, but maybe we could give it a last shot? Thanks a lot!

**puppydogbuddy** · May 26 '07, 04:53 AM

Peter,
Don’t worry about it. Several of our members are interested in the outcome of the parse function as used here, so I want to get it right.

I decided to compile the code myself. I took out quot and inserted " or "" as applicable.. The code comples on my end and should be good to go. Copy and paste over your previous code, then recompile and test. Let me know.

---------------------------------------------
[CODE=vbnet] Option Compare Database
Option Explicit

Public Function ParseName()
'This function breaks apart a single name field into seperate first, last & middle initial fields.
'This will work for a name entered in any of the following ways:
' {Smith, John} {Smith, John D} {Smith, John D.}
' {Smith,John} {Smith,John D} {Smith,John D.}
' {Smith John} {Smith John D} {Smith John D.}

'Start by adding 3 fields to the table where your single name field is (firstname, lastname, & MI)

On Error GoTo Parse_Err

Dim db As Database
Dim rs As Recordset
Dim fldName As Field
Dim x As Integer
Dim strLast As String, strFirst As String, strMI As String

Set db = CurrentDb()
Set rs = db.OpenRecordse t("qryClients ", dbOpenDynaset) 'open appropriate table
Set fldName = (rs!NewData) 'single name field

DoCmd.Hourglass True
Do Until rs.EOF
If IsNull(rs!First Name) Then
x = InStr(1, fldName, "", "")
If x = 0 Then
x = InStr(1, fldName, "")
strLast = Left(fldName, x - 1)
strFirst = Mid(fldName, x + 1)
If Right(strFirst, 1) = "." Then
strMI = Right(strFirst, 2)
strFirst = Left(strFirst, Len(strFirst) - 3)
Else
If Mid(strFirst, Len(strFirst) - 1, 1) = Chr(32) Then
strMI = Right(strFirst, 1)
strFirst = Left(strFirst, Len(strFirst) - 2)
Else
strMI = ""
strFirst = strFirst
End If
End If
Else
If Mid(fldName, x + 1, 1) = Chr(32) Then
strLast = Left(fldName, x - 1)
strFirst = Mid(fldName, x + 2)
If Right(strFirst, 1) = "." Then
strMI = Right(strFirst, 2)
strFirst = Left(strFirst, Len(strFirst) - 3)
Else
If Mid(strFirst, Len(strFirst) - 1, 1) = Chr(32) Then
strMI = Right(strFirst, 1)
strFirst = Left(strFirst, Len(strFirst) - 2)
Else
strMI = ""
strFirst = strFirst
End If
End If
Else
strLast = Left(fldName, x - 1)
strFirst = Mid(fldName, x + 1)
If Right(strFirst, 1) = "." Then
strMI = Right(strFirst, 2)
strFirst = Left(strFirst, Len(strFirst) - 3)
Else
If Mid(strFirst, Len(strFirst) - 1, 1) = Chr(32) Then
strMI = Right(strFirst, 1)
strFirst = Left(strFirst, Len(strFirst) - 2)
Else
strMI = ""
strFirst = strFirst
End If
End If
End If
End If

With rs
.Edit
!LastName = strLast
!FirstName = strFirst
!MI = strMI
.Update
.MoveNext
End With
Else
rs.MoveNext
End If
Loop
DoCmd.Hourglass False

rs.Close
db.Close

Parse_Exit:
Exit Function

Parse_Err:
DoCmd.Hourglass False
MsgBox Err.Number & ":" & ErrDescription
Resume Parse_Exit
End Function
[/CODE]

**PeterdeHoogh** · May 26 '07, 03:50 PM

Hi, when running the program I get the message:
Compilation error
The type hasn´t been defined by the user.
It points to the line: Dim db As Database

**puppydogbuddy** · May 26 '07, 04:24 PM

Originally posted by PeterdeHoogh

Hi, when running the program I get the message:
Compilation error
The type hasn´t been defined by the user.
It points to the line: Dim db As Database

Hi Peter,
My compiler did not catch that!
Change this:
Dim db As Database
Dim rs As Recordset

To This:
Dim db As DAO.Database
Dim rs As DAO.Recordset

Make sure you have a reference set to DAO in the VB reference Library.

**PeterdeHoogh** · May 26 '07, 05:35 PM

Originally posted by puppydogbuddy

Hi Peter,
My compiler did not catch that!
Change this:
Dim db As Database
Dim rs As Recordset

To This:
Dim db As DAO.Database
Dim rs As DAO.Recordset

Make sure you have a reference set to DAO in the VB reference Library.

AARGHH... I get another message:
Compilation error.
Variable has not been defined.

It points to the line on the end:
MsgBox Err.Number & ":" & ErrDescription

Would it be possible for you to send me your testfile so I can compare it to mine?

**puppydogbuddy** · May 26 '07, 06:13 PM

Originally posted by PeterdeHoogh

AARGHH... I get another message:
Compilation error.
Variable has not been defined.

It points to the line on the end:
MsgBox Err.Number & ":" & ErrDescription

Would it be possible for you to send me your testfile so I can compare it to mine?

Peter,
The code I sent you was verbatim from the file I compiled. What is happening is that you have a later version of Access then I do (I have Access 2000) and the compiler on your version is a little more sophicated. Give it a chance....we are almost there.........; ;;;; that compile error was due to <<<ErrDescripti on>>>> should have been Err.Description

change this:
MsgBox Err.Number & ":" & ErrDescription

To this:
MsgBox Err.Number & ":" & Err.Description

**PeterdeHoogh** · May 26 '07, 09:42 PM

Originally posted by puppydogbuddy

Peter,
The code I sent you was verbatim from the file I compiled. What is happening is that you have a later version of Access then I do (I have Access 2000) and the compiler on your version is a little more sophicated. Give it a chance....we are almost there.........; ;;;; that compile error was due to <<<ErrDescripti on>>>> should have been Err.Description

change this:
MsgBox Err.Number & ":" & ErrDescription

To this:
MsgBox Err.Number & ":" & Err.Description

Hello Again!
I changed the line. When I try to introduce a name not in the combo list I get:

3265: Didn't find the element in this collection

After that I get the message: "The name has been added to the list", but it really wasn't added neither parsed.

Any clues? Thanks!

**puppydogbuddy** · May 29 '07, 12:31 AM

Originally posted by PeterdeHoogh

Hello Again!
I changed the line. When I try to introduce a name not in the combo list I get:

3265: Didn't find the element in this collection

After that I get the message: "The name has been added to the list", but it really wasn't added neither parsed.

Any clues? Thanks!

Hi Peter,

The revised “NotInList” event code is shown below and works on my end. I simplified the code by including a more efficient parse code built into the “NotInList” event code. You no longer have to call the ParseName function. In conjunction with the elimination of the separate ParseFunction, the following changes were also made.
1. the combobox cboFullName was bound to the FullName control in the query/table and the concatenation expression combining first and last was eliminated because the notInList event code inserts fullname, last and first to the table.
2. the parse code was designed to parse two formats:
FirstName LastName
LastName, FirstName
3. You need to integrate to your application and let me know how it goes.

The code:

Code:

Option Compare Database
Option Explicit

Private Sub cboFullName_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
Dim strSQL As String
Dim strFirstName As String, strLastName As String, strFullName As String    'for capturing the parsed components ofFullName

'the fullname entered is not the table list; prompt user to ok adding it to list
intAnswer = MsgBox("The name" & Chr(34) & NewData & _
Chr(34) & " isn´t on the list." & vbCrLf & _
"Do you want to add it now?" _
, vbQuestion + vbYesNo, "Express")

' Background process for Parseing fullname entry into first and last name
strFullName = Trim(CStr(NewData)) ' Change Variant to String
If InStr(1, strFullName, ",") = 0 Then     'fullname entered----> First Last
       strFirstName = Left(strFullName, InStr(strFullName, " ") - 1)
       strLastName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, " "))
ElseIf InStr(1, strFullName, ",") > 0 Then  'fullname entered----> Last, First
        strLastName = Left(strFullName, InStr(strFullName, ",") - 1)
        strFirstName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, ",") - 1)
Else
        MsgBox "You entered the name without a separator between First and Last Name."
        Exit Sub
End If


'insert parsed components captured in strFullName, strFirstName, and strLastName to the table
If intAnswer = vbYes Then
    strSQL = "INSERT INTO qMemberList(Fullname, FirstName, LastName)" & _
    "VALUES ('" & strFullName & "', '" & strFirstName & "', '" & strLastName & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
        
    MsgBox "The name has been added to the list." _
        , vbInformation, "Express"
    'acDataAdded causes the combobox to get requeried, the new item is selected, and the focus moves
    Response = acDataErrAdded
Else
    MsgBox "Please select a name on the list." _
    , vbInformation, "Express"
    Response = acDataErrContinue
End If
End Sub

**PeterdeHoogh** · May 29 '07, 01:16 AM

Wow!!! You put a lot of effort in it, awesome!! I'll try it out right away and inform you on how it went. Thanks!

**PeterdeHoogh** · May 29 '07, 03:38 PM

IT WORKS!!!!! Great!
I especially like the fact that I can introduce middle names as well. I work a lot with Latin people who often use many names such as, José María de Jesús González. The programme effectively separates the last name from the first names via the comma.

It's hard to find people who really take time to help others as you did. Thanks a lot puppydogbuddy! I wish you the best!!!

Greetings,
Peter

**puppydogbuddy** · May 29 '07, 03:53 PM

Peter,
Glad I could help. Thanks for the feedback.
PDB

Names in a Combo Box

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment