Names in a Combo Box

[PeterdeHoogh]

Hi guys,
I'm trying to develop a little database of my store's clients. I made a combo box in which new clients can be added to the list. However, I don't know how to link a person's first name to it's last name in the form I'm using.
I used a table called Clients as the Row Source which includes the fields:First Name and Last Name.
In the form I want the programme to automatically list the First Name that goes with the Last Name that I choose from the list. I know it should be simple but I need some help.

Thanks in advance.

[MitchR]

You could use a DLookup Function on After update:

Private Sub NameOfBox_AfterUpdate()
    Dim strLastName As String
 
    If Me!NameOfBox > "" Then
        strLastName= "='" & DLookup("Clients", "LastName", _
                                "FirstName='" & Me!FirstName & "'") & "'"
    Else
        strLastName = ""
    End If
    Me!NameOfBox.ControlSource = strLastName
End Sub
 

This is untested and will need to be changed.

[puppydogbuddy]

In the query grid of your row source, add the following alias field to your row source:
FullName:[LastName] & ', " & [FirstName]

In order to display the FullName in your combobox, you will have to drag and position it as the next column after the bound column. the bound column is the first column (hidden column with width set to 0).

[PeterdeHoogh]

Hi Mitch,
Thanks for your help, however I must tell you that I have no experience in code language. In stead of NameOfBox I consequently put the name of the Combo Box. That doesn´t seem to be right though. Could you tell me what to type instead of: Me!NameofBox and Me!NameofBox.ControlSource?

Thanks!

You could use a DLookup Function on After update:

Private Sub NameOfBox_AfterUpdate()
    Dim strLastName As String
 
    If Me!NameOfBox > "" Then
        strLastName= "='" & DLookup("Clients", "LastName", _
                                "FirstName='" & Me!FirstName & "'") & "'"
    Else
        strLastName = ""
    End If
    Me!NameOfBox.ControlSource = strLastName
End Sub
 

This is untested and will need to be changed.

[PeterdeHoogh]

In the query grid of your row source, add the following alias field to your row source:
FullName:[LastName] & ', " & [FirstName]

In order to display the FullName in your combobox, you will have to drag and position it as the next column after the bound column. the bound column is the first column (hidden column with width set to 0).

Hi puppydogbuddy!
Thank for your help. I managed to create the combo box with the FullName variable in it.
I also inserted an event for the option NotinList in order to add new names to the Combo Box. However, I cannot add the new name that I type in the Combo Box into the FullName field. I guess I have to somehow decompose the new name (FullName) into FirstName and LastName again and than make Access store them in the underlying table?

Do you know how to do that?

Thanks!!!

[puppydogbuddy]

Hi puppydogbuddy!
Thank for your help. I managed to create the combo box with the FullName variable in it.
I also inserted an event for the option NotinList in order to add new names to the Combo Box. However, I cannot add the new name that I type in the Combo Box into the FullName field. I guess I have to somehow decompose the new name (FullName) into FirstName and LastName again and than make Access store them in the underlying table?

Do you know how to do that?

Thanks!!!

Hi Peter,
Yes, I do...the process is called parseing. I am going to give you a link to a powerful parseing function that you would place in a standard module and call it from your form code (e.g. the not in list code sub).

However, there might a better way to handle this. Try the following:
1. drag full name column all the way to the right so that the column order from left to right is Key, firstName, lastname, fullName. Set the widths of the colums as 0,.05", .07", 3.3"...You may have to play around with the widths of firstname,lastName.....but the idea is to make widths small enough that you can't see the 2 columns, but > 0 so that you will be typing into them by virtue of column order.

If you want to parse, here is your link:

http://www.tek-tips.com/faqs.cfm?fid=37

[PeterdeHoogh]

Hi Peter,
Yes, I do...the process is called parseing. I am going to give you a link to a powerful parseing function that you would place in a standard module and call it from your form code (e.g. the not in list code sub).

However, there might a better way to handle this. Try the following:
1. drag full name column all the way to the right so that the column order from left to right is Key, firstName, lastname, fullName. Set the widths of the colums as 0,.05", .07", 3.3"...You may have to play around with the widths of firstname,lastName.....but the idea is to make widths small enough that you can't see the 2 columns, but > 0 so that you will be typing into them by virtue of column order.

If you want to parse, here is your link:

http://www.tek-tips.com/faqs.cfm?fid=37

