Look up names and Not In List

[DoctorJDM]

This is a pretty basic question about looking up people's names then pulling in extra data about them, starting from the position that I want names stored as separate FirstName and LastName.

In the Northwind Orders form there are two lookups, both using the technique of a combo box with an ID source but showing a lookup to a text value.

Bill To : this is okay as it has a NotInList procedure

SalesPerson : this displays LastName, FirstName but doesn't have a NotInList procedure

The latter is my situation, ie how to make this allow selection of names from the list but respond in a sensible way when a new name has to be entered. For names in the list I want to automatically add things like address details, which I know how to do.

My standard NotInList procedures take what's entered as a new value in the combo box and transfers it into the form that allows creation of the lookup list. This process is smooth. With the Northwind method it gets messy because the displayed entry is a compound of the two name fields.

I see a lot written about the evils of this type of lookup so what's the better way of doing it?

 

[MajP]

I see a lot written about the evils of this type of lookup so what's the better way of doing it?

There is no problem if you do this on the form and not in the table. You never want the lookup control in the table. Most likely your rowsource for you combo box is from a table and returns three fields, something like

nameID
firstName
lastName

The combo is bound by the first column but the column widths are set something like
0";1",1"

Show you see the first and last name but the control is bound by the id. So what you really need to save is the ID not what is typed in the combo box.

There are a couple ways to do it. This is my general strategy:

I like to use a global variable to do this because it allows me to reuse form in many places, so in a standard module I would add a variable like

public glblNameID as long

Now in my not in list event:
1)Tell them it is not in the list and ask if they want to add
2) Now open a form to add personnel data because when you add a new person there are probably a lot of data that is required. I open this form in dialog
3)When you close the form save the ID to a global variable
4)On your main form clear out the combo, requery it, then assign the value to the globa variable

something like this on the main form:

Private Sub cmbName_NotInList(NewData As String, Response As Integer)
 Response = acDataErrContinue

  If MsgBox(NewData & " is not in a choice. Would you like to add it?", vbYesNo, "Add Choice?") = 6 Then
     DoCmd.OpenForm "frmName", , , , acFormAdd, acDialog
  End If
  'Since the popup form is acdialog the code stops   
  'executing at this point until the popup closes.
  cmbName.Undo
  cmbName.Requery
  cmbName = glblNameID
End Sub

and something like this on the popup form

Private Sub Form_Close()
  'some data verification code here
  glblNameID = Me.nameID
End Sub

 

[TheAceMan1]

How are ya DoctorJDM . . .

[blue] . . . With the Northwind method it gets messy because the displayed entry is a [purple]compound[/purple] of the two name fields.[/blue]

You need to breakout the compound elements!

In the [blue]NotInList[/blue] event you need to include:

[blue]   Dim db As DAO.Database, SQL As String, Ary
   
   Set db = CurrentDb
   Ary = Split(NewData, ",")
   
   SQL = "INSERT INTO [purple][b][i]TableName[/i][/b][/purple] (LastName, FirstName) " & _
         "VALUES ('" & Ary(0) & "', '" & Ary(1) & "');"
   db.Execute SQL, dbFailOnError
   Response = acDataErrAdded

   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886