Value selected in a combox remains for all records

[LDeBlois]

I must be doing something wrong. I used a combo box to enter a name (& at the same time check to see if it already exists). Now when I view my records, they all have a blank space for the last name, & when I select a last name, it makes all the records have that same last name. (I'm pretty green at this.) Can anyone tell what I'm doing wrong?
I just want a user to be able to add a person to the database, and know right away (before he gets all the other data on the form entered) whether that person already exists.

 

[Jack Cowley]

In the Before Update event of the LastName field use code something like this to see if that name already exists:

If Not IsNull(DLookup("[LName]", "TableName", "[LName] = '" & Me.LName & "'")) Then
MsgBox "That name already exists."
Me.Undo
Cancel = True
End If

If you have more than one Smith then you will need to do addition checking for first name, middle initial or whatever....

 

[David R]

It sounds like you're running a continuous form, and your combo box is unbound. The good news is, you probably haven't farked up your data for the entire table.
Move the combo box off of the continuous forms, possibly into the form header.

Now, you have a somewhat more thorny problem. How do you determine if a person is in the database already? Last name is pretty poor as a criteria: How many Johnsons do you know, personally? [LastName] & [FirstName] isn't that much better. Do you record a phone number, or national identification number (SSN for the U.S. is a pretty good one).

When you find a way to determine this, you can attack it a couple of different ways: If the person will usually be in your database already and you want to go to their record, then make your unbound combo box with hte wizard's third option, "Go to a record based on the value I select". There are code examples here in the archives of how to do this if you don't want to start over.
If the person will usually not be in the database but may occasionally be, you may be better off using the BeforeUpdate event of [Whatever the identifying field is].

Here's what I use for this:

Private Sub ParticipantID_BeforeUpdate(Cancel As Integer)
    If (Me.NewRecord = False) Then
        If (Me.ParticipantID = "" Or IsNull(Me.ParticipantID) = True) Then Me.Undo
    End If
    
    If DCount("[ParticipantID]", "tableParticipants", "[ParticipantID]= '" & Me![ParticipantID] & "'") = 1 Then
        MsgBox "This is a duplicate Participant ID."
        Me.Undo
        Cancel = True
    End If

End Sub

(Note that my PK is text so it has the single quote bits. You can leave those out if the field you're searching is numeric.)

Please post back if you need further help or if I've confused you hopelessly. Someone will see it and reply.

David R

[This message has been edited by David R (edited 04-05-2002).]