Hi, thanks for your answer. I'm having some trouble though to insert the names in the table. Let me tell you what I've got so far, so you can see where lies the problem.

So far I created a Combo box based on a query with the following fields:

 qryClients: Key, FirstName, LastName and FullName 
Bound column: 1
Column width: 0 cm; 0.050 cm; 0.050 cm; 5 cm
 

In the NotinList option I triggered the following event:

 
Private Sub Combo38_NotInList(NewData As String, Response As Integer)
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")
If intAnswer = vbYes Then
strSQL = "INSERT INTO qryClients([Fullname])" & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The name has been added to the list." _
, vbInformation, "Express"
Response = acDataErrAdded
Else
MsgBox "Please select a name on the list." _
, vbInformation, "Express"
Response = acDataErrContinue
End If
End Sub
 

When I introduce a name that is not on the list I get as response that ACCESS cannot insert anything into the field FullName.

Using the instructions you gave me, how would the NotinList code have to be changed?

Right now, in the combo the columns FirstName and LastName are hidden. When I pull down the list, the FullName appears and when I select a name on that list, just the FirstName is introduced in the field (because of Column Bound =1).

I hope I succeded in explaining my problem. I just got started with Access and I'm trying to apply some of what I read on the internet, but now and then I really nead a hand from an expert. Thanks in advance!!!

[puppydogbuddy]

Hi Peter,

Hopefully, your Not in List code won’t have to change if the following works:

Avoid making FullName the bound column- the bound column can not be Edited as you found out; try making the primary key of your table the bound column and try to set the width and column order as follows:

Try this first:

No of columns 4
Col widths: 0; 3.0 ; .10 ; .10

Col order: keyField; fullName;Last; First

The idea behind this is to see if fullName can be edited if last and first are made small enough so that you can’t see it, but big enough to see if leaving it active enables full name to be edited.

If that doesn’t work, try it this way:

No of columns 4
Col widths: 0; 1.5; 1.5; .02

Col order: keyField; Last;First;fullName

The idea behind the second setup is to enter last and first, making fullname small enough that you can’t see it, but leaving it available for use in not in list.

[PeterdeHoogh]

Thanks for your answer, however the trick with the columns didn't work.

Now I wanted to try the parse code you send me but it gives me an error message. Do you know what I should fill in the first row of the code between the brackets?

I think it says ParseName()
Thanks!

PS. Just a small other question. I introduced a calculation in the form in which
c=a-b, being a,b,and c fields in the form. The calculation works well only that there is a problem when b=0. I asigned it a standard value of 0, but the calculation only works when I manually introduce the value 0. Any suggestions?

Thanks, I really appreciate your help.

[puppydogbuddy]

Thanks for your answer, however the trick with the columns didn't work.

Now I wanted to try the parse code you send me but it gives me an error message. Do you know what I should fill in the first row of the code between the brackets?

I think it says ParseName()
Thanks!

PS. Just a small other question. I introduced a calculation in the form in which
c=a-b, being a,b,and c fields in the form. The calculation works well only that there is a problem when b=0. I asigned it a standard value of 0, but the calculation only works when I manually introduce the value 0. Any suggestions?

Thanks, I really appreciate your help.

Hi Peter,

Sorry the tricks did not work.

In answer to your first question about ParseName(), nothing goes in the brackets because they are empty brackets, but whenever you call the function, you call it without the brackets like this:

Call ParseName (See Details for the Parseing Function below)

In reference to your second question, if I understood you correctly that a,b and c are textbox controls on your form, your assignment statement should be as follows:

c.value = a.value – b.value

and if you want b to default to 0, you can set the default value property for that control to 0


----------------Details for the Parsing Function---------------

The instructions said to just copy the whole thing into a new module, replace the bolded area's with your actual table name and the actual field name of the field to be parsed------>FullName, respectively.

Change this:

Set db = CurrentDb()
Set rs = db.OpenRecordset("TableNameHere", dbOpenDynaset) 'open appropriate table
Set fldName = rs![NameFieldHere] 'single name field

To This:

Set db = CurrentDb()
Set rs = db.OpenRecordset("YourTable Name", dbOpenDynaset) 'open appropriate table
Set fldName = rs![FullName] 'single name field that is to be parsed
 

[PeterdeHoogh]

Hi puppydogbuddy!

I solved the problem with the calculation. I had used an AFTER UPDATE event and it should be an ENTER event.

About changing the tablename and the fieldname, that I had understood. In the module view however, when I introduce the tablename I get the message: "An expression was expected here".

Just for the record: I go to module view, paste the whole function in the window, paste the code in the immediate window and that's it? Or must I go to the NotinList option and paste it there?

I'm sorry I'm so stubborn about this. It's just that I've got everything ready to run the program in our store and this is the last bottle neck. Thanks again for your patience!!!

[puppydogbuddy]

Hi puppydogbuddy!

I solved the problem with the calculation. I had used an AFTER UPDATE event and it should be an ENTER event.

About changing the tablename and the fieldname, that I had understood. In the module view however, when I introduce the tablename I get the message: "An expression was expected here".

Just for the record: I go to module view, paste the whole function in the window, paste the code in the immediate window and that's it? Or must I go to the NotinList option and paste it there?

I'm sorry I'm so stubborn about this. It's just that I've got everything ready to run the program in our store and this is the last bottle neck. Thanks again for your patience!!!

Hi Peter,
No problem whatsoever. In looking over the parse code and your not in list code, it was difficult for me to figure it out, so no one could possibly expect you to figure it out on your own.
As usual there was one complication that required a work around. I did not realize that the word “quot” that you see in the ParseName code is an Oracle Server reserved word for the quotation mark delimiter, and that is why you were getting an error message. The work around….since it is not an Access reserved word, I declared it as a variable and assigned it the value of “. Hopefully that will work without any problems.
Regarding your question about pasting the call statement in the immediate window….that is just for testing/debugging so don’t worry about that. You do have to paste the whole function in a standard module and compile it along with your other code. Below, I provided you with specific instructions as to the changes to your “Not in List” code, changes to the ParseName function, and I provided you instructions on how to call the ParseName from the Not in List code. If it works the way it is supposed to, the ParseName code will parse NewData and update the First and Last Name fields in your table. If you have any problems with it, don’t hesitate to let me know….we’ll get it done.
In Your Code
---------------
Change this:

strSQL = "INSERT INTO qryClients([Fullname])" & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
 

To this:

DoCmd.SetWarnings False
Call ParseName ‘ use this syntax only if needed: "Call ParseName"
 

In the ParseName code
-------------------
Add the following line to the Declarations section:

Dim quot As String ‘ Delimiter---à “
quot = “ ‘ assign “ delimiter to string variable
 

Change this:

Set fldName = rs![FullName]
 

To this:

Set fldName = rs!(NewData) 
 

[PeterdeHoogh]

Thanks a lot for all the trouble! I'll try it out right away and let you know!

Greetings,
Peter

[PeterdeHoogh]

Hi,
I got an error message on this part:

[font=Tahoma]Dim quot As String ‘ Delimiter---[/font][font=Wingdings][font=Wingdings]à[/font][/font][font=Tahoma] “[/font]

[font=Tahoma] [/font]

[font=Tahoma]quot = “ ‘ assign “ delimiter to string variable[/font]

It may be because I get your message with all the font specifications included.
Should it be like this?

Dim quot As String ‘ Delimiter---à“
quot = “ ‘ assign “ delimiter to string variable

Thanks!

[puppydogbuddy]

Hi,
I got an error message on this part:

[font=Tahoma]Dim quot As String ‘ Delimiter---[/font][font=Wingdings][font=Wingdings]à[/font][/font][font=Tahoma] “[/font]

[font=Tahoma] [/font]

[font=Tahoma]quot = “ ‘ assign “ delimiter to string variable[/font]

It may be because I get your message with all the font specifications included.
Should it be like this?

Dim quot As String ‘ Delimiter---à“
quot = “ ‘ assign “ delimiter to string variable

Thanks!

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

Dim quot As String

quot = "

[PeterdeHoogh]

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]

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.


---------------------------------------------

 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.OpenRecordset("qryClients", dbOpenDynaset) 'open appropriate table
Set fldName = (rs!NewData) 'single name field
 
DoCmd.Hourglass True
Do Until rs.EOF
If IsNull(rs!FirstName) 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
 

[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

[puppydogbuddy]

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]

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]

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 <<<ErrDescription>>>> should have been Err.Description

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

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

[PeterdeHoogh]

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 <<<ErrDescription>>>> 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]

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:

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]

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]

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]

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